How to check connected user on psql

How to check connected user on psql :

\conninfo

example:

testsub=> \conninfo

You are connected to database "testsub" as user "sub" on host "localhost" (address "127.0.0.1") at port "5432".

Extracting DDL of Objects in PostgreSQL(get table ddl in PostgreSQL)

===================================================

2 Ways to get DDL of objects in PostgreSQL

1. The utility pg_dump is used to dump the DDL.

2. Can get the DDL just by running \d.

1. The utility pg_dump is used to dump the DDL.

Table ddl for testtable from TESTDB

$ pg_dump -d TESTDB -s -t testtable

Table ddl for testtable from TESTDB written in to file ddl.sql

$ pg_dump -d TESTDB -s -T testtable -f ddl.sql

Table ddls for all ables from from TESTDB

$ pg_dump -d TESTDB -s

2. Can get the DDL just by running \d.

TESTDB=# \d+ testtable

Oracle how to get an object Id from the rowid

=================================

select dbms_rowid.rowid_object('<RowID>') from dual;

select data_object_id, object_name, object_type from dba_objects where data_object_id in ('<OBJECT_ID>');

Oracle how to save output of the script in xls or html.

Oracle how to save output of the script in xls or html:

=========================================

SET MARKUP HTML ON

SPOOL <file_name>.xls

SET MARKUP HTML ON

SPOOL <file_name>.html

How to check compression enabled on partition table

=======================================

select

a.partition_name, a.tablespace_name, a.compression, a.compress_for , round(sum(a.num_rows / a.blocks),0) rows_per_block,

sum(a.num_rows) num_rows, sum(a.blocks) blocks , sum(b.bytes) /(1024*1024) mb

from dba_tab_partitions a, dba_segments b

where

a.table_name = 'TESTCOMP' and

a.partition_name = b.partition_name and

a.table_name = b.segment_name

group by a.partition_name, a.tablespace_name, a.compression, a.compress_for

order by 1;

Disable Constraints & Enable Constraints

Disable Constraints & Enable Constraints :

===============================

DISABLE:

select 'alter table '||owner||'.'||table_name||' disable constraint '||constraint_name||';' from user_constraints;

SQL> alter table TABLE_NAME disable constraint CONSTRAINT_NAME;

SQL> alter table TABLE_NAME disable constraint CONSTRAINT_NAME cascade;

ENABLE:

select 'alter table '||owner||'.'||table_name||' enable constraint '||constraint_name||';' from user_constraints;

SQL> alter table TABLE_NAME enable constraint CONSTRAINT_NAME;

How to enable compression on table.

Enable compression on table:

===================

Basic compression :

ALTER TABLE table_name move COMPRESS ;

Advanced compression :

alter table table_name move ROW STORE COMPRESS ADVANCED;

Please check other posts for compression on partition table.

Anuroop Challa