Go Premium for a chance to win a PS4. Enter to Win

x
?
Solved

Data Manager : Export Import

Posted on 2000-05-15
10
Medium Priority
?
1,663 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 35

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
VIDEO: THE CONCERTO CLOUD FOR HEALTHCARE

Modern healthcare requires a modern cloud. View this brief video to understand how the Concerto Cloud for Healthcare can help your organization.

 

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
 

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 900 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

Get free NFR key for Veeam Availability Suite 9.5

Veeam is happy to provide a free NFR license (1 year, 2 sockets) to all certified IT Pros. The license allows for the non-production use of Veeam Availability Suite v9.5 in your home lab, without any feature limitations. It works for both VMware and Hyper-V environments

Question has a verified solution.

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

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…
Have you ever had to make fundamental changes to a table in Oracle, but haven't been able to get any downtime?  I'm talking things like: * Dropping columns * Shrinking allocated space * Removing chained blocks and restoring the PCTFREE * Re-or…
This video shows how to copy a database user from one database to another user DBMS_METADATA.  It also shows how to copy a user's permissions and discusses password hash differences between Oracle 10g and 11g.
Video by: Steve
Using examples as well as descriptions, step through each of the common simple join types, explaining differences in syntax, differences in expected outputs and showing how the queries run along with the actual outputs based upon a simple set of dem…
Suggested Courses

971 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