oracle不同环境数据迁移,生产环境下，oracle不同用户间的数据迁移。第二部分

任务名称：生产环境下schema ELON数据迁移至schema TIAN

########################################

测试二：测试参数remap_tablespace

导出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

将dmp文件导入到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

LAS默认表空间的使用情况：

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 like ‘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

结论：如果不指定remap_tablespace，导入到TEST之后，数据还是在ELON用户的默认表空间。

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 like ‘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

重建TEST用户及表空间

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 like ‘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

再次导入数据：

查询用户的ELON的默认表空间

SQL> select username,default_tablespace,temporary_tablespace from dba_users where username = ‘ELON‘;

ELONELON_DATA01ELON_TEMP

确定索引表空间

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

导入数据

[[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

结论：通过制定remap_tablespace，新导入TEST的数据写入到TEST_DATA01，索引到TEST_IDX01

原文：https://www.cnblogs.com/elontian/p/8819497.html