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;