[標]ORACLE常用的一些語句記錄

標籤：des   style   color   io   ar   2014   sp   on   c   

--查詢實際的統計資訊
select     num_rows,blocks,empty_blocks,avg_space,avg_row_len,sample_size,
last_analyzed     from   dba_tables
where   upper(table_name)=
upper(‘T_HS_InivBalanceExpEntry‘);

--SQL語句次數和取數
select
SQL_TEXT,executions,fetches from v$sql where
parsing_schema_name=‘DBZY_JTGL_0901‘ and MODULE=‘WebDev.WebServer40.EXE‘
and
to_date(first_load_time,‘yyyy-mm-dd hh24:mi:ss‘) >=to_date(‘2014-09-07
10:18:00‘, ‘yyyy-mm-dd hh24:mi:ss‘)
and to_date(first_load_time,‘yyyy-mm-dd
hh24:mi:ss‘)<=to_date(‘2014-09-07 10:30:00‘, ‘yyyy-mm-dd
hh24:mi:ss‘)
ORDER BY  fetches desc,executions desc

--oracle 正在執行的SQL語句
SELECT b.sid oracleID, 
       b.username ,      
b.serial#, 
       spid OperationId, 
       paddr, 
       sql_text, 
       b.machine  
FROM v$process a
inner join v$session b on
a.addr = b.paddr 
inner join v$sqlarea c on b.sql_hash_value =
c.hash_value;

--表的大小
select round(BYTES/1024/1024,2)||‘M‘ from
user_segments where
segment_name=‘T_HS_INIVBALANCEEXP_H‘;

--鎖表查詢SQL
SELECT object_name,
machine, s.sid, s.serial#
FROM gv$locked_object L
inner join  dba_objects
o on L.object_id=o.object_id
inner join gv$session s on l.session_id = s.sid;

--釋放SESSION SQL:
--alter system kill session ‘sid, serial#‘;
ALTER system kill session ‘441, 61701‘;

[標]ORACLE常用的一些語句記錄