Get DDL for a view or table

How to generate the DLL for a table or a view from sqlplus, useful if you’ve not got a GUI tool. So as the schema owner connect and run the following, in this example the table is called TEST_TABLE set long 1000 select DBMS_METADATA.GET_DDL(‘TABLE’,’TEST_TABLE’) from dual; output   view example set long 1000 select DBMS_METADATA.GET_DDL(‘VIEW’,’TEST_VIEW’)from dual; output

Shrink Database Objects

In Oracle10g and onwards, we have the option to shrink a segment that helps to manage space more efficiently.  Shrink operations can be performed only on segments in locally managed tablespaces with ASSM. The segment advisor will highlight any segments that can be shrunk and then you can do the following steps to shrink the object to […]

Finding table name for lob segment

So i have a lob segment taking up lots of space so i need to find out the corresposding table for the lob. SYSTEM@TEST> select count(*) from dba_lobs where SEGMENT_NAME=’SYS_LOB0000068405C00008$$’; COUNT(*) ———- 1 Just need to query dba_lobs with the corresponding lob segment name. SYSTEM@TEST> select OWNER,TABLE_NAME from dba_lobs where SEGMENT_NAME=’SYS_LOB0000068405C00008$$’; OWNER           […]