Metadata API (DBMS_METADATA)

Prior to Oracle9i metadata could only be extracted using SQL statements, export utilities and the OCIDescribeAny interface, all of which are limited.

The SQL approach is limited in that as versions change, so must your scripts.
Using export with ROWS=N and an import with SHOW=Y will produce the text to allow you to recreate a schema, but it can require considerable editing.
The OCIDescribeAny interface is limited in the objects it supports.

To solve these issues Oracle9i has introduced the DBMS_METADATA package which can be used to retrieve object definitions as DDL or XML. There are a number of ways to use the API, but I suspect the majority of people will only ever use the following functions.

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 NULL)
RETURN CLOB;

FUNCTION get_dependent_ddl (
  object_type         IN VARCHAR2,
  base_object_name    IN VARCHAR2,
  base_object_schema  IN VARCHAR2 DEFAULT NULL,
  version             IN VARCHAR2 DEFAULT 'COMPATIBLE',
  model               IN VARCHAR2 DEFAULT 'ORACLE',
  transform           IN VARCHAR2 DEFAULT 'DDL',
  object_count        IN NUMBER   DEFAULT 10000)
RETURN CLOB;

FUNCTION get_xml (
  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 NULL)
RETURN CLOB;

These functions can be used as part of a select statement to produce output for groups of objects, such as object creation scripts, like the one shown below.

CONN scott/tiger

SET LONG 20000
SET PAGESIZE 0

SELECT DBMS_METADATA.get_ddl (object_type, object_name, USER)
FROM   user_objects;

  CREATE TABLE "SCOTT"."BONUS"
   (	"ENAME" VARCHAR2(10),
	"JOB" VARCHAR2(9),
	"SAL" NUMBER,
	"COMM" NUMBER
   ) 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 "SYSTEM"



  CREATE TABLE "SCOTT"."DEPT"
   (	"DEPTNO" NUMBER(2,0),
	"DNAME" VARCHAR2(14),
	"LOC" VARCHAR2(13),
	 CONSTRAINT "PK_DEPT" PRIMARY KEY ("DEPTNO")
  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 "SYSTEM"  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 "SYSTEM"



  CREATE TABLE "SCOTT"."EMP"
   (	"EMPNO" NUMBER(4,0),
	"ENAME" VARCHAR2(10),
	"JOB" VARCHAR2(9),
	"MGR" NUMBER(4,0),
	"HIREDATE" DATE,
	"SAL" NUMBER(7,2),
	"COMM" NUMBER(7,2),
	"DEPTNO" NUMBER(2,0),
	 CONSTRAINT "PK_EMP" PRIMARY KEY ("EMPNO")
  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 "SYSTEM"  ENABLE,
	 CONSTRAINT "FK_DEPTNO" FOREIGN KEY ("DEPTNO")
	  REFERENCES "SCOTT"."DEPT" ("DEPTNO") 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 "SYSTEM"



  CREATE UNIQUE INDEX "SCOTT"."PK_DEPT" ON "SCOTT"."DEPT" ("DEPTNO")
  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 "SYSTEM"



  CREATE UNIQUE INDEX "SCOTT"."PK_EMP" ON "SCOTT"."EMP" ("EMPNO")
  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 "SYSTEM"



  CREATE TABLE "SCOTT"."SALGRADE"
   (	"GRADE" NUMBER,
	"LOSAL" NUMBER,
	"HISAL" NUMBER
   ) 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 "SYSTEM"

6 rows selected.

SQL>

The results can be spooled into a file for editing.

For more information see:

DBMS_METADATA

Hope this helps. Regards Tim...

Back to the Top.