oracle中非常有用的查询语句

来源：互联网发布：淘宝女装一件代发厂家编辑：程序博客网时间：2024/05/01 02:35

--表空间使用率
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';

0 0