jan_l_keersmaekers
asked on
Data Manager : Export Import
Who can help me with this Oracle 7 export import problem ?
I exported 28 oracle 7.3.4.0.1 tables with Export in Data Manager 1.3.5
Then i deleted the tables in Schema Manager.
Then i Importerd the tables with with Import in Data Manager 1.3.5
All worked fine except for one table i can't import. I don't see a big difference between this and the other tables.
So the import worked for the other tables in de export file.
The export file EXPDAT.DMP containt the following sql.
TABLE "TIT_IDS_53_SHEET"
CREATE TABLE "TIT_IDS_53_SHEET" ("I53_PROJECT" VARCHAR2(7), "I53_LOOP" VARCHAR2(11), "I53_TAG" VARCHAR2(11), "I53_SERVICE" VARCHAR2(50), "I53_PI" VARCHAR2(30), "I53_MFR" VARCHAR2(25), "I53_MODEL" VARCHAR2(35), "I53_DESCRIPTION1" VARCHAR2(70), "I53_DESCRIPTION2" VARCHAR2(70), "I53_REM01" VARCHAR2(70), "I53_REM02" VARCHAR2(70), "I53_REM03" VARCHAR2(70), "I53_REM04" VARCHAR2(70), "I53_REM05" VARCHAR2(70), "I53_REM06" VARCHAR2(70), "I53_REM07" VARCHAR2(70), "I53_REM08" VARCHAR2(70), "I53_REM09" VARCHAR2(70), "I53_REM10" VARCHAR2(70), "I53_REMX" LONG) PCTFREE 10 PCTUSED 40 INITRANS 1 MAXTRANS 255 STORAGE(INITIAL 31897600 NEXT 102400 MINEXTENTS 1 MAXEXTENTS 2147483645 PCTINCREASE 0 FREELISTS 1 FREELIST GROUPS 1) TABLESPACE "TIPS_IDS_D"
INSERT INTO "TIT_IDS_53_SHEET" ("I53_PROJECT", "I53_LOOP", "I53_TAG", "I53_SERVICE", "I53_PI", "I53_MFR", "I53_MODEL", "I53_DESCRIPTION1", "I53_DESCRIPTION2", "I53_REM01", "I53_REM02", "I53_REM03", "I53_REM04", "I53_REM05", "I53_REM06", "I53_REM07", "I53_REM08", "I53_REM09", "I53_REM10", "I53_REMX") VALUES (:1, :2, :3, :4, :5, :6, :7, :8, :9, :10, :11, :12, :13, :14, :15, :16, :17, :18, :19, :20)
The Import gives the following errors :
Import: Release 7.3.3.0.0 - Production on Mon May 15 17:35:50 2000
Copyright (c) Oracle Corporation 1979, 1996. All rights reserved.
Connected to: Oracle7 Server Release 7.3.4.0.1 - Production
With the distributed and replication options
PL/SQL Release 2.3.4.0.0 - Production
Connected to: Oracle7 Server Release 7.3.4.0.1 - Production
With the distributed and replication options
PL/SQL Release 2.3.4.0.0 - Production
Export file created by EXPORT:V07.03.03 via conventional path
Export file created by EXPORT:V07.03.03 via conventional path
. importing SQLUSER's objects into
. importing SQLUSER's objects into
IMP-00017:
IMP-00017: following statement failed with ORACLE error 1741:following statement failed with ORACLE error 1741:
"ALTER SCHEMA = """
"ALTER SCHEMA = """
IMP-00003:
IMP-00003: ORACLE error 1741 encounteredORACLE error 1741 encountered
ORA-01741: illegal zero-length identifier
ORA-01741: illegal zero-length identifier
. importing SQLUSER's objects into
. importing SQLUSER's objects into
IMP-00017:
IMP-00017: following statement failed with ORACLE error 1741:following statement failed with ORACLE error 1741:
"ALTER SCHEMA = """
"ALTER SCHEMA = """
IMP-00003:
IMP-00003: ORACLE error 1741 encounteredORACLE error 1741 encountered
ORA-01741: illegal zero-length identifier
ORA-01741: illegal zero-length identifier
. importing SQLUSER's objects into
. importing SQLUSER's objects into
IMP-00017:
IMP-00017: following statement failed with ORACLE error 1741:following statement failed with ORACLE error 1741:
"ALTER SCHEMA = """
"ALTER SCHEMA = """
IMP-00003:
IMP-00003: ORACLE error 1741 encounteredORACLE error 1741 encountered
ORA-01741: illegal zero-length identifier
ORA-01741: illegal zero-length identifier
. importing SQLUSER's objects into
. importing SQLUSER's objects into
IMP-00017:
IMP-00017: following statement failed with ORACLE error 1741:following statement failed with ORACLE error 1741:
"ALTER SCHEMA = """
"ALTER SCHEMA = """
IMP-00003:
IMP-00003: ORACLE error 1741 encounteredORACLE error 1741 encountered
ORA-01741: illegal zero-length identifier
ORA-01741: illegal zero-length identifier
. importing SQLUSER's objects into
. importing SQLUSER's objects into
IMP-00017:
IMP-00017: following statement failed with ORACLE error 1741:following statement failed with ORACLE error 1741:
"ALTER SCHEMA = """
"ALTER SCHEMA = """
IMP-00003:
IMP-00003: ORACLE error 1741 encounteredORACLE error 1741 encountered
ORA-01741: illegal zero-length identifier
ORA-01741: illegal zero-length identifier
. importing SQLUSER's objects into
. importing SQLUSER's objects into
IMP-00017:
IMP-00017: following statement failed with ORACLE error 1741:following statement failed with ORACLE error 1741:
"ALTER SCHEMA = """
"ALTER SCHEMA = """
IMP-00003:
IMP-00003: ORACLE error 1741 encounteredORACLE error 1741 encountered
ORA-01741: illegal zero-length identifier
ORA-01741: illegal zero-length identifier
. importing SQLUSER's objects into
. importing SQLUSER's objects into
IMP-00017:
IMP-00017: following statement failed with ORACLE error 1741:following statement failed with ORACLE error 1741:
"ALTER SCHEMA = """
"ALTER SCHEMA = """
IMP-00003:
IMP-00003: ORACLE error 1741 encounteredORACLE error 1741 encountered
ORA-01741: illegal zero-length identifier
ORA-01741: illegal zero-length identifier
. importing SQLUSER's objects into
. importing SQLUSER's objects into
IMP-00017:
IMP-00017: following statement failed with ORACLE error 1741:following statement failed with ORACLE error 1741:
"ALTER SCHEMA = """
"ALTER SCHEMA = """
IMP-00003:
IMP-00003: ORACLE error 1741 encounteredORACLE error 1741 encountered
ORA-01741: illegal zero-length identifier
ORA-01741: illegal zero-length identifier
. importing SQLUSER's objects into
. importing SQLUSER's objects into
IMP-00017:
IMP-00017: following statement failed with ORACLE error 942:following statement failed with ORACLE error 942:
"ALTER TABLE "TIT_IDS_53_SHEET" ADD CONSTRAINT "TIT_IDS_53_SHEET_PK" PRIMAR"
"ALTER TABLE "TIT_IDS_53_SHEET" ADD CONSTRAINT "TIT_IDS_53_SHEET_PK" PRIMAR"
"Y KEY ("I53_TAG","I53_PROJECT") USING INDEX STORAGE (INITIAL 972800 NEXT 10"
"Y KEY ("I53_TAG","I53_PROJECT") USING INDEX STORAGE (INITIAL 972800 NEXT 10"
"2400 MINEXTENTS 1 MAXEXTENTS 2147483645 PCTINCREASE 0 FREELISTS 1 ) TABLESP"
"2400 MINEXTENTS 1 MAXEXTENTS 2147483645 PCTINCREASE 0 FREELISTS 1 ) TABLESP"
"ACE "TIPS_IDS_I""
"ACE "TIPS_IDS_I""
IMP-00003:
IMP-00003: ORACLE error 942 encounteredORACLE error 942 encountered
ORA-00942: table or view does not exist
ORA-00942: table or view does not exist
IMP-00017:
IMP-00017: following statement failed with ORACLE error 1741:following statement failed with ORACLE error 1741:
"ALTER SCHEMA = """
"ALTER SCHEMA = """
IMP-00003:
Lesson #1.
Make sure that you have a good backup that you can restore to and get your database back the way it was prior to dropping any table!
Lesson #2.
Always test import using the actual export file before dropping a table.
(You can rename the original table(s) or test the import in a different schema - both of these assume there is sufficient space for two copies of the tables.)
This error looks like the kind of problem that Oracle tech support could best help you with. If you are running Oracle and don't have a support contract, I feel sorry for you. I find Oracle to be a very good, and usually reliable, database product but I wouldn't dare run a production database on Oracle without having support available.
Make sure that you have a good backup that you can restore to and get your database back the way it was prior to dropping any table!
Lesson #2.
Always test import using the actual export file before dropping a table.
(You can rename the original table(s) or test the import in a different schema - both of these assume there is sufficient space for two copies of the tables.)
This error looks like the kind of problem that Oracle tech support could best help you with. If you are running Oracle and don't have a support contract, I feel sorry for you. I find Oracle to be a very good, and usually reliable, database product but I wouldn't dare run a production database on Oracle without having support available.
I think this is what you're looking for:
IMPORT WITH IGNORE=Y GIVES ORA-1741
==========================
Bug No.: 661004
Date: 24-APR-1998
Product: Oracle Server - Enterprise Edition V7
Version: 7.3.3.0
Fixed in version: 7.3.4
RDBMS version: 7.3.3.0
Platform: DEC Alpha Windows NT
Error: ORA-1741
Status: 93 - Closed, Not Verified by Filer
when customer imports a table with data-manager with ignore=y, error ora-1741
occurs systematically.
In trace-file this error occurs in the parsing of statement
alter session set current_schema=""
i put in bug661004 :
ora00089.trc = trace file of import
import.log = log of import
Your problem is with the Data Manager's release which is:
Import: Release 7.3.3.0.0.
You should either use a higher version of Data Manager or do this import manually.
IMPORT WITH IGNORE=Y GIVES ORA-1741
==========================
Bug No.: 661004
Date: 24-APR-1998
Product: Oracle Server - Enterprise Edition V7
Version: 7.3.3.0
Fixed in version: 7.3.4
RDBMS version: 7.3.3.0
Platform: DEC Alpha Windows NT
Error: ORA-1741
Status: 93 - Closed, Not Verified by Filer
when customer imports a table with data-manager with ignore=y, error ora-1741
occurs systematically.
In trace-file this error occurs in the parsing of statement
alter session set current_schema=""
i put in bug661004 :
ora00089.trc = trace file of import
import.log = log of import
Your problem is with the Data Manager's release which is:
Import: Release 7.3.3.0.0.
You should either use a higher version of Data Manager or do this import manually.
Have you tried importing manually?
The bug seems to regard import with "ignore =y" option only.
you should be able to run it without that option.
Ignore=Y is used to indicate the
import to continue after errors.
In your case, having the table dropped,
there should'nt be any errors, and
the import should succedd.
(An error should occure while trying to
import an existing table and then, among other situations, is ignore=y
used).
your command should be
imp file=export.dmp touser=sqluser tables="sqluser.TIT_IDS_53
(the defaults are ignore=n and rows=y).
Try that.
The bug seems to regard import with "ignore =y" option only.
you should be able to run it without that option.
Ignore=Y is used to indicate the
import to continue after errors.
In your case, having the table dropped,
there should'nt be any errors, and
the import should succedd.
(An error should occure while trying to
import an existing table and then, among other situations, is ignore=y
used).
your command should be
imp file=export.dmp touser=sqluser tables="sqluser.TIT_IDS_53
(the defaults are ignore=n and rows=y).
Try that.
ASKER
How do i import manual ?
Or where can I find a higher version of 'data manager'.
Tried with following settings which gave same errors General:
Import File: c:\bck\oracle\ids.DMP # (Local Machine)
Import Mode: Tables
Import Objects: SQLUSER.TIT_IDS_53_SHEET
Associated Objects: Grants = No
Indexes = No
Rows = Yes
Ignore Creation Errors = Yes
Advanced:
Commit after each array = No
Overwrite existing data files = Yes
Scheduling:
Execute Immediately
Or where can I find a higher version of 'data manager'.
Tried with following settings which gave same errors General:
Import File: c:\bck\oracle\ids.DMP # (Local Machine)
Import Mode: Tables
Import Objects: SQLUSER.TIT_IDS_53_SHEET
Associated Objects: Grants = No
Indexes = No
Rows = Yes
Ignore Creation Errors = Yes
Advanced:
Commit after each array = No
Overwrite existing data files = Yes
Scheduling:
Execute Immediately
ASKER
Tried with grands & indexes = yes before
ASKER
Is there a tool to make an asci load file from the expdat.dmp file ?
Did you try to create the table manually before importing the data?
Perhaps everything works fine if the import doesn't have to create the table.
Perhaps everything works fine if the import doesn't have to create the table.
ASKER CERTIFIED SOLUTION
membership
This solution is only available to members.
To access this solution, you must be a member of Experts Exchange.
ASKER
Thanks all,
sbenyo you brougt me the idea to try 'imp' instead of datamanager. I no nothing about unix but managed to start 'imp'. After trying 'imp' a few times with ignore=yes and no, i had the same error as with data manager but also the error there wasn't enough space for the initial block. So after enlarging the space with storeage manager the 'imp' worked fine.
Thank you all.
sbenyo you brougt me the idea to try 'imp' instead of datamanager. I no nothing about unix but managed to start 'imp'. After trying 'imp' a few times with ignore=yes and no, i had the same error as with data manager but also the error there wasn't enough space for the initial block. So after enlarging the space with storeage manager the 'imp' worked fine.
Thank you all.
Are all the tables in the same owner?