db_test

数据库表空间整理
（数据导入方法）
刘凯 20101115

经现网查看，现网数据库表空间已全部更换
现网表空间：
TBS_MREAD_HISDAT
TBS_MREAD_HISLOG
TBS_MREAD_DAT
TBS_MREAD_IDX

本地环境表空间：
manager
mread
mread_data
idx
MREAD_HISLOG
MREAD_HISDAT

如今要与现网一致：
manager
mread                                              TBS_MREAD_DAT  
mread_data

idx TBS_MREAD_IDX

MREAD_HISLOG TBS_MREAD_HISLOG

MREAD_HISDAT TBS_MREAD_HISDAT

操作步骤：
sys用户dba权限登录数据库
1.备份数据库：
expdp manager/manager dumpfile=***.dump directory=impdir
2.备份成功后，删除用户
drop user manager  cascade;
3.删除原来表空间
drop tablespace xxx including contents and datafiles;
4.创建新的表空间
CREATE TABLESPACE TBS_MREAD_DAT DATAFILE '/opt/oracle/oradata/TBS_MREAD_DAT.dbf' SIZE 500M REUSE AUTOEXTEND ON NEXT 10M MAXSIZE UNLIMITED EXTENT MANAGEMENT LOCAL;
CREATE TABLESPACE TBS_MREAD_IDX DATAFILE '/opt/oracle/oradata/TBS_MREAD_IDX.dbf' SIZE 500M REUSE AUTOEXTEND ON NEXT 10M MAXSIZE UNLIMITED EXTENT MANAGEMENT LOCAL;
CREATE TABLESPACE TBS_MREAD_HISLOG DATAFILE '/opt/oracle/oradata/ TBS_MREAD_HISLOG.dbf' SIZE 500M REUSE AUTOEXTEND ON NEXT 10M MAXSIZE UNLIMITED EXTENT MANAGEMENT LOCAL;
CREATE TABLESPACE TBS_MREAD_HISDAT DATAFILE '/opt/oracle/oradata/ TBS_MREAD_HISDAT.dbf' SIZE 500M REUSE AUTOEXTEND ON NEXT 10M MAXSIZE UNLIMITED EXTENT MANAGEMENT LOCAL;
5.新建用户
create user manager identified by manager default tablespace TBS_MREAD_DAT temporary tablespace temp;
6.给新用户赋权限
grant connect,resource,create public database link,drop public database link to manager;
GRANT UNLIMITED TABLESPACE TO manager WITH ADMIN OPTION;
GRANT SELECT ANY TABLE TO manager WITH ADMIN OPTION;
GRANT CREATE ANY TABLE TO manager WITH ADMIN OPTION;
GRANT ALTER ANY TABLE TO manager WITH ADMIN OPTION;
GRANT DROP ANY TABLE TO manager WITH ADMIN OPTION;
GRANT CREATE ANY view TO manager WITH ADMIN OPTION;
GRANT DROP ANY view TO manager WITH ADMIN OPTION;
grant sysdba to manager;
grant unlimited tablespace to manager with admin option;

7.赋予读取备份文件权限----备份文件“***.dump”在/opt/oracle/expdir目录下
create or replace directory expdir as '/opt/oracle/expdir';
grant read,write on directory expdir to manager;
8.导入备份的数据库文件
impdp manager/manager  DIRECTORY=expdir DUMPFILE=***.dump remap_schema=
iread49:manager transform=oid:n remap_tablespace=MANAGER:TBS_MREAD_DAT,MREAD_DATA:
TBS_MREAD_DAT,idx:TBS_MREAD_idx,MREAD:TBS_MREAD_DAT,MREAD_HISDAT:TBS_MREAD_HISDAT,MREAD_HISLOG:TBS_MREAD_HISLOG
说明：DIRECTORY：导入文件的存放目录值为7步中的目录权限名
   DUMPFILE：导入文件的文件名
   remap_schema：指导导入用户转换，值“导出用户的用户名：导入用户的用户名”
   remap_tablespace：表空间转换，值“导出是表存在的表空间：导入后导入的表空间”