Anuroop Challa

  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: ========================================= SET MARKUP HTML ON SPOOL <file_name>.xls or 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: 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;