I have encountered a problem in importing the dump file to a new database. When importing the dump file it requires a new tablespace which does not exist in the database. To create the tablespace I need to hijack some scripts which is readonly.For this reason it is complicated to export the table structure through imp tool of Oracle so my colleage changed the dump file manually and it is able to be imported. Is it OK to change the dump file manually in order to import the file if it is the quickest way?
+2
A:
if are comfortable to change the dump file manually then it is fine, keeping that you are aware of the the complete structure of the .dmp file.
i will suggest u to use data pump as it remaps the table space of the existing schema with the new one. and performance wise data pump is much faster then normal dump.
Mrityunjay
2010-10-14 04:30:47
Thanks that is what I wanted to do but because the dump file was not created by data pump, I will need the dump file to be re-created (which is done by someone by requesting). So I guess changing the dump file is the quickest way, although I had some doubt initially.
newguy
2010-10-14 04:43:04
@newguy if u have taken the dump through dump file then u can easily remap the tablespace with out any problem..use the following command that will help you..impdp schema1/schema1@db1 remap_schema=schema1:schema2 remap_tablespace= schema1:schema2 transform=SEGMENT_ATTRIBUTES:n directory=DATA_PUMP_DIR dumpfile=DP_FullBkp.dmp logfile=DP_FullBkp.(IMP).log schemas=schema1 status=120
Mrityunjay
2010-10-14 04:48:01
as the above statement will remap the schema and table space, there is no need for you to change the dump file manually.
Mrityunjay
2010-10-14 04:49:25
+2
A:
As an alternative, get a dummy database and
- create the tablespace/schema.
- Do the import there with ROWS=N
- ALTER TABLE ... MOVE .... to put the tables into the desired tablespace
- Export tables (structures) from there
- import corrected structures
- Import the data with IGNORE=Y so that the data can be imported over the existing structures.
Gary
2010-10-14 05:03:08
A:
if you create the user with a default tablespace that is a tablespace that exists, you can import with rows=n and ignore=y and that should bring the objects in for you into that tablespace.
erbsock
2010-10-14 16:41:07