Page 326
tablespace of the user. Before Oracle can import the data into TABLESPACE_B, you must give a large enough quota on the tablespace to the USER_A user. This is shown in the following step.
The preceding steps show how you can use the Import and Export utilities, along with
knowledge of SQL, to do powerful operations on data with relative ease. One of the most useful
capabilities of the Import and Export utilities is the use of the
SHOW and INDEXFILE options,
as described in the following section.
You can use the SHOW parameter to generate all SQL statements used to create the database structure and all objects. This includes creating comments, tablespaces, users, privilege grants, roles and their assignments, quota definitions, rollback segments, sequences, tables, constraints, indexes, packages, procedures, partitions, user-defined datatypes, and so on.
One powerful use of the SHOW parameter is to create a script file that can re-create part or all
of the database. The statements are listed in the proper order of dependenciesthat is, a table
is created before an index, a foreign key that references a primary key is created after the
primary key, and so on. Listing 13.3 shows a sample portion of the output from
specifying SHOW=Y.
Listing 13.3CHP13_3.lstSample Portion of Importing with the SHOW=Y Specification
"ALTER SCHEMA = "QUE"" "CREATE UNIQUE INDEX "I_PRICE" ON "PRICE" ("PRODUCT_ID" , "START_DATE" ) PC" "TFREE 10 INITRANS 2 MAXTRANS 255 STORAGE (INITIAL 10240 NEXT 10240 MINEXTEN" "TS 1 MAXEXTENTS 121 PCTINCREASE 50 FREELISTS 1) TABLESPACE "USER_DATA" LOGG" "ING" "ALTER TABLE "PRICE" ADD CHECK (PRODUCT_ID IS NOT NULL) ENABLE" "ALTER TABLE "PRICE" ADD CHECK (START_DATE IS NOT NULL) ENABLE" "ALTER TABLE "PRICE" ADD CHECK (LIST_PRICE IS NULL OR MIN_PRICE IS NULL OR " "MIN_PRICE <= LIST_PRICE) ENABLE" "ALTER TABLE "PRICE" ADD CHECK (END_DATE IS NULL OR START_DATE <= END_DATE)" " ENABLE" "ALTER TABLE "PRICE" ADD PRIMARY KEY ("PRODUCT_ID","START_DATE") ENABLE" "GRANT SELECT ON "PRICE" TO PUBLIC" "ALTER SCHEMA = "QUE"" "COMMENT ON TABLE "PRICE" IS `Prices (both standard and minimum) of product" "s. Database tracks both effective dates and expiration dates for prices.'"
Page 327
"COMMENT ON COLUMN "PRICE"."PRODUCT_ID" IS `Product number to which price a" "pplies. Product name found in table PRICE.'" "COMMENT ON COLUMN "PRICE"."LIST_PRICE" IS `Undiscounted price (in U.S.dol" "lars).'" "ALTER TABLE "PRICE" ADD FOREIGN KEY ("PRODUCT_ID") REFERENCES "PRODUCT" ("P" "RODUCT_ID") ENABLE"
To make a file from the results, specify the LOG=filename parameter specification. This file may be modified to change almost any aspect of the database. Each line begins and ends with a quotation mark. Be sure to string these quotation marks from the beginning and ending of each line. Additionally, Oracle does not word-wrap lines in the output. This results in having statements with the likelihood of words and numbers being cut in two. To remedy this, you must manually join the lines in each statement. The sample listing, shown in the preceding listing, could be cleaned up to look like Listing 13.4.
Listing 13.4CHP13_4.lstSQL Statements Resulting from Cleaning Up the Import File Created with the SHOW=Y Specification
ALTER SCHEMA = "QUE"; CREATE UNIQUE INDEX "I_PRICE" ON "PRICE" ("PRODUCT_ID" , "START_DATE" ) PCTFREE 10 INITRANS 2 MAXTRANS 255 STORAGE (INITIAL 10240 NEXT 10240 MINEXTENTS 1 MAXEXTENTS 121 PCTINCREASE 50 FREELISTS 1) TABLESPACE "USER_DATA" LOGGING; ALTER TABLE "PRICE" ADD CHECK (PRODUCT_ID IS NOT NULL) ENABLE; ALTER TABLE "PRICE" ADD CHECK (START_DATE IS NOT NULL) ENABLE; ALTER TABLE "PRICE" ADD CHECK (LIST_PRICE IS NULL OR MIN_PRICE IS NULL OR MIN_PRICE <= LIST_PRICE) ENABLE; ALTER TABLE "PRICE" ADD CHECK (END_DATE IS NULL OR START_DATE <= END_DATE) ENABLE; ALTER TABLE "PRICE" ADD PRIMARY KEY (PRODUCT_ID,START_DATE) ENABLE; GRANT SELECT ON "PRICE" TO PUBLIC; COMMENT ON TABLE "PRICE" IS `Prices (both standard and minimum) of products. Database tracks both effective dates and expiration dates for prices.'; COMMENT ON COLUMN "PRICE"."PRODUCT_ID" IS `Product number to which price applies. Product name found in table PRICE.'; COMMENT ON COLUMN "PRICE"."LIST_PRICE" IS `Undiscounted price (in U.S. dollars).'; ALTER TABLE "PRICE" ADD FOREIGN KEY (PRODUCT_ID) REFERENCES PRODUCT (PRODUCT_ID) ENABLE;
You can use the INDEXFILE parameter to generate CREATE INDEX statements. The value of the INDEXFILE parameter specifies the name of the file to be created. By default, Oracle appends a .SQL extension unless otherwise specified. Generic table creation statements are shown, commented out so that they will not execute if the script is run. The INDEXFILE parameter does not generate CREATE primary key or unique key clauses. Listing 13.5 is a portion of the output file X.LOG from an import with INDEXFILE=X.LOG specified. Notice how Oracle word-wraps all lines
Page 328
appropriately and does not add quotation marks before and after each line. This allows for immediate use of the indexfile with no further modifications.
Listing 13.5X.LOGSample Portion of the X.LOG File Created from
Importing with the INDEXFILE=X.LOG Specification
REM CREATE TABLE "QUE"."PRICE" ("PRODUCT_ID" NUMBER(6, 0), "LIST_PRICE" REM NUMBER(8, 2), "MIN_PRICE" NUMBER(8, 2), "START_DATE" DATE, "END_DATE" REM DATE) PCTFREE 10 PCTUSED 40 INITRANS 1 MAXTRANS 255 LOGGING REM STORAGE(INITIAL 10240 NEXT 10240 MINEXTENTS 1 MAXEXTENTS 121 REM PCTINCREASE 50 FREELISTS 1 FREELIST GROUPS 1) TABLESPACE "USER_DATA" ; REM ... 58 rows CONNECT QUE; CREATE UNIQUE INDEX "QUE"."I_PRICE" ON "PRICE" ("PRODUCT_ID" , "START_DATE" ) PCTFREE 10 INITRANS 2 MAXTRANS 255 STORAGE (INITIAL 10240 NEXT 10240 MINEXTENTS 1 MAXEXTENTS 121 PCTINCREASE 50 FREELISTS 1) TABLESPACE "USER_DATA" LOGGING ;