| Exporting the database to text format [message #72228] |
Thu, 17 April 2003 06:58  |
Bala chander
Messages: 1
Registered: April 2003
|
Junior Member |
|
|
Using the export command, I want to export all my database object in ASCII mode ( not in the binary mode)i.e. I want CREATE TABLE , CREATE PROCEDURE statement inside the dumb file as text format. Whether it is possible to do it using exp command or any other method to do it (without using any other GUI tools)
Thanks
Bala
|
|
|
|
|
|
| Re: Exporting the database to text format [message #72243 is a reply to message #72228] |
Tue, 22 April 2003 15:01  |
Aaron Smith
Messages: 1
Registered: April 2003
|
Junior Member |
|
|
Have you tried using the "INDEXFILE" option on the import utility to get the acutal CREATE TABLE, etc... statements??
C:>exp userid=evaldba/evaldba@eval file=c:owner.dmp tables=course, program, evaluation rows=n log=c:eval.log
Export: Release 9.2.0.2.1 - Production on Tue Apr 22 14:35:37 2003
Copyright (c) 1982, 2002, Oracle Corporation. All rights reserved.
Connected to: Oracle9i Enterprise Edition Release 9.2.0.2.1 - Production
With the Partitioning, OLAP and Oracle Data Mining options
JServer Release 9.2.0.2.0 - Production
Export done in WE8MSWIN1252 character set and AL16UTF16 NCHAR character set
server uses US7ASCII character set (possible charset conversion)
Note: table data (rows) will not be exported
About to export specified tables via Conventional Path ...
. . exporting table COURSE
. . exporting table PROGRAM
. . exporting table EVALUATION
Export terminated successfully without warnings.
Now import the file using the INDEXFILE option
C:>imp evaldba/evaldba@eval file=c:owner.dmp indexfile=c:owner.sql tables= course, program, evaluation
Import: Release 9.2.0.2.1 - Production on Tue Apr 22 14:39:09 2003
Copyright (c) 1982, 2002, Oracle Corporation. All rights reserved.
Connected to: Oracle9i Enterprise Edition Release 9.2.0.2.1 - Production
With the Partitioning, OLAP and Oracle Data Mining options
JServer Release 9.2.0.2.0 - Production
Export file created by EXPORT:V09.02.00 via conventional path
import done in WE8MSWIN1252 character set and AL16UTF16 NCHAR character set
import server uses US7ASCII character set (possible charset conversion)
Import terminated successfully without warnings.
Now when I open the file "owner.sql" that was created with the indexfile option I get the following:
REM CREATE TABLE "EVALDBA"."COURSE" ("COURSE_PK" NUMBER(9, 0),
REM "COURSE_NUM" VARCHAR2(10) CONSTRAINT "COURSE_NUM_NN" NOT NULL ENABLE,
REM "NAME" VARCHAR2(100) CONSTRAINT "COURSE_NAME_NN" NOT NULL ENABLE,
REM "PROG_ID" NUMBER(6, 0)) PCTFREE 10 PCTUSED 80 INITRANS 1 MAXTRANS 255
REM STORAGE(INITIAL 57344 FREELISTS 1 FREELIST GROUPS 1) TABLESPACE
REM "DATA01" LOGGING NOCOMPRESS ;
CONNECT EVALDBA;
CREATE INDEX "EVALDBA"."COURSE_PK" ON "COURSE" ("COURSE_PK" ) PCTFREE 10
INITRANS 2 MAXTRANS 255 STORAGE(INITIAL 32768 FREELISTS 1 FREELIST GROUPS
1) TABLESPACE "INDX01" LOGGING ;
REM ALTER TABLE "EVALDBA"."COURSE" ADD CONSTRAINT "COURSE_PK" PRIMARY KEY
REM ("COURSE_PK") DEFERRABLE INITIALLY DEFERRED USING INDEX PCTFREE 10
REM INITRANS 2 MAXTRANS 255 STORAGE(INITIAL 32768 FREELISTS 1 FREELIST
REM GROUPS 1) TABLESPACE "INDX01" LOGGING ENABLE ;
REM CREATE TABLE "EVALDBA"."PROGRAM" ("PROG_ID" NUMBER(6, 0), "PROG_NAME"
REM VARCHAR2(100) CONSTRAINT "PROGRAM_NAME_NN" NOT NULL ENABLE, "ABBR"
REM VARCHAR2(10), "DEPT_ID" NUMBER(9, 0)) PCTFREE 10 PCTUSED 80 INITRANS
REM 1 MAXTRANS 255 STORAGE(INITIAL 32768 FREELISTS 1 FREELIST GROUPS 1)
REM TABLESPACE "DATA01" LOGGING NOCOMPRESS ;
CREATE INDEX "EVALDBA"."PROGRAM_PROG_ID_PK" ON "PROGRAM" ("PROG_ID" )
PCTFREE 10 INITRANS 2 MAXTRANS 255 STORAGE(INITIAL 32768 FREELISTS 1
FREELIST GROUPS 1) TABLESPACE "INDX01" LOGGING ;
REM ALTER TABLE "EVALDBA"."PROGRAM" ADD CONSTRAINT "PROGRAM_PROG_ID_PK"
REM PRIMARY KEY ("PROG_ID") DEFERRABLE INITIALLY DEFERRED USING INDEX
REM PCTFREE 10 INITRANS 2 MAXTRANS 255 STORAGE(INITIAL 32768 FREELISTS 1
REM FREELIST GROUPS 1) TABLESPACE "INDX01" LOGGING ENABLE ;
REM CREATE TABLE "EVALDBA"."EVALUATION" ("EVAL_ID" NUMBER(9, 0),
REM "EVAL_DATE" DATE, "INSTRUCT_ID" NUMBER(9, 0), "COURSE_PK" NUMBER(9,
REM 0), "LOGINAME" VARCHAR2(10), "COURSEPASS" CHAR(8)) PCTFREE 10 PCTUSED
REM 80 INITRANS 1 MAXTRANS 255 STORAGE(INITIAL 57344 FREELISTS 1 FREELIST
REM GROUPS 1) TABLESPACE "DATA01" LOGGING NOCOMPRESS ;
CREATE INDEX "EVALDBA"."EVALUATION_ID_PK" ON "EVALUATION" ("EVAL_ID" )
PCTFREE 10 INITRANS 2 MAXTRANS 255 STORAGE(INITIAL 32768 FREELISTS 1
FREELIST GROUPS 1) TABLESPACE "INDX01" LOGGING ;
REM ALTER TABLE "EVALDBA"."EVALUATION" ADD CONSTRAINT "EVALUATION_ID_PK"
REM PRIMARY KEY ("EVAL_ID") DEFERRABLE INITIALLY DEFERRED USING INDEX
REM PCTFREE 10 INITRANS 2 MAXTRANS 255 STORAGE(INITIAL 32768 FREELISTS 1
REM FREELIST GROUPS 1) TABLESPACE "INDX01" LOGGING ENABLE ;
REM ALTER TABLE "EVALDBA"."COURSE" ADD CONSTRAINT "COURSE_PROG_ID_FK"
REM FOREIGN KEY ("PROG_ID") REFERENCES "PROGRAM" ("PROG_ID") DEFERRABLE
REM INITIALLY DEFERRED ENABLE NOVALIDATE ;
REM ALTER TABLE "EVALDBA"."PROGRAM" ADD CONSTRAINT "PROGRAM_DEPT_ID_FK"
REM FOREIGN KEY ("DEPT_ID") REFERENCES "DEPARTMENT" ("DEPT_ID")
REM DEFERRABLE INITIALLY DEFERRED ENABLE NOVALIDATE ;
REM ALTER TABLE "EVALDBA"."EVALUATION" ADD CONSTRAINT
REM "EVAL_INSTRUCT_ID_FK" FOREIGN KEY ("INSTRUCT_ID") REFERENCES
REM "INSTRUCTOR" ("INSTRUCT_ID") DEFERRABLE INITIALLY DEFERRED ENABLE
REM NOVALIDATE ;
REM ALTER TABLE "EVALDBA"."EVALUATION" ADD CONSTRAINT "EVAL_COURSE_PK_FK"
REM FOREIGN KEY ("COURSE_PK") REFERENCES "COURSE" ("COURSE_PK")
REM DEFERRABLE INITIALLY DEFERRED ENABLE NOVALIDATE ;
REM ALTER TABLE "EVALDBA"."COURSE" ENABLE CONSTRAINT "COURSE_PROG_ID_FK" ;
REM ALTER TABLE "EVALDBA"."PROGRAM" ENABLE CONSTRAINT
REM "PROGRAM_DEPT_ID_FK" ;
REM ALTER TABLE "EVALDBA"."EVALUATION" ENABLE CONSTRAINT
REM "EVAL_INSTRUCT_ID_FK" ;
REM ALTER TABLE "EVALDBA"."EVALUATION" ENABLE CONSTRAINT
REM "EVAL_COURSE_PK_FK" ;
If you have to make any changes do so here and jsut run the file like a regular script in oracle.
I hope this helps
Aaron
|
|
|
|
|
|
] 
Blog
Search
Help
Members
Register
Login
Home
