Extracts the DDL for creating a database object, using dbms_metadata.get_ddl
SQL> @metad
Object Types:
ASSOCIATION; AUDIT; AUDIT_OBJ; CLUSTER; COMMENT; CONSTRAINT; CONTEXT; DB_LINK; DEFAULT_ROLE; DIMENSION
DIRECTORY; FUNCTION; INDEX; INDEXTYPE; JAVA_SOURCE; LIBRARY; MATERIALIZED_VIEW; MATERIALIZED_VIEW_LOG
OBJECT_GRANT; OPERATOR; OUTLINE; PACKAGE; PACKAGE_SPEC; PACKAGE_BODY; PROCEDURE; PROFILE; PROXY; REF_CONSTRAINT
ROLE; ROLE_GRANT; ROLLBACK_SEGMENT; SEQUENCE; SYNONYM; SYSTEM_GRANT; TABLE; TABLESPACE; TABLESPACE_QUOTA; TRIGGER
TRUSTED_DB_LINK; TYPE; TYPE_SPEC; TYPE_BODY; USER; VIEW; XMLSCHEMA
Owner [user]: john
Object type [TABLE]:
Name: countries
CREATE TABLE "JOHN"."COUNTRIES"
( "ID" NUMBER NOT NULL ENABLE,
"IBCC_CODE" VARCHAR2(4) NOT NULL ENABLE,
"COUNTRY_NAME" VARCHAR2(50) NOT NULL ENABLE,
"POPULATION_COUNT" NUMBER DEFAULT 101,
"NUM_CITIES" NUMBER,
"CREATED_DATE" DATE,
"CREATED_BY" VARCHAR2(30),
"UPDATED_DATE" DATE,
"UPDATED_BY" VARCHAR2(30),
"GEN_COL" VARCHAR2(30),
"ADATE" DATE,
"REFERENCE_CODE" VARCHAR2(30),
"SEQUENCE_COLUMN" NUMBER,
"DEFAULT_CURRENCY" VARCHAR2(10),
"DATE_TWO" DATE,
"HIDDEN_COLUMN" VARCHAR2(10),
"CHANGE_TIMESTAMP" TIMESTAMP (6),
"CHECK_FOR" VARCHAR2(100),
CONSTRAINT "COUNTRIES_PK" PRIMARY KEY ("IBCC_CODE")
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 FLASH_CACHE DEFAULT CELL_FLASH_CACHE DEFAULT)
TABLESPACE "USERS" ENABLE
) SEGMENT CREATION IMMEDIATE
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 FLASH_CACHE DEFAULT CELL_FLASH_CACHE DEFAULT)
TABLESPACE "USERS"
-- Name : metad.sql
prompt
prompt returns DDL for creating a database object
prompt
-- Date last modified : 2015/09/29
-- Oracle Version: 11g
-- Other Oracle versions: should work on all current versions
----------------------------------------------------------------------------------------
set long 1000000
set feed off
set head off
prompt
prompt Object Types:
prompt
prompt ASSOCIATION; AUDIT; AUDIT_OBJ; CLUSTER; COMMENT; CONSTRAINT; CONTEXT; DB_LINK; DEFAULT_ROLE; DIMENSION;
prompt DIRECTORY; FUNCTION; INDEX; INDEXTYPE; JAVA_SOURCE; LIBRARY; MATERIALIZED_VIEW; MATERIALIZED_VIEW_LOG;
prompt OBJECT_GRANT; OPERATOR; OUTLINE; PACKAGE; PACKAGE_SPEC; PACKAGE_BODY; PROCEDURE; PROFILE; PROXY; REF_CONSTRAINT;
prompt ROLE; ROLE_GRANT; ROLLBACK_SEGMENT; SEQUENCE; SYNONYM; SYSTEM_GRANT; TABLE; TABLESPACE; TABLESPACE_QUOTA; TRIGGER;
prompt TRUSTED_DB_LINK; TYPE; TYPE_SPEC; TYPE_BODY; USER; VIEW; XMLSCHEMA
col user_t new_value user_s noprint
select user user_t from dual;
accept x_owner prompt "Owner [user]: " default '&user_s'
accept x_type prompt "Object type [TABLE]: " default 'TABLE'
accept x_name prompt "Name: "
col "Metadata" for a250
spool metad.rpt
select decode( upper( '&x_type' ),
'USER', dbms_metadata.get_ddl( upper( '&x_type' ), upper( '&x_name' ) ),
'PROFILE', dbms_metadata.get_ddl( upper( '&x_type' ), upper( '&x_name' ) ),
-- default:
dbms_metadata.get_ddl( upper( '&x_type' ), upper( '&x_name' ), upper( '&x_owner' ) )
) "Metadata"
from dual;
spool off
set long 10000