This discussion is archived
6 Replies Latest reply: Mar 10, 2009 1:22 AM by 658474 RSS

Schema level export/import

658474 Newbie
Currently Being Moderated
Hi,

Taking schmea(owner) export using sys id and import to target DB on another machine using the sys id to same schema(created) will be any issue.

I am just migrating every schema's object from one DB to other DB having same(created) schema to target DB.

Regards,
  • 1. Re: Schema level export/import
    247514 Expert
    Currently Being Moderated
    user9945534 wrote:
    Hi,

    Taking schmea(owner) export using sys id and import to target DB on another machine using the sys id to same schema(created) will be any issue.

    I am just migrating every schema's object from one DB to other DB having same(created) schema to target DB.

    Regards,
    What's Oracle version?

    It will work but better not use SYS to do export/import use other DBA account instead.

    If the source and target schema name is different you need to use FROMUSER/TOUSER with original exp/imp
  • 2. Re: Schema level export/import
    Kamran Agayev A. Oracle ACE Director
    Currently Being Moderated
    You can use [REMAP_SCHEMA|http://download.oracle.com/docs/cd/B19306_01/server.102/b14215/dp_import.htm#sthref340] option of [Data Pump Utility|http://download.oracle.com/docs/cd/B19306_01/server.102/b14215/dp_import.htm]


    - - - - - - - - - - - - - - - - - - - - -
    Kamran Agayev A. (10g OCP)
    http://kamranagayev.wordpress.com

    Edited by: Kamran Agayev A. on Mar 10, 2009 9:46 AM
  • 3. Re: Schema level export/import
    dba-hyd Pro
    Currently Being Moderated
    Use the following,

    exp username/pwd file=exp.dmp log=exp.log grants=Yes statistics=none buffer=800000

    imp username/pwd file=exp.dmp log=imp.log fromuser=username touser=username buffer=100000
  • 4. Re: Schema level export/import
    658474 Newbie
    Currently Being Moderated
    Hi,

    Thanks for your response.
    Actually i am using User name :Oracle which having sysdba privileges for export on source DB and doing import using sys user only.
    I will create one user to target machine which would have dba privileges and will import the same.


    while I am importing the dmp fle to target machine I am getting like below error:
    IMP-00015: following statement failed because the object already exists:
    "CREATE SEQUENCE "REPORT_RUN_NUMBER_SEQ" MINVALUE 1 MAXVALUE 999999999999999"
    "999999999999 INCREMENT BY 1 START WITH 6168 CACHE 20 NOORDER NOCYCLE"

    IMP-00017: following statement failed with ORACLE error 1917:
    "GRANT INSERT ON "PLAN_TABLE_OLD" TO "HPCMSASRPT""
    IMP-00003: ORACLE error 1917 encountered

    ORA-01917: user or role 'HPCMSASRPT' does not exist

    IMP-00017: following statement failed with ORACLE error 1652:
    "CREATE INDEX "WH_QRY_STAT_REQBEGDT_QRYRDTIM" ON "WH_QUERY_STATS" ("REQUEST_
    "BEGIN_DATE" , "QUERY_READ_TIME" ) PCTFREE 10 INITRANS 2 MAXTRANS 255 STORA
    "GE(INITIAL 163840 FREELISTS 1 FREELIST GROUPS 1) TABLESPACE "EDW_DET_EXPRES
    "S" LOGGING"

    ORA-06512: at "SYS.DBMS_STATS", line 3750
    ORA-06512: at "SYS.DBMS_STATS", line 3855
    ORA-06512: at line 1
    . . importing table "WH_QUERY_STMTS" 3870558 rows imported
    IMP-00017: following statement failed with ORACLE error 1652:
    "CREATE UNIQUE INDEX "QUERYSTMT_IDX" ON "WH_QUERY_STMTS" ("QUERY_STMT_ID" )
    " PCTFREE 10 INITRANS 2 MAXTRANS 255 STORAGE(INITIAL 262144 FREELISTS 1 FREE
    "LIST GROUPS 1) TABLESPACE "EDW_DET_EXPRESS" LOGGING"
    IMP-00003: ORACLE error 1652 encountered
    ORA-01652: unable to extend temp segment by 64 in tablespace TEMPTS1

    please let me know the possible workaround.

    Regards,
  • 5. Re: Schema level export/import
    Kamran Agayev A. Oracle ACE Director
    Currently Being Moderated
    user9945534 wrote:

    while I am importing the dmp fle to target machine I am getting like below error:
    IMP-00015: following statement failed because the object already exists:
    "CREATE SEQUENCE "REPORT_RUN_NUMBER_SEQ" MINVALUE 1 MAXVALUE 999999999999999"
    "999999999999 INCREMENT BY 1 START WITH 6168 CACHE 20 NOORDER NOCYCLE"

    IMP-00017: following statement failed with ORACLE error 1917:
    "GRANT INSERT ON "PLAN_TABLE_OLD" TO "HPCMSASRPT""
    IMP-00003: ORACLE error 1917 encountered

    ORA-01917: user or role 'HPCMSASRPT' does not exist

    IMP-00017: following statement failed with ORACLE error 1652:
    "CREATE INDEX "WH_QRY_STAT_REQBEGDT_QRYRDTIM" ON "WH_QUERY_STATS" ("REQUEST_
    "BEGIN_DATE" , "QUERY_READ_TIME" ) PCTFREE 10 INITRANS 2 MAXTRANS 255 STORA
    "GE(INITIAL 163840 FREELISTS 1 FREELIST GROUPS 1) TABLESPACE "EDW_DET_EXPRES
    "S" LOGGING"

    ORA-06512: at "SYS.DBMS_STATS", line 3750
    ORA-06512: at "SYS.DBMS_STATS", line 3855
    ORA-06512: at line 1
    . . importing table "WH_QUERY_STMTS" 3870558 rows imported
    IMP-00017: following statement failed with ORACLE error 1652:
    "CREATE UNIQUE INDEX "QUERYSTMT_IDX" ON "WH_QUERY_STMTS" ("QUERY_STMT_ID" )
    " PCTFREE 10 INITRANS 2 MAXTRANS 255 STORAGE(INITIAL 262144 FREELISTS 1 FREE
    "LIST GROUPS 1) TABLESPACE "EDW_DET_EXPRESS" LOGGING"
    IMP-00003: ORACLE error 1652 encountered
    ORA-01652: unable to extend temp segment by 64 in tablespace TEMPTS1

    please let me know the possible workaround.

    Regards,
    Wow. So many errors :)
    1. You have already REPORT_RUN_NUMBER_SEQ sequence. It will not import it once more
    2. GRANT INSERT ON "PLAN_TABLE_OLD" TO "HPCMSASRPT => You want to grant insert on plan_table_old to user HPCMSASRPT which you haven't . Create this user
    3. CREATE INDEX "WH_QRY_STAT_REQBEGDT_QRYRDTIM" ON "WH_QUERY_STATS => While creating Index, you faced ORA-01652: unable to extend temp segment by 64 in tablespace TEMPTS1 error.
    To avoid this problem make your temporary tablespace be autoextendable.
    ALTER DATABASE TEMPFILE '/u01/temp001.dbf' AUTOEXTEND ON NEXT 500M
    - - - - - - - - - - - - - - - - - - - - -
    Kamran Agayev A. (10g OCP)
    http://kamranagayev.wordpress.com
  • 6. Re: Schema level export/import
    658474 Newbie
    Currently Being Moderated
    I am doing so.

    I can see in my source DB number of users doesn't have quota on their relvant tablespace, but they have synonyms.
    users who having synonym only doen't require quota?

    SQL> select bytes/1024/1024,tablespace_name from dba_ts_quotas where username='E
    XPDPADMIN';

    BYTES/1024/1024 TABLESPACE_NAME
    --------------- ------------------------------
    0 USERS

    Regards,

    Edited by: user9945534 on Mar 10, 2009 1:20 AM

Legend

  • Correct Answers - 10 points
  • Helpful Answers - 5 points