Home » RDBMS Server » Server Utilities » Can i partially import dump? (Oracle 9i, unix)
Can i partially import dump? [message #445864] Thu, 04 March 2010 07:37 Go to next message
amit.sehrawat
Messages: 29
Registered: September 2009
Location: India
Junior Member

Hi,

I am not a DBA, but managing few database instances for testing purposes, I got this dump from production, with the structural and data dump.
Structural dump contains: tablespaces, users, roles, grants....
Data dump contains: data corresponding to user XYZ

Now, I know how to import, from user to user, but this structural dump contains many users which i do not need. i just need single user from the dump, which is XYZ.

This is how structural dump looks like:

BEGINSYS
CONNECT SYSTEM
CREATE TEMPORARY TABLESPACE "TEMP_001_XYZ" BLOCKSIZE 8192 TEMPFILE '/database/oracle/XYZ/TEMP_001_XYZ_001.dbf' SIZE 1073741824 REUSE, '/database/oracle/XYZ/TEMP_001_XYZ_002.dbf' SIZE 1048576000 REUSE, '/database/oracle/XYZ/TEMP_001_XYZ_003.dbf' SIZE 3000M REUSE EXTENT MANAGEMENT LOCAL UNIFORM SIZE 1048576
CREATE TABLESPACE "TOOLS_001_XYZ" BLOCKSIZE 8192 DATAFILE '/database/oracle/XYZ/TOOLS_001_XYZ_001.dbf' SIZE 52428800 REUSE EXTENT MANAGEMENT LOCAL AUTOALLOCATE ONLINE PERMANENT NOLOGGING
CREATE TABLESPACE "USER_001_XYZ" BLOCKSIZE 8192 DATAFILE '/database/oracle/XYZ/USER_001_XYZ_001.dbf' SIZE 105906176 REUSE EXTENT MANAGEMENT LOCAL AUTOALLOCATE ONLINE PERMANENT NOLOGGING
CREATE TABLESPACE "PERFDATA" BLOCKSIZE 8192 DATAFILE '/database/oracle/XYZ/PERFDATA_001_XYZ_001.dbf' SIZE 943718400 REUSE EXTENT MANAGEMENT LOCAL AUTOALLOCATE ONLINE PERMANENT NOLOGGING
CREATE TABLESPACE "RBS_001_XYZ" BLOCKSIZE 8192 DATAFILE '/database/oracle/XYZ/RBS_001_XYZ_001.dbf' SIZE 2500M REUSE, '/database/oracle/XYZ/RBS_001_XYZ_002.dbf' SIZE 524288000 REUSE EXTENT MANAGEMENT LOCAL UNIFORM SIZE 10485760 ONLINE PERMANENT NOLOGGING
CREATE TABLESPACE "DATA_XS_001_XYZ" BLOCKSIZE 8192 DATAFILE '/database/oracle/XYZ/DATA_XS_001_XYZ_001.dbf' SIZE 209715200 REUSE EXTENT MANAGEMENT LOCAL UNIFORM SIZE 524288 ONLINE PERMANENT NOLOGGING SEGMENT SPACE MANAGEMENT AUTO
CREATE TABLESPACE "DATA_S_001_XYZ" BLOCKSIZE 8192 DATAFILE '/database/oracle/XYZ/DATA_S_001_XYZ_001.dbf' SIZE 536870912 REUSE EXTENT MANAGEMENT LOCAL UNIFORM SIZE 1048576 ONLINE PERMANENT NOLOGGING SEGMENT SPACE MANAGEMENT AUTO
CREATE TABLESPACE "DATA_M_001_XYZ" BLOCKSIZE 8192 DATAFILE '/database/oracle/XYZ/DATA_M_001_XYZ_001.dbf' SIZE 402653184 REUSE EXTENT MANAGEMENT LOCAL UNIFORM SIZE 10485760 ONLINE PERMANENT NOLOGGING SEGMENT SPACE MANAGEMENT AUTO
CREATE TABLESPACE "DATA_L_001_XYZ" BLOCKSIZE 8192 DATAFILE '/database/oracle/XYZ/DATA_L_001_XYZ_001.dbf' SIZE 5101M REUSE, '/database/oracle/XYZ/DATA_L_001_XYZ_002.dbf' SIZE 2199M REUSE EXTENT MANAGEMENT LOCAL UNIFORM SIZE 104857600 ONLINE PERMANENT NOLOGGING SEGMENT SPACE MANAGEMENT AUTO
CREATE TABLESPACE "DATA_XL_001_XYZ" BLOCKSIZE 8192 DATAFILE '/database/oracle/XYZ/DATA_XL_001_XYZ_001.dbf' SIZE 12001M REUSE, '/database/oracle/XYZ/DATA_XL_001_XYZ_002.dbf' SIZE 12450M REUSE, '/database/oracle/XYZ/DATA_XL_001_XYZ_003.dbf' SIZE 2250M REUSE, '/database/oracle/XYZ/DATA_XL_001_XYZ_004.dbf' SIZE 1950M REUSE EXTENT MANAGEMENT LOCAL UNIFORM SIZE 524288000 ONLINE PERMANENT NOLOGGING SEGMENT SPACE MANAGEMENT AUTO
CREATE TABLESPACE "INDX_XXS_001_XYZ" BLOCKSIZE 8192 DATAFILE '/database/oracle/XYZ/INDX_XXS_001_XYZ_001.dbf' SIZE 22020096 REUSE EXTENT MANAGEMENT LOCAL UNIFORM SIZE 65536 ONLINE PERMANENT NOLOGGING SEGMENT SPACE MANAGEMENT AUTO
CREATE TABLESPACE "INDX_XS_001_XYZ" BLOCKSIZE 8192 DATAFILE '/database/oracle/XYZ/INDX_XS_001_XYZ_001.dbf' SIZE 1048576000 REUSE EXTENT MANAGEMENT LOCAL UNIFORM SIZE 524288 ONLINE PERMANENT NOLOGGING SEGMENT SPACE MANAGEMENT AUTO
CREATE TABLESPACE "INDX_S_001_XYZ" BLOCKSIZE 8192 DATAFILE '/database/oracle/XYZ/INDX_S_001_XYZ_001.dbf' SIZE 891289600 REUSE EXTENT MANAGEMENT LOCAL UNIFORM SIZE 1048576 ONLINE PERMANENT NOLOGGING SEGMENT SPACE MANAGEMENT AUTO
CREATE TABLESPACE "INDX_M_001_XYZ" BLOCKSIZE 8192 DATAFILE '/database/oracle/XYZ/INDX_M_001_XYZ_001.dbf' SIZE 2350M REUSE EXTENT MANAGEMENT LOCAL UNIFORM SIZE 10485760 ONLINE PERMANENT NOLOGGING SEGMENT SPACE MANAGEMENT AUTO
CREATE TABLESPACE "INDX_L_001_XYZ" BLOCKSIZE 8192 DATAFILE '/database/oracle/XYZ/INDX_L_001_XYZ_001.dbf' SIZE 18000M REUSE, '/database/oracle/XYZ/INDX_L_001_XYZ_002.dbf' SIZE 6050M REUSE, '/database/oracle/XYZ/INDX_L_001_XYZ_003.dbf' SIZE 7000M REUSE, '/database/oracle/XYZ/INDX_L_001_XYZ_004.dbf' SIZE 6450M REUSE EXTENT MANAGEMENT LOCAL UNIFORM SIZE 104857600 ONLINE PERMANENT NOLOGGING SEGMENT SPACE MANAGEMENT AUTO
CREATE TABLESPACE "INDX_XL_001_XYZ" BLOCKSIZE 8192 DATAFILE '/database/oracle/XYZ/INDX_XL_001_XYZ_001.dbf' SIZE 6750M REUSE, '/database/oracle/XYZ/INDX_XL_001_XYZ_002.dbf' SIZE 9409M REUSE, '/database/oracle/XYZ/INDX_XL_001_XYZ_003.dbf' SIZE 5341M REUSE, '/database/oracle/XYZ/INDX_XL_001_XYZ_004.dbf' SIZE 8500M REUSE EXTENT MANAGEMENT LOCAL UNIFORM SIZE 524288000 ONLINE PERMANENT NOLOGGING SEGMENT SPACE MANAGEMENT AUTO
CREATE TABLESPACE "PRC_I3_OR_TAB" BLOCKSIZE 8192 DATAFILE '/database/oracle/XYZ/PRC_I3_OR_TAB_001.dbf' SIZE 104857600 REUSE EXTENT MANAGEMENT LOCAL AUTOALLOCATE ONLINE PERMANENT NOLOGGING
CREATE TEMPORARY TABLESPACE "PRC_I3_OR_TMP" BLOCKSIZE 8192 TEMPFILE '/database/oracle/XYZ/PRC_I3_OR_TMP_001.dbf' SIZE 52428800 REUSE EXTENT MANAGEMENT LOCAL UNIFORM SIZE 1048576
CREATE UNDO TABLESPACE "UNDO_001_XYZ" BLOCKSIZE 8192 DATAFILE '/database/oracle/XYZ/UNDO_001_XYZ_001.dbf' SIZE 5000M REUSE EXTENT MANAGEMENT LOCAL
CREATE TABLESPACE "PATROL_TBS" BLOCKSIZE 8192 DATAFILE '/database/oracle/XYZ/PATROL_TBS_XYZ_001.dbf' SIZE 52428800 REUSE EXTENT MANAGEMENT LOCAL AUTOALLOCATE ONLINE PERMANENT NOLOGGING SEGMENT SPACE MANAGEMENT AUTO
ALTER USER "SYS" IDENTIFIED BY VALUES '55F9423C0E6B8C0F' TEMPORARY TABLESPACE "TEMP_001_XYZ"
ALTER USER "SYSTEM" IDENTIFIED BY VALUES 'A6D15216D0ECD880' TEMPORARY TABLESPACE "TEMP_001_XYZ"
CREATE USER "OUTLN" IDENTIFIED BY VALUES 'C6E37B008DE60C3F' TEMPORARY TABLESPACE "TEMP_001_XYZ"
CREATE USER "WMSYS" IDENTIFIED BY VALUES '456841663644FC73' TEMPORARY TABLESPACE "TEMP_001_XYZ" ACCOUNT LOCK
CREATE USER "OPS$ORACLE" IDENTIFIED EXTERNALLY DEFAULT TABLESPACE "USER_001_XYZ" TEMPORARY TABLESPACE "TEMP_001_XYZ"
CREATE USER "USRTOU" IDENTIFIED BY VALUES '2760E3E272EB2E33' DEFAULT TABLESPACE "TOOLS_001_XYZ" TEMPORARY TABLESPACE "TEMP_001_XYZ"
CREATE USER "DBSNMP" IDENTIFIED BY VALUES '2EEE7ADF4EE099CC' TEMPORARY TABLESPACE "TEMP_001_XYZ"
CREATE USER "RTXYZ" IDENTIFIED BY VALUES '7A7B1892338BEF1D' DEFAULT TABLESPACE "USER_001_XYZ" TEMPORARY TABLESPACE "TEMP_001_XYZ"
CREATE USER "XYZ" IDENTIFIED BY VALUES 'A2E4641EA075847A' DEFAULT TABLESPACE "USER_001_XYZ" TEMPORARY TABLESPACE "TEMP_001_XYZ"
CREATE USER "BKTUSER" IDENTIFIED BY VALUES 'F2C497DCD69AC001' DEFAULT TABLESPACE "TOOLS_001_XYZ" TEMPORARY TABLESPACE "TEMP_001_XYZ"
CREATE USER "SUIVI_XYZ" IDENTIFIED BY VALUES '70E9F840A7885FB4' DEFAULT TABLESPACE "USER_001_XYZ" TEMPORARY TABLESPACE "TEMP_001_XYZ"
CREATE USER "PERFSTAT" IDENTIFIED BY VALUES '14146C06525833EF' DEFAULT TABLESPACE "PERFDATA" TEMPORARY TABLESPACE "TEMP_001_XYZ"
CREATE USER "PSSORACLE" IDENTIFIED BY VALUES '7A4C515E78D016F1' DEFAULT TABLESPACE "PRC_I3_OR_TAB" TEMPORARY TABLESPACE "PRC_I3_OR_TMP"
CREATE USER "OEMCLI" IDENTIFIED BY VALUES '1B3DC60650E647C6' DEFAULT TABLESPACE "TOOLS_001_XYZ" TEMPORARY TABLESPACE "TEMP_001_XYZ"
CREATE USER "PATROL" IDENTIFIED BY VALUES '0478B8F047DECC65' DEFAULT TABLESPACE "PATROL_TBS" TEMPORARY TABLESPACE "TEMP_001_XYZ"
CREATE USER "RMANCLI" IDENTIFIED BY VALUES '6A4C8251A9A531C4' DEFAULT TABLESPACE "TOOLS_001_XYZ" TEMPORARY TABLESPACE "TEMP_001_XYZ"


After this there are roles, and table structures, functions, packages etc.
I just want one user out of these, so what are my options??
Can i simply do it fromuser touser???


NOTE:Sorry if i posted in some wrong manner. Kindly help.
Re: Can i partially import dump? [message #445867 is a reply to message #445864] Thu, 04 March 2010 07:50 Go to previous messageGo to next message
Michel Cadot
Messages: 63807
Registered: March 2007
Location: Nanterre, France, http://...
Senior Member
Account Moderator
Quote:
Can i simply do it fromuser touser???

Yes.

Regards
Michel
Re: Can i partially import dump? [message #445870 is a reply to message #445867] Thu, 04 March 2010 07:57 Go to previous messageGo to next message
amit.sehrawat
Messages: 29
Registered: September 2009
Location: India
Junior Member

Michel Cadot wrote on Thu, 04 March 2010 07:50
Quote:
Can i simply do it fromuser touser???

Yes.

Regards
Michel


So, tell me if am right,

1) i will create tablespace, will create user XYZ and assign the tablespace to user.

2)import structure: imp system/password full=y file=dumpname.dmp log=logname.log fromuser=XYZ touser=ABC ignore=y buffer=10000 feedback=10000;

3) import data dump.

Re: Can i partially import dump? [message #445872 is a reply to message #445870] Thu, 04 March 2010 08:07 Go to previous messageGo to next message
Michel Cadot
Messages: 63807
Registered: March 2007
Location: Nanterre, France, http://...
Senior Member
Account Moderator
Quote:
import structure: imp system/password full=y file=dumpname.dmp log=logname.log fromuser=XYZ touser=ABC ignore=y buffer=10000 feedback=10000;

This will import structure AND data.
Increase the buffer size this one is at least 1000 times too small.

Regards
Michel
Re: Can i partially import dump? [message #445875 is a reply to message #445872] Thu, 04 March 2010 08:22 Go to previous messageGo to next message
amit.sehrawat
Messages: 29
Registered: September 2009
Location: India
Junior Member

Michel Cadot wrote on Thu, 04 March 2010 08:07
Quote:
import structure: imp system/password full=y file=dumpname.dmp log=logname.log fromuser=XYZ touser=ABC ignore=y buffer=10000 feedback=10000;

This will import structure AND data.
Increase the buffer size this one is at least 1000 times too small.


but i have two different dumps for structure and data.
The import command i specified, will it work on structure dump?
Data dump i can handle, but i don't know how to handle structure dump.


Regards
Michel

Re: Can i partially import dump? [message #445877 is a reply to message #445875] Thu, 04 March 2010 08:25 Go to previous messageGo to next message
Michel Cadot
Messages: 63807
Registered: March 2007
Location: Nanterre, France, http://...
Senior Member
Account Moderator
If data dump is a real Oracle export dump then it contains both structures and data.
But you can do the both import if you want.

Regards
Michel
Re: Can i partially import dump? [message #445879 is a reply to message #445877] Thu, 04 March 2010 08:31 Go to previous messageGo to next message
amit.sehrawat
Messages: 29
Registered: September 2009
Location: India
Junior Member

See the structural dump is creating users, if i will import that as it is, it will create users, which i don't need.
So, how can i import the structure but avoid creating extra tablesspaces and users???

Regards
Michel
[/quote]
Re: Can i partially import dump? [message #445884 is a reply to message #445879] Thu, 04 March 2010 08:43 Go to previous messageGo to next message
Michel Cadot
Messages: 63807
Registered: March 2007
Location: Nanterre, France, http://...
Senior Member
Account Moderator
Regarding databases metadata (users, tablespaces and so on) you can't choose.
Regarding schema metadata (tables, indexes...) and data, you can choose.

Regards
Michel
Re: Can i partially import dump? [message #445978 is a reply to message #445884] Fri, 05 March 2010 00:27 Go to previous messageGo to next message
amit.sehrawat
Messages: 29
Registered: September 2009
Location: India
Junior Member

Can i open the dmp file in some text editor and comment lines where they are creating the users and tablespaces??? will that work?
Re: Can i partially import dump? [message #445983 is a reply to message #445978] Fri, 05 March 2010 00:46 Go to previous message
Michel Cadot
Messages: 63807
Registered: March 2007
Location: Nanterre, France, http://...
Senior Member
Account Moderator
No, you can't.

Regards
Michel
Previous Topic: Dump Table from One Schema to Other schema
Next Topic: Database Installation using ASM in DBCA???
Goto Forum:
  


Current Time: Wed Sep 28 14:19:08 CDT 2016

Total time taken to generate the page: 0.16889 seconds