export import issue
DBAsupport.com Forums - Powered by vBulletin
Results 1 to 6 of 6

Thread: export import issue

Hybrid View

  1. #1
    Join Date
    Oct 2008
    Location
    Chandigarh, India
    Posts
    322

    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.
    lucky

  2. #2
    Join Date
    Oct 2008
    Location
    Chandigarh, India
    Posts
    322
    My bad, the import log file is attached now.
    Attached Files Attached Files
    lucky

  3. #3
    Join Date
    Mar 2007
    Location
    Ft. Lauderdale, FL
    Posts
    3,554
    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.
    Pablo (Paul) Berzukov

    Author of Understanding Database Administration available at amazon and other bookstores.

    Disclaimer: Advice is provided to the best of my knowledge but no implicit or explicit warranties are provided. Since the advisor explicitly encourages testing any and all suggestions on a test non-production environment advisor should not held liable or responsible for any actions taken based on the given advice.

  4. #4
    Join Date
    Oct 2008
    Location
    Chandigarh, India
    Posts
    322
    Quote Originally Posted by PAVB View Post
    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!
    lucky

  5. #5
    Join Date
    Mar 2007
    Location
    Ft. Lauderdale, FL
    Posts
    3,554
    Quote Originally Posted by mahajanakhil198 View Post
    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;
    Pablo (Paul) Berzukov

    Author of Understanding Database Administration available at amazon and other bookstores.

    Disclaimer: Advice is provided to the best of my knowledge but no implicit or explicit warranties are provided. Since the advisor explicitly encourages testing any and all suggestions on a test non-production environment advisor should not held liable or responsible for any actions taken based on the given advice.

  6. #6
    Join Date
    May 2002
    Posts
    2,645
    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:

    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.
    Last edited by stecal; 04-09-2011 at 10:03 AM.

Posting Permissions

  • You may not post new threads
  • You may not post replies
  • You may not post attachments
  • You may not edit your posts
  •  



Click Here to Expand Forum to Full Width