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

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=http://www.likecs.com/oradata/expdp/smart_20190329.dmp log=http://www.likecs.com/oradata/expdp/smart_20190329.log feedback=100000 buffer=524288000 fromuser=smart touser=smart &

imp rims/rims2019csmd file=http://www.likecs.com/oradata/expdp/rims_20190308.dmp log=http://www.likecs.com/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: