1. Get rid of all advertisements and get unlimited access to documents by upgrading to Premium Membership. Upgrade to Premium Now and also get a Premium Badge!

Import schema contain trigger and sequence

Discussion in 'SQL PL/SQL' started by mzainal, Jan 18, 2010.

  1. mzainal

    mzainal Forum Advisor

    Messages:
    72
    Likes Received:
    0
    Trophy Points:
    130
    Hi,

    I'm using Oracle DB. So, i want to import schema Z from database A to database B. What is step i need to do? I use regular import but fail. Got so many error.

    So, i need some idea from expert here that deal with oracle db at real time. Please suggest me a step.

    Thank you.
     
  2. zargon

    zargon Community Moderator Forum Guru

    Messages:
    2,346
    Likes Received:
    347
    Trophy Points:
    1,430
    Location:
    Aurora, CO
    No one can 'suggest you' anything at this point except to post all of these 'so many error' so we can see what you're generating and, as a result, offer usable suggestions. You do not post the Oracle version or versions you're using, which user account was used to make the export, which user account was used to perform the import, you've posted nothing of any real use to allow someone to provide assistance. We are not mind-readers nor do we have a crystal ball to divine answers.

    Post the requested information; someone may be able to help you after you do.
     
  3. mzainal

    mzainal Forum Advisor

    Messages:
    72
    Likes Received:
    0
    Trophy Points:
    130
    I use this command:
    Code (Text):
    impdp ifms/password directory=extdata dumpfile=dump12jan10.all full=y remap_schema=azmansl:ifms remap_tablespace=missiontest:databank
    Error:
    Code (Text):
    ORA-31685: Object type SEQUENCE:"IFMS"."TR_VENUE_ID" failed due to insufficient privileges. Failing sql is:
     CREATE SEQUENCE  "IFMS"."TR_VENUE_ID"  MINVALUE 1 MAXVALUE 999999999999999999999999999 INCREMENT BY 1 START WITH 81 CACHE 20 NOORDER  NOCYCLE

    ORA-31685: Object type SEQUENCE:"IFMS"."TR_ZONE_ID" failed due to insufficient privileges. Failing sql is:
     CREATE SEQUENCE  "IFMS"."TR_ZONE_ID"  MINVALUE 1 MAXVALUE 999999999999999999999999999 INCREMENT BY 1 START WITH 42 CACHE 20 NOORDER  NOCYCLE

    ORA-31685: Object type SEQUENCE:"IFMS"."UPLOADEDFILEIDSEQ" failed due to insufficient privileges. Failing sql is:
     CREATE SEQUENCE  "IFMS"."UPLOADEDFILEIDSEQ"  MINVALUE 1 MAXVALUE 999999999999999999999999999 INCREMENT BY 1 START WITH 1 CACHE 24 NOORDER  NOCYCLE

    ORA-31685: Object type SEQUENCE:"IFMS"."VEHICLEREQSEQ" failed due to insufficient privileges. Failing sql is:
     CREATE SEQUENCE  "IFMS"."VEHICLEREQSEQ"  MINVALUE 1 MAXVALUE 999999999999999999999999999 INCREMENT BY 1 START WITH 1000 CACHE 20 NOORDER  NOCYCLE

    ORA-31685: Object type SEQUENCE:"IFMS"."VEHICLESSEQ" failed due to insufficient privileges. Failing sql is:
     CREATE SEQUENCE  "IFMS"."VEHICLESSEQ"  MINVALUE 1 MAXVALUE 999999999999999999999999999 INCREMENT BY 1 START WITH 335 CACHE 20 NOORDER  NOCYCLE

    ORA-31685: Object type SEQUENCE:"IFMS"."VENDORSEQ" failed due to insufficient privileges. Failing sql is:
     CREATE SEQUENCE  "IFMS"."VENDORSEQ"  MINVALUE 1 MAXVALUE 999999999999999999999999999 INCREMENT BY 1 START WITH 21 CACHE 20 NOORDER  NOCYCLE

    ORA-31685: Object type SEQUENCE:"IFMS"."VIPATTENDEESSEQ" failed due to insufficient privileges. Failing sql is:
     CREATE SEQUENCE  "IFMS"."VIPATTENDEESSEQ"  MINVALUE 1 MAXVALUE 999999999999999999999999999 INCREMENT BY 1 START WITH 1 CACHE 20 NOORDER  NOCYCLE

    Processing object type SCHEMA_EXPORT/TABLE/TABLE
    ORA-39083: Object type TABLE failed to create with error:
    ORA-06564: object EXTERNALDATA does not exist
    Failing sql is:
    CREATE TABLE "IFMS"."HRMIS_BIODATA" ("COBIODATAID" NUMBER(10,0), "ICNO" CHAR(12), "TITLECD" CHAR(4), "CONM" VARCHAR2(80), "GENDERCD" CHAR(1), "RACECD" CHAR(2), "COHPHONENO" VARCHAR2(14), "COOFFTELNO" VARCHAR2(14), "COBIRTHDT" TIMESTAMP (6), "APID" NUMBER(10,0), "APTITLE" VARCHAR2(150), "PERSONEL_GRED" VARCHAR2(30), "BUID" NUMBER(10,0), "ADDRTYPECD" CHAR(2), "ADDR1" VARCHAR2(50), "ADDR2"
    ORA-39083: Object type TABLE failed to create with error:
    ORA-06564: object EXTERNALDATA does not exist
    Failing sql is:
    CREATE TABLE "IFMS"."HRMIS_TITLES" ("TITLECD" VARCHAR2(4), "TITLE" VARCHAR2(40)) ORGANIZATION EXTERNAL ( TYPE ORACLE_LOADER DEFAULT DIRECTORY "EXTERNALDATA" ACCESS PARAMETERS ( RECORDS DELIMITED BY NEWLINE
                    LOAD WHEN (TitleCd != "TitleCd")
                    FIELDS TERMINATED BY ','
                    OPTIONALLY ENCLOSED BY '"'
                    LRTRIM
                    MISSING FIEL
    Processing object type SCHEMA_EXPORT/TABLE/TABLE_DATA
    . . imported "IFMS"."FR_APPLICATIONSTATUS"               89.50 KB    1248 rows
    . . imported "IFMS"."T200STUDENT"                        126.4 MB  642771 rows
    . . imported "IFMS"."ES_PEOPLE"                          69.14 KB     366 rows
    . . imported "IFMS"."CHK_STAF"                           5.929 KB      10 rows
    . . imported "IFMS"."FR_APPLICATIONACTIVITY"             14.93 MB  625040 rows
    Processing object type SCHEMA_EXPORT/TABLE/INDEX/INDEX
    Processing object type SCHEMA_EXPORT/TABLE/CONSTRAINT/CONSTRAINT
    Processing object type SCHEMA_EXPORT/TABLE/INDEX/STATISTICS/INDEX_STATISTICS
    Processing object type SCHEMA_EXPORT/TABLE/COMMENT
    Processing object type SCHEMA_EXPORT/PACKAGE/PACKAGE_SPEC
    ORA-31685: Object type PACKAGE:"IFMS"."PKG_RECORDS" failed due to insufficient privileges. Failing sql is:
    CREATE PACKAGE "IFMS"."PKG_RECORDS" IS

        FUNCTION F_ISNULL(IP_RECORD_NAME VARCHAR2,
                            IP_FIELD_NAME VARCHAR2) RETURN BOOLEAN;

        FUNCTION F_EQUALS(IP_SRS_RECORD_NAME VARCHAR2,
                            IP_SRS_FIELD_NAME VARCHAR2,
                            IN_TGT_RECORD_NAME VARCHAR2,
                            IP_TGT_FIELD_NAME VARCHAR2) RETURN BOOLEAN;
    END
    ORA-31685: Object type PACKAGE:"IFMS"."PKG_RECORDS_DATA_IO" failed due to insufficient privileges. Failing sql is:
    CREATE PACKAGE "IFMS"."PKG_RECORDS_DATA_IO"
    IS
        TYPE REC IS RECORD(FIELD1 VARCHAR2(20), FIELDS DATE,
                            FIELD3 NUMBER);

        FUNCTION F_DATA_INPUT_TO_RECORD(IP_FIELD1 VARCHAR2,
                                        IP_FIELD2 DATE,
                                        IP_FIELD3 NUMBER) RETURN REC;

        FUNCTION F
    Processing object type SCHEMA_EXPORT/PROCEDURE/PROCEDURE
    ORA-31685: Object type PROCEDURE:"IFMS"."DATE_CHK" failed due to insufficient privileges. Failing sql is:
    CREATE PROCEDURE "IFMS"."DATE_CHK" IS
    DATE_CHECKER VARCHAR2(20);
    /******************************************************************************
       NAME:       DATE_CHK
       PURPOSE:

       REVISIONS:
       Ver        Date        Author           Description
       ---------  ----------  ---------------  ------------------------------------
       1.0        1/6/2009          1. Created this procedure.

       NOTES:
    ORA-31685: Object type  failed due to insufficient privileges. Failing sql is:
    ORA-39125: Worker unexpected fatal error in KUPW$WORKER.PUT_DDL while calling DBMS_SQL.PARSE [PROCEDURE:"IFMS"."MYMOHES_T200STUDENT"]
    ORA-06502: PL/SQL: numeric or value error
    ORA-01031: insufficient privileges

    ORA-06512: at "SYS.DBMS_SYS_ERROR", line 105
    ORA-06512: at "SYS.KUPW$WORKER", line 6313

    ----- PL/SQL Call Stack -----
      object      line  object
      handle    number  name
    0x86885b18     15032  package body SYS.KUPW$WORKER
    0x86885b18      6372  package body SYS.KUPW$WORKER
    0x86885b18     12800  package body SYS.KUPW$WORKER
    0x86885b18     12080  package body SYS.KUPW$WORKER
    0x86885b18      3346  package body SYS.KUPW$WORKER
    0x86885b18      6972  package body SYS.KUPW$WORKER
    0x86885b18      1314  package body SYS.KUPW$WORKER
    0xc5f02e48         2  anonymous block

    Job "IFMS"."SYS_IMPORT_FULL_01" stopped due to fatal error at 09:27:20

    [oracle@ketuka extdata]$
     
     
  4. zargon

    zargon Community Moderator Forum Guru

    Messages:
    2,346
    Likes Received:
    347
    Trophy Points:
    1,430
    Location:
    Aurora, CO
    You need to use a DBA-privileged account to perform full imports and the account you are using does not possess those elevated privileges. Use a DBA-privileged account for this import and I expect most, if not all, of your errors will go away.
     
  5. mzainal

    mzainal Forum Advisor

    Messages:
    72
    Likes Received:
    0
    Trophy Points:
    130
    Hi,

    I use full privileges but still got same error. Some of my friend said need to exclude when import and generate .sql.

    I don't know because i'm system admin not db administrator. Please advice me.
     
  6. zargon

    zargon Community Moderator Forum Guru

    Messages:
    2,346
    Likes Received:
    347
    Trophy Points:
    1,430
    Location:
    Aurora, CO
    Not according to Oracle as

    ORA-01031: insufficient privileges

    is the predominant error. The ifms user is not a DBA or SYSDBA privileged account according to the error messages reported by impdp.
     
  7. rajavu

    rajavu Forum Guru

    Messages:
    815
    Likes Received:
    52
    Trophy Points:
    610
    Location:
    @ Bangalore , India
    Why cant you do impdp from the SYS user ?