dbms_metadata的使用

给出两个检查表结构的方法:

1.desc

2.dbms_metadata.get_ddl包

 

在oracle9i之后提供一个dbma_metadata包用于获得对象的创建脚本。

在$ORACLE_HOME/rdbms/demo下有两个相关的demo：mddemo.sql、mddemo2.sql

 

Concept

The DBMS_METADATA package provides interfaces for extracting complete definitions of database objects. The definitions can be expressed either as XML or as SQL DDL. Two styles of interface are provided:

l  A flexible, sophisticated interface for programmatic control

l  A simplified interface for ad hoc querying

 

 

创建表：

create table test as select * from scott.dept;

create view test_v as select * from test;

 

测试：

SQL> select dbms_metadata.get_ddl('TABLE','TEST') from dual;

DBMS_METADATA.GET_DDL('TABLE',

--------------------------------------------------------------------------------

  CREATE TABLE "APPS"."TEST"

   ( "DEPTNO" NUMBER(2,0),

    "DNAME" VARCHAR2(14),

    "LOC" VARCHAR2(13)

   ) PCTFREE 10 PCTUSED 40 INITRANS 1 MAXTRANS 255 NOCOMPRESS LOGGING

  STORAGE(INITIAL 131072 NEXT 131072 MINEXTENTS 1 MAXEXTENTS 2147483645

  PCTINCREASE 0 FREELISTS 1 FREELIST GROUPS 1 BUFFER_POOL DEFAULT)

  TABLESPACE "APPS_TS_TX_DATA"

SQL> select dbms_metadata.get_ddl('VIEW','TEST_V') from dual;

DBMS_METADATA.GET_DDL('VIEW','

--------------------------------------------------------------------------------

  CREATE OR REPLACE FORCE VIEW "APPS"."TEST_V" ("DEPTNO", "DNAME", "LOC") AS

  select "DEPTNO","DNAME","LOC" from test

 

 

1.得到一个表的ddl语句：

SET SERVEROUTPUT ON
SET LINESIZE 1000
SET FEEDBACK OFF
set long 999999             ------显示不完整
SET PAGESIZE 1000    ----分页
 
EXECUTE DBMS_METADATA.SET_TRANSFORM_PARAM(DBMS_METADATA.SESSION_TRANSFORM,'STORAGE',false);  ---去除storage等多余参数
 
SELECT DBMS_METADATA.GET_DDL('TABLE','TCC_NE_FRAME') FROM DUAL;
SELECT DBMS_METADATA.GET_DDL('TABLE','TCC_NE_SNAP') FROM DUAL;
 
2.得到一个用户下的所有表，索引，存储过程的ddl
 
SET SERVEROUTPUT ON
SET LINESIZE 1000
SET FEEDBACK OFF
set long 999999  ------显示不完整
SET PAGESIZE 1000  ----分页
---去除storage等多余参数
EXECUTE DBMS_METADATA.SET_TRANSFORM_PARAM(DBMS_METADATA.SESSION_TRANSFORM,'STORAGE',false);
 
SELECT DBMS_METADATA.GET_DDL(U.OBJECT_TYPE, u.object_name)
  FROM USER_OBJECTS u
 where U.OBJECT_TYPE IN ('TABLE','INDEX','PROCEDURE');
 
3.得到所有表空间的ddl语句
 
SET SERVEROUTPUT ON
SET LINESIZE 1000
SET FEEDBACK OFF
set long 999999------显示不完整
SET PAGESIZE 1000----分页
---去除storage等多余参数
 
SELECT DBMS_METADATA.GET_DDL('TABLESPACE', TS.tablespace_name)
FROM DBA_TABLESPACES TS;

4.得到所有创建用户的ddl
 

SET SERVEROUTPUT ON
SET LINESIZE 1000
SET FEEDBACK OFF
set long 999999------显示不完整
SET PAGESIZE 1000----分页
---去除storage等多余参数
 
SELECT DBMS_METADATA.GET_DDL('USER',U.username) 
FROM DBA_USERS U;
另外，若执行不了该包，则需要正确安装好相应的包。