exp/imp
DBAsupport.com Forums - Powered by vBulletin
Results 1 to 9 of 9

Thread: exp/imp

  1. #1
    Join Date
    Jul 2005
    Posts
    87

    exp/imp

    1)I have a full export full=y with warnings so if i import it import will succeed with warnings
    2)export is done by user=xxx and if imported by system does all the rows get imported ? i am new to exp/imp please help

    Thanks

  2. #2
    Join Date
    Nov 2000
    Location
    Pittsburgh, PA
    Posts
    4,014
    Quote Originally Posted by areeb
    1)I have a full export full=y with warnings so if i import it import will succeed with warnings
    2)export is done by user=xxx and if imported by system does all the rows get imported ? i am new to exp/imp please help

    Thanks
    What warnings did you get? Did you get warnings about invalid statistics? If so specify statistics=none. Did you get warnings about the nls language? If so you may need to specify a different nls language. If you post the log for export you might get a better answer.
    this space intentionally left blank

  3. #3
    Join Date
    Jul 2005
    Posts
    87
    Below is export and import log ,

    %%%%% Start full export of 'hr8prd': Wed Nov 15 22:30:02 EST 2006 %%%%%

    Export: Release 8.1.7.3.0 - Production on Wed Nov 15 22:30:02 2006

    (c) Copyright 2000 Oracle Corporation. All rights reserved.


    Connected to: Oracle8i Enterprise Edition Release 8.1.7.3.0 - 64bit Production
    With the Partitioning option
    JServer Release 8.1.7.3.0 - 64bit Production
    Export done in WE8ISO8859P1 character set and WE8ISO8859P1 NCHAR character set

    About to export the entire database ...
    . exporting tablespace definitions
    . exporting profiles
    . exporting user definitions
    . exporting roles
    . exporting resource costs
    . exporting rollback segment definitions
    . exporting database links
    . exporting sequence numbers
    . exporting directory aliases
    . exporting context namespaces
    . exporting foreign function library names
    . exporting object type definitions
    . exporting system procedural objects and actions
    . exporting pre-schema procedural objects and actions
    . exporting cluster definitions
    . about to export SYSTEM's tables via Direct Path ...
    . . exporting table ALERTLOG_TEMP 0 rows exported
    EXP-00067: Table DEF$_AQCALL will be exported in conventional path.
    . . exporting table DEF$_AQCALL 0 rows exported
    EXP-00067: Table DEF$_AQERROR will be exported in conventional path.
    . . exporting table DEF$_AQERROR 0 rows exported
    . . exporting table DEF$_CALL 0 rows exported
    . . exporting table DEF$_CALLDEST 0 rows exported
    . . exporting table DEF$_DEFAULTDEST 0 rows exported
    . . exporting table DEF$_DESTINATION 0 rows exported
    . . exporting table DEF$_ERROR 0 rows exported
    EXP-00067: Table DEF$_LOB will be exported in conventional path.
    . . exporting table DEF$_LOB 0 rows exported
    . . exporting table DEF$_ORIGIN 0 rows exported
    . . exporting table DEF$_PROPAGATOR 0 rows exported
    . . exporting table DEF$_PUSHED_TRANSACTIONS 0 rows exported
    EXP-00067: Table DEF$_TEMP$LOB will be exported in conventional path.
    . . exporting table DEF$_TEMP$LOB 0 rows exported
    . . exporting table EVT_CARRIER_CONFIGURATION 0 rows exported
    . . exporting table EVT_DEST_PROFILE 0 rows exported

    . exporting views
    . exporting stored procedures
    . exporting operators
    . exporting indextypes
    . exporting bitmap, functional and extensible indexes
    . exporting posttables actions
    . exporting triggers
    . exporting snapshots
    . exporting snapshot logs
    . exporting job queues
    . exporting refresh groups and children
    . exporting dimensions
    . exporting post-schema procedural objects and actions
    . exporting user history table
    . exporting default and system auditing options
    . exporting statistics
    Export terminated successfully with warnings.

    import log
    -----------------------
    ***********************

    Connected to: Oracle8i Enterprise Edition Release 8.1.7.3.0 - 64bit Production
    With the Partitioning option
    JServer Release 8.1.7.3.0 - 64bit Production

    Export file created by EXPORT:V08.01.07 via direct path

    Warning: the objects were exported by OPS$ORACLE2P, not by you

    import done in WE8ISO8859P1 character set and WE8ISO8859P1 NCHAR character set
    IMP-00032: SQL statement exceeded buffer length
    IMP-00032: SQL statement exceeded buffer length
    IMP-00032: SQL statement exceeded buffer length
    IMP-00032: SQL statement exceeded buffer length
    IMP-00008: unrecognized statement in the export file:
    ULT TABLESPACE "PSTEMP" TEMPORARY TABLESPACE "PSTEMP"
    IMP-00008: unrecognized statement in the export file:
    GRA"WILLIARS"
    IMP-00017: following statement failed with ORACLE error 933:
    "GRANT "PSUSER" TO "AZ2282" TO "AZ23681""
    IMP-00003: ORACLE error 933 encountered
    ORA-00933: SQL command not properly ended
    IMP-00017: following statement failed with ORACLE error 1917:
    "GRANT "PSUSER" TO "AZ66519""
    IMP-00003: ORACLE error 1917 encountered
    ORA-01917: user or role 'AZ66519' does not exist
    IMP-00017: following statement failed with ORACLE error 1917:
    "GRANT "PSUSER" TO "AZ66446""
    IMP-00003: ORACLE error 1917 encountered
    ORA-01652: unable to extend temp segment by 64 in tablespace TEMP
    IMP-00017: following statement failed with ORACLE error 1652:
    "CREATE INDEX "QUEST_IX_RUN_OBS_TAB_ITM_NDX1" ON "QUEST_IX_RUN_OBS_TABLE_ITE"
    "M" ("RUN_ID" , "OBSERVATION_ID" , "COLUMN_NO" ) PCTFREE 10 INITRANS 2 MAXT"
    "RANS 255 STORAGE(INITIAL 94371840 NEXT 2097152 MINEXTENTS 1 MAXEXTENTS 2147"
    "483645 PCTINCREASE 0 FREELISTS 1 FREELIST GROUPS 1 BUFFER_POOL DEFAULT) TAB"
    "LESPACE "TOOLS" LOGGING"
    IMP-00003: ORACLE error 1652 encountered
    ORA-01652: unable to extend temp segment by 64 in tablespace TEMP
    IMP-00017: following statement failed with ORACLE error 1652:
    "ALTER TABLE "QUEST_IX_RUN_OBS_TABLE_ITEM" ADD CONSTRAINT "QUEST_IX_RUN_OBS"
    "_TABLE_ITEM_PK" PRIMARY KEY ("RUN_ID", "OBSERVATION_ID", "ROW_NO", "COLUMN_"
    "NO", "SEQUENCE_NO") USING INDEX PCTFREE 10 INITRANS 2 MAXTRANS 255 STORAGE("
    IMP-00017: following statement failed with ORACLE error 1652:
    "ALTER TABLE "QUEST_IX_RUN_OBS_TABLE_ITEM" ADD CONSTRAINT "QUEST_IX_RUN_OBS"
    "_TABLE_ITEM_PK" PRIMARY KEY ("RUN_ID", "OBSERVATION_ID", "ROW_NO", "COLUMN_"
    "NO", "SEQUENCE_NO") USING INDEX PCTFREE 10 INITRANS 2 MAXTRANS 255 STORAGE("
    "INITIAL 96665600 NEXT 720896 MINEXTENTS 1 MAXEXTENTS 2147483645 PCTINCREASE"
    " 0 FREELISTS 1 FREELIST GROUPS 1 BUFFER_POOL DEFAULT) TABLESPACE "TOOLS" EN"
    "ABLE "
    IMP-00003: ORACLE error 1652 encountered
    ORA-01652: unable to extend temp segment by 64 in tablespace TEMP
    . . importing table "QUEST_IX_RUN_PARAMETERS"
    IMP-00020: long column too large for column buffer size (512)
    IMP-00028: partial import of previous table rolled back: 27 rows rolled back
    IMP-00008: unrecognized statement in the export file:

    IMP-00008: unrecognized statement in the export file:

    IMP-00008: unrecognized statement in the export file:
    Claim Entry^A
    IMP-00008: unrecognized statement in the export file:
    Enrollment^A
    IMP-00008: unrecognized statement in the export file:
    Enrollment^A
    IMP-00008: unrecognized statement in the export file:

    IMP-00017: following statement failed with ORACLE error 942:
    "CREATE UNIQUE INDEX "PS_MTCH_GFT_CTL_ZN" ON "PS_MTCH_GFT_CTL_ZN" ("EFFDT" D"
    "ESC ) PCTFREE 10 INITRANS 2 MAXTRANS 255 STORAGE(INITIAL 16384 NEXT 106496"
    " MINEXTENTS 1 MAXEXTENTS 110 PCTINCREASE 0 FREELISTS 1 FREELIST GROUPS 1 BU"
    "FFER_POOL DEFAULT) TABLESPACE "PSINDEX" LOGGING"
    IMP-00003: ORACLE error 942 encountered
    ORA-00942: table or view does not exist
    IMP-00017: following statement failed with ORACLE error 942:
    "CREATE UNIQUE INDEX "PS_NDT_HCE_TBL" ON "PS_NDT_HCE_TBL" ("EFFDT" DESC ) P"
    "CTFREE 10 INITRANS 2 MAXTRANS 255 STORAGE(INITIAL 16384 NEXT 8192 MINEXTENT"
    "S 1 MAXEXTENTS 110 PCTINCREASE 0 FREELISTS 1 FREELIST GROUPS 1 BUFFER_POOL "
    "DEFAULT) TABLESPACE "PSINDEX" LOGGING"
    IMP-00003: ORACLE error 942 encountered
    ORA-00942: table or view does not exist
    IMP-00017: following statement failed with ORACLE error 942:
    "CREATE UNIQUE INDEX "PS_OBSTACLES_MOBIL" ON "PS_OBSTACLES_MOBIL" ("EMPLID" "
    ", "EFFDT" DESC , "OBSTACLES_MOBILITY" ) PCTFREE 10 INITRANS 2 MAXTRANS 255"
    " STORAGE(INITIAL 16384 NEXT 8192 MINEXTENTS 1 MAXEXTENTS 110 PCTINCREASE 0 "
    "FREELISTS 1 FREELIST GROUPS 1 BUFFER_POOL DEFAULT) TABLESPACE "PSINDEX" LOG"
    "GING"
    IMP-00041: Warning: object created with compilation warnings
    "CREATE FORCE VIEW "SYSADM"."PS_TL_COMP_BAL_SCH" (""
    "ROWSECCLASS","EMPLID","EMPL_RCD","NAME") AS "
    "SELECT V.ROWSECCLASS ,V.EMPLID ,V.EMPL_RCD , V.NAME FROM"
    " PS_TL_ADM_EERCD_VW V WHERE EXISTS ( SELECT 'X' FROM PS_TL_EMPL_COMP E"
    " WHERE E.EMPLID=V.EMPLID AND V.EMPL_RCD=E.EMPL_RCD)"
    IMP-00041: Warning: object created with compilation warnings
    "CREATE FORCE VIEW "SYSADM"."PS_TL_ATTEND_HSRCH" (""
    "ROWSECCLASS","EMPLID","DUR","EXCEPTION_ID","PUNCH_BEGIN","NAME") AS "
    "SELECT A.ROWSECCLASS , B.EMPLID , B.DUR ,"
    " B.EXCEPTION_ID , B.PUNCH_BEGIN , C.NAME FROM PS_TL_ADM_SRCH_VW A ,"
    " PS_TL_ATTEND_HIST B , PS_PERSONAL_DATA C WHERE A.EMPLID = B.EMPLID"
    " AND A.EMPLID = C.EMPLID"

    "import_hr8cnv.log" 102687 lines, 6151457 characters
    Last edited by areeb; 11-22-2006 at 02:00 PM.

  4. #4
    Join Date
    Nov 2000
    Location
    Pittsburgh, PA
    Posts
    4,014
    If you are importing a lot of blobs, clobs, or longs you may need to set buffer=102400. What is the import command that you are using?
    this space intentionally left blank

  5. #5
    Join Date
    Jul 2005
    Posts
    87
    #!/bin/sh
    . $HOME/.profile
    trap '' 1 # nohup
    export ORACLE_SID=hr8cnv
    /etc/mknod /backup1t/hr8cnv/exp/hr8cnv.dmp p
    uncompress /backup1t/hr8cnv/exp/hr8cnv.dmp &
    sleep 2
    /util1at/app/oracle/product/8.1.7.3_64/bin/imp system/manager FILE=/backup1t/hr8cnv/exp/hr8cnv.dmp IGNORE=Y ANALYZE=N FULL=Y LOG=/user1/oracle2t/log/import_hr
    8cnv.log &

  6. #6
    Join Date
    Nov 2000
    Location
    Pittsburgh, PA
    Posts
    4,014
    Quote Originally Posted by areeb
    #!/bin/sh
    . $HOME/.profile
    trap '' 1 # nohup
    export ORACLE_SID=hr8cnv
    /etc/mknod /backup1t/hr8cnv/exp/hr8cnv.dmp p
    uncompress /backup1t/hr8cnv/exp/hr8cnv.dmp &
    sleep 2
    /util1at/app/oracle/product/8.1.7.3_64/bin/imp system/manager FILE=/backup1t/hr8cnv/exp/hr8cnv.dmp IGNORE=Y ANALYZE=N FULL=Y LOG=/user1/oracle2t/log/import_hr
    8cnv.log &
    but did you try it with the buffer parameter???

    Code:
    imp system/manager FILE=/backup1t/hr8cnv/exp/hr8cnv.dmp IGNORE=Y ANALYZE=N FULL=Y buffer=102400 LOG=/user1/oracle2t/log/import_hr8cnv.log &
    I really hope your password for system is not manager!!!!
    this space intentionally left blank

  7. #7
    Join Date
    Jul 2005
    Posts
    87
    nope ,i am new to exp/imp ( refreshing db) and i had this script given to me by lead dba

  8. #8
    Join Date
    Jan 2001
    Posts
    3,131
    Are the versions of Oracle the same?

    Try re-doing the export with the buffer parameter set high
    BUFFER=10000000

    I have seen exports bomb with tables that were longer then the default buffer paramter in export.
    I remember when this place was cool.

  9. #9
    Join Date
    Jul 2005
    Posts
    87
    Versions of oracle are same,so with Buffer=higher value should do the trick what about other errors in the log?

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