描述:
有时会需要在linux下获取对象的创建语句,或者用户的授权语句,可以分别使用dbms_metadata.get_ddl和dbms_metadata.get_granted_ddl包来获取创建语句。
参考oracle官方文档《Oracle® Database Database PL/SQL Packages and Types Reference》
一,获取对象创建语句dbms_metadata.get_ddl
DBMS_METADATA.GET_DDL (
object_type IN VARCHAR2,
name IN VARCHAR2,
schema IN VARCHAR2 DEFAULT NULL,
version IN VARCHAR2 DEFAULT 'COMPATIBLE',
model IN VARCHAR2 DEFAULT 'ORACLE',
transform IN VARCHAR2 DEFAULT 'DDL')
RETURN CLOB;
SQL> set linesize 9999;
SQL> set long 9999;
SQL> set pagesize 9999;
1,获取表system.t23的DDL语句
select dbms_metadata.get_ddl('TABLE','T23','SYSTEM') from dual;
2,获取表空间TP的DDL语句
select dbms_metadata.get_ddl('TABLESPACE','TP') from dual;
3,获取用户的scott的DDL语句
select dbms_metadata.get_ddl('USER','SCOTT') from dual;
4,获取存储过程mondb.p_del_partitions的DDL语句
SELECT dbms_metadata.get_ddl('PROCEDURE','P_DEL_PARTITIONS','MONDB') FROM dual;
二,获取对象授权DDL语句dbms_metadata.get_granted_ddl
DBMS_METADATA.GET_GRANTED_DDL (
object_type IN VARCHAR2,
grantee IN VARCHAR2 DEFAULT NULL,
version IN VARCHAR2 DEFAULT 'COMPATIBLE',
model IN VARCHAR2 DEFAULT 'ORACLE',
transform IN VARCHAR2 DEFAULT 'DDL',
object_count IN NUMBER DEFAULT 10000)
RETURN CLOB;
1,获取SCOTT的对象权限的DDL语句
SELECT dbms_metadata.get_granted_ddl('OBJECT_GRANT','SCOTT') from dual;
2,获取SCOTT的系统权限的DDL语句
SELECT dbms_metadata.get_granted_ddl('SYSTEM_GRANT','SCOTT') from dual;
3,获取SCOTT的角色权限的DDL语句
SELECT dbms_metadata.get_granted_ddl('ROLE_GRANT','SCOTT') from dual;
如果该package获取的结果为空,则会报错:
SQL> SELECT dbms_metadata.get_granted_ddl('ROLE_GRANT','SCOTT') from dual;
ERROR:
ORA-31608: specified object of type ROLE_GRANT not found
ORA-06512: at "SYS.DBMS_METADATA", line 6069
ORA-06512: at "SYS.DBMS_METADATA", line 8817
ORA-06512: at line 1
三,获取依赖该对象的DDL语句DBMS_METADATA.GET_DEPENDENT_DDL
DBMS_METADATA.GET_DEPENDENT_DDL (
object_type IN VARCHAR2,
base_object_name IN VARCHAR2,
base_object_schema IN VARCHAR2 DEFAULT NULL,
version IN VARCHAR2 DEFAULT 'COMPATIBLE',
model IN VARCHAR2 DEFAULT 'ORACLE',
transform IN VARCHAR2 DEFAULT 'DDL',
object_count IN NUMBER DEFAULT 10000)
RETURN CLOB;
1,查看sys.dba_objects的对象权限授给了谁,及其DDL授权语句:
SQL> SELECT DBMS_METADATA.GET_DEPENDENT_DDL('OBJECT_GRANT','DBA_OBJECTS','SYS') FROM DUAL;
DBMS_METADATA.GET_DEPENDENT_DDL('OBJECT_GRANT','DBA_OBJECTS','SYS')
--------------------------------------------------------------------------------
GRANT SELECT ON "SYS"."DBA_OBJECTS" TO "SYSTEM"
GRANT SELECT ON "SYS"."DBA_OBJECTS" TO "APEX_040200"
GRANT SELECT ON "SYS"."DBA_OBJECTS" TO "MDSYS"
GRANT SELECT ON "SYS"."DBA_OBJECTS" TO "CTXSYS" WITH GRANT OPTION
GRANT SELECT ON "SYS"."DBA_OBJECTS" TO "ORACLE_OCM"
GRANT SELECT ON "SYS"."DBA_OBJECTS" TO "AUDIT_ADMIN"
GRANT SELECT ON "SYS"."DBA_OBJECTS" TO "SELECT_CATALOG_ROLE"
四,相关参数信息
Parameter | Description |
object_type | The type of object to be retrieved. This parameter takes the same values as the OPEN object_type parameter, except that it cannot be a heterogeneous object type. The attributes of the object type must be appropriate to the function. That is, for GET_xxx it must be a named object. |
name | The object name. It is used internally in a NAME filter. (If the name is longer than 30 characters, it will be used in a LONGNAME filter.) If this parameter is NULL, then no NAME or LONGNAME filter is specified. See Table 99-18 for a list of filters. |
schema | The object schema. It is used internally in a SCHEMA filter. The default is the current user. |
version | The version of metadata to be extracted. This parameter takes the same values as the OPEN version parameter. |
model | The object model to use. This parameter takes the same values as the OPEN model parameter. |
transform | The name of a transformation on the output. This parameter takes the same values as the ADD_TRANSFORM name parameter. For GET_XML this must not be DDL . |
base_object_name | The base object name. It is used internally in a BASE_OBJECT_NAME filter. |
base_object_schema | The base object schema. It is used internally in a BASE_OBJECT_SCHEMA filter. The default is the current user. |
grantee | The grantee. It is used internally in a GRANTEE filter. The default is the current user. |
object_count | The maximum number of objects to return. See SET_COUNT Procedure . |