Solved

how to fix import error IMP-00033: Warning: Table "HOLDINGS" not found in export file

Posted on 2010-09-21
29
2,471 Views
Last Modified: 2012-05-10
I am trying to import a table and I keep getting the following error:

IMP-00033: Warning: Table "BIB_HOLDINGS" not found in export file
Import terminated successfully with warnings.
 
The table does exist in the dump file and I have the right permissions to read the file. I use all the parameters fromuser touser etc but i keep getting this error. what can I do to resolve this issue?
0
Comment
Question by:sikyala
  • 13
  • 11
  • 3
29 Comments
 
LVL 76

Expert Comment

by:slightwv (䄆 Netminder)
Comment Utility
Please post the results of your imp command with show=Y added.
0
 
LVL 76

Expert Comment

by:slightwv (䄆 Netminder)
Comment Utility
Better, post the results of:
Imp user/pass file=yourfile.dmp full=Y show=Y
0
 
LVL 15

Expert Comment

by:Devinder Singh Virdi
Comment Utility
Check the type of object with this name in your schema before taking export using the following:-
select object_name, object_type from user_objects where object_name ='BIB_HOLDINGS';

it is possible that this might be synonym.
0
 

Author Comment

by:sikyala
Comment Utility
initially I tried using the following parfile:

userid ="tem/<my pwd>!"
Commit  = N
Constraints=N
Destroy = N
Ignore=Y
Touser=tem
File    = /u03/tems.dmp
Full    = N
Tables=bib_holdings
Grants  = N
Log     = /u03/tems09212010.log
Rows    = Y
Show    = N
Buffer = 1000000
Statistics = none
0
 

Author Comment

by:sikyala
Comment Utility
Then I tried command land where I answer the prompted questions. See below:

imp tem/<my pwd> File= /u03/tems.dmp Log=/u03/tems09212010.log Tables=bib_holdings Touser=tem
Import: Release 10.2.0.4.0 - Production on Tue Sep 21 13:37:51 2010
Copyright (c) 1982, 2007, Oracle.  All rights reserved.

IMP-00058: ORACLE error 28011 encountered
ORA-28011: the account will expire soon; change your password now
Connected to: Oracle Database 10g Enterprise Edition Release 10.2.0.4.0 - 64bit Production
With the Partitioning, Data Mining and Real Application Testing options
Export file created by EXPORT:V10.02.01 via conventional path
Warning: the objects were exported by TEMSLOAD, not by you
import done in US7ASCII character set and AL16UTF16 NCHAR character set
import server uses WE8ISO8859P1 character set (possible charset conversion)
IMP-00033: Warning: Table "BIB_HOLDINGS" not found in export file
Import terminated successfully with warnings.
0
 

Author Comment

by:sikyala
Comment Utility
Then I tried responding to the prompts:

imp tem/<my pwd>
Import: Release 10.2.0.4.0 - Production on Tue Sep 21 13:43:11 2010
Copyright (c) 1982, 2007, Oracle.  All rights reserved.

IMP-00058: ORACLE error 28011 encountered
ORA-28011: the account will expire soon; change your password now
Connected to: Oracle Database 10g Enterprise Edition Release 10.2.0.4.0 - 64bit Production
With the Partitioning, Data Mining and Real Application Testing options
Import file: expdat.dmp > tems.dmp
Enter insert buffer size (minimum is 8192) 30720>
Export file created by EXPORT:V10.02.01 via conventional path
Warning: the objects were exported by TEMSLOAD, not by you
import done in US7ASCII character set and AL16UTF16 NCHAR character set
import server uses WE8ISO8859P1 character set (possible charset conversion)
List contents of import file only (yes/no): no >
Ignore create error due to object existence (yes/no): no >
Import grants (yes/no): yes >
Import table data (yes/no): yes >
Import entire export file (yes/no): no >
Username: temsload
Enter table(T) or partition(T:P) names. Null list means all tables for user
Enter table(T) or partition(T:P) name or . if done: BIB_HOLDINGS
Enter table(T) or partition(T:P) name or . if done:
. importing TEMSLOAD's objects into TEM
IMP-00033: Warning: Table "BIB_HOLDINGS" not found in export file
Import terminated successfully with warnings.
0
 
LVL 76

Expert Comment

by:slightwv (䄆 Netminder)
Comment Utility
Please post the output of what I asked for in http:#a33729902.
0
 

Author Comment

by:sikyala
Comment Utility
imp tem/<my pwd> file=tems.dmp full=Y show=Y

Import: Release 10.2.0.4.0 - Production on Wed Sep 22 11:01:23 2010

Copyright (c) 1982, 2007, Oracle.  All rights reserved.


Connected to: Oracle Database 10g Enterprise Edition Release 10.2.0.4.0 - 64bit Production
With the Partitioning, Data Mining and Real Application Testing options

Export file created by EXPORT:V10.02.01 via conventional path

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

import done in US7ASCII character set and AL16UTF16 NCHAR character set
import server uses WE8ISO8859P1 character set (possible charset conversion)
. importing TEMSLOAD's objects into TEM
. importing TEMS_ADM's objects into TEMS_ADM
 "ALTER SESSION SET CURRENT_SCHEMA= "TEMS_ADM""
 "CREATE TABLE "BIB_HOLDINGS" ("PDF_EXISTS" VARCHAR2(1), "PDF_NAME" VARCHAR2("
 "255), "TITLE" VARCHAR2(2000), "SUBJECTKEYWORDS" VARCHAR2(4000), "IAC_REPORT"
 "_ID" VARCHAR2(20), "CATEGORY" VARCHAR2(70), "TYPE" VARCHAR2(70), "LOCATION""
 " VARCHAR2(255), "AUTHOR" VARCHAR2(255), "PUBLISHER" VARCHAR2(255), "PUBLICA"
 "TION_DATE" DATE, "COPIES" NUMBER, "CLASSIFICATION" VARCHAR2(50), "ORG_PERFO"
 "RMING" VARCHAR2(255), "SECONDARY_DIST" VARCHAR2(4000), "ORG_MONITORING" VAR"
 "CHAR2(255), "ABSTRACT" VARCHAR2(4000), "ABSTRACT2" VARCHAR2(4000), "NUM_PAG"
 "ES" NUMBER, "COMMENTS" VARCHAR2(4000), "ENTERED_BY" VARCHAR2(50), "SITE" VA"
 "RCHAR2(10) NOT NULL ENABLE, "COPYRIGHT_EXISTS" VARCHAR2(1), "DISTRIBUTION_C"
 "ODE" VARCHAR2(1), "COPYRIGHT_INFO" VARCHAR2(255), "SUPPLEMENTARY_NOTES" VAR"
 "CHAR2(4000), "MODIFIED_DATE" DATE, "HIERARCHY_1" VARCHAR2(4000), "HIERARCHY"
 "_2" VARCHAR2(255), "HIERARCHY_3" VARCHAR2(255), "HIERARCHY_4" VARCHAR2(255)"
 ", "HIERARCHY_5" VARCHAR2(255), "HIERARCHY_6" VARCHAR2(255), "CONTRACT_GRANT"
 "_ID" VARCHAR2(255), "AD_ID" VARCHAR2(20), "PERFORMING_REPORT_ID" VARCHAR2(2"
 "55), "MONITOR_REPORT_ID" VARCHAR2(255), "PURCHASE_PRICE" VARCHAR2(12), "PUR"
 "CHASE_INFO" VARCHAR2(255), "FILE_NAME" VARCHAR2(255), "EXPORT_CONTROL" VARC"
 "HAR2(2), "FOR_PAY" VARCHAR2(1), "OTHER_CLASSIFICATION_MARKINGS" VARCHAR2(40"
 "00), "EXTRA_TEXT_FIELD1" VARCHAR2(255), "EXTRA_TEXT_FIELD2" VARCHAR2(255), "
 ""EXTRA_TEXT_FIELD3" VARCHAR2(255), "EXTRA_TEXT_FIELD4" VARCHAR2(255), "EXTR"
 "A_TEXT_FIELD5" VARCHAR2(255), "EXTRA_DATE_FIELD1" DATE, "EXTRA_DATE_FIELD2""
 " DATE, "EXTRA_NUMBER_FIELD1" NUMBER, "EXTRA_NUMBER_FIELD2" NUMBER, "ID" NUM"
 "BER(*,0), "DB_INSERTED" TIMESTAMP (6), "DB_UPDATED" TIMESTAMP (6))  PCTFREE"
 " 10 PCTUSED 40 INITRANS 1 MAXTRANS 255 STORAGE(INITIAL 2082471936 FREELISTS"
 " 1 FREELIST GROUPS 1 BUFFER_POOL DEFAULT) TABLESPACE "TEMS_DATA" LOGGING NO"
 "COMPRESS"
. . skipping table "BIB_HOLDINGS"
0
 
LVL 15

Expert Comment

by:Devinder Singh Virdi
Comment Utility
When you exported this table, how many rows were exported.
Please check with export log files.
0
 
LVL 15

Expert Comment

by:Devinder Singh Virdi
Comment Utility
Please ignore my previous comment. I was late in typing
0
 

Author Comment

by:sikyala
Comment Utility
It was an export from a production database that is at my client's site.
0
 
LVL 76

Expert Comment

by:slightwv (䄆 Netminder)
Comment Utility
How did you get the export? It might be corrupt.

Also, 10.2.0.4 probably should have been considered a major release not a 'patchset'. The imp show: Export file created by EXPORT:V10.02.01.

Verify this was really done with 10.2.0.1 or if they are on 10.2.0.4 and Oracle just didn't update the exp header info. There might be a compatibility problem.
0
 

Author Comment

by:sikyala
Comment Utility
I copied it from the client's SFTP server to my development server. I didn't notice the export version. The client did upgrade to 10.2.0.4 a couple of years ago. I don't why the version would not reflect the correct version. I will verify with them.
0
Do You Know the 4 Main Threat Actor Types?

Do you know the main threat actor types? Most attackers fall into one of four categories, each with their own favored tactics, techniques, and procedures.

 
LVL 76

Expert Comment

by:slightwv (䄆 Netminder)
Comment Utility
>>I copied it from the client's SFTP server to my development server.
Binary transfer?

>> I don't why the version would not reflect the correct
Probably just an oversight on Oracles patch process.  Some patches don't update all the version numbers correctly.

0
 

Author Comment

by:sikyala
Comment Utility
I use Winscp to copy the file
0
 
LVL 76

Expert Comment

by:slightwv (䄆 Netminder)
Comment Utility
I'm not familiar with that product.  Does it automatically do a binary copy from an FTP server?
0
 

Author Comment

by:sikyala
Comment Utility
It is a SFTP and FTP client for Microsoft Windows. Its main function is to secure file transfer between a local and a remote computer. It uses Secure Shell (SSH) and supports the SCP protocol in addition to SFTP.
0
 
LVL 76

Expert Comment

by:slightwv (䄆 Netminder)
Comment Utility
OK but relying on my long ago UNIX command line days you have to tell ftp to do a binary transfer.  Does winscp 'automatically' know to to a binary transfer.

Oracle DMP files must be transferred in binary mode.

From a quick Google:
http://winscp.net/eng/docs/transfer_mode

Available Modes
WinSCP offers you the chance to select between Text and Binary transfer modes. It is set as one of the transfer settings.
0
 

Author Comment

by:sikyala
Comment Utility
yes I do have it configured to maintain file integrity
0
 
LVL 76

Expert Comment

by:slightwv (䄆 Netminder)
Comment Utility
With that error message, if the versions match, I'm still thinking corrupt DMP file.

I really doubt this will fix it but try pre-creating the table.  If you specify indexfile=somefile.txt the create table will be in there but commented out.  You can copy and paste that into sql*plus then try the import again.

Again, from that error message, I don't hold out much hope it will work but you never know.

If you are 100% confident the file isn't corrupt (can you check the checksum values) I would open an SR with Oracle.

0
 

Author Comment

by:sikyala
Comment Utility
I had my SA do a checksum on the file and he said they were the same in both environments. I will try your recommendation
0
 
LVL 76

Accepted Solution

by:
slightwv (䄆 Netminder) earned 500 total points
Comment Utility
I think it's a DUH moment.....

You are running the imp as TEM.  The output in http:#a33735274 shows the table owned by TEMS_ADM

You need to add a fromuser to the import parameters.
0
 

Author Comment

by:sikyala
Comment Utility
I tried that
0
 
LVL 76

Expert Comment

by:slightwv (䄆 Netminder)
Comment Utility
Not in any of the example output posted.

0
 

Author Comment

by:sikyala
Comment Utility
I think somehow Oracle was confused because I have a user named TEMS_ADM so even though I specified the TOUSER as TEM it was trying to load the data into the TEMS_ADM schema. So I cleaned up the TEMS_ADM schema and loaded the data into that schema. I really needed it in the TEM schema so I had to export it and import it into the TEM schema
0
 
LVL 76

Expert Comment

by:slightwv (䄆 Netminder)
Comment Utility
I feel http:#a33736838 was the correct answer even though sikyala said they had tried it, none of the example output posted showed the results of that.

I've used exp with fromuser/touser many times without Oracle getting confused.
0
 

Author Closing Comment

by:sikyala
Comment Utility
thanks
0

Featured Post

What Should I Do With This Threat Intelligence?

Are you wondering if you actually need threat intelligence? The answer is yes. We explain the basics for creating useful threat intelligence.

Join & Write a Comment

Attention: This article will no longer be maintained. If you have any questions, please feel free to mail me. jgh@FreeBSD.org Please see http://www.freebsd.org/doc/en_US.ISO8859-1/articles/freebsd-update-server/ for the updated article. It is avail…
Checking the Alert Log in AWS RDS Oracle can be a pain through their user interface.  I made a script to download the Alert Log, look for errors, and email me the trace files.  In this article I'll describe what I did and share my script.
This video shows how to Export data from an Oracle database using the Original Export Utility.  The corresponding Import utility, which works the same way is referenced, but not demonstrated.
This video shows how to recover a database from a user managed backup

763 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

Need Help in Real-Time?

Connect with top rated Experts

12 Experts available now in Live!

Get 1:1 Help Now