dbms_metadata.get_ddl学习

最新推荐文章于 2026-04-28 22:17:21 发布

转载最新推荐文章于 2026-04-28 22:17:21 发布 · 169 阅读

标签

#java #数据库

今天在网上看到介绍dbms_metadata.get_ddl的文章，很不错，学习下

1.显示设置：

/*创建DBMS_METADATA：
@?/rdbms/admin/catmeta.sql
*/
SET SERVEROUTPUT ON
SET LINESIZE 1000
SET FEEDBACK OFF
set long 999999
SET PAGESIZE 1000
/*若希望不显示storage参数：
EXEC DBMS_METADATA.SET_TRANSFORM_PARAM(DBMS_METADATA.SESSION_TRANSFORM,'STORAGE',false);
*/

2.9i R2所支持的45个OBJECT TYPE：

Type Name Meaning
------------------------------ ------------------------------
AUDIT_OBJ audits of schema objects
AUDIT audits of SQL statements
ASSOCIATION associate statistics
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

3.举例：

--表：(注意：表名要大写)
SQL> SELECT DBMS_METADATA.GET_DDL('TABLE','T2') FROM DUAL;

DBMS_METADATA.GET_DDL('TABLE','T2')
--------------------------------------------------------------------------------

CREATE TABLE "TEST"."T2"
   ( "OWNER" VARCHAR2(30),
   "OBJECT_NAME" VARCHAR2(128),
   "SUBOBJECT_NAME" VARCHAR2(30),
   "OBJECT_ID" NUMBER,
   "DATA_OBJECT_ID" NUMBER,
   "OBJECT_TYPE" VARCHAR2(18),
   "CREATED" DATE,
   "LAST_DDL_TIME" DATE,
   "TIMESTAMP" VARCHAR2(19),
   "STATUS" VARCHAR2(7),
   "TEMPORARY" VARCHAR2(1),
   "GENERATED" VARCHAR2(1),
   "SECONDARY" VARCHAR2(1)
   ) 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 "EXAMPLE"

--索引：
SQL> SELECT DBMS_METADATA.GET_DDL('INDEX','IDX_OBJECT_NAME') FROM DUAL;

DBMS_METADATA.GET_DDL('INDEX','IDX_OBJECT_NAME')
--------------------------------------------------------------------------------

CREATE INDEX "TEST"."IDX_OBJECT_NAME" ON "TEST"."T2" ("OBJECT_NAME")
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 "EXAMPLE"

--主键：
SQL> SELECT DBMS_METADATA.GET_DDL('CONSTRAINT','PK_AA') FROM DUAL;

DBMS_METADATA.GET_DDL('CONSTRAINT','PK_AA')
--------------------------------------------------------------------------------

ALTER TABLE "TEST"."PARENT" ADD CONSTRAINT "PK_AA" PRIMARY KEY ("BB")
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 "EXAMPLE" ENABLE

--外键：
SQL> SELECT DBMS_METADATA.GET_DDL('REF_CONSTRAINT','FK_AA') FROM DUAL;

DBMS_METADATA.GET_DDL('REF_CONSTRAINT','FK_AA')
--------------------------------------------------------------------------------

ALTER TABLE "TEST"."CHILD" ADD CONSTRAINT "FK_AA" FOREIGN KEY ("AA")
   REFERENCES "TEST"."PARENT" ("BB") ENABLE

--表空间：
SQL> SELECT DBMS_METADATA.GET_DDL('TABLESPACE','SYSAUX') FROM DUAL;

DBMS_METADATA.GET_DDL('TABLESPACE','SYSAUX')
------------------------------------------------------------------------

CREATE TABLESPACE "SYSAUX" DATAFILE
'/u01/oracle/oradata/orcl/sysaux01.dbf

--用户：
DBMS_METADATA.GET_DDL('USER','SYSTEM')
-------------------------------------------------------------------------------

ALTER USER "SYSTEM" IDENTIFIED BY VALUES 'S:C27C11320D7002613C610B3F5C293AE8

4.综上所述：select dbms_metadata.get_ddl(’OBJECT_TYPE’,'OBJECT_NAME’,'SCHEMA’) from dual|user_xxx|all_xxx|dba_xxx;

来自 “ ITPUB博客 ” ，链接：http://blog.itpub.net/8183550/viewspace-667778/，如需转载，请注明出处，否则将追究法律责任。

转载于:http://blog.itpub.net/8183550/viewspace-667778/