常用sql语句

–1、查看Oracle数据库中数据文件信息的命令方法：
select b.file_name 物理文件名,
b.tablespace_name 表空间,
b.bytes / 1024 / 1024 大小M,
(b.bytes - sum(nvl(a.bytes, 0))) / 1024 / 1024 已使用M,
substr((b.bytes - sum(nvl(a.bytes, 0))) / (b.bytes) * 100, 1, 5) 利用率
from dba_free_space a, dba_data_files b
where a.file_id = b.file_id
group by b.tablespace_name, b.file_name, b.bytes
order by b.tablespace_name;

–2、表空间大小和使用情况

select a.a1 表空间名称,
c.c2 类型,
c.c3 区管理,
b.b2 / 1024 / 1024 表空间大小M,
(b.b2 - a.a2) / 1024 / 1024 已使用M,
substr((b.b2 - a.a2) / b.b2 * 100, 1, 5) 利用率
from (select tablespace_name a1, sum(nvl(bytes, 0)) a2
from dba_free_space
group by tablespace_name) a,
(select tablespace_name b1, sum(bytes) b2
from dba_data_files
group by tablespace_name) b,
(select tablespace_name c1, contents c2, extent_management c3
from dba_tablespaces) c
where a.a1 = b.b1
and c.c1 = b.b1;

–3、表分析的时间
select a.table_name, a.num_rows, a.blocks, a.sample_size, a.last_analyzed
from dba_tables a
where a.owner = ‘PMS_SC’
and a.partitioned = ‘YES’
order by a.table_name;

SELECT * FROM XT_BFXRZ;

–4、undo增长情况
select round(max(a.UNDOBLKS / ((a.END_TIME - a.BEGIN_TIME) * 24 * 3600))*8)
from v$undostat a;

select * from dba_jobs_running;
–5、undo段扩展情况
select round(sum(a.UNDOBLKS) / 1024), trunc(a.BEGIN_TIME, ‘dd’)
from v$undostat a
group by trunc(a.BEGIN_TIME, ‘dd’);

–6、undo段扩展情况
select round(sum(a.UNDOBLKS) / 1024), trunc(a.BEGIN_TIME, ‘dd’)
from dba_hist_undostat a
group by trunc(a.BEGIN_TIME, ‘dd’);

—7、undo状态
SELECT a.TABLESPACE_NAME, a.STATUS, round(sum(a.BYTES) / 1024 / 1024)
FROM Dba_Undo_Extents a
group by a.TABLESPACE_NAME, a.STATUS;

—8、占用undo空间的事务
select s.USERNAME,
s.SID,
s.SQL_ID,
t.XIDUSN,
t.UBABLK / 1024,
t.USED_UBLK / 1024,s.SQL_EXEC_START
from gv$session s, gv$transaction t
where s.SADDR = t.SES_ADDR

—9、占用undo空间的事务
select * from gv$transaction;

–10、察看各个时段产生的归档日志数量
SELECT
TO_CHAR(first_time,‘MM/DD’) DAY
–, TO_CHAR(first_time,‘YYYY/MM/DD’) DAY2
, SUM(DECODE(TO_CHAR(first_time, ‘HH24’),‘00’,1,0)) H00
, SUM(DECODE(TO_CHAR(first_time, ‘HH24’),‘01’,1,0)) H01
, SUM(DECODE(TO_CHAR(first_time, ‘HH24’),‘02’,1,0)) H02
, SUM(DECODE(TO_CHAR(first_time, ‘HH24’),‘03’,1,0)) H03
, SUM(DECODE(TO_CHAR(first_time, ‘HH24’),‘04’,1,0)) H04
, SUM(DECODE(TO_CHAR(first_time, ‘HH24’),‘05’,1,0)) H05
, SUM(DECODE(TO_CHAR(first_time, ‘HH24’),‘06’,1,0)) H06
, SUM(DECODE(TO_CHAR(first_time, ‘HH24’),‘07’,1,0)) H07
, SUM(DECODE(TO_CHAR(first_time, ‘HH24’),‘08’,1,0)) H08
, SUM(DECODE(TO_CHAR(first_time, ‘HH24’),‘09’,1,0)) H09
, SUM(DECODE(TO_CHAR(first_time, ‘HH24’),‘10’,1,0)) H10
, SUM(DECODE(TO_CHAR(first_time, ‘HH24’),‘11’,1,0)) H11
, SUM(DECODE(TO_CHAR(first_time, ‘HH24’),‘12’,1,0)) H12
, SUM(DECODE(TO_CHAR(first_time, ‘HH24’),‘13’,1,0)) H13
, SUM(DECODE(TO_CHAR(first_time, ‘HH24’),‘14’,1,0)) H14
, SUM(DECODE(TO_CHAR(first_time, ‘HH24’),‘15’,1,0)) H15
, SUM(DECODE(TO_CHAR(first_time, ‘HH24’),‘16’,1,0)) H16
, SUM(DECODE(TO_CHAR(first_time, ‘HH24’),‘17’,1,0)) H17
, SUM(DECODE(TO_CHAR(first_time, ‘HH24’),‘18’,1,0)) H18
, SUM(DECODE(TO_CHAR(first_time, ‘HH24’),‘19’,1,0)) H19
, SUM(DECODE(TO_CHAR(first_time, ‘HH24’),‘20’,1,0)) H20
, SUM(DECODE(TO_CHAR(first_time, ‘HH24’),‘21’,1,0)) H21
, SUM(DECODE(TO_CHAR(first_time, ‘HH24’),‘22’,1,0)) H22
, SUM(DECODE(TO_CHAR(first_time, ‘HH24’),‘23’,1,0)) H23
, COUNT()||’(’||trim(to_char(sum(blocksblock_size)/1024/1024,‘99,999.9’))||‘M)’ TOTAL
FROM
(select max(blocks) blocks,max(block_size) block_size,max(first_time) first_time
from
v$archived_log a
where COMPLETION_TIME > sysdate - 5
and dest_id = 1
group by sequence#
)
group by TO_CHAR(first_time,‘MM/DD’), TO_CHAR(first_time,‘YYYY/MM/DD’)
order by TO_CHAR(first_time,‘YYYY/MM/DD’) desc;

–11、查看各个表所占用的空间大小
Select Segment_Name,
round(Sum(bytes) / 1024 / 1024 / 1024, 2) G,
tablespace_name
From User_Extents
Group By Segment_Name, tablespace_name
order by G desc;
–查询锁
select * from dba_jobs_running