Shulou(Shulou.com)05/31 Report--

Editor to share with you how to use the indexfile parameters of IMP, I believe most people do not know much about it, so share this article for your reference, I hope you can learn a lot after reading this article, let's go to know it!

The indexfile parameter of imp specifies the SQL script that maintains create index on import

Default value of indexfile parameter: none

Specifies that the file in which the index is created is stored. If CONSTRAINTS=y, the constraints are also stored in the index file. To make it easy to identify the index, the file has CREATE TABLE,CREATE CLUSTER statements. such as

When indexes=n

[oracle@lzl ~] $vi parfile_imp.txt

[oracle@lzl ~] $cat parfile_imp.txt

Userid= "scott/tiger"

File=/home/oracle/exp_lzl.dmp

Log=/home/oracle/imp_lzl.log

Buffer=102400

Tables=emp

Fromuser=scott

Touser=smith

Indexes=n

Indexfile=/home/oracle/imp_indexfile.sql

[oracle@lzl ~] $imp parfile=parfile_imp.txt

Import: Release 11.2.0.3.0-Production on Tue Jul 11 15:36:14 2017

Copyright (c) 1982, 2011, Oracle and/or its affiliates. All rights reserved.

Connected to: Oracle Database 11g Enterprise Edition Release 11.2.0.3.0-64bit Production

With the Partitioning, OLAP, Data Mining and Real Application Testing options

Export file created by EXPORT:V11.02.00 via conventional path

Warning: the objects were exported by SYS, not by you

Import done in ZHS16GBK character set and AL16UTF16 NCHAR character set

. . Skipping table "EMP"

Import terminated successfully without warnings.

[oracle@lzl ~] $cat imp_indexfile.sql

REM CREATE TABLE "SMITH". "EMP" ("EMPNO" NUMBER (4,0), "ENAME"

REM VARCHAR2 (10), "JOB" VARCHAR2 (9), "MGR" NUMBER (4,0), "HIREDATE" DATE

REM "SAL" NUMBER (7,2), "COMM" NUMBER (7,2), "DEPTNO" NUMBER (2,0))

REM PCTFREE 10 PCTUSED 40 INITRANS 1 MAXTRANS 255STORAGE (INITIAL 65536

REM NEXT 1048576 MINEXTENTS 1 FREELISTS 1 FREELIST GROUPS 1 BUFFER_POOL

REM DEFAULT) TABLESPACE "USERS_LZL" LOGGING NOCOMPRESS

REM... 14 rows

REM ALTER TABLE "SMITH". "EMP" ADD CONSTRAINT "PK_EMP" PRIMARY KEY

REM ("EMPNO") USING INDEX PCTFREE 10 INITRANS 2 MAXTRANS 255

REM STORAGE (INITIAL 65536 NEXT 1048576 MINEXTENTS 1 FREELISTS 1 FREELIST

REM GROUPS 1 BUFFER_POOL DEFAULT) TABLESPACE "USERS_LZL" LOGGING ENABLE

REM ALTER TABLE "SMITH". "EMP" ADD CONSTRAINT "FK_DEPTNO" FOREIGN KEY

REM ("DEPTNO") REFERENCES "DEPT" ("DEPTNO") ENABLE NOVALIDATE

REM ALTER TABLE "SMITH". EMP "ENABLE CONSTRAINT" FK_DEPTNO "

Only table and constraint definitions are created in indexfile and annotated by REM

When indexes=y

[oracle@lzl ~] $vi parfile_imp.txt

[oracle@lzl ~] $cat parfile_imp.txt

Userid= "scott/tiger"

File=/home/oracle/exp_lzl.dmp

Log=/home/oracle/imp_lzl.log

Buffer=102400

Tables=emp

Fromuser=scott

Touser=smith

Indexes=y

Indexfile=/home/oracle/imp_indexfile_y.sql

[oracle@lzl ~] $cat imp_indexfile_y.sql

REM CREATE TABLE "SMITH". "EMP" ("EMPNO" NUMBER (4,0), "ENAME"

REM VARCHAR2 (10), "JOB" VARCHAR2 (9), "MGR" NUMBER (4,0), "HIREDATE" DATE

REM "SAL" NUMBER (7,2), "COMM" NUMBER (7,2), "DEPTNO" NUMBER (2,0))

REM PCTFREE 10 PCTUSED 40 INITRANS 1 MAXTRANS 255STORAGE (INITIAL 65536

REM NEXT 1048576 MINEXTENTS 1 FREELISTS 1 FREELIST GROUPS 1 BUFFER_POOL

REM DEFAULT) TABLESPACE "USERS_LZL" LOGGING NOCOMPRESS

REM... 14 rows

CONNECT SMITH

CREATE UNIQUE INDEX "SMITH". "PK_EMP" ON "EMP" ("EMPNO") PCTFREE 10

INITRANS 2 MAXTRANS 255STORAGE (INITIAL 65536 NEXT 1048576 MINEXTENTS 1

FREELISTS 1 FREELIST GROUPS 1 BUFFER_POOL DEFAULT) TABLESPACE "USERS_LZL"

LOGGING

REM ALTER TABLE "SMITH". "EMP" ADD CONSTRAINT "PK_EMP" PRIMARY KEY

REM ("EMPNO") USING INDEX PCTFREE 10 INITRANS 2 MAXTRANS 255

REM STORAGE (INITIAL 65536 NEXT 1048576 MINEXTENTS 1 FREELISTS 1 FREELIST

REM GROUPS 1 BUFFER_POOL DEFAULT) TABLESPACE "USERS_LZL" LOGGING ENABLE

REM ALTER TABLE "SMITH". "EMP" ADD CONSTRAINT "FK_DEPTNO" FOREIGN KEY

REM ("DEPTNO") REFERENCES "DEPT" ("DEPTNO") ENABLE NOVALIDATE

REM ALTER TABLE "SMITH". EMP "ENABLE CONSTRAINT" FK_DEPTNO "

The only things that can be executed in indexfile are connect and create index statements.

How to use this indexfile feature?

1. Specify the INDEXFILE parameter export and report an error create index statement to the file. If the indexfile parameter is specified, the import will not actually occur, only the SQL script file that creates the object will be generated.

two。 Edit the file and change the password for connect.

3. Using indexes=n, re-import. At this time, no index was imported, only other objects were imported.

4. Create the index by performing the SQL script in the first step

The above steps actually complete the indexes=y when imp, but only perform the creation of index manually.

The indexfile parameter is actually very useful. Using the indexfile parameter before the actual imp, you can retain the DDL statement at the time of imp, which has a good effect on verifying imported objects.

Even if the execution fails in a DDL part of an import command, you can extract the corresponding sql statement in the indexfile to execute manually

The above is all the contents of the article "how to use the indexfile parameters of IMP". Thank you for reading! I believe we all have a certain understanding, hope to share the content to help you, if you want to learn more knowledge, welcome to follow the industry information channel!

Welcome to subscribe "Shulou Technology Information " to get latest news, interesting things and hot topics in the IT industry, and controls the hottest and latest Internet news, technology news and IT industry trends.