Data migration between Oracle's different users in a production environment. Part II

Task Name: Schema Elon Data migration to schema TIAN in the production environment
########################################

Test two: Test parameter remap_tablespace

Export all data for schema Elon:
[[Email protected] ~] $EXPDP system/xxxxxx schemas=elon directory=expdp_dir dumpfile =elon_ ' date + '%y%m%d%h%m%s '. DMP Logfile=elon_ ' date + '%y%m%d%h%m%s ' _exp.log
Import the DMP file into the LAS:
[[Email protected] ~] $IMPDP system/xxxxxx directory=expdp_dir dumpfile=elon_ ' date + "%y%m%d%h%m%s" '. DMP Logfile=elon_ ' Date + "%y%m%d%h%m%s" ' _impdp.log remap_schema=elon:test Table_exists_action=skip

Usage of the LAS Default table space:
Sql> Select Df.tablespace_name, Totalspace total_size, (totalspace-freespace) Used_size,freespace avail_size, round ((1-freespace/totalspace) *100) as Used_ratio
From (select Tablespace_name,round (sum (bytes)/1024/1024) totalspace
From Dba_data_files GROUP by Tablespace_name) DF, (select Tablespace_name,round (sum (bytes)/1024/1024) freespace
From Dba_free_space GROUP by Tablespace_name) FS where Df.tablespace_name=fs.tablespace_name and Df.tablespace_name not L Ike ' undotbs% ';

Tablespace_name total_size used_size avail_size used_ratio
------------------------------ ---------- ---------- ---------- ----------
ELON_IDX01 10240 88 10152 1
Sysaux 3072 1753 1319 57
Elon_data01 20480 2812 17668 14
las_idx01 10240 3591 6649 35
USERS 1024 2 1022 0
SYSTEM 2048 847 1201 41

Conclusion: If you do not specify Remap_tablespace, after importing to test, the data is still in the default tablespace of the Elon user.

Sql> Select Df.tablespace_name, Totalspace total_size, (totalspace-freespace) Used_size,freespace avail_size, round ((1-freespace/totalspace) *100) as Used_ratio
From (select Tablespace_name,round (sum (bytes)/1024/1024) totalspace
From Dba_data_files GROUP by Tablespace_name) DF, (select Tablespace_name,round (sum (bytes)/1024/1024) freespace
From Dba_free_space GROUP by Tablespace_name) FS where Df.tablespace_name=fs.tablespace_name and Df.tablespace_name not L Ike ' undotbs% ';

Tablespace_name total_size used_size avail_size used_ratio
------------------------------ ---------- ---------- ---------- ----------
ELON_IDX01 10240 88 10152 1
Sysaux 3072 1753 1319 57
Elon_data01 20480 1979 18501 10
las_idx01 10240 3591 6649 35
USERS 1024 2 1022 0
SYSTEM 2048 845 1203 41
Rebuilding test user and table space
sql> drop user TEST cascade;
User dropped.
sql> Create tablespace test_data01 datafile '/data/app/oracle/oradata/test_data01. DBF ' size 1G autoextend on;
Tablespace created.
sql> Create tablespace test_idx01 datafile '/data/app/oracle/oradata/test_idx01. DBF ' size 1G autoextend on;
Tablespace created.
sql> Create temporary tablespace test_temp01 tempfile '/data/app/oracle/oradata/test_temp01. DBF ' size 1G autoextend on;
Tablespace created.
Sql> create user test identified by TEST default tablespace test_data01 temporary tablespace test_temp01;
User created.
Sql> Select Df.tablespace_name, Totalspace total_size, (totalspace-freespace) Used_size,freespace avail_size, round ((1-freespace/totalspace) *100) as Used_ratio
From (select Tablespace_name,round (sum (bytes)/1024/1024) totalspace
From Dba_data_files GROUP by Tablespace_name) DF, (select Tablespace_name,round (sum (bytes)/1024/1024) freespace
From Dba_free_space GROUP by Tablespace_name) FS where Df.tablespace_name=fs.tablespace_name and Df.tablespace_name not L Ike ' undotbs% ';

Tablespace_name total_size used_size avail_size used_ratio
------------------------------ ---------- ---------- ---------- ----------
ELON_IDX01 10240 88 10152 1
TEST_DATA01 1024 1 1023 0
Sysaux 3072 1754 1318 57
Elon_data01 20480 1979 18501 10
las_idx01 10240 3591 6649 35
USERS 1024 2 1022 0
SYSTEM 2048 845 1203 41
TEST_IDX01 1024 1 1023 0

Import data again:
Query the user's default tablespace for Elon
sql> select Username,default_tablespace,temporary_tablespace from Dba_users where username = ' ELON ';
Elonelon_data01elon_temp
Determines the index table space
Sql>select owner,index_name,tablespace_name from Dba_indexes where Owner= ' ELON ';
Elonidx_info_req_date elon_data01
elonpk_t_sys_exception elon_idx01
elonpk_t_message ELON_IDX01
Import data
[[email protected] ~] $IMPDP system/xxxxxx directory=expdp_dir dumpfile=elon_ ' date + '%y%m%d%h%m%s '. DMP Logfile=elon_ ' date + "%y%m%d%h%m%s" ' _impdp.log remap_schema=elon:test remap_tablespace=elon_data01:test_data01, ELON_IDX01:TEST_IDX01,ELON_TEMP:TEST_TEMP01 Table_exists_action=skip

Sql> Select Df.tablespace_name, Totalspace total_size, (totalspace-freespace) Used_size,freespace avail_size, round ((1-freespace/totalspace) *100) as Used_ratio
2 from (select Tablespace_name,round (sum (bytes)/1024/1024) totalspace
3 from Dba_data_files GROUP by Tablespace_name) DF, (select Tablespace_name,round (sum (bytes)/1024/1024) freespace
4 from Dba_free_space GROUP by Tablespace_name) FS where df.tablespace_name=fs.tablespace_name;

Tablespace_name total_size used_size avail_size used_ratio
------------------------------ ---------- ---------- ---------- ----------
ELON_IDX01 10240 88 10152 1
TEST_DATA01 2117 2029 88 96
Sysaux 3072 1754 1318 57
Elon_data01 20480 1979 18501 10
UNDOTBS1 83910 8169 75741 10
las_idx01 10240 3591 6649 35
USERS 1024 2 1022 0
SYSTEM 2048 845 1203 41
TEST_IDX01 1024 94 930 9

Conclusion: By making remap_tablespace, the newly imported test data is written to TEST_DATA01, indexed to TEST_IDX01

Data migration between different users of Oracle in a production environment. Part Two