Detailed description of imp import parameter show = y in oracle

In the help of imp: SHOW just list file contents (N). The default value is N.

The Code is as follows:

Copy code

In the help of imp: SHOW just list file contents (N). The default value is N. The following experiment explains two points: 1. show = y: displays the statements for creating objects in the dmp file. 2. show = y, as oracle said, just list file contents. Import is not performed here. Prepare the experiment environment: 1. Create a user and grant permissions: SQL> create user testshow identified by a123; User created SQL> grant connect, resource to testshow; Grant succeeded SQL> grant create synonym to testshow 2/ Grant succeeded 2. Create an object: SQL> create table test (id number, name varchar2 (20 )); Table created SQL> create synonym emp for scott. emp; Synonym created 3. Check whether the object is successfully created: SQL> select count (*) from emp; COUNT (*) ---------- 16 SQL> select * from test; ID NAME ------------------------------ Start the experiment: 1. Export all objects of the testshow User: C: Usersyafeishi> exp system/dang file = testshow. dmp compress = n owner = testshow Export: Release 10.2.0.3.0-Production on Monday August 27 13:20:00 2012 Copyright (c) 1982,200 5, Oracle. All rights reserved. Connect to: Oracle Database 10g Enterprise Edition Release 10.2.0.3.0-Production With the Partitioning, OLAP and Data Mining options The ZHS16GBK and AL16UTF16 NCHAR character sets have been exported. Will export the specified user... . Exporting objects and operations in the pre-schema Process . Exporting the external function library name of user TESTSHOW . Export PUBLIC type Synonyms . Exporting the special type Synonym . Exporting the object type definition of user TESTSHOW The TESTSHOW object to be exported... . Exporting database link... . Exporting sequence number . Exporting cluster Definition . The TESTSHOW table will be exported through the regular path... .. Export table TEST export 0 rows . Exporting Synonym . Exporting view... . Exporting the Stored Procedure . Exporting Operator . The reference integrity constraints are being exported. . Exporting trigger... . Exporting index type... . Exporting bitmap, functional index, and scalable Index . Exporting table activity later Exporting the Materialized View Exporting snapshot logs . Exporting Job Queue... . Exporting refresh group and sub-group . Exporting dimension... . Exporting objects and operations in the post-schema Process . Exporting statistics The export is successfully terminated without a warning. 2. Delete the test table and emp table under testshow with the same name: SQL> drop table test; Table dropped SQL> select * from test; Select * from test

ORA-00942: Table or view does not exist

The Code is as follows:	Copy code
SQL> drop synonym emp; Synonym dropped SQL> select count (*) from emp; Select count (*) from emp ORA-00942: Table or view does not exist 3. Import with show = y

C: Usersyafeishi> imp system/dang file = testshow. dmp log = testshow. log show = y from
User = testshow touser = testshow
Import: Release 10.2.0.3.0-Production on Monday August 27 13:20:45 2012
Copyright (c) 1982,200 5, Oracle. All rights reserved.
Connect to: Oracle Database 10g Enterprise Edition Release 10.2.0.3.0-Production
With the Partitioning, OLAP and Data Mining options
EXPORT File Created by EXPORT: V10.02.01 in the normal path
The ZHS16GBK Character Set and AL16UTF16 NCHAR character set have been imported.
. Importing the TESTSHOW object to TESTSHOW.

The Code is as follows:

Copy code

"BEGIN" "Sys. dbms_logrep_imp.instantiate_schema (schema_name => SYS_CONTEXT ('userenv ','" "CURRENT_SCHEMA"), export_db_name => 'orcl. REGRESS. RDBMS. DEV. US. ORACLE. com', I" "Nst_scn => '123 ');" "COMMIT; END ;" "Alter session set CURRENT_SCHEMA =" TESTSHOW "" "Create table" TEST "(" ID "NUMBER," NAME "VARCHAR2 (20) PCTFREE 10 PCTUSED" "40 INITRANS 1 MAXTRANS 255 STORAGE (INITIAL 65536 FREELISTS 1 freelist group" "S 1 BUFFER_POOL DEFAULT) TABLESPACE" USERS "logging nocompress" ... Skipping table "TEST" "Alter session set CURRENT_SCHEMA =" TESTSHOW "" "Create synonym" EMP "FOR" SCOTT "." EMP ""

Import terminated successfully. No warning is displayed.

The log shows the statement used to create an object, proving the first point.

Check whether objects in the database are imported:

The Code is as follows:	Copy code
SQL> select * from test; Select * from test

ORA-00942: Table or view does not exist

The Code is as follows:	Copy code
SQL> select count (*) from emp; Select count (*) from emp

ORA-00942: Table or view does not exist

The object is not imported, which proves the second point.

4. show = n Import

The Code is as follows:	Copy code
C: Usersyafeishi> imp system/dang file = testshow. dmp log = testshow. log show = n igno Re = y fromuser = testshow touser = testshow Import: Release 10.2.0.3.0-Production on Monday August 27 13:21:35 2012 Copyright (c) 1982,200 5, Oracle. All rights reserved. Connect to: Oracle Database 10g Enterprise Edition Release 10.2.0.3.0-Production With the Partitioning, OLAP and Data Mining options

EXPORT File Created by EXPORT: V10.02.01 in the normal path
The ZHS16GBK Character Set and AL16UTF16 NCHAR character set have been imported.
. Importing the TESTSHOW object to TESTSHOW.
.. Importing the table "TEST" into 0 rows...
Import terminated successfully. No warning is displayed.

The Code is as follows:	Copy code
SQL> select * from test; ID NAME ------------------------------ SQL> select count (*) from emp; COUNT (*) ---------- 16 The object is also imported. --- EOF