set pagesize 1000;

col tablespace_name for a14;
select d.tablespace_name,space "sum_space(m)",blocks sum_blocks,space-nvl(free_space,0) "used_space(m)",
round((1-nvl(free_space,0)/space)*100,2) "used_rate(%)",free_space "free_space(m)"                     
from                                                                                                   
(select tablespace_name,round(sum(bytes)/(1024*1024),2) space,sum(blocks) blocks                       
from dba_data_files                                                                                    
group by tablespace_name) d,                                                                           
(select tablespace_name,round(sum(bytes)/(1024*1024),2) free_space                                     
from dba_free_space                                                                                    
group by tablespace_name) f                                                                            
where d.tablespace_name = f.tablespace_name(+) union all                                               
select d.tablespace_name,space "sum_space(m)",blocks sum_blocks,                                       
used_space "used_space(m)",round(nvl(used_space,0)/space*100,2) "used_rate(%)",                        
nvl(free_space,0) "free_space(m)"                                                                      
from                                                                                                   
(select tablespace_name,round(sum(bytes)/(1024*1024),2) space,sum(blocks) blocks                       
from dba_temp_files                                                                                    
group by tablespace_name) d,                                                                           
(select tablespace_name,round(sum(bytes_used)/(1024*1024),2) used_space,                               
round(sum(bytes_free)/(1024*1024),2) free_space                                                        
from v$temp_space_header                                                                               
group by tablespace_name) f                                                                            
where d.tablespace_name = f.tablespace_name(+);
 ----------------------------------------------------------------------------------

SELECT a.tablespace_name, a.BYTES total, a.bytes - nvl(b.bytes, 0) free

  FROM (SELECT   tablespace_name, SUM (bytes) bytes FROM dba_temp_files GROUP BY tablespace_name) a,
       (SELECT   tablespace_name, SUM (bytes_cached) bytes FROM v$temp_extent_pool GROUP BY tablespace_name) b
WHERE a.tablespace_name = b.tablespace_name(+);

select f.file_name,f.tablespace_name,f.bytes/1024/1024 from dba_data_files f order by f.tablespace_name,f.file_name;

-----------------------------------------------------------------------------------

select l.GROUP#,l.THREAD#,l.BYTES/1024/1024 from v$log l;
select value from v$nls_parameters where parameter= 'NLS_CHARACTERSET';

select sum(bytes/1024/1024) from dba_data_files;

select t.file_name,t.tablespace_name,t.bytes/1024/1024 from dba_temp_files t;

select value from V$PARAMETER where name='db_block_size'；

查看有哪些用户有DBA权限

select * from dba_role_privs where granted_role='DBA';

无效对象

select object_name,object_type,status from dba_objects where status='INVALID' ；

 

select file_name,bytes/1024/1024 from dba_temp_files;

select name from gv$tablespace；

 

SELECT (1 - (SUM(DECODE(NAME, 'physical reads', VALUE, 0)) /

(SUM(DECODE(NAME, 'db block gets', VALUE, 0)) +
SUM(DECODE(NAME, 'consistent gets', VALUE, 0))))) * 100
"缓冲区命中率"
FROM V$SYSSTAT;

 

SELECT (1 - (SUM(GETMISSES) / SUM(GETS))) * 100 "数据字典命中率"

FROM V$ROWCACHE;

 

SELECT SUM(PINS) / (SUM(PINS) + SUM(RELOADS)) * 100 "库缓存命中率"

FROM V$LIBRARYCACHE;

 

select a.value "磁盘排序",

b.value "内存排序",
round((100 * b.value) /decode((a.value + b.value), 0, 1, (a.value + b.value)),2) "内存排序百分比"
from v$sysstat a, v$sysstat b
where a.name = 'sorts (disk)'
and b.name = 'sorts (memory)';

 

空闲数据缓冲区比例

SELECT SUM(DECODE(STATUS,'AVAILABLE',VAL,0)) "AVAILABLE",SUM(DECODE(STATUS,'BEING USED',VAL,0)) "BEING USED",
SUM(DECODE(STATUS,'AVAILABLE',VAL,0))/(SUM(DECODE(STATUS,'AVAILABLE',VAL,0))+SUM(DECODE(STATUS,'BEING USED',VAL,0)))*100||'%' "AVAILABLE PERCENT"
FROM(SELECT DECODE(STATE,0,'FREE',1,DECODE(LRBA_SEQ, 0, 'AVAILABLE', 'BEING USED'),3,'BEING USED',STATE) "STATUS",
COUNT(*) VAL
FROM X$BH
GROUP BY DECODE(STATE,0,'FREE',1,DECODE(LRBA_SEQ, 0, 'AVAILABLE', 'BEING USED'),3,'BEING USED',STATE));

重做
SELECT name, gets, misses, immediate_gets, immediate_misses,
100 - Decode(gets,0,0,misses/(gets+misses))*100 ratio1,
100 - Decode(immediate_gets+immediate_misses,0,0,immediate_misses/(immediate_gets+immediate_misses))*100 ratio2
FROM v$latch WHERE name IN ('redo allocation', 'redo copy');