Home » RDBMS Server » Server Utilities » Import Schema (Contain trigger)
Import Schema [message #439561] Mon, 18 January 2010 06:53 Go to next message
mzarM
Messages: 8
Registered: August 2009
Junior Member
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.
Re: Import Schema [message #439595 is a reply to message #439561] Mon, 18 January 2010 09:46 Go to previous messageGo to next message
Michel Cadot
Messages: 63812
Registered: March 2007
Location: Nanterre, France, http://...
Senior Member
Account Moderator
Quote:
I use regular import but fail. Got so many error.

Without the command you executed and the errors you got, we can't help as well as you your doctor can't help you if you just telling him you're sick.

Regards
Michel
Re: Import Schema [message #439639 is a reply to message #439561] Mon, 18 January 2010 19:02 Go to previous messageGo to next message
mzarM
Messages: 8
Registered: August 2009
Junior Member
Here error i got:

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

Re: Import Schema [message #439640 is a reply to message #439639] Mon, 18 January 2010 19:13 Go to previous messageGo to next message
BlackSwan
Messages: 24912
Registered: January 2009
Senior Member
>Without the command you executed, we can't help as well as you your doctor can't help you if you just telling him you're sick.

It appears the target schema is missing some privileges.
Re: Import Schema [message #439641 is a reply to message #439561] Mon, 18 January 2010 20:49 Go to previous messageGo to next message
mzarM
Messages: 8
Registered: August 2009
Junior Member
Sorry,my mistake. This is my command.
 impdp ifms/password directory=extdata dumpfile=frgs14jan10.all full=y remap_schema=azmansl:ifms remap_tablespace=missiontest:databank
Re: Import Schema [message #439642 is a reply to message #439641] Mon, 18 January 2010 21:10 Go to previous messageGo to next message
BlackSwan
Messages: 24912
Registered: January 2009
Senior Member
The new schema ifms needs to have the same privileges that schema azmansl has/had in the old/previous database.

[Updated on: Mon, 18 January 2010 21:11]

Report message to a moderator

Re: Import Schema [message #439717 is a reply to message #439561] Tue, 19 January 2010 03:55 Go to previous messageGo to next message
mzarM
Messages: 8
Registered: August 2009
Junior Member
First, i set same privileges with azmansl. The result is still same.

So, i came out with other idea. Grant all privileges to ifms and the result also same. So, any suggestion?
Re: Import Schema [message #439721 is a reply to message #439717] Tue, 19 January 2010 04:00 Go to previous message
Michel Cadot
Messages: 63812
Registered: March 2007
Location: Nanterre, France, http://...
Senior Member
Account Moderator
Are you sure you set them in the database you import?

Regards
Michel
Previous Topic: ora-01536 when importing
Next Topic: Indexes and tables import to different tablespaces.
Goto Forum:
  


Current Time: Fri Sep 30 09:01:07 CDT 2016

Total time taken to generate the page: 0.11234 seconds