开始,只是认为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','EXAMPLE') FROM DUAL;
DBMS_METADATA.GET_DDL('TABLESPACE','EXAMPLE')
--------------------------------------------------------------------------------
CREATE TABLESPACE "EXAMPLE" DATAFILE
'/oracle/oradata/ora9i/example01.dbf' SIZE 125829120 REUSE
AUTOEXTEND ON NEXT 655360 MAXSIZE UNLIMITED
LOGGING ONLINE PERMANENT BLOCKSIZE 8192
EXTENT MANAGEMENT LOCAL AUTOALLOCATE SEGMENT SPACE MANAGEMENT AUTO
--用户:
SQL> SELECT DBMS_METADATA.GET_DDL('USER','TEST') FROM DUAL;
DBMS_METADATA.GET_DDL('USER','TEST')
--------------------------------------------------------------------------------
CREATE USER "TEST" IDENTIFIED BY VALUES '7A0F2B316C212D67'
DEFAULT TABLESPACE "TEST_MSSM"
TEMPORARY TABLESPACE "TEMP"
4.综上所述:select dbms_metadata.get_ddl(’OBJECT_TYPE’,'OBJECT_NAME’,'SCHEMA’) from dual|user_xxx|all_xxx|dba_xxx;
分享到:
相关推荐
作用:DBMS_STATS.GATHER_TABLE_STATS统计表,列,索引的统计信息. DBMS_STATS.GATHER_TABLE_STATS的语法如下: DBMS_STATS.GATHER_TABLE_STATS ( ownname VARCHAR2, tabname VARCHAR2, partname VARCHAR2, estimate_...
以下是对Oracle中dbms_metadata.get_ddl的用法进行了详细的分析介绍,需要的朋友参考下
GBase 8t/8s/informix获取DDL函数DBMS_METADATA.GET_DDL CREATE FUNCTION DBMS_METADATA.GET_DDL( object_type varchar(16) , name varchar(128), schema varchar(32) default null, v_version varchar(32) ...
Oracle 18c bug 执行 DBMS_PDB.CHECK_PLUG_COMPATIBILITY报错_ITPUB博客.mhtml
NULL 博文链接:https://duqiangcise.iteye.com/blog/648491
dbms_comp_advisor.getratio 预估压缩比例的存储过程脚本 11gR2以前使用,11gR2后可以使用系统自带的存储。
支持ArcGIS10.2版本的PostgreSQL_DBMS_for_windows_922,ESRI官方原版资源。
oracle dbms_lob
通过此版本可以把oracle时区版本调整到为最新版本,一般配合时区补丁使用 可以参考https://blog.csdn.net/weixin_43885834/article/details/105745901 https://download.csdn.net/download/weixin_43885834/12360971...
Oracle统计分析-dbms_stats.pdf
检查是否设置了utl_file_dir参数 show parameter utl ...分析的结果其它会话无法访问 用以下方法转储: create table logmnr3 as select * from GV$LOGMNR_CONTENTS; 结束分析 EXECUTE DBMS_LOGMNR.END_LOGMNR;
oracle dbms_sql的使用方法,非常使用
简单的dbms_stats操作,简单的dbms_stats操作简单的dbms_stats操作
使用C语言实现的数据库管理系统。 支持简单类 SQL语言。
ORACLE数据库使用dbms_stats包手动收集关于表的、索引的统计信息。
How to Move from ANALYZE (using DBMS_UTILITY) to DBMS_STATS Is ANALYZE on the Data Dictionary Supported (TABLES OWNED BY SYS)?
dbms_random是oracle提供的一个随机函数包,以下介绍一些dbms_random的常用示例: dbms_random.value用法: 生成一个大于等于0,小于等于1的38位小数 代码如下: — FUNCTION value RETURN NUMBER; select dbms_...