获取对象DDL语句

描述：

        有时会需要在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 .