数据库表空间整理
(数据导入方法)
刘凯 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:表空间转换,值“导出是表存在的表空间:导入后导入的表空间”
(数据导入方法)
刘凯 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:表空间转换,值“导出是表存在的表空间:导入后导入的表空间”