oracle数据库迁移的步骤
目的：将192.168.9.102上GGWSXT/GGWSXT@192.168.9.102:1521/ORACLE部署到104上1、查看原用户表空间
select tablespace_name,count(0) from dba_segments where owner=upper('GGWSXT') group by tablespace_name;
2、查看原用户创建表空间的目录
select * from dba_data_files；
3、为原用户创建表空间
create tablespace TS_GGWSXT_XT
datafile '/步骤2/TS_GGWSXT_XT.bdf'> AUTOEXTEND ON NEXT 256M MAXSIZE UNLIMITED
EXTENT MANAGEMENT LOCAL
SEGMENT SPACE MANAGEMENT auto;
4、创建新用户
Create user GGWSXT_01
Identified by GGWSXT_01
Default tablespace TS_GGWSXT_XT
Temporary tablespace TEMP
5、为新用户赋权限
Grant connect,resource,dba,select any table to GGWSXT_01;
grant read,write on directory dpdir to GGWSXT_01;
select * from dba_directories
6、为原用户赋目录权限
grant read,write on directory dpdir to GGWSXT_01;
7、通过expdp（数据泵方式）导出原用户数据 [如果时从11g迁移到10g后面添加 version=10.2.0.1.0低版本] su - oralce 下执行
nohup expdp ggwsxt/ggwsxt@192.168.9.102/oracle schemas=ggwsxt dumpfile=ggwsxt_20150713.dmp DIRECTORY=dpdir &
tail －f nohup.out
8、数据库不在一台服务器上，备份文件目录不同时；
select ＊ from dba_directories; 查询导入导出dmp文件的路径
scp /导出库路径/GGWSXT_20150713.dmp root@192.168.9.104:/导入库路径/GGWSXT_20150713.dmp
9、chown oralce.oinstall(或者是dba) GGWSXT_20150713.dmp
10、通过impdp将数据导入新用户，语句中间无换行
nohup impdp GGWSXT_01/GGWSXT_01@192.168.9.104/ORACLE directory=dpdir dumpfile=GGWSXT_20150713.dmp remap_schema=GGWSXT:GGWSXT01 remap_tablespace=原用户表空间名：新用户表空间名，原用户表空间名：新用户表空间名 &
（注释：表空间名查询方式select tablespace_name,count(0) from dba_segments where owner=upper('用户名') group by tablespace_name;)
11、数据导入后，需执行一下语句以实现用户对象的重新编译在plsql中
execute dbms_utility.compile_schema(upper('新用户名GGWSXT_01'));
注：11.5：若导入的是两个有关联的库，请导入之后更新新用户同义词
declare
v_ywuser varchar2(20):='BDSQZBZS';
v_gluser varchar2(20):='BDSQXTGLZS';
v_sql varchar2(512):=null;
begin
for v_synonym in(select synonym_name,table_owner,table_name from all_synonyms where owner=upper(v_ywuser))
loop
v_sql:='create or replace synonym '||v_ywuser||'.'||v_synonym.synonym_name||' for '||v_gluser||'.'||v_synonym.table_name;
--dbms_output.put_line(v_sql);
execute immediate v_sql;
end loop;
for v_synonym in(select synonym_name,table_owner,table_name from all_synonyms where owner=upper(v_gluser))
loop
v_sql:='create or replace synonym '||v_gluser||'.'||v_synonym.synonym_name||' for '||v_ywuser||'.'||v_synonym.table_name;
--dbms_output.put_line(v_sql);
execute immediate v_sql;
end loop;
end;
execute dbms_utility.compile_schema('新用户');
页:
[1]