Bookmark and Share
Oracle DBA常用脚本(2) -- dump table DDL from the database
(Publish Date: 2007-3-29 4:09pm, Total Visits: 15332, Today: 3, This Week: 5, This Month: 13)

###a script to dump a table DDL creation script for a schema provided as the parameter###
下面这个script用来产生一个能生成某个schema的所有objects DDL的script.

$> more extract_objects.sql
-- create_ddl.sql
-- Use this to generate the DDL to create objects
set arraysize 1
set echo off
set heading off
set feedback off
set verify off
set pagesize 0
set linesize 200
set long 2000
set longchunksize 200
set heading off
spool object_ddl.sql

select 'select dbms_metadata.get_ddl(' || '''' ||
OBJECT_TYPE || '''' || ',' || '''' || OBJECT_NAME ||
'''' || ',' || '''' || OWNER || '''' || ') from dual;'
FROM dba_objects
Where Owner = '&schema' and OBJECT_TYPE != 'TABLE PARTITION';

spool off

在sqlplus中运行:

SQL> @extract_objects.sql;
Enter value for schema: CHERRY
select dbms_metadata.get_ddl('TABLE','CITY','CHERRY') from dual;
select dbms_metadata.get_ddl('INDEX','SYS_C0025132','CHERRY') from dual;
select dbms_metadata.get_ddl('TABLE','PHONE','CHERRY') from dual;
select dbms_metadata.get_ddl('INDEX','SYS_C0025133','CHERRY') from dual;

结果会输出到文件object_ddl.sql,编辑object_ddl.sql,加入以下行:

set arraysize 1
set echo off
set heading off
set feedback off
set verify off
set pagesize 0
set linesize 200
set long 2000
set longchunksize 200
set heading off
spool table_ddl.sql

在sqlplus中运行编辑后的object_ddl.sql,结果输出到table_ddl.sql,table_ddl.sql会有所有这个schema的table DDL和index DDL。


[Total Users: 6]

I want to comment on it

Display: 1 - 0 of 6, Total Pages: 0