这个是之前生产上打算迁移的文档，后面离职了没有在停机迁移，但是测试过几次没有问题，其中需要把9I的110,120库迁移到11g一个数据库中，但是110，120库之间有相同的表名字，以及有DBLINK。
一、迁移前查询
110库 活动的用户为以下几个用户（BOSS,MD_QZY,SMART,SRDQ,RIMS）
120库 活动的用户(BOSS,SMART)
select distinct username from v$session;
BOSS
MD_QZY
RIMS
SMART
SRDQ
关闭监听，杀进程
ps -ef |grep ora|awk '{print $2}'|xargs kill -9
查看迁移前的对象个数(不同用户查看)：
set lin 200 pages 100
select owner,object_type,count(*)
from dba_objects
where OWNER in ('BOSS', 'MD_QZY', 'SMART', 'SRDQ','RIMS')
GROUP BY OWNER ,object_type ;
OWNER
OBJECT_TYPE
COUNT(*)
------------------------------ ------------------ ----------
RIMS
INDEX
13
RIMS
TABLE
9
SMART
VIEW
10
SMART
INDEX
124
SMART
TABLE
323
SMART
TRIGGER
212
SMART
FUNCTION
3
SMART
SEQUENCE
228
SMART
PROCEDURE
2
SMART
DATABASE LINK
9
SMART
INDEX PARTITION
2021
SMART
TABLE PARTITION
3347
SMART
TABLE SUBPARTITION
192
1、查看110与120相同的对象名称(smart对象名称一样的特别多)。
smart用户：conn smart/lzxMZD135468
select object_type,object_name
from user_objects
where object_name in
(select object_name from user_objects@db_lin_120.sjzk.com.cn)
order by 1;
SEQUENCE
S_KEEPCODE_SEQ
TABLE
S_KEEPCODE
TABLE
S_SCODE2MO
TRIGGER
S_KEEPCODE_TRIG
2、查看110 boss用户无对象。
boss用户：
select object_type,object_name
from user_objects
只有smart,RIMS/rims2019csmd 用户才有表：
查看表
SELECT ROUND(SUM(BYTES / 1024 / 1024 / 1024),2) g
FROM USER_SEGMENTS
WHERE SEGMENT_TYPE='TABLE'
AND SEGMENT_NAME not IN (
'S_MOBACKUPQUEUE',
'S_T_RETURN_REPORT_TJ_FULL',
'S_SYS_ERROR',
'S_T_SEND_REPORT_TJ_FULL',
'S_OPERATELOG_TONGJI_FULL',
'S_T_RETURN_REPORT_LTJ'
);
SELECT SEGMENT_NAME, ROUND(SUM(BYTES / 1024 / 1024 / 1024),2) g
FROM USER_SEGMENTS
WHERE SEGMENT_TYPE='TABLE'
AND SEGMENT_NAME not IN (
'S_MOBACKUPQUEUE',
'S_T_RETURN_REPORT_TJ_FULL',
'S_SYS_ERROR',
'S_T_SEND_REPORT_TJ_FULL',
'S_OPERATELOG_TONGJI_FULL',
'S_T_RETURN_REPORT_LTJ'
)
GROUP BY SEGMENT_NAME
ORDER BY 2 DESC
select tname ||',' from tab where TABTYPE='TABLE' and tname not
in (
'S_MOBACKUPQUEUE',
'S_T_RETURN_REPORT_TJ_FULL',
'S_SYS_ERROR',
'S_T_SEND_REPORT_TJ_FULL',
'S_OPERATELOG_TONGJI_FULL',
'S_T_RETURN_REPORT_LTJ'
) order by length(tname) ;
select object_type,object_name
from user_objects
先修改MD_QZY,RIMS的密码：
alter user SRDQ identified by oracle;
alter user MD_QZY identified by oracle;
后续修改：
alter user MD_QZY identified by values 'E7AD6E6562822EBE';
alter user SRDQ identified by values '6F1E875DA235F4F2';
停止监听：
杀进程：
ps -ef | grep LOCAL=NO | awk '{print $2}' | xargs kill -9
导出文件：
- Add comments to the columns 为乱码
system/lsplgj20080808
export LANG=C
export NLS_LANG="SIMPLIFIED CHINESE_CHINA.ZHS16GBK" 或者 export NLS_LANG=AMERICAN_AMERICA.ZHS16GBK
导出smart的表，不要触发器：
nohup exp smart/lzxMZD135468@DB_110 direct=y recordlength=65535 buffer=104857600 file=http://www.likecs.com/oracle/smart_20190419.dmp log=http://www.likecs.com/oracle/smart_20190419.log feedback=10000 tables=(
) TRIGGERS=N &
利用PLSQL导出函数，出处过程,序列,触发器，试图，db_link(这个要单独导出),同义词等。
exp rims/rims2019csmd@DB_110 direct=y recordlength=65535 buffer=104857600 file=http://www.likecs.com/oracle/rims_20190419.dmp log=http://www.likecs.com/oracle/rims_20190419.log feedback=10000 tables=(
T_BOSS_DEPARTMENT_INFO ,
T_BOSS_RIMS_ENT
,
T_BOSS_RIMS_MENU
,
T_BOSS_RIMS_ROLE
,
T_BOSS_RIMS_ROLE_MENU ,
T_BOSS_RIMS_SETTLEMENT ,
T_BOSS_RIMS_SN_INFO
,
T_BOSS_RIMS_STAFF_ROLE ,
T_BOSS_STAFF_INFO
) TRIGGERS=N &
--exp BOSS/BOSS@DB_110 direct=y recordlength=65535 buffer=104857600 file=http://www.likecs.com/oracle/boss_20190124.dmp log=http://www.likecs.com/oracle/boss_20190124.log feedback=10000 owner=boss
--exp SRDQ/oracle@DB_110 direct=y recordlength=65535 buffer=104857600 file=http://www.likecs.com/oracle/SRDQ_20190124.dmp log=http://www.likecs.com/oracle/SRDQ_20190124.log feedback=10000 owner=SRDQ
--exp MD_QZY/oracle@DB_110 direct=y recordlength=65535 buffer=104857600 file=http://www.likecs.com/oracle/MD_QZY_20190124.dmp log=http://www.likecs.com/oracle/MD_QZY_20190124.log feedback=10000 owner=MD_QZY
查看（BOSS,MD_QZY,SMART,SRDQ）条数要用一会儿时间：
利用plsql导出smart 用户的非表的对象（序列，同义词，等等）
set serveroutput on size 1000000
set pages 50000
spool /home/oracle/laoku-smart.txt
DECLARE
v_cnt number;
BEGIN
FOR rec in (select 'SMART.' || TABLE_NAME AS tanme from dba_tables where owner='SMART' and table_name not in
('S_MOBACKUPQUEUE',
'S_T_RETURN_REPORT_TJ_FULL',
'S_SYS_ERROR',
'S_T_SEND_REPORT_TJ_FULL',
'S_OPERATELOG_TONGJI_FULL',
'S_T_RETURN_REPORT_LTJ')
order by 1)
LOOP
execute immediate 'select count(*) from '||rec.tanme into v_cnt;
dbms_output.put_line(rpad(rec.tanme,40,'-')||v_cnt);
END LOOP;
END;
/
倒出部分
=============================================================
查看使用的表空间：