Previous | Table of Contents | Next

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.
  1. Issue "ALTER USER USER_A QUOTA UNLIMITED ON TABLESPACE_B;". By giving an unlimited quota, the import will succeed, providing that TABLESPACE_B is large enough to handle all the database objects being imported.
  2. Import the database objects that were exported. By default, the Import utility attempts to import them into TABLESPACE_A. Because the user does not have a quota on that tablespace, however, the objects will be created in USER_A's default tablespace TABLESPACE_B.

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.

Using the SHOW and INDEXFILE Options

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 dependencies—that 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.lst—Sample 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.lst—SQL 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.LOG—Sample 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 ;

Previous | Table of Contents | Next