Can i partially import dump? [message #445864] |
Thu, 04 March 2010 07:37 |
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 #445870 is a reply to message #445867] |
Thu, 04 March 2010 07:57 |
amit.sehrawat
Messages: 29 Registered: September 2009 Location: India
|
Junior Member |
|
|
Michel Cadot wrote on Thu, 04 March 2010 07:50Quote: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 #445875 is a reply to message #445872] |
Thu, 04 March 2010 08:22 |
amit.sehrawat
Messages: 29 Registered: September 2009 Location: India
|
Junior Member |
|
|
Michel Cadot wrote on Thu, 04 March 2010 08:07Quote: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 #445879 is a reply to message #445877] |
Thu, 04 March 2010 08:31 |
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]
|
|
|
|
|
|