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

最新推荐文章于 2024-04-09 09:37:52 发布

执笔画情ora

最新推荐文章于 2024-04-09 09:37:52 发布

阅读量7.4k

点赞数 1

CC 4.0 BY-SA版权

分类专栏： ORACLE数据库迁移

本文链接：https://blog.youkuaiyun.com/oradbm/article/details/53422016

ORACLE数据库迁移专栏收录该内容

29 篇文章

订阅专栏

本文介绍如何使用Oracle的dbms_metadata包来获取数据库对象的定义语句，包括表、视图、索引、过程等功能，并展示了如何调整输出格式。

摘要生成于 C知道，由 DeepSeek-R1 满血版支持，前往体验 >

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