oracel 常用脚本

SELECT  t.TABLESPACE_NAME,t.segment_name, t.segment_type, sum(t.bytes / 1024 / 1024) "占用空间(M)"
from dba_segments t
where t.segment_type='TABLE' 
group by t.TABLESPACE_NAME,t.segment_name, t.segment_type
HAVING sum(t.bytes / 1024 / 1024)>500;

--查看某个表空间下的索引
SELECT INDEX_NAME,INDEX_TYPE FROM dba_INDEXES WHERE TABLESPACE_NAME='?';

--查看表大小
SELECT 
table_name
,num_rows
,cast( num_rows * avg_row_len/1024/1024 as int) as sizeMB
from dba_tables where tablespace_name='tableName'
order by sizeMB desc,num_rows desc;

--查看表索引
select 
index_name 
,column_name
,descend
from dba_ind_columns where table_name = '?';

--慢SQL
select *
 from (select 
        sa.SQL_FULLTEXT,
        sa.EXECUTIONS "执行次数",
        round(sa.ELAPSED_TIME / 1000000, 2) "总执行时间",
        round(sa.ELAPSED_TIME / 1000000 / sa.EXECUTIONS, 2) "平均执行时间",
        sa.LAST_ACTIVE_TIME,
        --sa.COMMAND_TYPE,
        --sa.PARSING_USER_ID "用户ID",
        u.username "用户名"
        --,sa.HASH_VALUE
     from v$sqlarea sa
     left join all_users u
      on sa.PARSING_USER_ID = u.user_id
     where sa.EXECUTIONS > 0
        and u.username not in ('SYS','EXFSYS')
        and round(sa.ELAPSED_TIME / 1000000 / sa.EXECUTIONS, 2)>3
     order by (sa.ELAPSED_TIME / sa.EXECUTIONS) desc)
 where rownum <= 20;

--临时表空间占用情况
SELECT temp_used.tablespace_name, total - used as "Free", total as "Total", round(nvl(total - used, 0) * 100 / total, 3) "Free percent"
  FROM (SELECT tablespace_name, SUM(bytes_used)/1024/1024 used FROM V$TEMP_SPACE_HEADER GROUP BY tablespace_name) temp_used,
       (SELECT tablespace_name, SUM(bytes)/1024/1024 total FROM dba_temp_files GROUP BY tablespace_name) temp_total
WHERE temp_used.tablespace_name = temp_total.tablespace_name;

select * from database_properties where property_name='DEFAULT_TEMP_TABLESPACE';
select tablespace_name,file_name,bytes/1024/1024 file_size,autoextensible from dba_temp_files;