Oralce数据库迁移-dbms_metadata.get_ddl 的使用方法总结

（1）dbms_metadata.get_ddl 的使用方法总结

 --输出信息采用缩排或换行格式化
    exec dbms_metadata.set_transform_param(dbms_metadata.session_transform, 'pretty', true);
    --确保每个语句都带分号
    exec dbms_metadata.set_transform_param(dbms_metadata.session_transform, 'sqlterminator', true);
    --关闭表索引、外键等关联（后面单独生成）
    exec dbms_metadata.set_transform_param(dbms_metadata.session_transform, 'constraints', false);
    exec dbms_metadata.set_transform_param(dbms_metadata.session_transform, 'ref_constraints', false);
    exec dbms_metadata.set_transform_param(dbms_metadata.session_transform, 'constraints_as_alter', false);
    --关闭存储、表空间属性
    exec dbms_metadata.set_transform_param(dbms_metadata.session_transform, 'storage', false);
    exec dbms_metadata.set_transform_param(dbms_metadata.session_transform, 'tablespace', false);
    --关闭创建表的PCTFREE、NOCOMPRESS等属性
    exec dbms_metadata.set_transform_param(dbms_metadata.session_transform, 'segment_attributes', false);

1 获取profile

SELECT DBMS_METADATA.GET_DDL('PROFILE','APP_USER') FROM DUAL;

1 获取db_link定义metadata信息如下：

SELECT dbms_metadata.get_ddl('DB_LINK',DB_LINK,OWNER) FROM DBA_DB_LINKS;

select dbms_metadata.get_ddl('TABLE','TABLE_NAME','TABLE_OWNER') from dual;

select dbms_metadata.get_ddl('INDEX','INDEX_NAME','INDEX_OWNER') from dual;

select dbms_metadata.get_ddl('VIEW','VIEW_NAME','VIEW_OWNER') from dual;

select dbms_metadata.get_ddl('PROCEDURE','PROCEDURE_NAME','PROCEDURE_OWNER') from dual;

select dbms_metadata.get_ddl('FUNCTION','FUNCTION_NAME','FUNCTION_OWNER') from dual;

set pagesize 0

set long 90000

set feedback off

set echo off

spool schema_ddl.sql

SELECTDBMS_METADATA.GET_DDL('TABLE',u.table_name,u.owner) FROM DBA_TABLES u;

SELECTDBMS_METADATA.GET_DDL('VIEW',u.view_name,u.owner) FROM DBA_VIEWS u;

SELECT DBMS_METADATA.GET_DDL('INDEX',u.index_name,u.owner)FROM DBA_INDEXES u;

selectdbms_metadata.get_ddl('PROCEDURE',u.object_name, u.owner,) from dba_objects uwhere u.object_type = 'PROCEDURE';

selectdbms_metadata.get_ddl('FUNCTION',u.object_name, u.owner,) from dba_objects uwhere u.object_type = 'FUNCTION';

spool off;

1.显示设置：

/*创建DBMS_METADATA： 
@?/rdbms/admin/catmeta.sql 
*/ 
SET SERVEROUTPUT ON 
SET LINESIZE 1000 
SET FEEDBACK OFF 
set long 999999 
SET PAGESIZE 1000 
/*若希望不显示storage参数： 
EXECDBMS_METADATA.SET_TRANSFORM_PARAM(DBMS_METADATA.SESSION_TRANSFORM,'STORAGE',false);

 

set line 200

set pagesize 0

set long 99999

set feedback off

set echo off

select dbms_metadata.get_ddl('TABLESPACE','TBS_NAME') from dual;

SELECT DBMS_METADATA.GET_DDL('TABLESPACE',TS.tablespace_name)

FROM DBA_TABLESPACES TS;

 

selectdbms_metadata.get_ddl('USER','EPAY_USER') from dual;

SELECTDBMS_METADATA.GET_DDL('USER',U.username)  FROM DBA_USERS U;

4 )

4) 获取物化视图

1) that would bethe correct approach 

2) you definitely have the ability to either a) run more than one query intothe same output file or b) run a single query that is a bunch of union ALLstatements. 


3) you can get materialized views. 

 

ops$tkyte%ORA10GR2>exec dbms_metadata.set_transform_param( DBMS_METADATA.SESSION_TRANSFORM,'SQLTERMINATOR', TRUE );

 

PL/SQL proceduresuccessfully completed.

 

ops$tkyte%ORA10GR2>create materialized view mv

  2  as

  3 select * from t;

 

Materializedview created.

 

ops$tkyte%ORA10GR2>

ops$tkyte%ORA10GR2>select dbms_metadata.get_ddl( 'MATERIALIZED_VIEW', 'MV' ) from dual

  2  /

 

DBMS_METADATA.GET_DDL('MATERIALIZED_VIEW','MV')

-------------------------------------------------------------------------------

 

  CREATE MATERIALIZED VIEW"OPS$TKYTE"."MV"

  ORGANIZATION HEAP PCTFREE 10 PCTUSED 40INITRANS 1 MAXTRANS 255 NOCOMPRESS LO

GGING

  STORAGE(INITIAL 65536 NEXT 1048576 MINEXTENTS1 MAXEXTENTS 2147483645

  PCTINCREASE 0 FREELISTS 1 FREELIST GROUPS 1BUFFER_POOL DEFAULT)

  TABLESPACE "USERS"

  BUILD IMMEDIATE

  USING INDEX PCTFREE 10 INITRANS 2 MAXTRANS255

  STORAGE(INITIAL 65536 NEXT 1048576 MINEXTENTS1 MAXEXTENTS 2147483645

  PCTINCREASE 0 FREELISTS 1 FREELIST GROUPS 1BUFFER_POOL DEFAULT)

  TABLESPACE "USERS"

  REFRESH FORCE ON DEMAND

  WITH PRIMARY KEY USING DEFAULT LOCAL ROLLBACKSEGMENT

  DISABLE QUERY REWRITE

  AS SELECT "T"."X""X","T"."Y" "Y" FROM "T""T";

 

5) 获取公共同义词

SELECTDBMS_METADATA.get_ddl(OBJECT_TYPE,OBJECT_NAME,schema => 'PUBLIC') FROMDBA_OBJECTS WHERE OBJECT_NAME='PUBLIC'

6) config

get the table ddl without constraints then if you are going to get theconstraints later. 

 

ops$tkyte%ORA11GR2> create table t (xint primary key);

 

Table created.

 

ops$tkyte%ORA11GR2> connect /

Connected.

ops$tkyte%ORA11GR2> selectdbms_metadata.get_ddl('TABLE','T') from dual;

 

DBMS_METADATA.GET_DDL('TABLE','T')

-------------------------------------------------------------------------------

 

 CREATE TABLE "OPS$TKYTE"."T"

  (    "X" NUMBER(*,0),

        PRIMARY KEY ("X")

 USING INDEX PCTFREE 10 INITRANS 2 MAXTRANS 255 NOCOMPRESS LOGGING

 TABLESPACE "USERS" ENABLE

   )SEGMENT CREATION DEFERRED

 PCTFREE 10 PCTUSED 40 INITRANS 1 MAXTRANS 255 NOCOMPRESS LOGGING

 TABLESPACE "USERS"

ops$tkyte%ORA11GR2> execute DBMS_METADATA.SET_TRANSFORM_PARAM(DBMS_METADATA.SESSION_TRANSFORM,'CONSTRAINTS',false);

 

PL/SQL procedure successfully completed.

 

ops$tkyte%ORA11GR2> selectdbms_metadata.get_ddl('TABLE','T') from dual;

DBMS_METADATA.GET_DDL('TABLE','T')

-------------------------------------------------------------------------------

 

 CREATE TABLE "OPS$TKYTE"."T"

  (    "X" NUMBER(*,0)

   )SEGMENT CREATION DEFERRED

 PCTFREE 10 PCTUSED 40 INITRANS 1 MAXTRANS 255 NOCOMPRESS LOGGING

 TABLESPACE "USERS"

SET SERVEROUTPUT ON
SET LINESIZE 1000
SET FEEDBACK OFF
set long 999999
SET PAGESIZE 1000

 
SELECT DBMS_METADATA.GET_DDL('TABLESPACE', TS.tablespace_name)
FROM DBA_TABLESPACES TS;


set head off
set pages 0
set long 9999999

SELECT DBMS_METADATA.GET_DDL('USER', USERNAME) || '/' DDL
FROM DBA_USERS where username in ('NCAS')
UNION ALL
SELECT DBMS_METADATA.GET_GRANTED_DDL('ROLE_GRANT', USERNAME) || '/' DDL
FROM DBA_USERS where username in ('NCAS')
UNION ALL
SELECT DBMS_METADATA.GET_GRANTED_DDL('SYSTEM_GRANT', USERNAME) || '/' DDL
FROM DBA_USERS where username in ('NCAS
UNION ALL
SELECT DBMS_METADATA.GET_GRANTED_DDL('OBJECT_GRANT', USERNAME) || '/' DDL
FROM DBA_USERS where username in ('NCAS');