dbms_metadata

admin · 发表于 2020-5-30 07:33:23

dbms_meta包用于获取对象的ddl定义
先格式化输出

set linesize 180
set pagesize 1000
set long 2000
EXEC DBMS_METADATA.SET_TRANSFORM_PARAM(DBMS_METADATA.SESSION_TRANSFORM,'PRETTY',TRUE);
EXEC DBMS_METADATA.SET_TRANSFORM_PARAM(DBMS_METADATA.SESSION_TRANSFORM,'SQLTERMINATOR',TRUE);

复制代码

--全部表空间

select dbms_metadata.get_ddl('TABLESPACE',tablespace_name) from dba_tablespaces;

复制代码

--特定表空间

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

复制代码

--某用户的所有表

select dbms_metadata.get_ddl('TABLE',table_name,'&owner') from dba_tables where owner='&owner';

复制代码

--某用户的某表

select dbms_metadata.get_ddl('TABLE','&table_name','&schema_name') from dual;

复制代码

--全部用户

select dbms_metadata.get_ddl('USER',u.username) from dba_users u;

复制代码

--部分用户

select dbms_metadata.get_ddl('USER',u.username) from dba_users u where u.username in ('NAME1','NAME2');

复制代码

--单个用户

select dbms_metadata.get_ddl('USER','&U_NAME') from dual;

复制代码

--索引

select dbms_metadata.get_ddl('INDEX',U.OBJECT_NAME,'HR') from dba_objects u where u.object_type ='INDEX'and u.owner='HR';

复制代码

--视图

select dbms_metadata.get_ddl('VIEW','V_RPT_MS_USER_GATHER_KGZG','FCB_STATHB') from dual；

复制代码

--过程

select dbms_metadata.get_ddl('PROCEDURE',u.object_name,'DBMON') from dba_objects u where u.object_type='PROCEDURE' and u.owner='DBMON';

复制代码

--dblink

select dbms_metadata.get_ddl('DB_LINK','DBL','PUBLIC') from dual

复制代码

--包

select DBMS_METADATA.GET_DDL('PACKAGE','PACKAGENAME','USERNAME') from dual；

复制代码

--包体

select DBMS_METADATA.GET_DDL('PACKAGE BODY','PACKAGEBODYNAME','USERNAME') from dual；

复制代码

--主键约束

SELECT DBMS_METADATA.GET_DDL('CONSTRAINT','CONSTRAINTNAME','USERNAME') FROM DUAL;

复制代码

--外键约束

SELECT DBMS_METADATA.GET_DDL('REF_CONSTRAINT','REF_CONSTRAINTNAME','USERNAME') FROM DUAL;

复制代码

--物化视图

select dbms_metadata.get_ddl('MATERIALIZED_VIEW','&MV_NAME','&OWNER') FROM DUAL;

复制代码

--触发器

select DBMS_METADATA.GET_DDL('TRIGGER','TRIGGERNAME','USERNAME) FROM DUAL;

复制代码

--函数

select DBMS_METADATA.GET_DDL('FUNCTION','FUNCTIONNAME','USERNAME') from DUAL

复制代码

--序列

select dbms_metadata.get_ddl('SEQUENCE','S_I_INTERFACE_LOG','SEA') from dual;

复制代码

--角色授权

select dbms_metadata.get_granted_ddl('ROLE_GRANT','MW_IQ') from dual;

复制代码

系统权限授权

select dbms_metadata.get_granted_ddl('SYSTEM_GRANT','MW_IQ') from dual;

复制代码

--查询一个用户的创建和授权语句

select dbms_metadata.get_ddl('USER','ZHYU') from dual ;
select dbms_metadata.get_granted_ddl('ROLE_GRANT','ZHYU') from dual ;
select dbms_metadata.get_granted_ddl('SYSTEM_GRANT','ZHYU') from dual ;
select dbms_metadata.get_granted_ddl('OBJECT_GRANT','ZHYU') from dual ;

复制代码

如何格式化输出
DBMS_METADATA.set_transform_param格式化输出获得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);

复制代码

例如查询一个目录'dump'的ddl语句以及所有备授权使用的用户：

set linesize 180
set pagesize 1000
set long 2000
set heading off
EXEC DBMS_METADATA.SET_TRANSFORM_PARAM(DBMS_METADATA.SESSION_TRANSFORM,'PRETTY',TRUE);
EXEC DBMS_METADATA.SET_TRANSFORM_PARAM(DBMS_METADATA.SESSION_TRANSFORM,'SQLTERMINATOR',TRUE);
select dbms_metadata.get_ddl('DIRECTORY','&&DIRECTORY_NAME') as directory_ddl from dual
union all
SELECT DBMS_METADATA.GET_DEPENDENT_DDL('OBJECT_GRANT','&&DIRECTORY_NAME') as directory_ddl FROM DUAL;

复制代码

假设要读取test用户，用以下语句实现，主语用户名要uppercase

set longchunksize 20000 pagesize 0 feedback off verify off trimspool on
column Extracted_DDL format a1000
EXEC DBMS_METADATA.SET_TRANSFORM_PARAM(DBMS_METADATA.SESSION_TRANSFORM,'PRETTY',TRUE);
EXEC DBMS_METADATA.SET_TRANSFORM_PARAM(DBMS_METADATA.SESSION_TRANSFORM,'SQLTERMINATOR',TRUE);
undefine User_in_Uppercase;
set linesize 1000
set long 2000000000
select (case
when ((select count(*)
from dba_users
where username = '&&User_in_Uppercase' and profile <> 'DEFAULT') > 0)
then chr(10)||' -- Note: Profile'||(select dbms_metadata.get_ddl('PROFILE', u.profile) AS ddl from dba_users u where u.username = '&User_in_Uppercase')
else to_clob (chr(10)||' -- Note: Default profile, no need to create!')
end ) from dual
UNION ALL
select (case
when ((select count(*)
from dba_users
where username = '&User_in_Uppercase') > 0)
then ' -- Note: Create user statement'||dbms_metadata.get_ddl ('USER', '&User_in_Uppercase')
else to_clob (chr(10)||' -- Note: User not found!')
end ) Extracted_DDL from dual
UNION ALL
select (case
when ((select count(*)
from dba_ts_quotas
where username = '&User_in_Uppercase') > 0)
then ' -- Note: TBS quota'||dbms_metadata.get_granted_ddl( 'TABLESPACE_QUOTA', '&User_in_Uppercase')
else to_clob (chr(10)||' -- Note: No TS Quotas found!')
end ) from dual
UNION ALL
select (case
when ((select count(*)
from dba_role_privs
where grantee = '&User_in_Uppercase') > 0)
then ' -- Note: Roles'||dbms_metadata.get_granted_ddl ('ROLE_GRANT', '&User_in_Uppercase')
else to_clob (chr(10)||' -- Note: No granted Roles found!')
end ) from dual
UNION ALL
select (case
when ((select count(*)
from V$PWFILE_USERS
where username = '&User_in_Uppercase' and SYSDBA='TRUE') > 0)
then ' -- Note: sysdba'||chr(10)||to_clob (' GRANT SYSDBA TO '||'"'||'&User_in_Uppercase'||'"'||';')
else to_clob (chr(10)||' -- Note: No sysdba administrative Privilege found!')
end ) from dual
UNION ALL
select (case
when ((select count(*)
from dba_sys_privs
where grantee = '&User_in_Uppercase') > 0)
then ' -- Note: System Privileges'||dbms_metadata.get_granted_ddl ('SYSTEM_GRANT', '&User_in_Uppercase')
else to_clob (chr(10)||' -- Note: No System Privileges found!')
end ) from dual
UNION ALL
select (case
when ((select count(*)
from dba_tab_privs
where grantee = '&User_in_Uppercase') > 0)
then ' -- Note: Object Privileges'||dbms_metadata.get_granted_ddl ('OBJECT_GRANT', '&User_in_Uppercase')
else to_clob (chr(10)||' -- Note: No Object Privileges found!')
end ) from dual
/

复制代码

支持获取ddl的object type scope

ORACLE 9.0.1
Type Name Meaning
ASSOCIATION associate statistics
AUDIT audits of SQL statements
AUDIT_OBJ audits of schema objects
CLUSTER clusters
COMMENT comments
CONSTRAINT constraints
CONTEXT application contexts
DB_LINK database links
DEFAULT_ROLE default roles
DIMENSION dimensions
DIRECTORY directories
FUNCTION stored functions
INDEX indexes
INDEXTYPE indextypes
JAVA_SOURCE java sources
LIBRARY external procedure libraries
MATERIALIZED_VIEW materialized views
MATERIALIZED_VIEW_LOG materialized view logs
OBJECT_GRANT object grants
OPERATOR operators
OUTLINE stored outlines
PACKAGE stored packages
PACKAGE_SPEC package specifications
PACKAGE_BODY package bodies
PROCEDURE stored procedures
PROFILE profiles
PROXY proxy authentications
REF_CONSTRAINT referential constraint
ROLE roles
ROLE_GRANT role grants
ROLLBACK_SEGMENT rollback segments
SEQUENCE sequences
SYNONYM synonyms
SYSTEM_GRANT system privilege grants
TABLE tables
TABLESPACE tablespaces
TABLESPACE_QUOTA tablespace quotas
TRIGGER triggers
TRUSTED_DB_LINK trusted links
TYPE user-defined types
TYPE_SPEC type specifications
TYPE_BODY type bodies
USER users
VIEW views
XMLSCHEMA XML schema
ORACLE 10.1.0
Type Name Meaning
AQ_QUEUE queues
AQ_QUEUE_TABLE additional metadata for queue tables
AQ_TRANSFORM transforms
DATABASE_EXPORT all metadata objects in a database
FGA_POLICY fine-grained audit policies
INDEX_STATISTICS indextypes
JOB scheduler jobs (Current support scheduled for 11.2 .. See NOTE:567504.1)
REFRESH_GROUP refresh groups
RESOURCE_COST resource cost info
RLS_CONTEXT driving contexts for enforcement of fine-grained access-control policies
RLS_GROUP fine-grained access-control policy groups
RLS_POLICY fine-grained access-control policies
RMGR_CONSUMER_GROUP resource consumer groups
RMGR_INTITIAL_CONSUMER_GROUP assign initial consumer groups to users
RMGR_PLAN resource plans
RMGR_PLAN_DIRECTIVE resource plan directives
SCHEMA_EXPORT sequences
TABLE_DATA metadata describing row data for a table, nested table, or partition
TABLE_EXPORT metadata for a table and its associated objects
TABLE_STATISTICS precomputed statistics on tables
TRANSPORTABLE_EXPORT metadata for objects in a transportable tablespace set
ORACLE 10.2.0
No Additions to the above..
ORACLE 11.1.0
No Additions to the above.
ORACLE 11.2.0
No Additions to the above.

复制代码

How to Capture DDL for Scheduler Jobs (Doc ID 567504.1)
Please note: The DBMS_METADATA package cannot be used to capture the DDL of jobs scheduled using DBMS_JOB. We can use the following workaround to capture the DDL of jobs scheduled using DBMS_JOB.

spool on
select 'exec dbms_job.isubmit(job=>'||job||',what=>'''||what||''',next_date=>'''||next_date||''',interval=>'''||interval||''',no_parse=>TRUE);' from dba_jobs;
spool off

复制代码

		自动登录	找回密码
密码			立即注册

[技术专题] dbms_metadata

浏览过的版块