Table of Contents
This chapter briefly introduces the DBMS_METADATA package, and describes how to use the function of the package.
DBMS_METADATA provides a function for creating a DDL creation script by querying DB object metadata. Metadata is created by querying data directory information.
Creates a DDL creation script.
Details about the GET_DDL function are as follows:
Prototype
DBMS_METADATA.GET_DDL
(
object_type IN VARCHAR2,
name IN VARCHAR2,
schema IN VARCHAR2 DEFAULT NULL
)
RETURN CLOB;
Parameter
| Parameter | Description |
|---|---|
| object_type | Type of the object to extract metadata from. This function is currently available for tablespaces, tables, indexes, materialized views, materialized_view_logs, and constraints. |
| object_type | Type of the object to extract metadata from. This function is currently available for:
|
| name | Name of the object to extract metadata from. |
| schema | Object owner. |
Example
SQL> create table emp(
empno number(4,0),
ename varchar2(10),
job varchar2(9),
mgr number(4,0),
hiredate date
);
Table 'EMP' created.
select to_char(dbms_metadata.get_ddl('TABLE', 'EMP', 'MY_USER')) from dual
SQL> /
TO_CHAR(DBMS_METADATA.GET_DDL('TABLE','EMP','MY_USER'))
--------------------------------------------------------------------------------
CREATE TABLE MY_USER.EMP
(EMPNO NUMBER(4), ENAME VARCHAR(10), JOB VARCHAR(9), MGR NUMBER(4),
HIREDATE DATE)
TABLESPACE USR
PCTFREE 10
INITRANS 2
STORAGE(INITIAL 131072 NEXT 131072 MINEXTENTS 1 MAXEXTENTS 4294967295
BUFFER_POOL DEFAULT)
LOGGING
NOCOMPRESS
NOPARALLEL
DISABLE ROW MOVEMENT
;
1 row selected.
SQL> create table dept(
deptno number(2,0),
dname varchar2(14),
loc varchar2(13),
constraint pk_dept primary key (deptno)
); 2 3 4 5 6
Table 'DEPT' created.
SQL> select to_char(dbms_metadata.get_ddl('INDEX', 'PK_DEPT', 'MY_USER'))
from dual;
TO_CHAR(DBMS_METADATA.GET_DDL('INDEX','PK_DEPT','MY_USER'))
--------------------------------------------------------------------------------
CREATE UNIQUE INDEX MY_USER.PK_DEPT ON MY_USER.DEPT
(DEPTNO ASC)
LOGGING TABLESPACE USR
PCTFREE 10 INITRANS 2
STORAGE(INITIAL 131072 NEXT 131072 MINEXTENTS 1 MAXEXTENTS 4294967295
BUFFER_POOL DEFAULT);