oracle常用sql语句

--查询表空间使用情况

set line 132

set wrap off

select t.*

from (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 --if have tempfile

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(%)",

SPACE - USED_SPACE "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,

ROUND(SUM(BLOCKS * 8192) / (1024 * 1024), 2) USED_SPACE

FROM V$SORT_USAGE

GROUP BY TABLESPACE) F

WHERE D.TABLESPACE_NAME = F.TABLESPACE(+)) t
order by "USED_RATE(%)" desc;

观察undo表空间的变化情况
------undospace usage-------

SELECT DISTINCT STATUS, SUM(BYTES)/1024/1024, COUNT(*) FROM DBA_UNDO_EXTENTS WHERE tablespace_name = 'UNDOTBS1' GROUP BY STATUS;

--快速查找锁并kill进程

SELECT a.sid,d.spid,e.event,C.SQL_TEXT,B.USERNAME,B.OSUSER,B.MACHINE,B.PROGRAM
FROM V$SESS_IO A,V$SESSION B,V$SQL C,V$PROCESS D,v$session_wait e
WHERE A.SID=B.SID AND B.SQL_HASH_VALUE=C.HASH_VALUE and B.sid=E.sid
AND B.PADDR=D.ADDR and A.sid in(select sid from v$Lock where block=1);

Kill -9 spid这列即可

SELECT d.spid,a.sid,e.event,C.SQL_TEXT,B.USERNAME,B.OSUSER,B.MACHINE,B.PROGRAM
FROM V$SESS_IO A,V$SESSION B,V$SQL C,V$PROCESS D,v$session_wait e
WHERE A.SID=B.SID AND B.SQL_HASH_VALUE=C.HASH_VALUE and B.sid=E.sid
AND B.PADDR=D.ADDR and A.sid in(select sid from v$session_wait where event not in('SQL*Net message from client','rdbms ipc message','SQL*Net message to client'));

SELECT d.spid,a.sid,e.event,C.SQL_TEXT,B.USERNAME,B.OSUSER,B.MACHINE,B.PROGRAM
FROM V$SESS_IO A,V$SESSION B,V$SQL C,V$PROCESS D,v$session_wait e
WHERE a.sid=285

--数据库服务器字符集

select * from nls_database_parameters

--查询出最大的表

select * from (select segment_name,sum(bytes)/1024/1024/1024 as s from user_segments
where segment_type like 'TABLE%' group by segment_name order by s desc)

select sum(S) from (select segment_name,sum(bytes)/1024/1024/1024 as s from user_segments
where segment_type like 'TABLE%' group by segment_name order by s desc)

删除用户下所有表的语句:

select 'drop table '||table_name||' cascade constraints;' from user_tables;

监控索引重建的进度:

col opname format a32
col target_desc format a32
col perwork format a12
set lines 131
select sid,OPNAME,TARGET_DESC,sofar,TOTALWORK,trunc(sofar/totalwork*100,2)||'%' as perwork from v$session_longops where sofar!=totalwork;

查看每天产生归档日志的数据量：

select
trunc(completion_time) as "Date"
,count(*) as "Count"
,(sum(blocks * block_size)) /1024 /1024) as "MB"
from v$archived_log
group by trunc(completion_time);

对于非归档库：

select to_char(first_time,'yyyy-mm-dd') day,count(*) from v$log_history group by to_char(first_time,'yyyy-mm-dd') order by day desc;

大小得出来再乘以日志大小，就可以算归档量了。