Re: imp - how to get a listing of an export file...?

From: <fitzjarrell_at_cox.net>
Date: 1 Nov 2005 13:03:48 -0800
Message-ID: <1130879028.367178.105710@f14g2000cwb.googlegroups.com>

Mark Bole wrote:
> Eric Jenkinson wrote:
> > On 1 Nov 2005 11:06:45 -0800, "Tommy" <tommynoble_at_hotmail.com> wrote:
> >
> >
> >>I've tried imp user/pass_at_alias file=<my exp file> show=YES, but it's
> >>not giving me anything useful and it's taking ages to do it.
> >>
> >>I have yet to find any examples of using SHOW=YES nor have I been able
> >>to get any useful results. Am I missing something?
> >>
> >>Using Oracle 9i rel 2, patch set 4 (9.2.0.5)
> >>
> [...]
> >
> > According to the docs the SHOW parameter can only be used with the
> > FULL=Y, FROMUSER, TOUSER, or TABLES parameter. FULL defaults to N and
> > FROMUSER, TOUSER and TABLES defaults to none. You might want to try
> > changing your command to one of the following:
> [...]
>
> Better yet, the OP should tell us how he made the export (FULL=,
> TABLES=, etc.), and then what results he got from the import, whether or
> not he thought they were "useful". (Perhaps the normal output of SHOW
> simply isn't what he expected).
>
> With the SHOW option, it still takes time to read through all the rows
> of data in the export file, even though they are not imported or listed,
> so if you have large tables, that could account for the "taking ages"
> aspect.
>
> The following sequence works fine:
>
> exp user/pass_at_connect file=x.dmp
> imp user/pass_at_connect file=x.dmp show=y
>
> -Mark Bole

Agreed. Granted, my SCOTT schema is pretty much a scrap heap for anything I want to test, but using Mark's listed commands I have no problem exporting or producing a 'task listing' from the dump file (output severely edited):

/export/home/oracle/exports> exp scott/*************** file=scott.dmp

Export: Release 9.2.0.6.0 - Production on Tue Nov 1 20:31:59 2005

Connected to: Oracle9i Enterprise Edition Release 9.2.0.6.0 - 64bit Production
With the Partitioning, OLAP and Oracle Data Mining options JServer Release 9.2.0.6.0 - Production
Export done in WE8ISO8859P15 character set and AL16UTF16 NCHAR character set

. exporting pre-schema procedural objects and actions
. exporting foreign function library names for user SCOTT
. exporting PUBLIC type synonyms
. exporting private type synonyms
. exporting object type definitions for user SCOTT

About to export SCOTT's objects ...

. exporting database links
. exporting sequence numbers
. exporting cluster definitions
. about to export SCOTT's tables via Conventional Path ...
. . exporting table                  ADDRESS_TABLE          0 rows

exported

...
. exporting synonyms
. exporting views
. exporting stored procedures
. exporting operators
. exporting referential integrity constraints
. exporting triggers
. exporting indextypes
. exporting bitmap, functional and extensible indexes
. exporting posttables actions
. exporting materialized views
. exporting snapshot logs
. exporting job queues
. exporting refresh groups and children
. exporting dimensions
. exporting post-schema procedural objects and actions
. exporting statistics

Export terminated successfully without warnings. /export/home/oracle/exports> imp scott/*************** file=scott.dmp show=y

Import: Release 9.2.0.6.0 - Production on Tue Nov 1 20:32:22 2005

Connected to: Oracle9i Enterprise Edition Release 9.2.0.6.0 - 64bit Production
With the Partitioning, OLAP and Oracle Data Mining options JServer Release 9.2.0.6.0 - Production

Export file created by EXPORT:V09.02.00 via conventional path import done in WE8ISO8859P15 character set and AL16UTF16 NCHAR character set
. importing SCOTT's objects into SCOTT
"BEGIN "
"sys.dbms_logrep_imp.instantiate_schema(schema_name=>'SCOTT', export_db_name"

 "=>'DEV9', inst_scn=>'47459047');"
 "COMMIT; END;"
 "CREATE TYPE "CUST_ADDRESS_TYP_NEW" TIMESTAMP '2004-12-17:18:13:57'

OID 'EB7"

 "4B1F386FD0A9CE0340003BA7AB525'   AS OBJECT"
 "    ( street_address     VARCHAR2(40)"
 "    , postal_code        VARCHAR2(10)"
 "    , city               VARCHAR2(30)"
 "    , state_province     VARCHAR2(10)"
 "    , country_id         CHAR(2)"
 "    );"
 "CREATE TYPE "UOB_TYPE" TIMESTAMP '2005-02-23:14:53:03' OID

'F0C9D228C9D9075"

 "7E0340003BA7AB525'   AS OBJECT ("
 "object_name VARCHAR2(128), object_type VARCHAR2(18));"
 "CREATE TYPE "T_UOB_TYPE" TIMESTAMP '2005-02-23:14:53:04' OID

'F0C9D228C9DE0"
"757E0340003BA7AB525' AS TABLE OF uob_type;" "CREATE TYPE "UW_SEL_ROW" TIMESTAMP '2005-04-18:04:12:59' OID 'F4FF351B406B0"

 "236E0340003BA7AB525'   AS OBJECT ("
 "part_num NUMBER, part_name VARCHAR2(15));"
 "CREATE TYPE "UW_SEL_TAB" TIMESTAMP '2005-04-18:04:13:01' OID

'F4FF351B40700"
"236E0340003BA7AB525' AS TABLE OF uw_sel_row;" ...

 "ALTER TRIGGER "TEST_TRIG_UPD_TRIG"  ENABLE"
 "ALTER TABLE "EMP3" ENABLE CONSTRAINT "EMP3_FK""
 "ALTER TABLE "EMP" ENABLE CONSTRAINT "EMP_FK""

Import terminated successfully without warnings. /export/home/oracle/exports> exit

This occurred in a relatively short time span; a larger file might have required a longer interval to complete. The OP needs to post more information before his question can be answered, such as how long is 'forever' in his mind and how he performed the export. Also the dump file size would be helpful, as well as operating system information like installed memory, number of disks and process load, any of which can affect performance.

David Fitzjarrell Received on Tue Nov 01 2005 - 15:03:48 CST