9i oracle数据库迁移到11G（exp）

这个是之前生产上打算迁移的文档，后面离职了没有在停机迁移，但是测试过几次没有问题，其中需要把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=/oracle/smart_20190419.dmp log=/oracle/smart_20190419.log feedback=10000 tables=(

) TRIGGERS=N &

利用PLSQL导出函数，出处过程,序列,触发器，试图，db_link(这个要单独导出),同义词等。

exp rims/rims2019csmd@DB_110 direct=y recordlength=65535 buffer=104857600 file=/oracle/rims_20190419.dmp log=/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=/oracle/boss_20190124.dmp log=/oracle/boss_20190124.log feedback=10000 owner=boss
--exp SRDQ/oracle@DB_110 direct=y recordlength=65535 buffer=104857600 file=/oracle/SRDQ_20190124.dmp log=/oracle/SRDQ_20190124.log feedback=10000 owner=SRDQ
--exp MD_QZY/oracle@DB_110 direct=y recordlength=65535 buffer=104857600 file=/oracle/MD_QZY_20190124.dmp log=/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;
/

倒出部分
=============================================================

查看使用的表空间：

select distinct tablespace_name
from dba_tables
where owner in ('BOSS', 'MD_QZY', 'SMART', 'SRDQ','RIMS')
union
select distinct tablespace_name
from dba_indexes
where owner in ('BOSS', 'MD_QZY', 'SMART', 'SRDQ','RIMS')
union
select distinct tablespace_name
from dba_tab_partitions
where table_owner in ('BOSS', 'MD_QZY', 'SMART', 'SRDQ','RIMS')
UNION
select DISTINCT TABLESPACE_NAME
FROM DBA_IND_PARTITIONS
where INDEX_owner in ('BOSS', 'MD_QZY', 'SMART', 'SRDQ','RIMS')
UNION
select distinct tablespace_name
from dba_tab_subpartitions
where table_owner in ('BOSS', 'MD_QZY', 'SMART', 'SRDQ','RIMS')
UNION
select DISTINCT TABLESPACE_NAME
FROM DBA_IND_SUBPARTITIONS
where INDEX_owner in ('BOSS', 'MD_QZY', 'SMART', 'SRDQ','RIMS');

54个：

RETURN01_01
RIMS
SMART
SMART_DBAK1
SMART_DBAK2
SMART_DT1
SMART_DT2
SMART_DWMOB
SMART_DXBAO01
SMART_DXBAO02
SMART_IMDATA
SMART_INDEX
SMART_INDEX02
SMART_INDEX03
SMART_INDEX05
SMART_INDEX06
SMART_JINGDONG
SMART_JT1
SMART_MOBAK
SMART_MOBAK00
SMART_MOBAK02
SMART_MOBAK03
SMART_MOBAK04
SMART_MOBIBAK
SMART_MOQU
SMART_MTQ
SMART_NEWSYS01
SMART_NMSGID01
SMART_NWHITELIST
SMART_NWHITELIST_MS
SMART_OPINDEX
SMART_OPLOG01
SMART_OPLOG02
SMART_OPLOG03
SMART_OPLOG04
SMART_OPLOG05
SMART_OVERFLOW
SMART_PUSHRETURN
SMART_QUEUE
SMART_SHENJI
SMART_SNBLST
SMART_SRPINDEX
SMART_STSRRT
SMART_STSRRTINDEX
SMART_TJ01
SMART_TONGJI00
SMART_TONGJI01
SMART_TONGJI02
SMART_TONGJI03
SMART_ZHIFU
SMART_ZHIXINGLI
T_PROFIT_STATIS
T_PROFIT_STATIS_IDX

1、查看表空间
set lin 200 pages 1000
select * from (select tablespace_name,round(sum(bytes/1024/1024/1024),2) total_G,
round(sum(maxbytes/1024/1024/1024),2) max_extended_G,
round(sum((maxbytes-bytes)/1024/1024/1024),2) need_extend_G,
round(sum(bytes)/sum(maxbytes),4)*100 "MAXRate"
from dba_data_files group by tablespace_name
union
select tablespace_name,round(sum(bytes/1024/1024/1024),2) ,
round(sum(maxbytes/1024/1024/1024),2) ,
round(sum((maxbytes-bytes)/1024/1024/1024),2) ,
round(sum(bytes)/sum(maxbytes),4)*100
from dba_temp_files group by tablespace_name)
;

TABLESPACE_NAME TOTAL_G MAX_EXTENDED_G NEED_EXTEND_G MAXRate
------------------------------ ---------- -------------- ------------- ----------
UNDOTBS1 41.77 64 22.23 65.26
SMART_TONGJI00 40.19 64 23.81 62.8
SMART_INDEX06 33.11 64 30.89 51.73
SMART_MOBAK 14.02 32 17.98 43.81
SMART_INDEX 13.24 32 18.76 41.37
SMART_INDEX03 24.34 64 39.66 38.03
SMART_SHENJI 10.42 32 21.58 32.58
SMART 10.13 32 21.87 31.66
SMART_NWHITELIST 8.24 32 23.76 25.76
SMART_MOBAK02 15.97 64 48.03 24.95
SMART_MOBAK00 15.12 64 48.88 23.62
SMART_MOBAK03 21.99 96 74.01 22.91
SMART_MOBAK04 14.58 64 49.42 22.78
STPTBL 6.93 32 25.07 21.67
SMART_TONGJI01 13.48 64 50.52 21.06
SMART_INDEX02 5.49 32 26.51 17.15
SMART_SNBLST 2.99 32 29.01 9.34
SMART_STSRRT 2.66 32 29.34 8.3
SYSTEM 2.42 32 29.58 7.57
SMART_ZHIFU 4.51 64 59.49 7.04
SMART_DT2 1.81 32 30.19 5.65
SMART_OVERFLOW 1.44 32 30.56 4.5
SMART_QUEUE 1.26 32 30.74 3.94
SMART_DT1 1.22 32 30.78 3.8
T_PROFIT_STATIS 2 64 62 3.13
T_PROFIT_STATIS_IDX 2 64 62 3.13
SMART_OPLOG01 2.93 96 93.07 3.05
SMART_OPLOG03 2.93 96 93.07 3.05
SMART_OPLOG05 2.93 96 93.07 3.05
SMART_OPLOG06 2.93 96 93.07 3.05
SMART_OPLOG04 2.93 96 93.07 3.05
SMART_OPLOG02 2.93 96 93.07 3.05
SMART_TONGJI02 .62 32 31.38 1.92
SMART_MTQ .61 32 31.39 1.89
SMART_INDEX05 .57 32 31.43 1.77
SMART_NWHITELIST_MS .52 32 31.48 1.62
SMART_MOQU .42 32 31.58 1.3
SMART_DBAK1 .32 32 31.68 1.01
RETURN01_01 .24 32 31.76 .75
SMART_DBAK2 .2 32 31.8 .61
SMART_INDEX04 .2 32 31.8 .61
SMART_JINGDONG .39 64 63.61 .61
SMART_OPINDEX .59 96 95.41 .61
SMART_TJ01 .2 32 31.8 .61
SMART_TONGJI03 .2 32 31.8 .61
SMART_MOBAK01 .59 96 95.41 .61
SMART_DXBAO02 .2 32 31.8 .61
SMART_DXBAO01 .2 32 31.8 .61
SMART_JT1 .15 32 31.85 .46
SMART_SRPINDEX .14 32 31.86 .44
EXAMPLE .12 32 31.88 .37
SMART_IMDATA .1 32 31.9 .31
SMART_ZHIXINGLI .1 32 31.9 .31
SMART_NEWSYS01 .1 32 31.9 .31
SMART_DWMOB .05 32 31.95 .15
SMART_MOBIBAK .05 32 31.95 .15
SMART_NMSGID01 .05 32 31.95 .15
SMART_PUSHRETURN .05 32 31.95 .15
SMART_STSRRTINDEX .05 32 31.95 .15
SMART_SRNRINDEX .05 32 31.95 .15
XDB .04 32 31.96 .14
INDX .02 32 31.98 .08
USERS .02 32 31.98 .08
BOSS .02 32 31.98 .06
ODM .02 32 31.98 .06
CWMLITE .02 32 31.98 .06
DRSYS .02 32 31.98 .06
TOOLS .01 32 31.99 .03

68 rows selected.

查看99.206
sys@ZKMOBILE(192.168.99.206)>select file_name from dba_data_files union select file_name from dba_temp_files;

FILE_NAME
----------------------------------------------------------------------------------------------------
/oradata/ZKMOBILE/datafile/o1_mf_sysaux_g360gw16_.dbf
/oradata/ZKMOBILE/datafile/o1_mf_system_g360gs41_.dbf
/oradata/ZKMOBILE/datafile/o1_mf_temp_g360gxyr_.tmp
/oradata/ZKMOBILE/datafile/o1_mf_undotbs1_g360gxlg_.dbf
/oradata/ZKMOBILE/datafile/o1_mf_users_g360h21k_.dbf
/oradata/ZKMOBILE/datafile/temp02.dbf
/oradata/ZKMOBILE/datafile/temp03.dbf
/oradata/ZKMOBILE/datafile/undo02.dbf
/oradata/ZKMOBILE/datafile/undo03.dbf

9 rows selected.

sys@ZKMOBILE(192.168.99.206)>show parameter block

NAME TYPE VALUE
------------------------------------ ----------- ------------------------------
db_block_buffers integer 0
db_block_checking string FALSE
db_block_checksum string TYPICAL
db_block_size integer 16384
db_file_multiblock_read_count integer 64

删除用户：

drop user SMART cascade;
drop user BOSS cascade;
drop user DBSNMP cascade;
drop user SMART_CP cascade;
drop user MD_QZY cascade;
drop user SRDQ cascade;
drop user SMART_RO cascade;
drop user PERFSTAT cascade;
drop user MONITOR cascade;
drop user ZKDB cascade;
drop user rims cascade;

不浪费的表空间：

create tablespace RETURN01_01 datafile '/oradata/ZKMOBILE/datafile/RETURN01_0101.dbf' size 2g autoextend on next 1g;
create tablespace RIMS datafile '/oradata/ZKMOBILE/datafile/RIMS01.dbf' size 2g autoextend on next 1g;
create tablespace SMART datafile '/oradata/ZKMOBILE/datafile/SMART01.dbf' size 2g autoextend on next 1g;
create tablespace SMART_DBAK1 datafile '/oradata/ZKMOBILE/datafile/SMART_DBAK101.dbf' size 2g autoextend on next 1g;
create tablespace SMART_DBAK2 datafile '/oradata/ZKMOBILE/datafile/SMART_DBAK201.dbf' size 2g autoextend on next 1g;
create tablespace SMART_DT1 datafile '/oradata/ZKMOBILE/datafile/SMART_DT101.dbf' size 2g autoextend on next 1g;
create tablespace SMART_DT2 datafile '/oradata/ZKMOBILE/datafile/SMART_DT201.dbf' size 2g autoextend on next 1g;
create tablespace SMART_DWMOB datafile '/oradata/ZKMOBILE/datafile/SMART_DWMOB01.dbf' size 2g autoextend on next 1g;
create tablespace SMART_DXBAO01 datafile '/oradata/ZKMOBILE/datafile/SMART_DXBAO0101.dbf' size 2g autoextend on next 1g;
create tablespace SMART_DXBAO02 datafile '/oradata/ZKMOBILE/datafile/SMART_DXBAO0201.dbf' size 2g autoextend on next 1g;
create tablespace SMART_IMDATA datafile '/oradata/ZKMOBILE/datafile/SMART_IMDATA01.dbf' size 2g autoextend on next 1g;
create tablespace SMART_INDEX datafile '/oradata/ZKMOBILE/datafile/SMART_INDEX01.dbf' size 2g autoextend on next 1g;
create tablespace SMART_INDEX02 datafile '/oradata/ZKMOBILE/datafile/SMART_INDEX0201.dbf' size 2g autoextend on next 1g;
create tablespace SMART_INDEX03 datafile '/oradata/ZKMOBILE/datafile/SMART_INDEX0301.dbf' size 2g autoextend on next 1g;
create tablespace SMART_INDEX05 datafile '/oradata/ZKMOBILE/datafile/SMART_INDEX0501.dbf' size 2g autoextend on next 1g;
create tablespace SMART_INDEX06 datafile '/oradata/ZKMOBILE/datafile/SMART_INDEX0601.dbf' size 2g autoextend on next 1g;
create tablespace SMART_JINGDONG datafile '/oradata/ZKMOBILE/datafile/SMART_JINGDONG01.dbf' size 2g autoextend on next 1g;
create tablespace SMART_JT1 datafile '/oradata/ZKMOBILE/datafile/SMART_JT101.dbf' size 2g autoextend on next 1g;
create tablespace SMART_MOBAK datafile '/oradata/ZKMOBILE/datafile/SMART_MOBAK01.dbf' size 2g autoextend on next 1g;
create tablespace SMART_MOBAK00 datafile '/oradata/ZKMOBILE/datafile/SMART_MOBAK0001.dbf' size 2g autoextend on next 1g;
create tablespace SMART_MOBAK02 datafile '/oradata/ZKMOBILE/datafile/SMART_MOBAK0201.dbf' size 2g autoextend on next 1g;
create tablespace SMART_MOBAK03 datafile '/oradata/ZKMOBILE/datafile/SMART_MOBAK0301.dbf' size 2g autoextend on next 1g;
create tablespace SMART_MOBAK04 datafile '/oradata/ZKMOBILE/datafile/SMART_MOBAK0401.dbf' size 2g autoextend on next 1g;
create tablespace SMART_MOBIBAK datafile '/oradata/ZKMOBILE/datafile/SMART_MOBIBAK01.dbf' size 2g autoextend on next 1g;
create tablespace SMART_MOQU datafile '/oradata/ZKMOBILE/datafile/SMART_MOQU01.dbf' size 2g autoextend on next 1g;
create tablespace SMART_MTQ datafile '/oradata/ZKMOBILE/datafile/SMART_MTQ01.dbf' size 2g autoextend on next 1g;
create tablespace SMART_NEWSYS01 datafile '/oradata/ZKMOBILE/datafile/SMART_NEWSYS0101.dbf' size 2g autoextend on next 1g;
create tablespace SMART_NMSGID01 datafile '/oradata/ZKMOBILE/datafile/SMART_NMSGID0101.dbf' size 2g autoextend on next 1g;
create tablespace SMART_NWHITELIST datafile '/oradata/ZKMOBILE/datafile/SMART_NWHITELIST01.dbf' size 2g autoextend on next 1g;
create tablespace SMART_NWHITELIST_MS datafile '/oradata/ZKMOBILE/datafile/SMART_NWHITELIST_MS01.dbf' size 2g autoextend on next 1g;
create tablespace SMART_OPINDEX datafile '/oradata/ZKMOBILE/datafile/SMART_OPINDEX01.dbf' size 2g autoextend on next 1g;
create tablespace SMART_OPLOG01 datafile '/oradata/ZKMOBILE/datafile/SMART_OPLOG0101.dbf' size 2g autoextend on next 1g;
create tablespace SMART_OPLOG02 datafile '/oradata/ZKMOBILE/datafile/SMART_OPLOG0201.dbf' size 2g autoextend on next 1g;
create tablespace SMART_OPLOG03 datafile '/oradata/ZKMOBILE/datafile/SMART_OPLOG0301.dbf' size 2g autoextend on next 1g;
create tablespace SMART_OPLOG04 datafile '/oradata/ZKMOBILE/datafile/SMART_OPLOG0401.dbf' size 2g autoextend on next 1g;
create tablespace SMART_OPLOG05 datafile '/oradata/ZKMOBILE/datafile/SMART_OPLOG0501.dbf' size 2g autoextend on next 1g;
create tablespace SMART_OVERFLOW datafile '/oradata/ZKMOBILE/datafile/SMART_OVERFLOW01.dbf' size 2g autoextend on next 1g;
create tablespace SMART_PUSHRETURN datafile '/oradata/ZKMOBILE/datafile/SMART_PUSHRETURN01.dbf' size 2g autoextend on next 1g;
create tablespace SMART_QUEUE datafile '/oradata/ZKMOBILE/datafile/SMART_QUEUE01.dbf' size 2g autoextend on next 1g;
create tablespace SMART_SHENJI datafile '/oradata/ZKMOBILE/datafile/SMART_SHENJI01.dbf' size 2g autoextend on next 1g;
create tablespace SMART_SNBLST datafile '/oradata/ZKMOBILE/datafile/SMART_SNBLST01.dbf' size 2g autoextend on next 1g;
create tablespace SMART_SRPINDEX datafile '/oradata/ZKMOBILE/datafile/SMART_SRPINDEX01.dbf' size 2g autoextend on next 1g;
create tablespace SMART_STSRRT datafile '/oradata/ZKMOBILE/datafile/SMART_STSRRT01.dbf' size 2g autoextend on next 1g;
create tablespace SMART_STSRRTINDEX datafile '/oradata/ZKMOBILE/datafile/SMART_STSRRTINDEX01.dbf' size 2g autoextend on next 1g;
create tablespace SMART_TJ01 datafile '/oradata/ZKMOBILE/datafile/SMART_TJ0101.dbf' size 2g autoextend on next 1g;
create tablespace SMART_TONGJI00 datafile '/oradata/ZKMOBILE/datafile/SMART_TONGJI0001.dbf' size 2g autoextend on next 1g;
create tablespace SMART_TONGJI01 datafile '/oradata/ZKMOBILE/datafile/SMART_TONGJI0101.dbf' size 2g autoextend on next 1g;
create tablespace SMART_TONGJI02 datafile '/oradata/ZKMOBILE/datafile/SMART_TONGJI0201.dbf' size 2g autoextend on next 1g;
create tablespace SMART_TONGJI03 datafile '/oradata/ZKMOBILE/datafile/SMART_TONGJI0301.dbf' size 2g autoextend on next 1g;
create tablespace SMART_ZHIFU datafile '/oradata/ZKMOBILE/datafile/SMART_ZHIFU01.dbf' size 2g autoextend on next 1g;
create tablespace SMART_ZHIXINGLI datafile '/oradata/ZKMOBILE/datafile/SMART_ZHIXINGLI01.dbf' size 2g autoextend on next 1g;
create tablespace T_PROFIT_STATIS datafile '/oradata/ZKMOBILE/datafile/T_PROFIT_STATIS01.dbf' size 2g autoextend on next 1g;
create tablespace T_PROFIT_STATIS_IDX datafile '/oradata/ZKMOBILE/datafile/T_PROFIT_STATIS_IDX01.dbf' size 2g autoextend on next 1g;

删除表空间：

--drop tablespace DRSYS including contents and datafiles CASCADE CONSTRAINTS;
drop tablespace RETURN01_01 including contents and datafiles;
drop tablespace RIMS including contents and datafiles;
drop tablespace SMART including contents and datafiles;
drop tablespace SMART_DBAK1 including contents and datafiles;
drop tablespace SMART_DBAK2 including contents and datafiles;
drop tablespace SMART_DT1 including contents and datafiles;
drop tablespace SMART_DT2 including contents and datafiles;
drop tablespace SMART_DWMOB including contents and datafiles;
drop tablespace SMART_DXBAO01 including contents and datafiles;
drop tablespace SMART_DXBAO02 including contents and datafiles;
drop tablespace SMART_IMDATA including contents and datafiles;
drop tablespace SMART_INDEX including contents and datafiles;
drop tablespace SMART_INDEX02 including contents and datafiles;
drop tablespace SMART_INDEX03 including contents and datafiles;
drop tablespace SMART_INDEX05 including contents and datafiles;
drop tablespace SMART_INDEX06 including contents and datafiles;
drop tablespace SMART_JINGDONG including contents and datafiles;
drop tablespace SMART_JT1 including contents and datafiles;
drop tablespace SMART_MOBAK including contents and datafiles;
drop tablespace SMART_MOBAK00 including contents and datafiles;
drop tablespace SMART_MOBAK02 including contents and datafiles;
drop tablespace SMART_MOBAK03 including contents and datafiles;
drop tablespace SMART_MOBAK04 including contents and datafiles;
drop tablespace SMART_MOBIBAK including contents and datafiles;
drop tablespace SMART_MOQU including contents and datafiles;
drop tablespace SMART_MTQ including contents and datafiles;
drop tablespace SMART_NEWSYS01 including contents and datafiles;
drop tablespace SMART_NMSGID01 including contents and datafiles;
drop tablespace SMART_NWHITELIST including contents and datafiles;
drop tablespace SMART_NWHITELIST_MS including contents and datafiles;
drop tablespace SMART_OPINDEX including contents and datafiles;
drop tablespace SMART_OPLOG01 including contents and datafiles;
drop tablespace SMART_OPLOG02 including contents and datafiles;
drop tablespace SMART_OPLOG03 including contents and datafiles;
drop tablespace SMART_OPLOG04 including contents and datafiles;
drop tablespace SMART_OPLOG05 including contents and datafiles;
drop tablespace SMART_OVERFLOW including contents and datafiles;
drop tablespace SMART_PUSHRETURN including contents and datafiles;
drop tablespace SMART_QUEUE including contents and datafiles;
drop tablespace SMART_SHENJI including contents and datafiles;
drop tablespace SMART_SNBLST including contents and datafiles;
drop tablespace SMART_SRPINDEX including contents and datafiles;
drop tablespace SMART_STSRRT including contents and datafiles;
drop tablespace SMART_STSRRTINDEX including contents and datafiles;
drop tablespace SMART_TJ01 including contents and datafiles;
drop tablespace SMART_TONGJI00 including contents and datafiles;
drop tablespace SMART_TONGJI01 including contents and datafiles;
drop tablespace SMART_TONGJI02 including contents and datafiles;
drop tablespace SMART_TONGJI03 including contents and datafiles;
drop tablespace SMART_ZHIFU including contents and datafiles;
drop tablespace SMART_ZHIXINGLI including contents and datafiles;
drop tablespace T_PROFIT_STATIS including contents and datafiles;
drop tablespace T_PROFIT_STATIS_IDX including contents and datafiles;

2、检查无效对象
--统计失效的对象：
select owner, object_type,status, count(*)
from dba_objects
where status='INVALID'
group by owner, object_type, status
order by owner, object_type;

OWNER OBJECT_TYPE STATUS COUNT(*)
------------------------------ ------------------ ------- ----------
SMART PROCEDURE INVALID 1
SMART TRIGGER INVALID 1
SMART VIEW INVALID 72
ZKDB PROCEDURE INVALID 2
ZKDB VIEW INVALID 55

--查看具体失效对象
col owner for a20;
col object_name for a32;
col object_type for a16
col status for a8
select owner, object_name, object_type, status
from dba_objects
where status='INVALID'
order by 1, 2,3;

--执行脚本编译数据库失效对象。
@$ORACLE_HOME/rdbms/admin/utlrp.sql

查看总的触发器数：
select owner,
count(*)
from dba_triggers
where owner in (select username
from dba_users
where account_status = 'OPEN'
and username not in ('SYS', 'SYSTEM'))
group by owner;

OWNER COUNT(*)
------------------------------ ----------
SMART 212

select owner,
trigger_name,
trigger_type,
triggering_event,
table_owner,
base_object_type,
table_name
from dba_triggers
where owner in (select username
from dba_users
where account_status = 'OPEN'
and username not in ('SYS', 'SYSTEM'));

select sequence_owner, count(*)
from dba_sequences
where sequence_owner in
(select username
from dba_users
where account_status = 'OPEN'
and username not in ('SYS', 'SYSTEM'))
group by sequence_owner;

查看总的序列数：

SEQUENCE_OWNER COUNT(*)
------------------------------ ----------
PERFSTAT 1
SMART 277
ZKDB 80

查看总的function 包：

3、EXP 按用户导出

用户表空间
ZJJJ TBS_YW_DATA
set lin 200 pages 100
select username,account_status,default_tablespace,temporary_tablespace from dba_users where account_status='OPEN';

USERNAME ACCOUNT_STATUS DEFAULT_TABLESPACE TEMPORARY_TABLESPACE
------------------------------ -------------------------------- ------------------------------ ------------------------------
SYS OPEN SYSTEM TEMP
SYSTEM OPEN SYSTEM TEMP
DBSNMP OPEN SYSTEM TEMP
SMART_CP OPEN SMART TEMP
MD_QZY OPEN SMART TEMP
SRDQ OPEN SYSTEM TEMP
SMART_RO OPEN SMART TEMP
PERFSTAT OPEN STPTBL TEMP
MONITOR OPEN SYSTEM TEMP
BOSS OPEN BOSS TEMP
ZKDB OPEN ZKDB_LS TEMP
SMART OPEN SMART TEMP

已选择24行。

select * from dba_sys_privs where grantee in ('SMART','BOSS','DBSNMP','SMART_CP','MD_QZY','SRDQ','SMART_RO','PERFSTAT','MONITOR','ZKDB','MD_Q') order by 1;

GRANTEE PRIVILEGE ADM
------------------------------ ---------------------------------------- ---
BOSS CREATE SESSION NO
BOSS UNLIMITED TABLESPACE NO
DBSNMP SELECT ANY DICTIONARY NO
MONITOR UNLIMITED TABLESPACE NO
PERFSTAT ALTER SESSION NO
PERFSTAT CREATE PROCEDURE NO
PERFSTAT CREATE PUBLIC SYNONYM NO
PERFSTAT CREATE SEQUENCE NO
PERFSTAT CREATE SESSION NO
PERFSTAT CREATE TABLE NO
PERFSTAT DROP PUBLIC SYNONYM NO
SMART UNLIMITED TABLESPACE NO
SMART_CP CREATE SESSION NO
SMART_CP CREATE SNAPSHOT NO
SMART_CP CREATE TABLE NO
SMART_CP ON COMMIT REFRESH NO
SMART_CP UNLIMITED TABLESPACE NO
SMART_RO UNLIMITED TABLESPACE NO
SRDQ UNLIMITED TABLESPACE NO

19 rows selected.

已选择8行。

select * from dba_role_privs where grantee in ('SMART','BOSS','DBSNMP','SMART_CP','MD_QZY','SRDQ','SMART_RO','PERFSTAT','MONITOR','ZKDB','MD_Q')order by 1;

GRANTEE GRANTED_ROLE ADM DEF
------------------------------ ------------------------------ --- ---
BOSS CONNECT NO YES
BOSS RESOURCE NO YES
DBSNMP CONNECT NO YES
MD_QZY CONNECT NO YES
MD_QZY MD_Q NO YES
MONITOR CONNECT NO YES
MONITOR RESOURCE NO YES
MONITOR SELECT_CATALOG_ROLE NO YES
PERFSTAT SELECT_CATALOG_ROLE NO YES
SMART CONNECT NO YES
SMART DBA NO YES
SMART MD_Q YES YES
SMART RESOURCE NO YES
SMART_CP CONNECT NO YES
SMART_RO CONNECT NO YES
SMART_RO RESOURCE NO YES
SRDQ CONNECT NO YES
SRDQ RESOURCE NO YES
ZKDB CONNECT NO YES
ZKDB DBA NO YES
ZKDB RESOURCE NO YES

21 rows selected.

新建用户：

create user SMART identified by lzxMZD135468;
create user BOSS identified by BOSS;
create user DBSNMP identified by csmd2018;
create user SMART_CP identified by oracle;
create user MD_QZY identified by oracle;
create user SRDQ identified by oracle;
create user SMART_RO identified by oracle;
create user PERFSTAT identified by oracle;
create user MONITOR identified by oracle;
create user ZKDB identified by oracle;
create user rims identified by rims2019csmd;

alter user DBSNMP identified by values 'A0103B9F133B2E22';
alter user SMART_CP identified by values '88C0958CC2570C55';
alter user MD_QZY identified by values 'E7AD6E6562822EBE';
alter user SRDQ identified by values '6F1E875DA235F4F2';
alter user SMART_RO identified by values '1DEE3859FDA8CA41';
alter user PERFSTAT identified by values 'AC98877DE1297365';
alter user MONITOR identified by values '9AFC7F2344F99FF6';
alter user BOSS identified by values '2127DD06CE51E181';
alter user ZKDB identified by values '965E06A46BC6B0AC';
alter user SMART identified by values '5586BF85377BE4F2';

dba用户执行：

grant UNLIMITED TABLESPACE to smart;
grant CONNECT,DBA,MD_Q,RESOURCE to smart;
grant dba to SMART;
grant md_q to SMART with admin option;
grant CONNECT,RESOURCE to boss;
grant CREATE SESSION,UNLIMITED TABLESPACE to boss;
grant CONNECT,resource to dbsnmp;
grant SELECT ANY DICTIONARY to dbsnmp;
grant CREATE SESSION,CREATE SNAPSHOT,CREATE TABLE,ON COMMIT REFRESH,UNLIMITED TABLESPACE to SMART_CP;
grant CONNECT to SMART_CP;
grant CONNECT,MD_Q to MD_QZY;
grant UNLIMITED TABLESPACE to SRDQ;
grant CONNECT,RESOURCE to SRDQ;
grant UNLIMITED TABLESPACE to SMART_RO;
grant CONNECT,RESOURCE to SMART_RO;
grant ALTER SESSION,CREATE PROCEDURE,CREATE PUBLIC SYNONYM,CREATE SEQUENCE,CREATE SESSION,CREATE TABLE,DROP PUBLIC SYNONYM to PERFSTAT;
grant SELECT_CATALOG_ROLE to PERFSTAT;
grant UNLIMITED TABLESPACE to MONITOR;
grant CONNECT,RESOURCE,SELECT_CATALOG_ROLE to MONITOR;
grant CONNECT,RESOURCE,DBA to ZKDB;

grant connect to RIMS;
grant resource to RIMS;
grant create table to RIMS;
grant unlimited tablespace to RIMS;

-- smart Create the role
create role MD_Q;
-- Grant/Revoke object privileges

smart用户执行(导入表后在授权)：
grant select on S_MOBACKUPQUEUE to MD_Q;
grant select on S_MOQUEUE to MD_Q;
grant select on S_OPERATELOG_TONGJI to MD_Q;
grant select on S_REALINFO to MD_Q;
grant select on S_REGISTRYINFO to MD_Q;
grant select on S_T_RETURN_REPORT_TJ to MD_Q;
grant select on S_T_SEND_REPORT_TJ to MD_Q;
grant md_q to MD_QZY;
grant md_q to SMART with admin option;

--revoke DBA from SMART ;
--revoke DBA from BOSS
--revoke DBA from DBSNMP ;
--revoke DBA from SMART_CP;
--revoke DBA from MD_QZY ;
--revoke DBA from SRDQ ;
--revoke DBA from SMART_RO ;
--revoke DBA from PERFSTAT;
--revoke DBA from MONITOR ;
revoke DBA from ZKDB ;

设置字符集（expdp不用设置）

查看字符集：

SQL>select userenv('language') from dual;

export nls_lang="SIMPLIFIED CHINESE_CHINA.ZHS16GBK"

export NLS_LANG=AMERICAN_AMERICA.ZHS16GBK

4、检查对象下表的具体行数

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' 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;
/

=============================================================

*********************************

倒入部分
=============================================================

2、IMP按用户导入
设置字符集（expdp不用设置）

查看字符集：

export nls_lang="SIMPLIFIED CHINESE_CHINA.ZHS16GBK"

nohup imp smart/密码135468 file=/oradata/expdp/smart_20190329.dmp log=/oradata/expdp/smart_20190329.log feedback=100000 buffer=524288000 fromuser=smart touser=smart &

imp rims/密码 file=/oradata/expdp/rims_20190308.dmp log=/oradata/expdp/rims_20190308.log feedback=100000 buffer=524288000 fromuser=rims touser=rims

https://www.oraexcel.com/oracle-11gR1-ORA-25001

ORA-25001: cannot create this trigger type on this type of view

数据库： 11g第1版
错误代码： ORA-25001
描述：无法在此类视图上创建此触发器类型
原因：可以在任何不是版本视图的视图上创建INSTEAD OF触发器，而只能创建BEFORE和AFTER触发器在编辑视图上。
操作：将触发器类型更改为INSTEAD OF或更改您尝试创建DML触发器的视图。

数据库： 10g第1版
错误代码： ORA-25001
描述：无法在视图上创建此触发器类型
原因：只能在视图上创建INSTEAD OF触发器。
操作：将触发器类型更改为INSTEAD OF。

数据库： 10g第2版
错误代码： ORA-25001
描述：无法在视图上创建此触发器类型
原因：只能在视图上创建INSTEAD OF触发器。
操作：将触发器类型更改为INSTEAD OF。

数据库： 11g第2版
错误代码： ORA-25001
描述：无法在此类视图上创建此触发器类型
原因：可以在任何不是版本视图的视图上创建INSTEAD OF触发器，而只能创建BEFORE和AFTER触发器在编辑视图上。
操作：将触发器类型更改为INSTEAD OF或更改您尝试创建DML触发器的视图。

3、检查对象下表的具体行数

set serveroutput on size 1000000
set pages 50000
spool /oradata/xinku-smart.txt

DECLARE
v_cnt number;
BEGIN
FOR rec in (select 'SMART.' || TABLE_NAME AS tanme from dba_tables where owner='SMART' 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;
/

set serveroutput on size 1000000
set pages 50000
spool /oradata/xinku-BOSS.txt

DECLARE
v_cnt number;
BEGIN
FOR rec in (select 'BOSS.' || TABLE_NAME AS tanme from dba_tables where owner='BOSS' 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;
/

4、检查无效对象
--统计失效的对象：
select owner, object_type,status, count(*)
from dba_objects
where status='INVALID'
group by owner, object_type, status
order by owner, object_type

--执行脚本编译数据库失效对象。

@$ORACLE_HOME/rdbms/admin/utlrp.sql

5、收集对象统计信息

--查看表统计信息是否过期：
exec dbms_stats.flush_database_monitoring_info;

select owner, table_name,object_type,num_rows,sample_size,trunc(sample_size / num_rows * 100) estimate_percent,stale_stats, last_analyzed
from dba_tab_statistics
where
--table_name in upper('t1') and
owner = upper('SMART')
and (stale_stats = 'YES' or last_analyzed is null);

SELECT Table_Name,Num_Rows,Blocks,Empty_Blocks,Avg_Space,Chain_Cnt,Avg_Row_Len,Sample_Size,Last_Analyzed
FROM Dba_Tables WHERE owner = upper('SMART');

--查看表的直方图
select a.column_name,
b.num_rows,
a.num_distinct Cardinality,
round(a.num_distinct / b.num_rows * 100, 2) selectivity,
a.histogram,
a.num_buckets
from dba_tab_col_statistics a, dba_tables b
where a.owner = b.owner
and a.table_name = b.table_name
and a.owner = upper('SMART');
--and a.table_name = upper('t1');

--对某一个schma收集统计信息

BEGIN
dbms_stats.gather_schema_stats(ownname=> 'SMART',
estimate_percent => 100,
method_opt => 'for all columns size repeat',
no_invalidate => FALSE,
degree => 8,
cascade => TRUE);
END;
/

=============================================================

建立db_link:

drop database link DB_LIN_10.SJZK.COM.CN;
-- Create database link
create database link DB_LIN_10.SJZK.COM.CN
connect to SMART identified by 密码
using 'DB_110_STD';

drop database link DB_LIN_100.SJZK.COM.CN;
-- Create database link
create database link DB_LIN_100.SJZK.COM.CN
connect to SMART identified by 密码
using 'zk_lin_100';

drop database link DB_LIN_115.SJZK.COM.CN;
-- Create database link
create database link DB_LIN_115.SJZK.COM.CN
connect to SMART identified by 密码
using 'ZK_115';

posted @ 2019-10-29 23:12 翰墨文海阅读(2200) 评论(0) 编辑收藏举报

刷新页面返回顶部

翰墨文海

QV1319820057,转载望注明出处……

9i oracle数据库迁移到11G（exp）

公告