Want to protect your cyber security and still get fast solutions? Ask a secure question today.Go Premium

x
  • Status: Solved
  • Priority: Medium
  • Security: Public
  • Views: 3139
  • Last Modified:

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

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
sikyala
Asked:
sikyala
  • 13
  • 11
  • 3
1 Solution
 
slightwv (䄆 Netminder) Commented:
Please post the results of your imp command with show=Y added.
0
 
slightwv (䄆 Netminder) Commented:
Better, post the results of:
Imp user/pass file=yourfile.dmp full=Y show=Y
0
 
Devinder Singh VirdiLead Oracle DBA TeamCommented:
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
Concerto's Cloud Advisory Services

Want to avoid the missteps to gaining all the benefits of the cloud? Learn more about the different assessment options from our Cloud Advisory team.

 
sikyalaSenior Database AdministratorAuthor Commented:
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
 
sikyalaSenior Database AdministratorAuthor Commented:
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
 
sikyalaSenior Database AdministratorAuthor Commented:
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
 
slightwv (䄆 Netminder) Commented:
Please post the output of what I asked for in http:#a33729902.
0
 
sikyalaSenior Database AdministratorAuthor Commented:
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
 
Devinder Singh VirdiLead Oracle DBA TeamCommented:
When you exported this table, how many rows were exported.
Please check with export log files.
0
 
Devinder Singh VirdiLead Oracle DBA TeamCommented:
Please ignore my previous comment. I was late in typing
0
 
sikyalaSenior Database AdministratorAuthor Commented:
It was an export from a production database that is at my client's site.
0
 
slightwv (䄆 Netminder) Commented:
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
 
sikyalaSenior Database AdministratorAuthor Commented:
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
 
slightwv (䄆 Netminder) Commented:
>>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
 
sikyalaSenior Database AdministratorAuthor Commented:
I use Winscp to copy the file
0
 
slightwv (䄆 Netminder) Commented:
I'm not familiar with that product.  Does it automatically do a binary copy from an FTP server?
0
 
sikyalaSenior Database AdministratorAuthor Commented:
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
 
slightwv (䄆 Netminder) Commented:
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
 
sikyalaSenior Database AdministratorAuthor Commented:
yes I do have it configured to maintain file integrity
0
 
slightwv (䄆 Netminder) Commented:
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
 
sikyalaSenior Database AdministratorAuthor Commented:
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
 
slightwv (䄆 Netminder) Commented:
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
 
sikyalaSenior Database AdministratorAuthor Commented:
I tried that
0
 
slightwv (䄆 Netminder) Commented:
Not in any of the example output posted.

0
 
sikyalaSenior Database AdministratorAuthor Commented:
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
 
slightwv (䄆 Netminder) Commented:
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
 
sikyalaSenior Database AdministratorAuthor Commented:
thanks
0

Featured Post

Get expert help—faster!

Need expert help—fast? Use the Help Bell for personalized assistance getting answers to your important questions.

  • 13
  • 11
  • 3
Tackle projects and never again get stuck behind a technical roadblock.
Join Now