Skip to Main Content

Oracle Database Discussions

Announcement

For appeals, questions and feedback about Oracle Forums, please email oracle-forums-moderators_us@oracle.com. Technical questions should be asked in the appropriate category. Thank you!

Interested in getting your voice heard by members of the Developer Marketing team at Oracle? Check out this post for AppDev or this post for AI focus group information.

Data pump import error with nested tables

3520Feb 4 2010 — edited Feb 5 2010
So the problem is somewhat long :)
Actually the problems are two - why and how oracle are treating OO concept and why data pump doesn't work?
So scenario for the 1st one:
1) there is object type h1 and table of h1
2) there is unrelated object type row_text and table of row_text
3) there is object type h2 under h1 with attribute as table of row_text
4) there is table tab1 with column b with data type as table of h1. Of course column b is stored as nested table.

So how do you think - how many nested tables Oracle will create? The correct answer is 2. One explicitly defined and one hidden with system
generated name for the type h2 which is under type h1. So the question is WHY? Why if I create an instance of supertype Oracle tries to adapt
it for the subtype as well? Even more if I create another subtype h3 under h1 another hidden nested table appears.
This was the first part.

The second part is - if I do schema export and try to import it in another schema I got error saying that oracle failed to create storage table for
nested table column b. So the second question is - if Oracle has created such a mess with hidden nested tables how to import/export to another
schema?

Ok and here is test case to demonstrate problems above:
-- creating type h1 and table of it
SQL> create or replace type h1 as object (a number)
  2  not final;
  3  /

Type created.

SQL> create or replace type tbl_h1 as table of h1;
  2  /

Type created.

-- creating type row_text and table of it
SQL> create or replace type row_text as object (
  2    txt varchar2(100))
  3  not final;
  4  /

Type created.

SQL> create or replace type tbl_row_text as table of row_text;
  2  /

Type created.

-- creating type h2 as subtype of h1
SQL> create or replace type h2 under h1 (some_texts tbl_row_text);
  2  /

Type created.

SQL> create table tab1 (a number, b tbl_h1)
  2  nested table b
  3  store as tab1_nested;

Table created.

-- so we have 2 nested tables now
SQL> select table_name, parent_table_name, parent_table_column
  2  from user_nested_tables;

TABLE_NAME                     PARENT_TABLE_NAME
------------------------------ ------------------------------
PARENT_TABLE_COLUMN
-------------------------------------------------------------------
SYSNTfsl/+pzu3+jgQAB/AQB27g==  TAB1_NESTED
TREAT(SYS_NC_ROWINFO$ AS "GINTS"."H2")."SOME_TEXTS"

TAB1_NESTED                    TAB1
B

-- another subtype of t1
SQL> create or replace type h3 under h1 (some_texts tbl_row_text);
  2  /

Type created.

-- plus another nested table
SQL> select table_name, parent_table_name, parent_table_column
  2  from user_nested_tables;

TABLE_NAME                     PARENT_TABLE_NAME
------------------------------ ------------------------------
PARENT_TABLE_COLUMN
-------------------------------------------------------------------
SYSNTfsl/+pzu3+jgQAB/AQB27g==  TAB1_NESTED
TREAT(SYS_NC_ROWINFO$ AS "GINTS"."H2")."SOME_TEXTS"

SYSNTfsl/+pz03+jgQAB/AQB27g==  TAB1_NESTED
TREAT(SYS_NC_ROWINFO$ AS "GINTS"."H3")."SOME_TEXTS"

TAB1_NESTED                    TAB1
B


SQL> desc "SYSNTfsl/+pzu3+jgQAB/AQB27g=="
 Name                                      Null?    Type
 ----------------------------------------- -------- ---------------
 TXT                                                VARCHAR2(100)
OK let it be and now I'm trying to export and import in another schema:
[oracle@xxx]$ expdp gints/xxx@xxx directory=xxx dumpfile=gints.dmp logfile=gints.log

Export: Release 11.2.0.1.0 - Production on Thu Feb 4 22:32:48 2010

<irrelevant rows skipped>

Processing object type SCHEMA_EXPORT/TABLE/STATISTICS/TABLE_STATISTICS
. . exported "GINTS"."TAB1"                                  0 KB       0 rows
. . exported "GINTS"."SYSNTfsl/+pz03+jgQAB/AQB27g=="         0 KB       0 rows
. . exported "GINTS"."TAB1_NESTED"                           0 KB       0 rows
. . exported "GINTS"."SYSNTfsl/+pzu3+jgQAB/AQB27g=="         0 KB       0 rows
Master table "GINTS"."SYS_EXPORT_SCHEMA_01" successfully loaded/unloaded
******************************************************************************
And now import. In order to create types transformation of OIDs is applied and also remap_schema
Although it fails to create the table.
[oracle@xxx]$ impdp gints1/xxx@xxx directory=xxx dumpfile=gints.dmp logfile=gints_imp.log remap_schema=gints:gints1 transform=OID:n

Import: Release 11.2.0.1.0 - Production on Thu Feb 4 22:41:48 2010

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

Connected to: Oracle Database 11g Release 11.2.0.1.0 - Production
Master table "GINTS1"."SYS_IMPORT_FULL_01" successfully loaded/unloaded
Starting "GINTS1"."SYS_IMPORT_FULL_01":  gints1/********@xxx directory=xxx dumpfile=gints.dmp logfile=gints_imp.log remap_schema=gints:gints1 transform=OID:n
Processing object type SCHEMA_EXPORT/USER
ORA-31684: Object type USER:"GINTS1" 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
ORA-39083: Object type TABLE:"GINTS1"."TAB1" failed to create with error:
ORA-02320: failure in creating storage table for nested table column B
ORA-00904: : invalid identifier
Failing sql is:
CREATE TABLE "GINTS1"."TAB1" ("A" NUMBER, "B" "GINTS1"."TBL_H1" ) SEGMENT CREATION IMMEDIATE PCTFREE 10 PCTUSED 40 INITRANS 1 MAXTRANS 255 NOCOMPRESS LOGGING STORAGE(INITIAL 65536 NEXT 1048576 MINEXTENTS 1 MAXEXTENTS 2147483645 PCTINCREASE 0 FREELISTS 1 FREELIST GROUPS 1 BUFFER_POOL DEFAULT FLASH_CACHE DEFAULT CELL_
Processing object type SCHEMA_EXPORT/TABLE/TABLE_DATA
Processing object type SCHEMA_EXPORT/TABLE/INDEX/INDEX
Processing object type SCHEMA_EXPORT/TABLE/INDEX/STATISTICS/INDEX_STATISTICS
ORA-39083: Object type INDEX_STATISTICS failed to create with error:
ORA-01403: no data found
ORA-01403: no data found
Failing sql is:
DECLARE I_N VARCHAR2(60);   I_O VARCHAR2(60);   c DBMS_METADATA.T_VAR_COLL;   df varchar2(21) := 'YYYY-MM-DD:HH24:MI:SS'; BEGIN  DELETE FROM "SYS"."IMPDP_STATS";   c(1) :=   DBMS_METADATA.GET_STAT_COLNAME('GINTS1','TAB1_NESTED',NULL,'TREAT(SYS_NC_ROWINFO$ AS "GINTS"."H2")."SOME_TEXTS"',1);  DBMS_METADATA.GET_STAT_INDNAME('GINTS1','TAB1_NESTED',c,1,i_o,i_n);   INSERT INTO "
ORA-39083: Object type INDEX_STATISTICS failed to create with error:
ORA-01403: no data found
ORA-01403: no data found
Failing sql is:
DECLARE I_N VARCHAR2(60);   I_O VARCHAR2(60);   c DBMS_METADATA.T_VAR_COLL;   df varchar2(21) := 'YYYY-MM-DD:HH24:MI:SS'; BEGIN  DELETE FROM "SYS"."IMPDP_STATS";   c(1) :=   DBMS_METADATA.GET_STAT_COLNAME('GINTS1','TAB1_NESTED',NULL,'TREAT(SYS_NC_ROWINFO$ AS "GINTS"."H3")."SOME_TEXTS"',1);  DBMS_METADATA.GET_STAT_INDNAME('GINTS1','TAB1_NESTED',c,1,i_o,i_n);   INSERT INTO "
Processing object type SCHEMA_EXPORT/TABLE/STATISTICS/TABLE_STATISTICS
Job "GINTS1"."SYS_IMPORT_FULL_01" completed with 4 error(s) at 22:41:52
So any idea how to make export/import of such tables?

TIA
Gints

Comments

damorgan
Tom Kyte has said it repeatedly ... I will repeat it here for you.

The fact that Oracle allows you to build object tables is not an indication that you should.
Store your data relationally and build object_views on top of them.
http://www.morganslibrary.org/reference/object_views.html

If you model properly, and store properly, you don' have any issues.
3520
Yep I know it
I said that to our developers as well
Problem is, that they have created such constructions before

On the other hand creating functionality that only works sometimes or partially is also at least weird not to say neglectful. Unfortunately this more and more happens with Oracle...
1 - 2
Locked Post
New comments cannot be posted to this locked post.

Post Details

Locked on Mar 5 2010
Added on Feb 4 2010
2 comments
2,695 views