How to generate/spool table creation script for a schema using dbms_metadata.get

advertisements

_____________________________________________________________________________________________________________________

Step 1

$ vi gen_table_script.sql

set pages 0

set lines 90

set echo off feedback off verify off heading off

set termout off

spool cr_table_script.sql

select 'set long 50000' from dual;

select 'spool cr_table.sql' from dual;

select 'select dbms_metadata.get_ddl(''TABLE'''||','''||TABLE_NAME||''','''||OWNER||''') ||'';'' from dual;' from dba_tables where owner ='SCOTT' ;

select 'spool off' from dual;

spool off

@cr_table_script.sql

Step 2

Connect to sqlplus “sys as sysdba” and run gen_table_script.sql

SQL> @ gen_table_script.sql

Step 3

$!ls -ltr

-rw-r--r-- 1 oracle dba 400 Jun 14 10:22 gen_table.sql

-rw-r--r-- 1 oracle dba 728 Jun 14 10:22 cr_table_script.sql

-rw-r--r-- 1 oracle dba 9828 Jun 14 10:22 cr_table.sql

Now your script is ready in file cr_table.sql

Check it out!!!

_____________________________________________________________________________________________________________________

Archive