这个是之前生产上打算迁移的文档, 后面离职了没有在停机迁移, 但是测试过几次没有问题, 其中需要把 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/lzxMZD135468 file=/oradata/expdp/smart_20190329.dmp log=/oradata/expdp/smart_20190329.log feedback=100000 buffer=524288000 fromuser=smart touser=smart &
- imp rims/rims2019csmd 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
-- 查看具体失效对象
- 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
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 LZXMZD135468
- 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 LZXMZD135468
- 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 LZXMZD135468
- using 'ZK_115';
- drop database link DB_LIN_120.SJZK.COM.CN;
- -- Create database link
- create database link DB_LIN_120.SJZK.COM.CN
- connect to SMART identified by LZXMZD135468
- using 'ZK_120';
- drop database link DB_LIN_61.SJZK.COM.CN;
- -- Create database link
- create database link DB_LIN_61.SJZK.COM.CN
- connect to SMART identified by LZXMZD135468
- using 'ZK_61';
- drop database link DB_LIN_75.SJZK.COM.CN;
- -- Create database link
- create database link DB_LIN_75.SJZK.COM.CN
- connect to SMART identified by lzxMZD135468
- using 'ZK_75';
- drop database link DB_LIN_DB166.SJZK.COM.CN;
- -- Create database link
- create database link DB_LIN_DB166.SJZK.COM.CN
- connect to SMART identified by lzxMZD135468
- using 'DB166';
- -- Drop existing database link
- drop database link DB_LIN_DW02.SJZK.COM.CN;
- -- Create database link
- create database link DB_LIN_DW02.SJZK.COM.CN
- connect to SMART identified by lzxMZD135468
- using 'DW02';
- -- Drop existing database link
- drop database link DB_WIN_99.SJZK.COM.CN;
- -- Create database link
- create database link DB_WIN_99.SJZK.COM.CN
- connect to SMART identified by LZXMZD135468
- using 'zk_win_99';
来源: https://www.cnblogs.com/hmwh/p/11762268.html