Shulou(Shulou.com)06/01 Report--

This article will explain in detail how to import error reporting ORA-02304 for IMP objects in the same library. The content of the article is of high quality, so the editor will share it with you for reference. I hope you will have a certain understanding of the relevant knowledge after reading this article.

When we use exp/imp, there is actually no particularly good solution to error reporting ORA-02304. The essence of the error report when Type imports the same library is that Oracle will export the oid of type together with it when exporting. When you import it, you want to restore it to the same oid and cause a conflict.

So, is there nothing we can do? We can avoid this problem with the help of the data pump (Data Dump) tool proposed by Oracle 10g.

1. Environmental preparation

We also use Oracle 11gR2 to carry out experiments.

SQL > select * from v$version

BANNER

Oracle Database 11g Enterprise Edition Release 11.2.0.1.0-Production

PL/SQL Release 11.2.0.1.0-Production

CORE 11.2.0.1.0 Production

Under the scott user, we create some objects of type type.

SQL > grant imp_full_database to scott

Grant succeeded

SQL > grant exp_full_database to scott

Grant succeeded

SQL > conn scott/tiger@wilson

Connected to Oracle Database 11g Enterprise Edition Release 11.2.0.1.0

Connected as scott

SQL > create type mt_type as object (xm number, tchar varchar2 (10))

2 /

Type created

SQL > select type_name, type_oid from user_types

TYPE_NAME TYPE_OID

MT_TYPE C230A55B1FC34E1DE040A8C0580017C6

SQL > create table my_tabletype of mt_type

Table created

SQL > insert into my_tabletype values (1)

1 row inserted

SQL > commit

Commit complete

After that, we create the user scottback. Use the data pump expdp to export data from scott.

SQL > create user scottback identified by scottback

User created

SQL > grant resource to scottback

Grant succeeded

SQL > grant connect to scottback

Grant succeeded

SQL > grant exp_full_database to scottback

Grant succeeded

SQL > grant imp_full_database to scottback

Grant succeeded

2. Expdp data export

As a new generation of data backup and restore tool introduced in 10g, data pump DataDump has many good characteristics. DataDump is a server-side tool that needs to be executed on the server.

First, we need to create a directory object that corresponds to a directory location on the server.

[root@oracle11g /] # pwd

/

[root@oracle11g /] # mkdir export

[root@oracle11g /] # ls-l | grep export

Drwxr-xr-x 2 root root 4096 Jun 11 19:29 export

[root@oracle11g /] # chown oracle:oinstall export

[root@oracle11g /] # ls-l | grep export

Drwxr-xr-x 2 oracle oinstall 4096 Jun 11 19:39 export

Create a directory object and grant read write permissions to scott and scottback.

SQL > create or replace directory MY_DIR

2 as'/ export'

Directory created

SQL > grant write, read on directory my_dir to scott

Grant succeeded

SQL > grant write, read on directory my_dir to scottback

Grant succeeded'

Then use the expdp command line for export.

[oracle@oracle11g ~] $cd / export/

[oracle@oracle11g export] $pwd

/ export

[oracle@oracle11g export] $expdp scott/tiger@wilson directory=my_dir dumpfile=scott.dmp logfile=resexp.log schemas=scott

Export: Release 11.2.0.1.0-Production on Mon Jun 11 19:35:08 2012

[oracle@oracle11g export] $expdp scott/tiger@wilson directory=my_dir dumpfile=scott.dmp logfile=resexp.log schemas=scott

Export: Release 11.2.0.1.0-Production on Mon Jun 11 19:35:08 2012

Copyright (c) 1982, 2009, Oracle and/or its affiliates. All rights reserved.

Connected to: Oracle Database 11g Enterprise Edition Release 11.2.0.1.0-Production

With the Partitioning, OLAP, Data Mining and Real Application Testing options

Starting "SCOTT". "SYS_EXPORT_SCHEMA_01": scott/*@wilson directory=my_dir dumpfile=scott.dmp logfile=resexp.log schemas=scott

Estimate in progress using BLOCKS method...

Processing object type SCHEMA_EXPORT/TABLE/TABLE_DATA

The reason for the length of the space is partly omitted. .)

. . Exported "SCOTT". "T" 0 KB 0 rows

. . Exported "SCOTT". "T1" 0 KB 0 rows

. . Exported "SCOTT". "T2" 0 KB 0 rows

Master table "SCOTT". "SYS_EXPORT_SCHEMA_01" successfully loaded/unloaded

*

Dump file set for SCOTT.SYS_EXPORT_SCHEMA_01 is:

/ export/scott.dmp

Job "SCOTT". "SYS_EXPORT_SCHEMA_01" successfully completed at 19:36:00

[oracle@oracle11g export] $ls-l

Total 420

-rw-r--r-- 1 oracle oinstall 2467 Jun 11 19:36 resexp.log

-rw-r- 1 oracle oinstall 421888 Jun 11 19:36 scott.dmp

3. Impdp imports data

In the default impdp mode, type cannot be imported into the same database.

[oracle@oracle11g export] $impdp scottback/scottback@wilson directory=my_dir dumpfile=scott.dmp logfile=resimp.log remap_schema=scott:scottback

Import: Release 11.2.0.1.0-Production on Mon Jun 11 19:37:37 2012

Copyright (c) 1982, 2009, Oracle and/or its affiliates. All rights reserved.

Connected to: Oracle Database 11g Enterprise Edition Release 11.2.0.1.0-Production

With the Partitioning, OLAP, Data Mining and Real Application Testing options

Master table "SCOTTBACK". "SYS_IMPORT_FULL_01" successfully loaded/unloaded

Starting "SCOTTBACK". "SYS_IMPORT_FULL_01": scottback/*@wilson directory=my_dir dumpfile=scott.dmp logfile=resimp.log remap_schema=scott:scottback

Processing object type SCHEMA_EXPORT/USER

ORA-31684: Object type USER: "SCOTTBACK" already exists

Processing object type SCHEMA_EXPORT/SYSTEM_GRANT

Processing object type SCHEMA_EXPORT/ROLE_GRANT

Processing object type SCHEMA_EXPORT/DEFAULT_ROLE

Processing object type SCHEMA_EXPORT/PRE_SCHEMA/PROCACT_SCHEMA

Processing object type SCHEMA_EXPORT/TYPE/TYPE_SPEC

ORA-39083: Object type TYPE failed to create with error:

ORA-02304: invalid object identifier literal

Failing sql is:

CREATE TYPE "SCOTTBACK". "MT_TYPE" OID 'C230A55B1FC34E1DE040A8C0580017C6' as object (xm number, tchar varchar2 (10))

Processing object type SCHEMA_EXPORT/TABLE/TABLE

ORA-39117: Type needed to create table is not included in this operation. Failing sql is:

CREATE TABLE "SCOTTBACK". "MY_TABLETYPE" OF "SCOTTBACK". "MT_TYPE" OID 'C230B8AA21E527C9E040A8C058001816' OIDINDEX (PCTFREE 10 INITRANS 2 MAXTRANS 255STORAGE (INITIAL 65536 NEXT 1048576 MINEXTENTS 1 MAXEXTENTS 2147483645 PCTINCREASE 0 FREELISTS 1 FREELIST GROUPS 1 BUFFER_POOL DEFAULT FLASH_CACHE DEFAULT CELL_FLASH_CACHE DEFAULT) TABLESPACE "SYSTEM") PCTFREE 10 PCTUSED 40 INITRANS 1 MAXTRANS 255 NOCOMPRESS LOGGING

Processing object type SCHEMA_EXPORT/TABLE/TABLE_DATA

. . Imported "SCOTTBACK". "BASELINE_TEST" 22.90 KB 1 rows

(the reason for the length, omit the part. .)

Processing object type SCHEMA_EXPORT/TABLE/STATISTICS/TABLE_STATISTICS

Job "SCOTTBACK". "SYS_IMPORT_FULL_01" completed with 4 error (s) at 19:37:47

Note that by default, we still encounter problems with oid when we import data. It still shows that the oid information is included in the SQL created by type, causing oid conflicts. In turn, the associated data table my_tabletype cannot be created.

There is a clip to focus on:

CREATE TYPE "SCOTTBACK". "MT_TYPE" OID 'C230A55B1FC34E1DE040A8C0580017C6' as object (xm number, tchar varchar2 (10))

Processing object type SCHEMA_EXPORT/TABLE/TABLE

ORA-39117: Type needed to create table is not included in this operation. Failing sql is:

CREATE TABLE "SCOTTBACK". "MY_TABLETYPE" OF "SCOTTBACK". "MT_TYPE" OID 'C230B8AA21E527C9E040A8C058001816' OIDINDEX (PCTFREE 10 INITRANS 2 MAXTRANS 255STORAGE (INITIAL 65536 NEXT 1048576 MINEXTENTS)

The association oid is the same, indicating that the oid of the type is used as important information for the association within the Oracle.

In impdp, we can use the transform parameter setting to require the oid mapping in the original dmp file to be regenerated.

[oracle@oracle11g export] $impdp scottback/scottback@wilson directory=my_dir dumpfile=scott.dmp logfile=resimp2.log remap_schema=scott:scottback transform=oid:n

Import: Release 11.2.0.1.0-Production on Mon Jun 11 19:39:07 2012

Copyright (c) 1982, 2009, Oracle and/or its affiliates. All rights reserved.

Connected to: Oracle Database 11g Enterprise Edition Release 11.2.0.1.0-Production

With the Partitioning, OLAP, Data Mining and Real Application Testing options

Master table "SCOTTBACK". "SYS_IMPORT_FULL_01" successfully loaded/unloaded

Starting "SCOTTBACK". "SYS_IMPORT_FULL_01": scottback/*@wilson directory=my_dir dumpfile=scott.dmp logfile=resimp2.log remap_schema=scott:scottback transform=oid:n

Processing object type SCHEMA_EXPORT/USER

ORA-31684: Object type USER: "SCOTTBACK" already exists

Processing object type SCHEMA_EXPORT/SYSTEM_GRANT

Processing object type SCHEMA_EXPORT/ROLE_GRANT

Processing object type SCHEMA_EXPORT/DEFAULT_ROLE

Processing object type SCHEMA_EXPORT/PRE_SCHEMA/PROCACT_SCHEMA

Processing object type SCHEMA_EXPORT/TYPE/TYPE_SPEC

Processing object type SCHEMA_EXPORT/TABLE/TABLE

Processing object type SCHEMA_EXPORT/TABLE/TABLE_DATA

. . Imported "SCOTTBACK". "BASELINE_TEST" 22.90 KB 1 rows

. . Imported "SCOTTBACK". "DEPT" 5.937 KB 4 rows

. . Imported "SCOTTBACK". "EMP" 8.992 KB 14 rows

. . Imported "SCOTTBACK". "MY_TABLETYPE" 6.507 KB 1 rows

. . Imported "SCOTTBACK". "SALES_QUAL" 6.007 KB 6 rows

. . Imported "SCOTTBACK". "SALGRADE" 5.867 KB 5 rows

. . Imported "SCOTTBACK". "BONUS" 0 KB 0 rows

. . Imported "SCOTTBACK". "T" 0 KB 0 rows

. . Imported "SCOTTBACK". "T1" 0 KB 0 rows

. . Imported "SCOTTBACK". "T2" 0 KB 0 rows

Processing object type SCHEMA_EXPORT/TABLE/INDEX/INDEX

Processing object type SCHEMA_EXPORT/TABLE/CONSTRAINT/CONSTRAINT

Processing object type SCHEMA_EXPORT/TABLE/INDEX/STATISTICS/INDEX_STATISTICS

Processing object type SCHEMA_EXPORT/TABLE/COMMENT

Processing object type SCHEMA_EXPORT/VIEW/VIEW

ORA-31684: Object type VIEW: "SCOTTBACK". "V_T1" already exists

Processing object type SCHEMA_EXPORT/TABLE/CONSTRAINT/REF_CONSTRAINT

Processing object type SCHEMA_EXPORT/TABLE/STATISTICS/TABLE_STATISTICS

Job "SCOTTBACK". "SYS_IMPORT_FULL_01" completed with 2 error (s) at 19:39:20

Among them, the value oid:n of transform means that the oid information is not loaded and re-generated. The value of the data table is correct.

SQL > conn scottback/scottback@wilson

Connected to Oracle Database 11g Enterprise Edition Release 11.2.0.1.0

Connected as scottback

SQL > select * from my_tabletype

XM TCHAR

--

1 df

4. Conclusion

With the continuous improvement of Oracle functions, many new features can no longer be supported in exp/imp tools. Data Dump launched under Oracle 10g has many functions that we can use for reference.

About IMP with the library Type object import error ORA-02304 how to share here, I hope the above content can be of some help to you, can learn more knowledge. If you think the article is good, you can share it for more people to see.

Welcome to subscribe "Shulou Technology Information " to get latest news, interesting things and hot topics in the IT industry, and controls the hottest and latest Internet news, technology news and IT industry trends.