時(shí)間：2015/6/28來源：IT貓撲網(wǎng)作者：網(wǎng)管聯(lián)盟我要評(píng)論(0)
查看剩余表空間
select a.tablespace_name,free/total*100 pct_free,free/1024/1024 "free(M)" from
(select sum(bytes) free ,tablespace_name from dba_free_space group by tablespace_name) a,
(select sum(bytes) total ,tablespace_name from dba_data_files group by tablespace_name) b
where a.tablespace_name=b.tablespace_name
order by pct_free;
DROP TABLESPACE temp1 INCLUDING CONTENTS AND DATAFILES;
日常維護(hù)工作中,時(shí)常會(huì)碰到數(shù)據(jù)出錯(cuò)的情況.
一般有:鎖表,空間不夠,表無法擴(kuò)展,數(shù)據(jù)庫被某個(gè)寫的很爛的sql占用很大的資源等情況.
一下是一些經(jīng)常要用的sql腳本.希望對(duì)大家有幫助.
(不過這個(gè)可不是我整理出來的)
---增加臨時(shí)表空間大小
alter temporary tablespace temp add tempfile '/opt/oracle/oradata/ora9/temp10.dbf' size 1000M;
--查看表的字錄條數(shù)
select 'select count(1) from '||tname||';' from tab where tname not like '%BIN%'
--回滾段監(jiān)視
select n.usn 回滾段標(biāo)識(shí),
n.NAME 回滾段名稱,
s.osuser 操作系統(tǒng)用戶,
s.Username 用戶名,
s.sid 會(huì)話ID,
rs.EXTENTS 回滾段擴(kuò)展次數(shù),
rs.wraps,
rs.rssize/1024/1024 "使用空間(MBytes)",
rs.status 回滾段狀態(tài)
from v$rollname n, v$rollstat rs, v$session s, v$transaction t
where t.addr = s.taddr(+)
and rs.usn(+) = n.usn
and t.xidusn(+) = n.usn
/*and rs.status = 'ONLINE'*/
order by rs.rssize
--回滾段塊事務(wù)查詢
select s.sid,s.serial#,t.start_time,t.xidusn,s.username
from v$session s,v$transaction t,v$rollstat r
where s.saddr=t.ses_addr
and t.xidusn=r.usn
and ((r.curext=t.start_uext-1) or
((r.curext=r.extents-1) and t.start_uext=0));
--鎖監(jiān)視
SELECT b.os_user_name 操作系統(tǒng)用戶,
b.oracle_username ORACLE用戶,
b.session_id 會(huì)話ID,
b.process 進(jìn)程號(hào),
a.object_name 對(duì)象名,
a.subobject_name 子對(duì)象名,
d.machine 客戶端機(jī)器,
d.lockwait 鎖等待,
d.status 會(huì)話狀態(tài),
d.schemaname 數(shù)據(jù)庫對(duì)象名稱,
d.terminal 終端名,
d.program 終端程序名,
d.logon_time 登陸時(shí)間
FROM dba_objects a,v$locked_object b,v$session d
--,v$lock c
WHERE a.object_id=b.object_id
AND b.session_id=d.sid
select a.username, a.sid, a.serial#, b.id1
from v$session a, v$lock b
where a.lockwait = b.kaddr
select a.username, a.sid, a.serial#, b.id1
from v$session a, v$lock b
where b.id1 in
(select distinct e.id1
from v$session d, v$lock e
where d.lockwait = e.kaddr)
and a.sid = b.sid
and b.request = 0
#p#副標(biāo)題#e#
查看回滾段是否回退結(jié)束
select used_ublk,used_urec from v$transaction a,v$session b where a.ses_addr=b.saddr and b.sid=442;
select a.sid, a.username, b.xidusn, b.used_urec, b.used_ublk from v$session a, v$transaction b where a.saddr=b.ses_addr and a.sid=''
select distinct /*+ index_ffs(c,pk_auto) parallel_index_
(automobile, pk_auto) color, count(*)
from
automobiles
group by color;
鎖等待
SELECT DECODE(request,0,'Holder: ','Waiter: ')||sid sess,
id1, id2, lmode, request, type
FROM V$LOCK
WHERE (id1, id2, type) IN
(SELECT id1, id2, type FROM V$LOCK WHERE request>0)
ORDER BY id1, request;
SELECT /*+index(b,IDX_YHDA_NEW_YHBH)*/ (強(qiáng)制索引)
a.yhbh,v_rent.product_id,b.dhhm,NVL(ktrq,TO_DATE('19000101','YYYYMMDD')),TO_DATE('20500101','YYYYMMDD'),b.xq
FROM mcm_tyt_yhtf_cur a,mcm_tyt_yhda_new b
WHERE TO_CHAR(a.yhbh)=(b.yhbh)
AND b.jz=v_rent.jz
AND a.tf=v_rent.tf
AND a.xq=v_rent.xq
AND b.xq=v_rent.xq
AND b.tch LIKE'TRYT%'
AND a.ktrq<cur_end_date;
select * from v$sqlarea a,v$session b where a.address=b.sql_address AND a.sql_text like
表空間不能擴(kuò)展的表
select tablespace_name tablespace,
table_name table_name,
next_extent next
from dba_tables outer
where not exists (select 'x'
from sys.dba_free_space inner
where outer.tablespace_name = inner.tablespace_name
and bytes>=next_extent)
select 'alter system kill session '||''''||sid||','||serial#||''''||';' from v$session a,v$sqlarea b where a.sql_address=b.address and b.sql_text like '%REAL_FEE%' AND A.USERNAME in ('ZJLT','SZMCM')
--AND A.STATUS='INACTIVE'
select 'ALTER SYSTEM KILL SESSION'||''''||SID||','||SERIAL#||''''||';' from v$session A where status='INACTIVE' AND A.OSUSER='Administrator'
select * from v$process d,v$session e where d.addr=e.paddr and sid
in
(select sid from
v$session a,v$sqlarea b where a.sql_address=b.address and b.sql_text like
'%REAL_FEE%' AND A.USERNAME in ('ZJLT','SZMCM')
AND A.STATUS='INACTIVE')
--latch
select
c.name,a.addr,a.gets,a.misses,a.sleeps, a.immediate_gets,a.immediate_misses,b.pid
from v$latch a, v$latchholder b, v$latchname c
where
a.addr = b.laddr(+) and a.latch# = c.latch#
order by a.latch#;
select
name
from
v$latchname a, v$latch b
where
b.addr = '&addr' and b.latch#=a.latch#;
select
c.name,a.addr,a.gets,a.misses,a.sleeps,a.immediate_gets,
a.immediate_misses,b.pid
from
v$latch a, v$latchholder b, v$latchname c
where
a.addr = b.laddr(+) and a.latch# = c.latch# and c.name like '&latch_name%'
order by a.latch#;
#p#副標(biāo)題#e#
--查鎖資源
select a.sid,
decode(a.type,
'MR', 'Media Recovery',
'RT', 'Redo Thread',
'UN', 'User Name',
'TX', 'Transaction',
'TM', 'DML',
'UL', 'PL/SQL User Lock',
'DX', 'Distributed Xaction',
'CF', 'Control File',
'IS', 'Instance State',
'FS', 'File Set',
'IR', 'Instance Recovery',
'ST', 'Disk Space Transaction',
'IR', 'Instance Recovery',
'ST', 'Disk Space Transaction',
'TS', 'Temp Segment',
'IV', 'Library Cache Invalidation',
'LS', 'Log Start or Switch',
'RW', 'Row Wait',
'SQ', 'Sequence Number',
'TE', 'Extend Table',
'TT', 'Temp Table',
a.type) lock_type,
decode(a.lmode,
0, 'None', /* Mon Lock equivalent */
1, 'Null', /* N */
2, 'Row-S (SS)', /* L */
3, 'Row-X (SX)', /* R */
4, 'Share', /* S */
5, 'S/Row-X (SSX)', /* C */
6, 'Exclusive', /* X */
to_char(a.lmode)) mode_held,
decode(a.request,
0, 'None', /* Mon Lock equivalent */
1, 'Null', /* N */
2, 'Row-S (SS)', &
關(guān)鍵詞標(biāo)簽：Oracle常用腳本
相關(guān)閱讀
熱門文章
Oracle中使用alter table來增加,刪除,修改列
oracle中使用SQL語句修改字段類型-oracle修
使用低權(quán)限Oracle數(shù)據(jù)庫賬戶得到管理員權(quán)限
Oracle對(duì)user的訪問控制
人氣排行 ORACLE SQL 判斷字符串是否為數(shù)字的語句Oracle中使用alter table來增加,刪除,修改列的語法ORACLE和SQL語法區(qū)別歸納(1)oracle grant 授權(quán)語句如何加速Oracle大批量數(shù)據(jù)處理Oracle刪除表的幾種方法ORACLE修改IP地址后如何能夠使用Oracle 10g創(chuàng)建表空間和用戶并指定權(quán)限