Oracle数据泵数据迁移

1 表空间查询

1.1 检查用户与表空间对应情况

select username,default_tablespace from dba_users;

1.2    查看临时表空间

select tablespace_name from dba_temp_files;

1.3    查看表空间大小

SELECT UPPER(F.TABLESPACE_NAME) "表空间名",
D.TOT_GROOTTE_MB "表空间大小(M)",
D.TOT_GROOTTE_MB - F.TOTAL_BYTES "已使用空间(M)",
TO_CHAR(ROUND((D.TOT_GROOTTE_MB - F.TOTAL_BYTES) / D.TOT_GROOTTE_MB * 100,2),'990.99') || '%' "使用比",
F.TOTAL_BYTES "空闲空间(M)",
F.MAX_BYTES "最大块(M)"
FROM (SELECT TABLESPACE_NAME,
ROUND(SUM(BYTES) / (1024 * 1024), 2) TOTAL_BYTES,
ROUND(MAX(BYTES) / (1024 * 1024), 2) MAX_BYTES
FROM SYS.DBA_FREE_SPACE
GROUP BY TABLESPACE_NAME) F,
(SELECT DD.TABLESPACE_NAME,
 ROUND(SUM(DD.BYTES) / (1024 * 1024), 2) TOT_GROOTTE_MB
FROM SYS.DBA_DATA_FILES DD
GROUP BY DD.TABLESPACE_NAME) D
WHERE D.TABLESPACE_NAME = F.TABLESPACE_NAME
ORDER BY 1;

2      创建及删除表空间

2.1    查看数据文件路径

select * from dba_data_files;

2.2    创建表空间及设置初始化空间

创建表空间

CREATE TABLESPACE TS_USERNAME_TJ_DATA datafile '+DATA' autoextend on next 100M maxsize 30G;

创建临时表空间

CREATE TEMPORARY TABLESPACE TS_USERNAME_TEMP TEMPFILE '+DATA' SIZE 30G  AUTOEXTEND ON  NEXT 10M MAXSIZE UNLIMITED EXTENT MANAGEMENT LOCAL;

 

2.3    删除表空间

drop TABLESPACE TS_USERNAME_TEMP including contents and datafiles;

2.4    创建用户，关联表空间

CREATE USER MOE_WAREHOUSE  identified by password  DEFAULT TABLESPACE TS_USERNAME_TJ_DATA temporary tablespace TS_USERNAME_TEMP ;

3      用户授权

为用户赋予权限

grant unlimited tablespace to UserName;
grant create tablespace to UserName;
grant alter tablespace to UserName;
grant drop tablespace to UserName;
grant manage tablespace to UserName;
grant create view to UserName;
grant create session to UserName;
grant select on V_$session to UserName;
grant select on V_$sesstat to UserName;
grant select on V_$statname to UserName;
grant all privileges to UserName；
grant dba to UserName;

4 删除用户及清理数据

drop user USERNAME cascade;

5      数据迁移

5.1    注意事项

根据2.1和2.2完成新系统操作，数据泵数据迁移，只需要创建好表空间即可。数据泵数据备份是按用户模式进行的。

创建数据

 

5.2    创建数据备份目录

create directory UserName_dir as '/home/oracle';

grant read,write on directory UserName_dir to UserName;

 

5.3    数据导出

expdp \' / as sysdba \' parfile=exp_UserName.par

vim exp_UserName.par
dumpfile=exp_UserName_20201126.dmp
logfile=exp_UserName_20201126.log
directory=DBBAK
SCHEMAS=UserName

5.4    数据导入

impdp \' / as sysdba \' parfile=imp_moewarehouse.par

imp_UserName.par
dumpfile=exp_UserName_20201126.dmp
logfile=exp_UserName_20201126.log
directory=DBBAK    
FULL=y

6      数据库重启

shutdown immediate

startup

7      修改密码

alter user UserName identified by UserName2018$;

 

温馨提示：本文由自己记录，仅作参考，出了问题概不负责。。。。。