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

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

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

Check it out!!!
DBA Tips Data Pump Reference


Post a Comment


dba topics. Copyright 2011-17 All Rights Reserved | Site Map | Contact | Disclaimer | Google