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

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;