Solved

Data Manager : Export Import

Posted on 2000-05-15
10
1,615 Views
Last Modified: 2008-10-12

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:
0
Comment
Question by:jan_l_keersmaekers
  • 4
  • 2
  • 2
  • +2
10 Comments
 

Expert Comment

by:erezal
ID: 2811332
jan_l_keersmaekers ,

Are all the tables in the same owner?
0
 
LVL 34

Expert Comment

by:Mark Geerlings
ID: 2811858
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.
0
 
LVL 5

Expert Comment

by:sbenyo
ID: 2811955
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.

0
 

Expert Comment

by:erezal
ID: 2812251
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_SHEET"

(the defaults are ignore=n and rows=y).

Try that.
0
 

Author Comment

by:jan_l_keersmaekers
ID: 2813403
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
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.

 

Author Comment

by:jan_l_keersmaekers
ID: 2813405
Tried with grands & indexes = yes before
0
 

Author Comment

by:jan_l_keersmaekers
ID: 2813418
Is there a tool to make an asci load file from the expdat.dmp file ?
0
 
LVL 1

Expert Comment

by:pagerbak
ID: 2813457
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.
0
 
LVL 5

Accepted Solution

by:
sbenyo earned 300 total points
ID: 2813659
Hi,

First you can get a higer version of Data Manager  as part of the Oracle Enterprise Manager.

Version 1.4 , 1.5 , 1.6 and 2.x of OEM are avaliable.

You should know that this involves a new installation of the OEM, and that OEM version are compatiable with database version but also support prior database versions. (For instance versions 1.4-1.6 are compatiable with oracle 8.x and versions 2.x are compatiable with 8.1.x both can support 7.3.4)

OEM can be downloaded at technet.oracle.com but I don't think there are prior version to 2.x, so you have to order that from oracle.

Before trying to get a new version, try again with setting:

    Ignore Creation Errors = No

If this does not help try manually like this:

1. Open command window.

2. At command prompt issue:
c:\>imp help=y

see the version of the import utility.

2. Issue the command:

c:\>imp file=export.dmp touser=sqluser tables="sqluser.TIT_IDS_53_SHEET"

The table should not exist before else you get errors and the imp will fail (because it uses ignore=n)

The manual import must work, else oracle have a really severe bug.

Tell me if it worked, and if not what the version of the import utility.

Good Luck.
0
 

Author Comment

by:jan_l_keersmaekers
ID: 2816319
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.
0

Featured Post

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.

Join & Write a Comment

Suggested Solutions

I remember the day when someone asked me to create a user for an application developement. The user should be able to create views and materialized views and, so, I used the following syntax: (CODE) This way, I guessed, I would ensure that use…
Background In several of the companies I have worked for, I noticed that corporate reporting is off loaded from the production database and done mainly on a clone database which needs to be kept up to date daily by various means, be it a logical…
This video shows setup options and the basic steps and syntax for duplicating (cloning) a database from one instance to another. Examples are given for duplicating to the same machine and to different machines
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.

747 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

11 Experts available now in Live!

Get 1:1 Help Now