Solved

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

Posted on 2010-09-21
29
2,529 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)
ID: 33729892
Please post the results of your imp command with show=Y added.
0
 
LVL 76

Expert Comment

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

Expert Comment

by:Devinder Singh Virdi
ID: 33729906
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
ID: 33734999
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
ID: 33735049
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
ID: 33735092
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)
ID: 33735136
Please post the output of what I asked for in http:#a33729902.
0
 

Author Comment

by:sikyala
ID: 33735274
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
ID: 33735334
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
ID: 33735353
Please ignore my previous comment. I was late in typing
0
 

Author Comment

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

Expert Comment

by:slightwv (䄆 Netminder)
ID: 33735779
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
ID: 33735854
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
PRTG Network Monitor: Intuitive Network Monitoring

Network Monitoring is essential to ensure that computer systems and network devices are running. Use PRTG to monitor LANs, servers, websites, applications and devices, bandwidth, virtual environments, remote systems, IoT, and many more. PRTG is easy to set up & use.

 
LVL 76

Expert Comment

by:slightwv (䄆 Netminder)
ID: 33735989
>>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
ID: 33736005
I use Winscp to copy the file
0
 
LVL 76

Expert Comment

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

Author Comment

by:sikyala
ID: 33736101
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)
ID: 33736223
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
ID: 33736296
yes I do have it configured to maintain file integrity
0
 
LVL 76

Expert Comment

by:slightwv (䄆 Netminder)
ID: 33736369
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
ID: 33736804
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
ID: 33736838
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
ID: 33736849
I tried that
0
 
LVL 76

Expert Comment

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

0
 

Author Comment

by:sikyala
ID: 33935435
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)
ID: 33937401
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
ID: 33969463
thanks
0

Featured Post

Is Your Active Directory as Secure as You Think?

More than 75% of all records are compromised because of the loss or theft of a privileged credential. Experts have been exploring Active Directory infrastructure to identify key threats and establish best practices for keeping data safe. Attend this month’s webinar to learn more.

Question has a verified solution.

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

Suggested Solutions

Title # Comments Views Activity
SSH commands for Nas4free 21 380
Oracle DATE Column Space 11 63
Oracle Public Synonyms and Privileges 2 51
Oracle SQL Select unique values from two columns 4 33
Introduction A previously published article on Experts Exchange ("Joins in Oracle", http://www.experts-exchange.com/Database/Oracle/A_8249-Joins-in-Oracle.html) makes a statement about "Oracle proprietary" joins and mixes the join syntax with gen…
Cursors in Oracle: A cursor is used to process individual rows returned by database system for a query. In oracle every SQL statement executed by the oracle server has a private area. This area contains information about the SQL statement and the…
Via a live example show how to connect to RMAN, make basic configuration settings changes and then take a backup of a demo database
This video shows how to configure and send email from and Oracle database using both UTL_SMTP and UTL_MAIL, as well as comparing UTL_SMTP to a manual SMTP conversation with a mail server.

895 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

16 Experts available now in Live!

Get 1:1 Help Now