ITPub博客

首页 > Linux操作系统 > Linux操作系统 > 使用dbms_metadata.get_ddl查看对象的定义语句。

使用dbms_metadata.get_ddl查看对象的定义语句。

原创 Linux操作系统 作者:杨奇龙 时间:2011-04-15 20:44:01 0 删除 编辑
当我们想要查看某个表或者是表空间的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/,如需转载,请注明出处,否则将追究法律责任。

下一篇: 【转】fuser命令
请登录后发表评论 登录
全部评论
MySQL DBA NoSQL DEVOPS

注册时间:2009-10-07

  • 博文量
    1026
  • 访问量
    7717009