My Oracle Support Banner

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

To view full details, sign in with your My Oracle Support account.

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


In this Document
Symptoms
Changes
Cause
Solution
References


This document is being delivered to you via Oracle Support's Rapid Visibility (RaV) process and therefore has not been subject to an independent technical review.
My Oracle Support provides customers with access to over a million knowledge articles and a vibrant support community of peers and Oracle experts.