using DBMS_METADATA.GET_DDL

The DBMS_METADATA package provides a way for you to retrieve metadata from the database dictionary as XML or creation DDL and to submit the XML to re-create the object.

Today I need to show DBMS_METADATA.GET_DDL only.

DBMS_METADATA.GET_DDL using return metadata for single named object.

Example:

SQL> SELECT DBMS_METADATA.GET_DDL('TABLESPACE','USERS') FROM dual;

DBMS_METADATA.GET_DDL('TABLESPACE','USERS')
------------------------------------------------

CREATE TABLESPACE "USERS" DATAFILE
'+DATA/db/datafile/users.262.634505065' SIZE 5242880
AUTOEXTEND ON NEXT 1310720 MAXSIZE 131071M
LOGGING ONLINE PERMANENT BLOCKSIZE 32768
EXTENT MANAGEMENT LOCAL AUTOALLOCATE SEGMENT SPACE MANAGEMENT
AUTO


SQL> SELECT DBMS_METADATA.GET_DDL('TABLE', 'AUD$') FROM dual;


DBMS_METADATA.GET_DDL('TABLE','AUD$')

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

CREATE TABLE "SYS"."AUD$"
( "SESSIONID" NUMBER NOT NULL ENABLE,
"ENTRYID" NUMBER NOT NULL ENABLE,
"STATEMENT" NUMBER NOT NULL ENABLE, "
TIMESTAMP#" DATE, "USERID" VARCHAR2(30),
"USERHOST" VARCHAR2(128), "TERMINAL" VARCHAR2(255),

.
.
.

TABLESPACE "SYSTEM" ENABLE STORAGE IN ROW CHUNK 32768
PCTVERSION 10 NOCACHE LOGGING STORAGE(INITIAL 98304
NEXT 1048576 MINEXTENTS 1 MAXEXTENTS 2147483645
PCTINCREASE 0 FREELISTS 1 FREELIST GROUPS 1
BUFFER_POOL DEFAULT))

.......................................................