(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');