rajat
Reputation: 3553
Importing oracle10g data dmp file into oracle 11g
I am trying to import a dump file generated using oracle 10g, here is the export log that I got:
Connected to: Oracle Database 10g Enterprise Edition Release 10.2.0.4.0 - 64bit Production
With the Partitioning, OLAP, Data Mining, and Real Application Testing options
Export is done in US7ASCII character set and AL16UTF16 NCHAR character set
the server uses WE8MSWIN1252 character set (possible charset conversion)
About to export specified users ...
. exporting pre-schema procedural objects and actions
. exporting foreign function library names for user dbname
. exporting PUBLIC type synonyms
. exporting private type synonyms
. exporting object type definitions for user dbname
About to export dbname's objects ...
. exporting database links
. exporting sequence numbers
. exporting cluster definitions
. about to export dbname's tables via Conventional Path ...
. . exporting table CONTROL_SL_MV 2605 rows exported
. . exporting table CUBE$ACCEXCISE
. . exporting partition P_0_0 0 rows exported
. . exporting table CUBE$BANK_REGISTER
. . exporting partition P_0_0 0 rows exported
. . exporting partition P_55_96 71075 rows exported
. . exporting partition P_55_101 74949 rows exported
. . exporting table CUBE$BILLCOLL
. . exporting partition P_0_0 0 rows exported
. . exporting partition P_28_9 355263 rows exported
. . exporting table CUBE$BOMCHECK
. . exporting partition P_0_0 0 rows exported
. . exporting table CUBE$BUDGETANALYSIS
. . exporting partition P_0_0 0 rows exported
. . exporting table CUBE$FINOTSD
. . exporting partition P_0_0 0 rows exported
. . exporting partition P_2_102 24276 rows exported
. . exporting table CUBE$FINREG
. . exporting partition P_0_0 0 rows exported
. . exporting table CUBE$FINREGSITE
. . exporting partition P_0_0 0 rows exported
. . exporting partition P_36_69 342072 rows exported
. . exporting table CUBE$FINREGSL
. . exporting partition P_0_0 0 rows exported
. . exporting partition P_35_70 138696 rows exported
. . exporting partition P_35_135 252140 rows exported
. . exporting table CUBE$FINTDS
. . exporting partition P_0_0 0 rows exported
. . exporting partition P_37_59 7463 rows exported
. . exporting table CUBE$GATEENTRY
. . exporting partition P_0_0 0 rows exported
. . exporting table CUBE$INTRNSUM
. . exporting partition P_0_0 0 rows exported
. . exporting partition P_4_144 242 rows exported
. . exporting table CUBE$INVCONV
. . exporting partition P_0_0 0 rows exported
. . exporting partition P_3_1 52975 rows exported
. . exporting table CUBE$INVMISC
. . exporting partition P_0_0 0 rows exported
. . exporting partition P_11_24 35705 rows exported
. . exporting table CUBE$INVSPLIT
. . exporting partition P_0_0 0 rows exported
. . exporting table CUBE$LGT
. . exporting partition P_0_0 0 rows exported
. . exporting table CUBE$ORDSTAT
. . exporting partition P_0_0 0 rows exported
. . exporting partition P_22_80 726 rows exported
. . exporting table CUBE$PERMIT
. . exporting partition P_0_0 0 rows exported
. . exporting table CUBE$POSBILLDET
. . exporting partition P_0_0 0 rows exported
. . exporting partition P_29_23 277919 rows exported
. . exporting partition P_29_15 1137910 rows exported
. . exporting table CUBE$POSBILLSUMM
. . exporting partition P_0_0 0 rows exported
. . exporting table CUBE$POSDSR
. . exporting partition P_0_0 0 rows exported
. . exporting table CUBE$PRDJOBBOM
. . exporting partition P_0_0 0 rows exported
. . exporting partition P_45_106 4418 rows exported
. . exporting table CUBE$PRDJOBCNL
. . exporting partition P_0_0 0 rows exported
. . exporting partition P_51_119 4 rows exported
. . exporting table CUBE$PRDJOBITM
. . exporting partition P_0_0 0 rows exported
. . exporting partition P_42_105 896 rows exported
. . exporting table CUBE$PRDJOBRCT
. . exporting partition P_0_0 0 rows exported
. . exporting table CUBE$PRDJRCBOM
. . exporting partition P_0_0 0 rows exported
. . exporting partition P_54_112 3128 rows exported
. . exporting table CUBE$PRDLTT
. . exporting partition P_0_0 0 rows exported
. . exporting table CUBE$PRDPLANBOM
. . exporting partition P_0_0 0 rows exported
. . exporting partition P_47_99 7808 rows exported
. . exporting table CUBE$PRDPLANITM
. . exporting partition P_0_0 0 rows exported
. . exporting partition P_41_100 863 rows exported
. . exporting table CUBE$PRDPLANOPR
. . exporting partition P_0_0 0 rows exported
. . exporting partition P_43_104 1477 rows exported
. . exporting table CUBE$PRDWIPTXN
. . exporting partition P_0_0 0 rows exported
. . exporting partition P_48_107 3112 rows exported
. . exporting table CUBE$PRDWIPVAL
. . exporting partition P_0_0 0 rows exported
. . exporting partition P_49_120 7668 rows exported
. . exporting table CUBE$PSTK
. . exporting partition P_0_0 0 rows exported
. . exporting partition P_6_114 0 rows exported
. . exporting table CUBE$PTCREG
. . exporting partition P_0_0 0 rows exported
. . exporting partition P_31_93 33830 rows exported
. . exporting table CUBE$PURCHASE
. . exporting partition P_0_0 0 rows exported
. . exporting partition P_21_14 31774 rows exported
. . exporting table CUBE$PURCHG
. . exporting partition P_0_0 0 rows exported
. . exporting partition P_20_20 23374 rows exported
. . exporting table CUBE$PURINV
. . exporting partition P_0_0 0 rows exported
. . exporting partition P_19_94 14048 rows exported
. . exporting table CUBE$PURINVSUM
. . exporting partition P_0_0 0 rows exported
. . exporting partition P_39_97 14048 rows exported
. . exporting table CUBE$SALCHG
. . exporting partition P_0_0 0 rows exported
. . exporting partition P_23_16 41696 rows exported
. . exporting table CUBE$SALDC
. . exporting partition P_0_0 0 rows exported
. . exporting partition P_27_37 6787 rows exported
. . exporting partition P_27_57 1473651 rows exported
. . exporting partition P_27_142 349656 rows exported
. . exporting table CUBE$SALINV
. . exporting partition P_0_0 0 rows exported
. . exporting partition P_25_108 1047449 rows exported
. . exporting partition P_25_17 2054685 rows exported
. . exporting table CUBE$SALINVSUM
. . exporting partition P_0_0 0 rows exported
. . exporting partition P_38_92 43937 rows exported
. . exporting table CUBE$SALORD
. . exporting partition P_0_0 0 rows exported
. . exporting table CUBE$SITE2SITE
. . exporting partition P_0_0 0 rows exported
. . exporting partition P_5_51 764452 rows exported
. . exporting table CUBE$SITESTOCK
. . exporting partition P_0_0 0 rows exported
. . exporting partition P_26_86 962251 rows exported
. . exporting partition P_26_10 0 rows exported
. . exporting partition P_26_89 0 rows exported
. . exporting table CUBE$STF
. . exporting partition P_0_0 0 rows exported
. . exporting partition P_8_145 79 rows exported
. . exporting table CUBE$STKADJ
. . exporting partition P_0_0 0 rows exported
. . exporting partition P_13_115 35845 rows exported
. . exporting table CUBE$STKANLDET
. . exporting partition P_0_0 0 rows exported
. . exporting partition P_14_22 9921680 rows exported
. . exporting table CUBE$STKANLSUM
. . exporting partition P_0_0 0 rows exported
. . exporting partition P_7_3 3748950 rows exported
. . exporting partition P_7_138 2436579 rows exported
. . exporting partition P_7_143 4745478 rows exported
. . exporting partition P_7_73 4756191 rows exported
. . exporting partition P_7_146 928416 rows exported
. . exporting partition P_7_7 5435920 rows exported
. . exporting table CUBE$STKMOVE
. . exporting partition P_0_0 0 rows exported
. . exporting partition P_15_123 805427 rows exported
. . exporting partition P_15_66 908402 rows exported
. . exporting partition P_15_67 963341 rows exported
. . exporting table CUBE$STKMOVEGP
. . exporting partition P_0_0 0 rows exported
. . exporting table CUBE$STKTRANSIT
. . exporting partition P_0_0 0 rows exported
. . exporting partition P_10_103 647140 rows exported
. . exporting partition P_10_111 695534 rows exported
. . exporting partition P_10_121 784606 rows exported
. . exporting partition P_10_132 760269 rows exported
. . exporting table CUBE$STKVAL
. . exporting partition P_0_0 0 rows exported
. . exporting table CUBE$STKVALOU
. . exporting partition P_0_0 0 rows exported
. . exporting table EUL5_ACCESS_PRIVS 236 rows exported
. . exporting table EUL5_APP_PARAMS 17 rows exported
. . exporting table EUL5_ASMP_CONS 0 rows exported
. . exporting table EUL5_ASMP_LOGS 0 rows exported
. . exporting table EUL5_ASM_POLICIES 1 rows exported
. . exporting table EUL5_BAS 8 rows exported
. . exporting table EUL5_BATCH_PARAMS 0 rows exported
. . exporting table EUL5_BATCH_QUERIES 0 rows exported
. . exporting table EUL5_BATCH_REPORTS 0 rows exported
. . exporting table EUL5_BATCH_SHEETS 0 rows exported
. . exporting table EUL5_BA_OBJ_LINKS 96 rows exported
. . exporting table EUL5_BQ_DEPS 0 rows exported
. . exporting table EUL5_BQ_TABLES 0 rows exported
. . exporting table EUL5_BR_RUNS 0 rows exported
. . exporting table EUL5_DBH_NODES 38 rows exported
. . exporting table EUL5_DOCUMENTS 102 rows exported
. . exporting table EUL5_DOC_FOLDERS 1 rows exported
. . exporting table EUL5_DOMAINS 59 rows exported
. . exporting table EUL5_ELEM_XREFS 100 rows exported
. . exporting table EUL5_EUL_USERS 29 rows exported
. . exporting table EUL5_EXPRESSIONS 3063 rows exported
. . exporting table EUL5_EXP_DEPS 711 rows exported
. . exporting table EUL5_FREQ_UNITS 6 rows exported
. . exporting table EUL5_FUNCTIONS 271 rows exported
. . exporting table EUL5_FUN_ARGUMENTS 301 rows exported
. . exporting table EUL5_FUN_CTGS 10 rows exported
. . exporting table EUL5_FUN_FC_LINKS 262 rows exported
. . exporting table EUL5_GATEWAYS 2 rows exported
. . exporting table EUL5_HIERARCHIES 95 rows exported
. . exporting table EUL5_HI_NODES 424 rows exported
. . exporting table EUL5_HI_SEGMENTS 367 rows exported
. . exporting table EUL5_IG_EXP_LINKS 424 rows exported
. . exporting table EUL5_IHS_FK_LINKS 0 rows exported
. . exporting table EUL5_KEY_CONS 177 rows exported
. . exporting table EUL5_OBJS 96 rows exported
. . exporting table EUL5_OBJ_DEPS 0 rows exported
. . exporting table EUL5_OBJ_JOIN_USGS 0 rows exported
. . exporting table EUL5_PLAN_TABLE 0 rows exported
. . exporting table EUL5_QPP_STATS 12056 rows exported
. . exporting table EUL5_SEGMENTS 152 rows exported
. . exporting table EUL5_SEQUENCES 1 rows exported
. . exporting table EUL5_SQ_CRRLTNS 0 rows exported
. . exporting table EUL5_SUB_QUERIES 0 rows exported
. . exporting table EUL5_SUMMARY_OBJS 0 rows exported
. . exporting table EUL5_SUMO_EXP_USGS 0 rows exported
. . exporting table EUL5_SUM_BITMAPS 0 rows exported
. . exporting table EUL5_SUM_RFSH_SETS 0 rows exported
. . exporting table EUL5_VERSIONS 1 rows exported
. . exporting table ITEM_MV 240210 rows exported
. . exporting table LEDGER_MV 332 rows exported
. . exporting table MV_CAPABILITIES_TABLE 27 rows exported
. . exporting table PACKDEF 1 rows exported
. . exporting table SITE_DETAIL_MV 424 rows exported
. . exporting table SITE_MV 700 rows exported
. . exporting table SUB_LEDGER_MV 3538 rows exported
. exporting synonyms
. exporting views
. exporting stored procedures
. exporting operators
. exporting referential integrity constraints
. exporting triggers
. exporting indextypes
. exporting bitmap, functional and extensible indexes
. exporting posttables actions
. exporting materialized views
. exporting snapshot logs
. exporting job queues
. exporting refresh groups and children
. exporting dimensions
. exporting post-schema procedural objects and actions
. exporting statistics
Export terminated successfully without warnings.
Now i am trying to import this file into my oracle 11g standard installation using this command:
imp user/password FULL=Y FILE=dbname.dmp log=dbname.log
but for every table it produces an error saying:
> IMP-00003: ORACLE error 959 encountered ORA-00959: tablespace > 'GINOLAP' does not exist
after all the processing it produces:
> IMP-00003: ORACLE error 1435 encountered ORA-01435: user does not
> exist ORA-06512: at "SYS.DBMS_ISNAPSHOT", line 108 ORA-06512: at
> "SYS.DBMS_SNAPSHOT_UTL", line 1520 ORA-06512: at line 1 IMP-00000:
> Import terminated unsuccessfully
How should I go about importing this database dump file?
PS: The dmp file was created using exp command, Therefore i cannot user impdp command.
Upvotes: 4
Views: 5298
Answers (2)
Gustavo Rubio
Reputation: 10747
It means that you don't have a tablespace called GINOLAP on the target database server and that the user/schema is wrong.
Have you checked the TRANSPORT_TABLESPACES = Y options to create them on the target server and the FROMUSER and TOUSER to map users?
Upvotes: 2
Up_One
Reputation: 5271
Steps to follow when doing this :
- Set the same character on source server
- Exporting the data from source database
- Extract the table script from soured database with the help of below script
For tablespace
select ‘create tablespace ‘ || df.tablespace_name || chr(10)
|| ‘ datafile ”’ || df.file_name || ”’ size ‘ || df.bytes
|| decode(autoextensible,’N',null, chr(10) || ‘ autoextend on maxsize ‘
|| maxbytes)
|| chr(10)
|| ‘default storage ( initial ‘ || initial_extent
|| decode (next_extent, null, null, ‘ next ‘ || next_extent )
|| ‘ minextents ‘ || min_extents
|| ‘ maxextents ‘ || decode(max_extents,’2147483645′,’unlimited’,max_extents)
|| ‘) ;’
from dba_data_files df, dba_tablespaces t
where df.tablespace_name=t.tablespace_name
For the Users
set pagesize 0
set escape on
select ‘create user ‘ || U.username || ‘ identified ‘ ||
DECODE(password,
NULL, ‘EXTERNALLY’,
‘ by values ‘ || ”” || password || ””
)
|| chr(10) ||
‘default tablespace ‘ || default_tablespace || chr(10) ||
‘temporary tablespace ‘ || temporary_Tablespace || chr(10) ||
‘ profile ‘ || profile || chr(10) ||
‘quota ‘ ||
decode ( Q.max_bytes, -1, ‘UNLIMITED’, NULL, ‘UNLIMITED’, Q.max_bytes) ||
‘ on ‘ || default_tablespace ||
decode (account_status,’LOCKED’, ‘ account lock’,
‘EXPIRED’, ‘ password expire’,
‘EXPIRED \& LOCKED’, ‘ account lock password expire’,
null)
||
‘;’
from dba_users U, dba_ts_quotas Q
– Comment this clause out to include system & default users
where U.username not in (‘SYS’,'SYSTEM’)
and U.username=Q.username(+) and U.default_tablespace=Q.tablespace_name(+)
;
set pagesize 100
set escape off
Copy the files to the destination server.
Install Binary of Oracle 11g on Destination Server
Create the database on installed 11g
Run the script for creating tablespace and users
Set the same character on destination server
Importing dump file to destination server
In case you don't have the users and the DDL for the objects you are about to import you will need to extract the DDL from inside the dmp file.
Make sure your destination database is up and running
Make sure you have directory created(where the dump will be located and where the DDL log will be stored)
To extract the DDL issue the command bellow :
imp file=metadata.dmp userid=system/password full=y show=y log=/tmp/ddl_log.sql dumpdir=DIR_NAME
After you got you DDL extract the tablespace & users definitions and run then in the database.
Upvotes: 1
Related Questions
- how to import oracle 11g dump file to 10g oracle
- Import .dmp file from 11g to 10g
- import a oracle dumpfile
- Importing .dmp file from Oracle 11g to 10g returns error 'Unable to open log file'
- How to import an Oracle database from dmp file and log file?
- How to import data drom dumpfile using import utility?
- importing a dmp file into Oracle 11g XE
- Import more than one dump file in oracle
- Import data from dmp
- how to import oracle dump file to oracle db?