export import issue

I have an application that uses two database schemas for storing data - QUARKDMS and CTXSYS. When I do the import of QUARKDMS and CTXSYS users using the command "imp 'sys as sysdba' FROMUSER=quarkdms,CTXSYS TOUSER=QUARKDMS,CTXSYS FILE='...' LOG='...' ", I get the following error:

IMP-00017: following statement failed with ORACLE error 6550:
"BEGIN "
"ctxsys.driimp.create_index('AST_TEXT_I','MAM_ASSETS',USER,"
"'ID','2','FTS_TEXT_UC','96',"
"'1','156572','8130','225822',"
"'FTS_LANG',NULL, NULL, 0,NULL, NULL, NULL, NULL, NULL );"

The above is only an excerpt. The complete log file is attached.

I have created the CTXSYS user using the following commands:
create user ctxsys identified by qdmspwd;
grant connect,resource to CTXSYS;
alter user CTXSYS default tablespace ctx_data;
alter user CTXSYS quota unlimited on CTX_DATA;
alter user CTXSYS quota unlimited on QUARKDMS_DATA;
alter user CTXSYS quota unlimited on QUARKDMS_INDEX;

I have been recommended that I create the user using catctx.sql script file, but my worry is that I will create all the objects of CTXSYS schema and the objects containing text indexing related data will not be created when I import the CTXSYS schema.

I am currently importing only quarkdms schema and then I will try to import ctxsys schema. I will share my observations with you.

Meanwhile, any feedback will be highly appreciated.

My bad, the import log file is attached now.

I assume import happens in an instance different from the one export happend, is that correct?

If the answer is yes, extract user creation from source database and apply against target one - having exactly the same tablespace structure will also help.

Quote:

---

Originally Posted by PAVB

I assume import happens in an instance different from the one export happend, is that correct?

If the answer is yes, extract user creation from source database and apply against target one - having exactly the same tablespace structure will also help.

---

Yes, the import instance is different from export instance. How can I extract only the user creation information? Thanks!

Quote:

---

Originally Posted by mahajanakhil198

Yes, the import instance is different from export instance. How can I extract only the user creation information? Thanks!

---

All GUI frontend tools allow to do it in a way or another, if you rely exclusively in sqlplus - nothing wrong about it - you can always extract DDL via DBMS_METADATA.GET_DDL. In this case something like...

Code:

---

SET SERVEROUTPUT ON LINESIZE 256 FEEDBACK OFF PAGESIZE 1000
SET LONG 999999
SELECT DBMS_METADATA.GET_DDL ( 'USER_NAME', U.username)
FROM  DBA_USERS U;

---

Are your two tablespaces a transportable tablespace set? If not, they why are you importing as SYS? Self-inflicted wound number one.

Is CTXSYS in the source a name you created? And now finding out it conflicts with the real CTXSYS schema in Oracle? If you used the same name on purpose, that was not a very good plan. Self-inflicted wound number two.

The real CTXSYS is not exported in the first place:

Quote:

---

WHY SCHEMAS CTXSYS, MDSYS AND ORDSYS ARE NOT EXPORTED

You cannot export any schema that contains dictionary metadata. This is a restriction of export process.

Schemas that exclusively contain dictionary objects like SYS, CTXSYS, MDSYS and ORDSYS are never exported nor imported. This is because these schemas are created when you install the related database option or when you create your database. Thus it is not necessary for export to generate create statements for their objects.

---

If you thought you were exporting CTXSYS (the real one) from the source, no. Wound number three.