oracle11g迁移用户数据到其他表空间

# 环境

操作系统

$ uname -a

Linux jinhu-db02 2.6.32-696.el6.x86_64 #1 SMP Tue Mar 21 19:29:05 UTC 2017 x86_64 x86_64 x86_64 GNU/Linux

$ cat /etc/redhat-release 

CentOS release 6.9 (Final)

数据库

BANNER

--------------------------------------------------------------------------------

Oracle Database 11g Enterprise Edition Release 11.2.0.4.0 - 64bit Production

PL/SQL Release 11.2.0.4.0 - Production

CORE 11.2.0.4.0 Production

TNS for Linux: Version 11.2.0.4.0 - Production

NLSRTL Version 11.2.0.4.0 - Production

1. 创建所需表空间

CREATE TABLESPACE TBS_PREPAID DATAFILE '/home/oracle/oradata/orcl/datafile/tbs_prepaid01.dbf' SIZE 8G AUTOEXTEND ON NEXT 500M;

2. expdp导出整库

expdp \'/ as sysdba\' DIRECTORY=exp_dir DUMPFILE=expdp_full_20180409_01.dmp PARALLEL=2 FULL=y LOGFILE=expdpfull.log

expdp system/oracle DIRECTORY=exp_dir DUMPFILE=expdp_full_20180409_01.dmp PARALLEL=2 FULL=y LOGFILE=expdpfull.log

3. 查询已经连接到系统中的用户，并断开这些用户的连接

set head off

set echo off

set feedback off

spool /home/oracle/killuser.sql

SELECT 'ALTER SYSTEM KILL SESSION '||''''||sid||','||serial#||''';' FROM v$session WHERE username = 'A_ONLINEPAY' OR username = 'PROENV_PREPAID';

spool off

@/home/oracle/killuser.sql

4. 删除用户

DROP USER proenv_prepaid CASCADE;

DROP USER a_onlinepay CASCADE;

5. 导入数据

impdp \'/ as sysdba\' DIRECTORY=exp_dir DUMPFILE=expdp_full_20180409_01.dmp PARALLEL=2 SCHEMAS=proenv_prepaid REMAP_TABLESPACE=users:tbs_prepaid LOGFILE=impdp01.log

impdp \'/ as sysdba\' DIRECTORY=exp_dir DUMPFILE=expdp_full_20180409_01.dmp PARALLEL=2 SCHEMAS=a_onlinepay REMAP_TABLESPACE=users:tbs_proenv LOGFILE=impdp02.log

impdp system/oracle DIRECTORY=exp_dir DUMPFILE=expdp_full_20180409_01.dmp PARALLEL=2 SCHEMAS=proenv_prepaid REMAP_TABLESPACE=users:tbs_prepaid LOGFILE=impdp01.log

impdp system/oracle DIRECTORY=exp_dir DUMPFILE=expdp_full_20180409_01.dmp PARALLEL=2 SCHEMAS=a_onlinepay REMAP_TABLESPACE=users:tbs_proenv LOGFILE=impdp02.log

6. 查询用户默认表空间，并修改用户的默认表空间

SELECT username, default_tablespace FROM dba_users WHERE username in ('A_ONLINEPAY','PROENV_PREPAID');

USERNAME        DEFAULT_TABLESPACE

------------------------------ ------------------------------

A_ONLINEPAY        TBS_PROENV

PROENV_PREPAID        TBS_PREPAID

ALTER USER proenv_prepaid IDENTIFIED BY 123456 DEFAULT TABLESPACE tbs_prepaid;

ALTER USER a_onlinepay IDENTIFIED BY 123456 DEFAULT TABLESPACE tbs_proenv;

SELECT username, default_tablespace FROM dba_users WHERE username in ('A_ONLINEPAY','PROENV_PREPAID');

USERNAME        DEFAULT_TABLESPACE

------------------------------ ------------------------------

A_ONLINEPAY        TBS_PROENV

PROENV_PREPAID        TBS_PREPAID

7. 查看无效对象，并重新编译

SELECT owner,object_name, replace(object_type,' ','') object_type ,to_char(created,'yyyy-mm-dd') as created ,to_char(last_ddl_time,'yyyy-mm-dd') as last_ddl_time, status  

FROM dba_objects 

WHERE status='INVALID' AND owner='PROENV_PREPAID';  

SQL> ALTER PROCEDURE proenv_prepaid.pr_dailycheck COMPILE;

8. 相关测试

!!The End!!