DBMS_METADATA中使用SESSION_TRANSFORM过滤不想获取的DDL
来源:互联网 发布:excel删除数据不重复项 编辑:程序博客网 时间:2024/05/18 00:14
我们一般使用dbms_metadata.get_ddl获取对象的ddl的时候,有时会获取一些其他额外的信息,例如当你想获取表的创建语句的时候,你会得到表的约束信息,这个信息可能是你不想要的,那么就可以用SESSION_TRANSFORM对它进行过滤。
看下面的示例,创建一个有主键和外键的表,获取他的ddl语句:
SQL> CREATE TABLE tb1 (id int primary key);Table created.SQL> create table tb2 (id int primary key references tb1(id));Table created.SQL> insert into tb1 values(1);1 row created.SQL> commit;Commit complete.SQL> insert into tb2 values(1);1 row created.SQL> commit;SQL> select dbms_metadata.get_ddl('TABLE','TB2','TEST') from dual;DBMS_METADATA.GET_DDL('TABLE','TB2','TEST')-------------------------------------------------------------------------------- CREATE TABLE "TEST"."TB2" ("ID" NUMBER(*,0), PRIMARY KEY ("ID") 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 FLASH_CACHE DEFAULT CELL_FLASH_CACHE DEFAULT) TABLESPACE "USERS" ENABLE, FOREIGN KEY ("ID") REFERENCES "TEST"."TB1" ("ID") 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"
不单单是获取到了创建的基本字段的语句,还有主键,约束,外键,存储参数,表空间等,如果这些你不需要,都是可以进行过滤的,例如我过滤掉主键、外键、存储信息
使用如下语句:
exec DBMS_METADATA.SET_TRANSFORM_PARAM(DBMS_METADATA.SESSION_TRANSFORM,'REF_CONSTRAINTS',false);
exec DBMS_METADATA.SET_TRANSFORM_PARAM(DBMS_METADATA.SESSION_TRANSFORM,'CONSTRAINTS',false);
exec DBMS_METADATA.SET_TRANSFORM_PARAM(DBMS_METADATA.SESSION_TRANSFORM,'STORAGE',false);
exec DBMS_METADATA.SET_TRANSFORM_PARAM(DBMS_METADATA.SESSION_TRANSFORM,'REF_CONSTRAINTS',false);PL/SQL procedure successfully completed.exec DBMS_METADATA.SET_TRANSFORM_PARAM(DBMS_METADATA.SESSION_TRANSFORM,'CONSTRAINTS',false);PL/SQL procedure successfully completed.SQL> exec DBMS_METADATA.SET_TRANSFORM_PARAM(DBMS_METADATA.SESSION_TRANSFORM,'STORAGE',false);PL/SQL procedure successfully completed.SQL> SQL> select dbms_metadata.get_ddl('TABLE','TB2','TEST') from dual;DBMS_METADATA.GET_DDL('TABLE','TB2','TEST')-------------------------------------------------------------------------------- CREATE TABLE "TEST"."TB2" ("ID" NUMBER(*,0) ) SEGMENT CREATION IMMEDIATE PCTFREE 10 PCTUSED 40 INITRANS 1 MAXTRANS 255 NOCOMPRESS LOGGING TABLESPACE "USERS"还可以对其他的一些信息做过滤,表格如下:
Table 87-22 SET_TRANSFORM_PARAM: Transform Parameters for the DDLTransform
Object Type
Name
Datatype
Meaning
All objects
PRETTY
BOOLEAN
If TRUE, format the output with indentation and line feeds. Defaults toTRUE.
All objects
SQLTERMINATOR
BOOLEAN
If TRUE, append a SQL terminator (; or /) to each DDL statement. Defaults to FALSE.
TABLE
SEGMENT_ATTRIBUTES
BOOLEAN
If TRUE, include segment attributes clauses in the DDL. If FALSE, omit them. Defaults to TRUE.
TABLE
STORAGE
BOOLEAN
If TRUE, include storage clauses in the DDL. If FALSE, omit them. Defaults to TRUE. (Ignored if SEGMENT_ATTRIBUTES is FALSE.)
TABLE
TABLESPACE
BOOLEAN
If TRUE, include tablespace clauses in the DDL. If FALSE, omit them. (Ignored if SEGMENT_ATTRIBUTES is FALSE.) Defaults to TRUE.
TABLE
CONSTRAINTS
BOOLEAN
If TRUE, include all non-referential table constraints in the DDL. If FALSE, omit them. Defaults to TRUE.
TABLE
REF_CONSTRAINTS
BOOLEAN
If TRUE, include all referential constraints (foreign keys) in the DDL. IfFALSE, omit them. Defaults to TRUE.
TABLE
CONSTRAINTS_AS_ALTER
BOOLEAN
If TRUE, include table constraints as separate ALTER TABLE (and, if necessary, CREATE INDEX) statements. If FALSE, specify table constraints as part of the CREATE TABLE statement. Defaults to FALSE. Requires thatCONSTRAINTS be TRUE.
TABLE
OID
BOOLEAN
If TRUE, include the OID clause for object tables in the DDL. If FALSE, omit it. Defaults to FALSE.
TABLE
SIZE_BYTE_KEYWORD
BOOLEAN
If TRUE, include the BYTE keyword as part of the size specification ofCHAR and VARCHAR2 columns that use byte semantics. If FALSE, omit the keyword. Defaults to FALSE.
TABLE, INDEX
PARTITIONING
BOOLEAN
If TRUE, include partitioning clauses in the DDL. If FALSE, omit them. Defaults to TRUE.
INDEX, CONSTRAINT,ROLLBACK_SEGMENT,CLUSTER, TABLESPACE
SEGMENT_ATTRIBUTES
BOOLEAN
If TRUE, include segment attributes clauses (physical attributes, storage attributes, tablespace, logging) in the DDL. If FALSE, omit them. Defaults to TRUE.
INDEX, CONSTRAINT,ROLLBACK_SEGMENT, CLUSTER
STORAGE
BOOLEAN
If TRUE, include storage clauses in the DDL. If FALSE, omit them. (Ignored if SEGMENT_ATTRIBUTES is FALSE.) Defaults to TRUE.
INDEX, CONSTRAINT,ROLLBACK_SEGMENT, CLUSTER
TABLESPACE
BOOLEAN
If TRUE, include tablespace clauses in the DDL. If FALSE, omit them. (Ignored if SEGMENT_ATTRIBUTES is FALSE.) Defaults to TRUE.
TYPE
SPECIFICATION
BOOLEAN
If TRUE, include the type specification in the DDL. If FALSE, omit it. Defaults to TRUE.
TYPE
BODY
BOOLEAN
If TRUE, include the type body in the DDL. If FALSE, omit it. Defaults toTRUE.
TYPE
OID
BOOLEAN
If TRUE, include the OID clause in the DDL. If FALSE, omit it. Defaults toFALSE.
PACKAGE
SPECIFICATION
BOOLEAN
If TRUE, include the package specification in the DDL. If FALSE, omit it. Defaults to TRUE.
PACKAGE
BODY
BOOLEAN
If TRUE, include the package body in the DDL. If FALSE, omit it. Defaults to TRUE.
VIEW
FORCE
BOOLEAN
If TRUE, use the FORCE keyword in the CREATE VIEW statement. If FALSE, do not use the FORCE keyword in the CREATE VIEW statement. Defaults toTRUE.
OUTLINE
INSERT
BOOLEAN
If TRUE, include the INSERT statements into the OL$ dictionary tables that will create the outline and its hints. If FALSE, omit a CREATE OUTLINEstatement. Defaults to FALSE.
Note: This object type is being deprecated.
All objects
DEFAULT
BOOLEAN
Calling SET_TRANSFORM_PARAM with this parameter set to TRUE has the effect of resetting all parameters for the transform to their default values. Setting this FALSE has no effect. There is no default.
All objects
INHERIT
BOOLEAN
If TRUE, inherits session-level parameters. Defaults to FALSE. If an application calls ADD_TRANSFORM to add the DDL transform, then by default the only transform parameters that apply are those explicitly set for that transform handle. This has no effect if the transform handle is the session transform handle.
ROLE
REVOKE_FROM
Text
The name of a user from whom the role must be revoked. If this is a non-null string and if the CREATE ROLE statement grants you the role, aREVOKE statement is included in the DDL after the CREATE ROLEstatement.
Note: When you issue a CREATE ROLE statement, Oracle may grant you the role. You can use this transform parameter to undo the grant.
Defaults to null string.
TABLESPACE
REUSE
BOOLEAN
If TRUE, include the REUSE parameter for datafiles in a tablespace to indicate that existing files can be reused. If FALSE, omit the REUSEparameter.
Defaults to FALSE.
CLUSTER, INDEX,ROLLBACK_SEGMENT, TABLE,TABLESPACE
PCTSPACE
NUMBER
A number representing the percentage by which space allocation for the object type is to be modified. The value is the number of one-hundreths of the current allocation. For example, 100 means 100%.
If the object type is TABLESPACE, the following size values are affected:
- in file specifications, the value of SIZE
- MINIMUM EXTENT
- EXTENT MANAGEMENT LOCAL UNIFORM SIZE
For other object types, INITIAL and NEXT are affected.
- DBMS_METADATA中使用SESSION_TRANSFORM过滤不想获取的DDL
- ORACLE 使用DBMS_METADATA.GET_DDL获取DDL语句
- ORACLE 使用DBMS_METADATA.GET_DDL获取DDL语句
- ORACLE 使用DBMS_METADATA.GET_DDL获取DDL语句
- ORACLE 使用DBMS_METADATA.GET_DDL获取DDL语句
- DBMS_METADATA获取DDL语句
- ORACLE如何使用DBMS_METADATA.GET_DDL获取表,表空间,用户,视图等的DDL语句
- ORACLE如何使用DBMS_METADATA.GET_DDL获取表,表空间,用户,视图等的DDL语句
- ORACLE如何使用DBMS_METADATA.GET_DDL获取DDL语句
- [转]:ORACLE如何使用DBMS_METADATA.GET_DDL获取DDL语句
- ORACLE如何使用DBMS_METADATA.GET_DDL获取DDL语句
- ORACLE如何使用DBMS_METADATA.GET_DDL获取DDL语句
- ORACLE如何使用DBMS_METADATA.GET_DDL获取DDL语句
- 93、ORACLE如何使用DBMS_METADATA.GET_DDL获取DDL语句
- DBMS_METADATA.GET_DDL 获取数据库里面的ddl语句
- [Oracle]利用DBMS_METADATA.GET_DDL函数获取数据库对象的DDL语句
- 使用dbms_metadata.get_ddl得到表的ddl语句,增加itl事务槽个数
- dbms_metadata的使用
- Android 备忘录5
- POJ 1088 滑雪(经典DP)
- VS 快捷键
- android资源控件——ListView
- Android中调用发送邮件
- DBMS_METADATA中使用SESSION_TRANSFORM过滤不想获取的DDL
- 栈学习
- C语言程序学习(五)笔记
- oracle替代变量
- Div 与 table 的区别
- 活着(三)
- table和div设置height:100%无效的完美解决方法
- Tweened 动画资源 -Menu 菜单按钮
- Docker:集装箱式“运输”在软件上的实现