Still celebrating National IT Professionals Day with 3 months of free Premium Membership. Use Code ITDAY17

x
?
Solved

DMP file import issue

Posted on 2012-03-16
17
Medium Priority
?
807 Views
Last Modified: 2012-03-16
I am using oracle 10g. I am trying to import a DMP file and at the load message it shows scott.SYS_IMPORT_SCHEMA_01 is loaded. But I am not able to find that table in SQLDeveloper or query in it SQLPlus. When I query a select statement from that table it says "Table or view doesn not exist". Please help with any ideas or thoughts.
0
Comment
Question by:thomaszhwang
[X]
Welcome to Experts Exchange

Add your voice to the tech community where 5M+ people just like you are talking about what matters.

  • Help others & share knowledge
  • Earn cash & points
  • Learn & ask questions
  • 7
  • 6
  • 2
  • +1
17 Comments
 
LVL 48

Expert Comment

by:schwertner
ID: 37729895
Scott in most cases is a test Oracle schema.

So the correct way to access the tables of 'scott' will be:

SQL>connect scott/tiger@alias
SQL> SELECT * FROM user_tables;

Now you will see all tables of the scott schema.

If I mistaken your question, then see the Import log. It will display you which tables are imported and if there are errors.
0
 

Author Comment

by:thomaszhwang
ID: 37729946
That is how I was initially connected. And I can see only sample tables.
Please help.

SQL>scott/tiger@alias
SQL> select table_name from user_tables;

TABLE_NAME
------------------------------
DEPT
EMP
BONUS
SALGRADE
0
 
LVL 77

Expert Comment

by:slightwv (䄆 Netminder)
ID: 37729993
>>scott.SYS_IMPORT_SCHEMA_01 is loaded.

Did it not say: Job scott.SYS_IMPORT_SCHEMA_01 completed?

I believe that SYS_IMPORT_SCHEMA_01 is the datapump job name.
0
Microsoft Certification Exam 74-409

Veeam® is happy to provide the Microsoft community with a study guide prepared by MVP and MCT, Orin Thomas. This guide will take you through each of the exam objectives, helping you to prepare for and pass the examination.

 

Author Comment

by:thomaszhwang
ID: 37730019
Yes. This is the line,

Job "SCOTT"."SYS_IMPORT_SCHEMA_01" completed with 1359 error(s) at 11:04:02.

Even with errors previously I saw the table SYS_IMPORT_SCHEMA_01 creating under my connection. Every time I run it I see the table, I drop the table and run it again. Now after a while it had stopped creating in the fist place. This happens with the SYS user as well. Please help.
Thanks.
0
 
LVL 77

Accepted Solution

by:
slightwv (䄆 Netminder) earned 1500 total points
ID: 37730031
The word "Job" is the important word here.

SYS_IMPORT_SCHEMA_01 is not a table.  It is a database job created by datapump.  It is cleaned up when finished.
0
 

Author Comment

by:thomaszhwang
ID: 37730055
But what about the previous times when I actually saw those tables?

Say if it is cleanup but technically where should I find the table after import succeeds?

Thanks.
0
 
LVL 23

Expert Comment

by:David
ID: 37730060
Just for fun, let's alter the first advice 37729895, and ensure the import worked for schemas other than SCOTT.  Try:

SELECT owner, table_name FROM sys.all_tables
ORDER BY 1,2
/
0
 
LVL 77

Expert Comment

by:slightwv (䄆 Netminder)
ID: 37730091
>>technically where should I find the table after import succeeds?

I'm not up on all aspects of datapump but it might create some working tables while it is running.  I do know it creates a database job.

The SCOTT schema out of the box does not come with a table called SYS_IMPORT_SCHEMA_01.  If you didn't create it, I would not expect it to be there after the import completes.


Check the docs:
http://docs.oracle.com/cd/B25329_01/doc/admin.102/b25107/impexp.htm

Nowhere in the list does it say it is importing a talbe called SYS_IMPORT_SCHEMA_01.
0
 
LVL 77

Assisted Solution

by:slightwv (䄆 Netminder)
slightwv (䄆 Netminder) earned 1500 total points
ID: 37730104
I did find a reference to in on Oracle's Support site:
Datapump Import Results in ORA-39001: invalid argument value ORA-01775: looping chain of synonyms [ID 459151.1]

There is a temporary 'master' table created during impdp.  It is cleaned up automatically when the job completes.
0
 

Author Comment

by:thomaszhwang
ID: 37730150
Yes maybe it was a temporary table which cleared out after the job. I tried the import one more time but now I didn't see any temporary table creating on that name.

Anyways if the import succeeds I should find the table under by connection with
scott/tiger-->tables right?
0
 
LVL 23

Expert Comment

by:David
ID: 37730173
Just the four sample tables OWNED by SCOTT, not any others.  See my above query.
0
 
LVL 77

Expert Comment

by:slightwv (䄆 Netminder)
ID: 37730176
>>scott/tiger-->tables right?

For SCOTT's objects yes.  An import might have objects from other schemas.  It all depends on how it was exported.
0
 

Author Comment

by:thomaszhwang
ID: 37730213
Hmm thanks. I will get back if I have more questions.
0
 
LVL 48

Expert Comment

by:schwertner
ID: 37730391
you have to post how you invoke the import.
The user you use for connection, what kind of import is it (schema, table)
Without these details is  a hard task to guess what has happened.
0
 
LVL 77

Expert Comment

by:slightwv (䄆 Netminder)
ID: 37730412
>>Without these details is  a hard task to guess what has happened.

I believe everything has been resolved.

In the original question : "at the load message it shows scott.SYS_IMPORT_SCHEMA_01"

That is the job completed message.  This was suggested in htttp:#a37729993 and confirmed in http:#a37730019

Then there was a secondary question about an actual table seen while impdp was running but not after.  This was resolved in http:#a37730104
0
 

Author Comment

by:thomaszhwang
ID: 37730874
Okay here is another issue I am facing. I am trying to import a DMP file into a NEW database which as no grants,users etc that are present in the export file. All I need is data and I don't care about users,grants,sequences etc.

Below is a simple import statement which I used,

IMPDP scott/********@alias directory=TEST_DIR dumpfile=godirectBackup.dmp content=all schemas=GODIRECT logfile=imp3.log ( refer to imp-5193errors log)

But after looking at the errors, I tried to exclude certain things from the import and then the errors reduced.

IMPDP scott/********@alias dumpfile=godirectBackup.dmp table_exists_action=replace schemas=godirect directory=TEST_DIR exclude=grant,user,sequence,view,role_grant,default_role,tablespace_quota logfile=mylog.log ( refer imp-1358errors log)

Even when I don’t add “exclude=tablespace” in the import statement it has the same errors as 1358 file. Now I think all the errors are pointing to godirect tablespace and the other errors are dependent on it.

Should I create a tablespace manually? Or please help me proceed. Thanks.
imp-1358errors.log
imp-5193errors.log
0
 
LVL 77

Expert Comment

by:slightwv (䄆 Netminder)
ID: 37730890
>>Okay here is another issue I am facing.

This is a new question and should be asked as one.
0

Featured Post

 [eBook] Windows Nano Server

Download this FREE eBook and learn all you need to get started with Windows Nano Server, including deployment options, remote management
and troubleshooting tips and tricks

Question has a verified solution.

If you are experiencing a similar issue, please ask a related question

Why doesn't the Oracle optimizer use my index? Querying too much data Most Oracle developers know that an index is useful when you can use it to restrict your result set to a small number of the total rows in a table. So, the obvious side…
Shell script to create broker configuration file using current broker Configuration, solely for purpose of backup on Linux. Script may need to be modified depending on OS-installation. Please deploy and verify the script in a test environment.
This video shows how to recover a database from a user managed backup
This video shows how to copy an entire tablespace from one database to another database using Transportable Tablespace functionality.

721 members asked questions and received personalized solutions in the past 7 days.

Join the community of 500,000 technology professionals and ask your questions.

Join & Ask a Question