Table, View, Tablespace Creation Script in Oracle

In order to get definitions of view, table ,tablespace etc we can use GET_DDL function of DBMS_METADATA package. The simple syntax is

DBMS_METADATA.GET_DDL (
object_type IN VARCHAR2,
name IN VARCHAR2,
schema IN VARCHAR2 DEFAULT NULL
)

If you don't use schema then default schema is used. Now we need to keep attention about object_type. Which object type can be specified that from which type of objects we can extract ddl from database. Foe example we can't give DATABASE EXPORT or SCHEMA EXPORT or TABLE_EXPORT or TRANSPORTABLE_EXPORT as object_type. I give here the list of objects from which we can extract ddl.

AQ_QUEUE
AQ_QUEUE_TABLE
AQ_TRANSFORM
ASSOCIATION
AUDIT
AUDIT_OBJ
CLUSTER
COMMENT
CONSTRAINT
CONTEXT
DB_LINK
DEFAULT_ROLE
DIMENSION
DIRECTORY
FGA_POLICY
FUNCTION
INDEX_STATISTICS
INDEX
INDEXTYPE
JAVA_SOURCE
JOB
LIBRARY
MATERIALIZED_VIEW
MATERIALIZED_VIEW_LOG
OBJECT_GRANT
OPERATOR
OUTLINE
PACKAGE
PACKAGE_SPEC
PACKAGE_BODY
PROCEDURE
PROFILE
PROXY
REF_CONSTRAINT
REFRESH_GROUP
RESOURCE_COST
RLS_CONTEXT
RLS_GROUP
RLS_POLICY
RMGR_CONSUMER_GROUP
RMGR_INTITIAL_CONSUMER_GROUP
RMGR_PLAN
RMGR_PLAN_DIRECTIVE
ROLE
ROLE_GRANT
ROLLBACK_SEGMENT
SEQUENCE
SYNONYM
SYSTEM_GRANT
TABLE
TABLE_DATA
TABLE_STATISTICS
TABLESPACE
TABLESPACE_QUOTA
TRIGGER
TRUSTED_DB_LINK
TYPE
TYPE_SPEC
TYPE_BODY
USER
VIEW
XMLSCHEMA

Examples:
-------------------

1)To get ddl of Resource Role you can use,
SQL> select dbms_metadata.get_ddl('ROLE','DBA') FROM DUAL;

DBMS_METADATA.GET_DDL('ROLE','DBA')
--------------------------------------------------------------------------------

CREATE ROLE "DBA"

2)To get definition of TEST table in my current schema I can use,

SQL> SET LONG 9999999
SQL> SELECT DBMS_METADATA.GET_DDL('TABLE','TEST') FROM DUAL;

DBMS_METADATA.GET_DDL('TABLE','TEST')
--------------------------------------------------------------------------------

CREATE TABLE "SYS"."TEST"
( "A" NUMBER,
"B" VARCHAR2(10),
"C" BLOB,
PRIMARY KEY ("A")
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"
LOB ("C") STORE AS (
TABLESPACE "SYSTEM" ENABLE STORAGE IN ROW CHUNK 8192 PCTVERSION 10
NOCACHE LOGGING
STORAGE(INITIAL 65536 NEXT 1048576 MINEXTENTS 1 MAXEXTENTS 2147483645
PCTINCREASE 0 FREELISTS 1 FREELIST GROUPS 1 BUFFER_POOL DEFAULT))

3)To get creation objects or DDL of a view named test_v stored in SYSTEM schema you can use,
SQL> SELECT DBMS_METADATA.GET_DDL('VIEW','TEST_V','SYSTEM') FROM DUAL;

DBMS_METADATA.GET_DDL('VIEW','TEST_V','SYSTEM')
--------------------------------------------------------------------------------

CREATE OR REPLACE FORCE VIEW "SYSTEM"."TEST_V" ("USERNAME") AS
select username from dba_users

4)Similarly you can get ddl of the listed above object type. To get ddl of TABS synonym you can use. In this example I at first queried to know the owner of TABS synonym from dba_objects.

SQL> select owner from dba_objects where object_name='TABS' and object_type='SYNONYM';

OWNER
------------------------------
PUBLIC

SQL> SELECT DBMS_METADATA.GET_DDL('SYNONYM','TABS','PUBLIC') FROM DUAL;

DBMS_METADATA.GET_DDL('SYNONYM','TABS','PUBLIC')
--------------------------------------------------------------------------------

CREATE OR REPLACE PUBLIC SYNONYM "TABS" FOR "SYS"."USER_TABLES"
Related Documents:
-------------------------
Get DDL from Database Objects
Get DDL from dump
Use of SET LONG