--表空间使用率
select df.tablespace_name,
round(df.bytes / (1024 * 1024), 2) 总空间 ,
round(sum(fs.bytes) / (1024 * 1024), 2) 剩余空间 ,
round(sum(fs.bytes) * 100 / df.bytes) 剩余百分比,
round((df.bytes - sum(fs.bytes)) * 100 / df.bytes) 使用百分比
from dba_free_space fs,
(select tablespace_name, sum(bytes) bytes
from dba_data_files
group by tablespace_name) df
where fs.tablespace_name = df.tablespace_name
group by df.tablespace_name, df.bytes
order by tablespace_name;
--表所占空间大小
select OWNER, t.segment_name,t.tablespace_name, t.segment_type, sum(t.bytes / 1024 / 1024) mmm
from dba_segments t
where t.owner = 'TEST'
and t.segment_name like '%TEST%'
AND T.tablespace_name='TEST'
and t.segment_type in ('TABLE','TABLE PARTITION')
group by OWNER, t.segment_name,t.tablespace_name, t.segment_type
--HAVING sum(t.bytes / 1024 / 1024)>100
order by mmm desc;
--查看分区表分区大小
select OWNER,t.segment_name, t.partition_name,t.tablespace_name, t.segment_type, sum(t.bytes / 1024 / 1024) mmm
from dba_segments t
where t.owner = 'TEST'
-- and t.segment_name like '%TEST%'
-- AND T.tablespace_name='TEST'
and t.segment_type in ('TABLE','TABLE PARTITION')
group by OWNER,t.segment_name ,t.partition_name,t.tablespace_name, t.segment_type
--HAVING sum(t.bytes / 1024 / 1024)>100
order by mmm desc;
--索引及占空间大小
select t.OWNER, t.segment_name,b.table_name,c.column_name,t.tablespace_name, t.segment_type, sum(t.bytes / 1024 / 1024) mmm
from dba_segments t,dba_indexes b ,Dba_Ind_Columns c
where t.owner = 'TEST'
-- and t.segment_name like '%TEST%'
AND T.tablespace_name='TEST'
and t.segment_type not in ('TABLE','TABLE PARTITION')
and t.segment_name=b.index_name
and b.index_name =c.index_name
group by t.OWNER, t.segment_name,b.table_name,c.column_name,t.tablespace_name, t.segment_type
-- HAVING sum(t.bytes / 1024 / 1024)>100
order by mmm desc;
--查看阻塞
select c.username,a.sid,c.serial#,' is blocking ',d.username,b.sid,d.serial#
from
(select sid,id1,id2 from v$lock where block =1) a,
(select sid,id1,id2 from v$lock where request > 0) b,
(select sid,serial#,username from gv$session ) c,
(select sid,serial#,username from gv$session ) d
where a.id1=b.id1
and a.id2=b.id2
and a.sid=c.sid
and b.sid=d.sid;
--查看oracle锁表
select distinct
a.object_name 被锁表名,
b.ORACLE_USERNAME 数据库用户名,
b.OS_USER_NAME 操作系统用户名,
b.LOCKED_MODE 锁机制,
d.TYPE,
c.SID,
c.SERIAL#,
c.MACHINE 机器,
c.PROGRAM 应用程序,
to_char(c.LOGON_TIME,'yyyymmddhh24miss') 登陆时间,
c.COMMAND 命令,
c.LOCKWAIT 锁等待,
c.SQL_ADDRESS sql语句,
d.CTIME 被锁时间
from dba_objects a, v$locked_object b, v$session c,v$lock d
where a.object_id = b.OBJECT_ID
and b.SESSION_ID= c.SID
and c.SID=d.SID
and d.CTIME>3;
--查看触发器和索引是否有失效
select * from dba_indexes a where a.owner='TEST' and a.status<>'VALID';
select * from dba_triggers a where a.owner='TEST' and a.status<>'ENABLED';