6 Replies Latest reply on Mar 10, 2009 8:22 AM by 658474

    Schema level export/import

    658474
      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
          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.
            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-india
              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
                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.
                  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
                  1 person found this helpful
                  • 6. Re: Schema level export/import
                    658474
                    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