時間：2015-06-28 00:00:00 來源：IT貓撲網(wǎng) 作者：網(wǎng)管聯(lián)盟 我要評論(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ù)據(jù)出錯的情況.
一般有:鎖表,空間不夠,表無法擴展,數(shù)據(jù)庫被某個寫的很爛的sql占用很大的資源等情況.
一下是一些經(jīng)常要用的sql腳本.希望對大家有幫助.
(不過這個可不是我整理出來的)
---增加臨時表空間大小
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)識,
n.NAME 回滾段名稱,
s.osuser 操作系統(tǒng)用戶,
s.Username 用戶名,
s.sid 會話ID,
rs.EXTENTS 回滾段擴展次數(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 會話ID,
b.process 進(jìn)程號,
a.object_name 對象名,
a.subobject_name 子對象名,
d.machine 客戶端機器,
d.lockwait 鎖等待,
d.status 會話狀態(tài),
d.schemaname 數(shù)據(jù)庫對象名稱,
d.terminal 終端名,
d.program 終端程序名,
d.logon_time 登陸時間
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)*/ (強制索引)
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 select * from v$sqlarea a,v$session b where a.address=b.sql_address AND? a.sql_text like 表空間不能擴展的表 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修改SQL語句案例
誤刪Oracle數(shù)據(jù)庫實例的控制文件
為UNIX服務(wù)器設(shè)置Oracle全文檢索
人氣排行 oracle中使用SQL語句修改字段類型-oracle修改SQL語句案例 Oracle中使用alter table來增加,刪除,修改列的語法 ORACLE SQL 判斷字符串是否為數(shù)字的語句 ORACLE和SQL語法區(qū)別歸納(1) oracle grant 授權(quán)語句 ORACLE修改IP地址后如何能夠使用 如何加速Oracle大批量數(shù)據(jù)處理 Oracle刪除表的幾種方法