Oracle 9.2.0.05 running on Solaris

Is there a limit on the number of tables you can specify in an import
parameter file? If so I haven't found it in the 9i documentation.

I have a list of 598 tables that I need to reload from an import file
(all the tables for one Peoplesoft tablespace). I can't use wild cards
as they would probably match other tables in the export file. I
constructed a parameter file by spooling the table names and editing it.
When I run the import it just hangs. The import session is waiting
indefinitely on "sqlnet message from client" so it's doing nothing. The
parameter file is below. The only output I ever see is

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

Export file created by EXPORT:V09.02.00 via direct path
import done in US7ASCII character set and AL16UTF16 NCHAR character set
import server uses WE8ISO8859P15 character set (possible charset conversion)
export client uses WE8ISO8859P15 character set (possible charset
conversion)"

Here's what the import.par file looks like...

userid=/
file=/oramisc/backup/NSFSDEV/full_exportNSFSDEV121904_05:30.dmp
fromuser=sysadm
touser=sysadm
buffer=1048576
commit=y
tables=(
PS_ACCT_TYPCD_LNG,
PS_ACTION_LST,
[596 table names snipped]
)



--
To reply by email remove "_nospam"

[ Post a follow-up to this message ]

Chuck wrote:

> Oracle 9.2.0.05 running on Solaris
>
> Is there a limit on the number of tables you can specify in an import
> parameter file? If so I haven't found it in the 9i documentation.
>
> I have a list of 598 tables that I need to reload from an import file
> (all the tables for one Peoplesoft tablespace). I can't use wild cards
> as they would probably match other tables in the export file. I
> constructed a parameter file by spooling the table names and editing it.
> When I run the import it just hangs. The import session is waiting
> indefinitely on "sqlnet message from client" so it's doing nothing. The
> parameter file is below. The only output I ever see is
>
> "Connected to: Oracle9i Enterprise Edition Release 9.2.0.5.0 - Production
> With the Partitioning, OLAP and Oracle Data Mining options
> JServer Release 9.2.0.5.0 - Production
>
> Export file created by EXPORT:V09.02.00 via direct path
> import done in US7ASCII character set and AL16UTF16 NCHAR character set
> import server uses WE8ISO8859P15 character set (possible charset
> conversion)
> export client uses WE8ISO8859P15 character set (possible charset
> conversion)"
>
> Here's what the import.par file looks like...
>
> userid=/
> file=/oramisc/backup/NSFSDEV/full_exportNSFSDEV121904_05:30.dmp
> fromuser=sysadm
> touser=sysadm
> buffer=1048576
> commit=y
> tables=(
> PS_ACCT_TYPCD_LNG,
> PS_ACTION_LST,
> [596 table names snipped]
> )

Some random suggestions:

If you set the feedback parameter i.e. Feedback=1000, imp will print a
period every 1000 rows.  Might provide some perspective on whether it's
hanging, or just taking a long time.

I've also found setting the log parameter helps decipher what's going on
by providing a permanent log.

Try a dry run by setting show=y, rows=n

Try it with one table and see if you can get it to work.  If you are
running into some kind of limit on number of tables you can probably
break it up into half a dozen chunks.

Good luck.


--
//-Walt
//
//

[ Post a follow-up to this message ]

Walt wrote:
> Chuck wrote:
> 
>
>
> Some random suggestions:
>
> If you set the feedback parameter i.e. Feedback=1000, imp will print a
> period every 1000 rows.  Might provide some perspective on whether it's
> hanging, or just taking a long time.
>
> I've also found setting the log parameter helps decipher what's going on
> by providing a permanent log.
>
> Try a dry run by setting show=y, rows=n
>
> Try it with one table and see if you can get it to work.  If you are
> running into some kind of limit on number of tables you can probably
> break it up into half a dozen chunks.
>
> Good luck.
>
>

Forgot to mention that I was redirecting output instead of specifying a
log. That way it's not buffered and you don't have to wait for any
output to get to the file.

It might just be taking a very long time to chew through the export
file. There is some i/o activity but CPU being used by both the imp and
server background processes are close to nil.
--
To reply by email remove "_nospam"

[ Post a follow-up to this message ]

If it is chewing through an exp file, you should see something
happening (like allocating extents).  Perhaps it is expecting you to
enter something (maybe not liking the userid), but you don't see the
prompt because of the redir.

Try both Walt's suggestions and cutting the tables param down to a few
small tables.

jg
--
@home.com is bogus.
"We thought the trial was already over." - Potential juror for Robert
Blake trial.

[ Post a follow-up to this message ]

Joel Garry wrote:
> If it is chewing through an exp file, you should see something
> happening (like allocating extents).  Perhaps it is expecting you to
> enter something (maybe not liking the userid), but you don't see the
> prompt because of the redir.
>
> Try both Walt's suggestions and cutting the tables param down to a few
> small tables.
>
> jg
> --
> @home.com is bogus.
> "We thought the trial was already over." - Potential juror for Robert
> Blake trial.
>

Turns out I was just being too impatient. It was scanning the export
file looking for the first table. Import completed successfully - all
598 tables.


--
To reply by email remove "_nospam"

[ Post a follow-up to this message ]

Chuck wrote:
> Joel Garry wrote: 
to[vbcol=seagreen] 
the[vbcol=seagreen] 
few[vbcol=seagreen] 
Robert[vbcol=seagreen] 
>
> Turns out I was just being too impatient. It was scanning the export
> file looking for the first table. Import completed successfully - all

> 598 tables.

You can somewhat test the minimum of how long it should take by
grepping the dump file on the table name (capitalized).  exp tends to
do tables alphabetically, so grep for the first one alphabetically in
the table list.  Check the exp log if you have multiple schemata to see
which would be first.

jg
--
@home.com is bogus.
http://news.com.com/Torvalds+A+Sola..._3-5498799.html

[ Post a follow-up to this message ]