Hi All,
To get DDL for any object, we have DBMS_METADATA. Let say we have to get DDL for a table named JAY_TEST which is owned by SCOTT schema.
Query would be : select DBMS_METADATA.get_ddl('TABLE','JAY_TEST','SCOTT') txt from dual;
More of the time, new DBA’s don’t like to use this DBMS’s ..and like to use traditional EXP/IMP. Well this can also be used to get DDL for any object. Let see how.
padwsdpr pacdcorwprdd1 /home/oracle/amddba/dba/local/bin > exp / rows=n compress=n tables=SCOTT.JAY_TEST file=exp.dmp log=exp.log buffer=10240000
Export: Release 11.1.0.7.0 - Production on Wed Feb 15 07:06:24 2012
Copyright (c) 1982, 2007, Oracle. All rights reserved.
Connected to: Oracle Database 11g Enterprise Edition Release 11.1.0.7.0 - 64bit Production
With the Partitioning, OLAP, Data Mining and Real Application Testing options
Export done in WE8ISO8859P1 character set and AL16UTF16 NCHAR character set
server uses WE8MSWIN1252 character set (possible charset conversion)
Note: table data (rows) will not be exported
About to export specified tables via Conventional Path ...
Current user changed to SCOTT
. . exporting table JAY_TEST
EXP-00091: Exporting questionable statistics.
EXP-00091: Exporting questionable statistics.
EXP-00091: Exporting questionable statistics.
Export terminated successfully with warnings.
padwsdpr pacdcorwprdd1 /home/oracle/amddba/dba/local/bin > imp / rows=n full=y ignore=y show=y file=exp.dmp log=exp.log buffer=10240000
Import: Release 11.1.0.7.0 - Production on Wed Feb 15 07:07:03 2012
Copyright (c) 1982, 2007, Oracle. All rights reserved.
Connected to: Oracle Database 11g Enterprise Edition Release 11.1.0.7.0 - 64bit Production
With the Partitioning, OLAP, Data Mining and Real Application Testing options
Export file created by EXPORT:V11.01.00 via conventional path
import done in WE8ISO8859P1 character set and AL16UTF16 NCHAR character set
import server uses WE8MSWIN1252 character set (possible charset conversion)
. importing OPS$AMDDBA's objects into OPS$AMDDBA
. importing SCOTT's objects into SCOTT
"ALTER SESSION SET CURRENT_SCHEMA= "SCOTT""
"CREATE TABLE "JAY_TEST" ("EVENT_DW_ID" NUMBER NOT NULL ENABLE, "CORP_ID" NU"
"MBER NOT NULL ENABLE, "ROYALTY_AMOUNT" NUMBER, "ROYALTY_FACTOR_ID" NUMBER N"
"OT NULL ENABLE, "CHANNEL_ID" NUMBER NOT NULL ENABLE, "EVENT_DISTRIBUTOR_DW_"
"ID" NUMBER NOT NULL ENABLE, "EVENT_RATING_ID" NUMBER NOT NULL ENABLE, "CORP"
"_EVENT_SUBJECT_DW_ID" NUMBER NOT NULL ENABLE, "CORP_STUDIO_DW_ID" NUMBER NO"
"T NULL ENABLE, "EVENT_TYPE_ID" NUMBER NOT NULL ENABLE, "EVENT_STATUS_ID" NU"
"MBER NOT NULL ENABLE, "EVENT_DESC" VARCHAR2(20), "FIRST_ORDER_DATE" DATE NO"
"T NULL ENABLE, "FIRST_ORDER_TIME" VARCHAR2(12), "LAST_ORDER_DATE" DATE NOT "
"NULL ENABLE, "LAST_ORDER_TIME" VARCHAR2(12), "DATE_ID" DATE NOT NULL ENABLE"
", "HOUR_ID" NUMBER NOT NULL ENABLE, "START_TIME" VARCHAR2(12), "STOP_DATE" "
"DATE NOT NULL ENABLE, "STOP_TIME" VARCHAR2(12), "FREE_MINUTES" NUMBER, "EVE"
"NT_DISCOUNT_ID" NUMBER NOT NULL ENABLE, "EVENT_NO_ORDER_FLAG" NUMBER NOT NU"
"LL ENABLE, "SS_EVENT_ID" NUMBER NOT NULL ENABLE, "SS_SHOW_ID" NUMBER, "SS_S"
"TATUS" NUMBER, "EVENT_CNT" NUMBER, "EVENT_LEVEL_1_ID" NUMBER, "EVENT_EFF_DA"
"TE" TIMESTAMP (6) NOT NULL ENABLE, "EVENT_END_DATE" TIMESTAMP (6) NOT NULL "
"ENABLE, "DW_ADD_DATE" TIMESTAMP (6) NOT NULL ENABLE, "DW_UPDATE_DATE" TIMES"
"TAMP (6) NOT NULL ENABLE, "BASE_EVENT" VARCHAR2(5)) PCTFREE 10 PCTUSED 40 "
"INITRANS 1 MAXTRANS 255 STORAGE(INITIAL 65536 NEXT 1048576 MINEXTENTS 1 FRE"
"ELISTS 1 FREELIST GROUPS 1 BUFFER_POOL DEFAULT) TABLESPACE "SGDATA" LOGGING"
" NOCOMPRESS PARALLEL ( DEGREE DEFAULT INSTANCES DEFAULT)"
" ALTER TABLE "JAY_TEST" MODIFY ("ROYALTY_AMOUNT" DEFAULT 0)"
" ALTER TABLE "JAY_TEST" MODIFY ("FREE_MINUTES" DEFAULT 0)"
" ALTER TABLE "JAY_TEST" MODIFY ("SS_SHOW_ID" DEFAULT 0)"
" ALTER TABLE "JAY_TEST" MODIFY ("EVENT_CNT" DEFAULT 1)"
" ALTER TABLE "JAY_TEST" MODIFY ("EVENT_LEVEL_1_ID" DEFAULT 0)"
"GRANT DELETE ON "JAY_TEST" TO "SCOTT_READ_WRITE_ROLE""
"GRANT INSERT ON "JAY_TEST" TO "SCOTT_READ_WRITE_ROLE""
"GRANT SELECT ON "JAY_TEST" TO "SCOTT_READ_WRITE_ROLE""
"GRANT UPDATE ON "JAY_TEST" TO "SCOTT_READ_WRITE_ROLE""
"GRANT SELECT ON "JAY_TEST" TO "AMITAGA""
"CREATE UNIQUE INDEX "JAY_TEST_PK" ON "JAY_TEST" ("EVENT_DW_ID" ) PCTFREE 1"
"0 INITRANS 2 MAXTRANS 255 STORAGE(INITIAL 65536 NEXT 1048576 MINEXTENTS 1 F"
"REELISTS 1 FREELIST GROUPS 1 BUFFER_POOL DEFAULT) TABLESPACE "SGDATA" LOGGI"
"NG PARALLEL ( DEGREE 12 INSTANCES 1)"
"CREATE INDEX "JAY_TEST_IXN1" ON "JAY_TEST" ("SS_EVENT_ID" , "CORP_ID" ) PC"
"TFREE 10 INITRANS 2 MAXTRANS 255 STORAGE(INITIAL 65536 NEXT 1048576 MINEXTE"
"NTS 1 FREELISTS 1 FREELIST GROUPS 1 BUFFER_POOL DEFAULT) TABLESPACE "SGINDE"
"X" LOGGING"
"ALTER SESSION SET CURRENT_SCHEMA= "SCOTT""
"ALTER TABLE "JAY_TEST" ADD CONSTRAINT "JAY_TEST_PK" PRIMARY KEY ("EVENT_DW"
"_ID") USING INDEX PCTFREE 10 INITRANS 2 MAXTRANS 255 STORAGE(INITIAL 65536 "
"NEXT 1048576 MINEXTENTS 1 FREELISTS 1 FREELIST GROUPS 1 BUFFER_POOL DEFAULT"
") TABLESPACE "SGDATA" LOGGING ENABLE "
"ALTER TABLE "JAY_TEST" ADD CONSTRAINT "JAY_TEST_FK8" FOREIGN KEY ("EVENT_TY"
"PE_ID") REFERENCES "JAY_TEST_TYPE" ("EVENT_TYPE_ID") ENABLE NOVALIDATE"
"ALTER TABLE "JAY_TEST" ADD CONSTRAINT "JAY_TEST_FK9" FOREIGN KEY ("EVENT_ST"
"ATUS_ID") REFERENCES "JAY_TEST_STATUS" ("EVENT_STATUS_ID") ENABLE NOVALIDAT"
"E"
"ALTER TABLE "JAY_TEST" ADD CONSTRAINT "JAY_TEST_FK4" FOREIGN KEY ("EVENT_DI"
"STRIBUTOR_DW_ID") REFERENCES "JAY_TEST_DISTRIBUTOR" ("EVENT_DISTRIBUTOR_DW_"
"ID") ENABLE NOVALIDATE"
"ALTER TABLE "JAY_TEST" ADD CONSTRAINT "JAY_TEST_FK10" FOREIGN KEY ("EVENT_D"
"ISCOUNT_ID") REFERENCES "JAY_TEST_DISCOUNT" ("EVENT_DISCOUNT_ID") ENABLE NO"
"VALIDATE"
"ALTER TABLE "JAY_TEST" ADD CONSTRAINT "JAY_TEST_FK7" FOREIGN KEY ("CORP_STU"
"DIO_DW_ID") REFERENCES "LU_CORP_STUDIO" ("CORP_STUDIO_DW_ID") ENABLE NOVALI"
"DATE"
"ALTER TABLE "JAY_TEST" ADD CONSTRAINT "JAY_TEST_FK6" FOREIGN KEY ("CORP_EVE"
"NT_SUBJECT_DW_ID") REFERENCES "LU_CORP_EVENT_SUBJECT" ("CORP_EVENT_SUBJECT_"
"DW_ID") ENABLE NOVALIDATE"
"ALTER TABLE "JAY_TEST" ADD CONSTRAINT "JAY_TEST_FK1" FOREIGN KEY ("CORP_ID""
") REFERENCES "LU_CORP" ("CORP_ID") ENABLE NOVALIDATE"
"ALTER TABLE "JAY_TEST" ADD CONSTRAINT "JAY_TEST_FK3" FOREIGN KEY ("CHANNEL_"
"ID") REFERENCES "LU_CHANNEL" ("CHANNEL_ID") ENABLE NOVALIDATE"
"ALTER TABLE "JAY_TEST" ENABLE CONSTRAINT "JAY_TEST_FK8""
"ALTER TABLE "JAY_TEST" ENABLE CONSTRAINT "JAY_TEST_FK9""
"ALTER TABLE "JAY_TEST" ENABLE CONSTRAINT "JAY_TEST_FK4""
"ALTER TABLE "JAY_TEST" ENABLE CONSTRAINT "JAY_TEST_FK10""
"ALTER TABLE "JAY_TEST" ENABLE CONSTRAINT "JAY_TEST_FK7""
"ALTER TABLE "JAY_TEST" ENABLE CONSTRAINT "JAY_TEST_FK6""
"ALTER TABLE "JAY_TEST" ENABLE CONSTRAINT "JAY_TEST_FK1""
"ALTER TABLE "JAY_TEST" ENABLE CONSTRAINT "JAY_TEST_FK3""
Import terminated successfully without warnings.
padwsdpr pacdcorwprdd1 /home/oracle/amddba/dba/local/bin >
No comments:
Post a Comment