Oracle – Empty tables and recreate them

26 Sep

Find empty tables:

set echo off heading off feedback off lines 100 pages 0;
spool tmp.sql
select ‘select ”’ || table_name || ”’ from ‘ || table_name || ‘ having count(*) = 0;’ from user_tables;
spool off;
@tmp.sql

http://www.dbforums.com/oracle/979365-how-fine-empty-table-user.html

 

Generate ddl

set heading off;
set echo off;
Set pages 999;
set long 90000;
spool ddl_list.sql
select dbms_metadata.get_ddl(‘TABLE’,’DEPT’,’SCOTT’) from dual;
select dbms_metadata.get_ddl(‘INDEX’,’DEPT_IDX’,’SCOTT’) from dual;
spool off;

http://www.dba-oracle.com/oracle_tips_dbms_metadata.htm

https://forums.oracle.com/forums/thread.jspa?threadID=2379750

 

Advertisements

Leave a Reply

Fill in your details below or click an icon to log in:

WordPress.com Logo

You are commenting using your WordPress.com account. Log Out / Change )

Twitter picture

You are commenting using your Twitter account. Log Out / Change )

Facebook photo

You are commenting using your Facebook account. Log Out / Change )

Google+ photo

You are commenting using your Google+ account. Log Out / Change )

Connecting to %s

%d bloggers like this: