使用dbms_metadata.get_ddl檢視物件的定義語句。

楊奇龍發表於2011-04-15
當我們想要檢視某個表或者是表空間的DDL的時候,可以利用dbms_metadata.get_ddl這個包來檢視。
dbms_metadata包中的get_ddl函式詳細引數 
GET_DDL函式返回建立物件的原資料的DDL語句,詳細引數如下
-- object_type ---需要返回原資料的DDL語句的物件型別
-- name --- 物件名稱
-- schema ---物件所在的Schema,預設為當前使用者所在所Schema
-- version ---物件原資料的版本
-- model ---原資料的型別預設為ORACLE
-- transform. - XSL-T transform. to be applied.
-- RETURNS: 物件的原資料預設以CLOB型別返回
dbms_metadata包中的get_ddl函式定義 
FUNCTION 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; 
SET SERVEROUTPUT ON
SET LINESIZE 1000
SET FEEDBACK OFF
SET LONG  999999
檢視錶的DDL 語句。
yang@rac1>select dbms_metadata.get_ddl('TABLE','YANGTAB') FROM DUAL;
DBMS_METADATA.GET_DDL('TABLE','YANGTAB')
---------------------------------------------------------------------------
  CREATE TABLE "YANG"."YANGTAB"
   (    "ID" NUMBER,
        "NAME" VARCHAR2(15),
         PRIMARY KEY ("ID")
  USING INDEX PCTFREE 10 INITRANS 2 MAXTRANS 255 COMPUTE STATISTICS
  STORAGE(INITIAL 65536 NEXT 1048576 MINEXTENTS 1 MAXEXTENTS 2147483645
  PCTINCREASE 0 FREELISTS 1 FREELIST GROUPS 1 BUFFER_POOL DEFAULT)
  TABLESPACE "USERS"  ENABLE
   ) 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 "USERS"
檢視錶空間的DDL 語句。
sys@rac1>select dbms_metadata.get_ddl('TABLESPACE', 'USERS') FROM DUAL;
DBMS_METADATA.GET_DDL('TABLESPACE','USERS')
---------------------------------------------------------------------------
  CREATE TABLESPACE "USERS" DATAFILE
  'D:\ORACLE\ORADATA\ORACL\USERS01.DBF' SIZE 5242880
  AUTOEXTEND ON NEXT 1310720 MAXSIZE 32767M,
  'D:\ORACLE\ORADATA\ORACL\USERS02.DBF' SIZE 943718400
  LOGGING ONLINE PERMANENT BLOCKSIZE 8192
  EXTENT MANAGEMENT LOCAL AUTOALLOCATE SEGMENT SPACE MANAGEMENT AUTO
   ALTER DATABASE DATAFILE
  'D:\ORACLE\ORADATA\ORACL\USERS01.DBF' RESIZE 347340800
sys@rac1>select dbms_metadata.get_ddl('TABLESPACE', 'EXAMPLE') FROM DUAL;
DBMS_METADATA.GET_DDL('TABLESPACE','EXAMPLE')
---------------------------------------------------------------------------
  CREATE TABLESPACE "EXAMPLE" DATAFILE
  'D:\ORACLE\ORADATA\ORACL\EXAMPLE01.DBF' SIZE 104857600
  AUTOEXTEND ON NEXT 655360 MAXSIZE 32767M
  NOLOGGING ONLINE PERMANENT BLOCKSIZE 8192
  EXTENT MANAGEMENT LOCAL AUTOALLOCATE SEGMENT SPACE MANAGEMENT AUTO
   ALTER DATABASE DATAFILE
  'D:\ORACLE\ORADATA\ORACL\EXAMPLE01.DBF' RESIZE 314572800
檢視 使用者的定義語句。
sys@rac1>select dbms_metadata.get_ddl('USER', 'YANG') FROM DUAL;
DBMS_METADATA.GET_DDL('USER','YANG')
---------------------------------------------------------------------------
   CREATE USER "YANG" IDENTIFIED BY VALUES 'S:269264CD30B5AC166D2C9882AB88F
DA20'
      DEFAULT TABLESPACE "USERS"
      TEMPORARY TABLESPACE "TEMP"
檢視 索引的定義語句:
yang@rac1>select index_name ,table_name from user_indexes;

INDEX_NAME                     TABLE_NAME
------------------------------ ------------------------------
SYS_C0010473                   YANG_B
SYS_C0010476                   YANG_A
SYS_C0010278                   YANGTAB2
SYS_C0010277                   YANGTAB
SYS_C0010286                   MV_YANGTAB2
SYS_C0010285                   MV_YANGTAB
ID_SEQNO                       DA_TEST
yang@rac1>select dbms_metadata.get_ddl('INDEX','ID_SEQNO') FROM DUAL;
DBMS_METADATA.GET_DDL('INDEX','ID_SEQNO')
--------------------------------------------------------------------------------
  CREATE INDEX "YANG"."ID_SEQNO" ON "YANG"."DA_TEST" ("SEQNO")
  PCTFREE 10 INITRANS 2 MAXTRANS 255 COMPUTE STATISTICS
  STORAGE(INITIAL 65536 NEXT 1048576 MINEXTENTS 1 MAXEXTENTS 2147483645
  PCTINCREASE 0 FREELISTS 1 FREELIST GROUPS 1 BUFFER_POOL DEFAULT)
  TABLESPACE "USERS"
yang@rac1>select dbms_metadata.get_ddl('INDEX','SYS_C0010285') FROM DUAL;
DBMS_METADATA.GET_DDL('INDEX','SYS_C0010285')
--------------------------------------------------------------------------------
  CREATE UNIQUE INDEX "YANG"."SYS_C0010285" ON "YANG"."MV_YANGTAB" ("ID")
  PCTFREE 10 INITRANS 2 MAXTRANS 255 COMPUTE STATISTICS
  STORAGE(INITIAL 65536 NEXT 1048576 MINEXTENTS 1 MAXEXTENTS 2147483645
  PCTINCREASE 0 FREELISTS 1 FREELIST GROUPS 1 BUFFER_POOL DEFAULT)
  TABLESPACE "USERS"

來自 “ ITPUB部落格 ” ,連結:http://blog.itpub.net/22664653/viewspace-692641/,如需轉載,請註明出處,否則將追究法律責任。

相關文章