oracle数据库筛选备份

筛选备份

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

（先导入四张表结构在导入除四张外的数据）