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;


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;


Leave a Reply

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

You are commenting using your 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: