Generating SQL
Instead of importing the data and objects, you can generate the SQL for the objects (without the
data) and store it in a file on your operating system. The file will be written to the directory and
filename specified via the SQLFILE option. The SQLFILE option format is as follows:
SQLFILE=[directory_object:]file_name
NOTE
If you do not specify a value for the directory_object variable, the file
will be created in the dump file directory.
Here is the same parameter file I used for the import earlier in this chapter, modified to create
the SQL only:
directory=dpxfer
dumpfile=metadata_only.dmp
sqlfile=sql.txt
?–
?–
?–
?–
?–
?–
?–
?–
410 Oracle Database 11g DBA Handbook
Notice that we do not need the content=metadata_only or the remap_schema parameter,
since all we want to do is to create SQL statements.
impdp rjb/rjb parfile=dp_rjb_import_sql.par
In the sql.txt file that the import process creates, you will see entries for each of the object
types within the schema. Here is an excerpt from the file:
-- CONNECT RJB
ALTER SESSION SET EDITION = "ORA$BASE";
. . .
-- new object type path: SCHEMA_EXPORT/TABLE/TABLE
CREATE TABLE "RJB"."EMPLOYEE_ARCHIVE"
( "EMPLOYEE_ID" NUMBER(6,0),
"FIRST_NAME" VARCHAR2(20),
"LAST_NAME" VARCHAR2(25) NOT NULL ENABLE,
"EMAIL" VARCHAR2(25) NOT NULL ENABLE,
"PHONE_NUMBER" VARCHAR2(20),
"HIRE_DATE" DATE NOT NULL ENABLE,
"JOB_ID" VARCHAR2(10) NOT NULL ENABLE,
"COMMISSION_PCT" NUMBER(2,2),
"MANAGER_ID" NUMBER(6,0),
"DEPARTMENT_ID" NUMBER(4,0)
) 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 "USERS" ;
-- new object type path: SCHEMA_EXPORT/TABLE/GRANT/OWNER_GRANT/OBJECT_GRANT
GRANT SELECT ON "RJB".
Pages:
614
615
616
617
618
619
620
621
622
623
624
625
626
627
628
629
630
631
632
633
634
635
636
637
638