ORA-00904: "SYS_NC000nn$": Invalid Identifier On Data Pump Import (Doc ID 1297086.1)

Last updated on NOVEMBER 28, 2016

Applies to:

Oracle Database - Standard Edition - Version 10.1.0.2 to 11.2.0.2 [Release 10.1 to 11.2]
Information in this document applies to any platform.
***Checked for relevance on 24-Jan-2014***

Symptoms

...
Processing object type SCHEMA_EXPORT/TABLE/TABLE
Processing object type SCHEMA_EXPORT/TABLE/TABLE_DATA
ORA-31693: Table data object "TC1"."TAB1" failed to load/unload and is being skipped due to error:
ORA-00904: "SYS_NC00007$": invalid identifier
Processing object type SCHEMA_EXPORT/TABLE/INDEX/FUNCTIONAL_AND_BITMAP/INDEX
Job "TC1"."SYS_IMPORT_FULL_01" completed with 2 error(s) at 09:44:55
- Table uses function based index(es) and
- A XML column was added after function based indexes were created.



TEST CASE
The following test case illustrates this in more detail:


-- Create test user
drop user tc1 cascade;
create user tc1 identified by tc1 default tablespace users;
grant dba to tc1;


-- create table & other objects
drop table tc1.tab1;

-- STEP 1
create table tc1.tab1
(col1 number(12,7) not null enable,
col2 number(12,7),
col3 number(12,7)) ;

-- STEP 2
create index tc1.idx_1
on tc1.tab1 (col1+col2);

alter index tc1.idx_1 noparallel;

-- STEP 3
create index tc1.idx_2
on tc1.tab1 (col1+col3);

alter index tc1.idx_2 noparallel;

-- STEP 4
alter table tc1.tab1
add (col4 xmltype)
xmltype column col4 store as clob ;

-- Insert sample data
insert into tc1.tab1 values (1,1,1,xmltype('<A> scott </A>'));
commit;


connect / as sysdba
select COL#, SEGCOL#, SEGCOLLENGTH, NAME, TYPE#
from sys.col$
where obj# in
(select object_id from dba_objects
where owner='TC1'
and object_name='TAB1')
and name like '%NC%';

COL# SEGCOL# SEGCOLLENGTH NAME          TYPE#
---- ------- ------------ ------------- -----
   0       0           22 SYS_NC00004$      2 -- function based index
   0       0           22 SYS_NC00005$      2 -- function based index
   4       4         4000 SYS_NC00007$    112 -- XML column


-- directory
create or replace directory test_dp as '/tmp';

-- Export table/indexes
rm /tmp/export_schemas.dmp
expdp tc1/tc1 DIRECTORY=test_dp DUMPFILE=export_schemas.dmp


-- Import table/indexes
drop table tc1.TAB1;
purge dba_recyclebin;

impdp tc1/tc1 directory=test_dp DUMPFILE=export_schemas.dmp


Import: Release 10.2.0.4.0 - 64bit Production on Friday, 18 February, 2011 9:44:47

Copyright (c) 2003, 2007, Oracle. All rights reserved.

Connected to: Oracle Database 10g Enterprise Edition Release 10.2.0.4.0 - 64bit Production
With the Partitioning, OLAP, Data Mining and Real Application Testing options
Master table "TC1"."SYS_IMPORT_FULL_01" successfully loaded/unloaded
Starting "TC1"."SYS_IMPORT_FULL_01": tc1/******** directory=test_dp DUMPFILE=export_schemas.dmp
Processing object type SCHEMA_EXPORT/USER
ORA-31684: Object type USER:"TC1" 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/TABLE/TABLE
Processing object type SCHEMA_EXPORT/TABLE/TABLE_DATA
ORA-31693: Table data object "TC1"."TAB1" failed to load/unload and is being skipped due to error:
ORA-00904: "SYS_NC00007$": invalid identifier
Processing object type SCHEMA_EXPORT/TABLE/INDEX/FUNCTIONAL_AND_BITMAP/INDEX
Job "TC1"."SYS_IMPORT_FULL_01" completed with 2 error(s) at 09:44:55




Changes

Cause

Sign In with your My Oracle Support account

Don't have a My Oracle Support account? Click to get started

My Oracle Support provides customers with access to over a
Million Knowledge Articles and hundreds of Community platforms