筛选备份
Mdata01数据导到Sdata02
1.导出
1.给用户授予权限
grant connect,resource,dba to username;
grant read,write on directory dpdata to username;
2.查看数据库dba_directories目录
select * from dba_directories;
3.若没有则新建
create directorydpdata as '/data/dpdata'
4.查看username的默认表空间
selectusername,default_tablespace from dba_users;
5.查询数据比较大的历史表
Select Segment_Name,Sum(bytes)/1024/1024 as sizes fromUser_Extents Group By Segment_Name order by sizes desc
6.筛选备份语句
expdp username/password@orcl schemas=username dumpfile=20170703.dmpexclude=TABLE:\" IN \(\'T_T_HOLD_H\'\,\' T_F_FUND_FLOW_H\'\,\'T_T_USERNAME_H\'\,\'T_T_ORDER_H\'\)\" DIRECTORY=dpdata;
7.备份四张表空间
Expdp username/password@orcltables="('T_T_HOLD_H','T_F_FUND_FLOW_H','T_T_USERNAME_H','T_T_ORDER_H')"CONTENT=METADATA_ONLY directory=dpdata dumpfile=zheng.dmp
(先备份除四张历史表外的其他全部数据,在备份这四张历史表空间)
2.导入
1.查询数据库的用户,及对应的表空间
select username,default_tablespace from dba_users;
2.选择要导入的用户,若没有则新建
新建临时表空间
CREATE TEMPORARY TABLESPACE username_temp
TEMPFILE '/data/oradata/orcl/username_tmp.dbf'
SIZE 10240M AUTOEXTEND ON NEXT 500M
MAXSIZE unlimited
EXTENT MANAGEMENT LOCAL ;
新建表空间
create tablespace username
logging
datafile '/data/oradata/orcl/ username .dbf'
size 2 8m
autoextend on
next 500m
maxsize unlimited extent management local segment spacemanagement auto;
创建用户并指定表空间
create user username identified by username
default tablespace username
temporary tablespace username_temp;
给用户授予权限
grant connect,resource,dba to username ;
grant read,write on directory dpdata to username ;
3.导入
导入四张表结构
Impdp username /username @orclDIRECTORY=dpdata DUMPFILE=zheng.dmp remap_schema=username:username remap_tablespace=username:username TABLE_EXISTS_ACTION=REPLACE transform=oid:n
导入数据
impdp username /username @orclDIRECTORY=dpdata DUMPFILE=20170703.dmp remap_schema=username:username remap_tablespace=username:username TABLE_EXISTS_ACTION=REPLACE transform=oid:n
(先导入四张表结构在导入除四张外的数据)