UNDO表空间活动空间比例，redo log切换频率，分区表是否存在全局索引

-----UNDO表空间活动空间比例
select a.tablespace_name,
used_undo,
total_undo,
trunc(used_undo / total_undo * 100, 2) used_rat
from (select nvl(sum(bytes / 1024 / 1024), 0) used_undo, tablespace_name
from dba_undo_extents
where status = ‘ACTIVE’
group by tablespace_name) a,
(select tablespace_name, sum(bytes / 1024 / 1024) total_undo
from dba_data_files
where tablespace_name in
(select value
from v$spparameter where name = 'undo_tablespace' and (sid = (select instance_name from v$instance) or
sid = ‘*’))
group by tablespace_name) b
where a.tablespace_name = b.tablespace_name;

–检查redo log切换频率
select a.thread#,
row_number() over(partition by a.thread# order by a.thread#) num,
to_char(b.first_time, ‘yyyy-mm-dd hh24:mi:ss’) start_time,
to_char(a.first_time, ‘yyyy-mm-dd hh24:mi:ss’) end_time,
round(((a.first_time - b.first_time) * 24) * 60, 2) minutes
from v$log_history a, v$log_history b
where a.first_change# = b.next_change#
and a.thread# = b.thread#
and a.first_time > sysdate - 3
order by a.thread#, a.stamp asc;

----检查分区表是否存在全局索引
select a.owner, a.table_name, a.index_name, a.partitioned, a.status
from dba_indexes a, dba_part_tables b
where a.table_name = b.table_name
and a.owner = b.owner
and a.owner not in (‘SYS’,
‘SYSTEM’,
‘MDSYS’,
‘SYSMAN’,
‘MGMT_VIEW’,
‘ANONYMOUS’,
‘FLOWS_FILES’,
‘WKPROXY’,
‘WKSYS’,
‘FLOWS_030000’,
‘OLAPSYS’,
‘APEX_PUBLIC_USER’)
and a.partitioned <> ‘YES’;