Oracle DBA常用語(yǔ)句第1/2頁(yè)
更新時(shí)間:2009年08月04日 17:00:54 作者:
Oracle DBA常用語(yǔ)句,對(duì)于表空間大小等查看都是不錯(cuò)的sql語(yǔ)句。
查看表空間的名稱及大小
SQL>select t.tablespace_name, round(sum(bytes/(1024*1024)),0) ts_size
from dba_tablespaces t, dba_data_files d where t.tablespace_name = d.tablespace_name group by t.tablespace_name;
查看表空間物理文件的名稱及大小:
SQL>select tablespace_name, file_id, file_name,round(bytes/(1024*1024),0) total_space from dba_data_files order by tablespace_name;
查看回滾段名稱及大小:
SQL>select segment_name, tablespace_name, r.status,
(initial_extent/1024) InitialExtent,(next_extent/1024) NextExtent,
max_extents, v.curext CurExtent From dba_rollback_segs r, v$rollstat v
Where r.segment_id = v.usn(+) order by segment_name;
如何查看某個(gè)回滾段里面,跑的什么事物或者正在執(zhí)行什么sql語(yǔ)句:
SQL>select d.sql_text,a.name
from v$rollname a,v$transaction b,v$session c,v$sqltext d
where a.usn=b.xidusn and b.addr=c.taddr and c.sql_address=
d.address and c.sql_hash_value=d.hash_value
and a.usn=1;
(備注:你要看哪個(gè),就把usn=?寫成幾就行了)
查看控制文件:
SQL>select * from v$controlfile;
查看日志文件:
SQL> col member format a50
SQL>select * from v$logfile;
如何查看當(dāng)前SQL*PLUS用戶的sid和serial#:
SQL>select sid, serial#, status from v$session where audsid=userenv('sessionid');
如何查看當(dāng)前數(shù)據(jù)庫(kù)的字符集:
SQL>select userenv('language') from dual;
SQL>select userenv('lang') from dual;
怎么判斷當(dāng)前正在使用何種SQL優(yōu)化方式:
用explain plan產(chǎn)生EXPLAIN PLAN,檢查PLAN_TABLE中ID=0的POSITION列的值。
SQL>select decode(nvl(position,-1),-1,'RBO',1,'CBO') from plan_table where id=0;
如何查看系統(tǒng)當(dāng)前最新的SCN號(hào):
SQL>select max(ktuxescnw * power(2,32) + ktuxescnb) from x$ktuxe;
在ORACLE中查找TRACE文件的腳本:
SQL>select u_dump.value || '/' || instance.value || '_ora_' ||
v$process.spid || nvl2(v$process.traceid, '_' || v$process.traceid, null ) || '.trc'"Trace File" from v$parameter u_dump cross join v$parameter instance cross join v$process join v$session on v$process.addr = v$session.paddr where u_dump.name = 'user_dump_dest' and
instance.name = 'instance_name' and v$session.audsid=sys_context('userenv','sessionid');
SQL>select d.value || '/ora_' || p.spid || '.trc' trace_file_name
from (select p.spid from sys.v_$mystat m,sys.v_$session s,
sys.v_$process p where m.statistic# = 1 and
s.sid = m.sid and p.addr = s.paddr) p,(select value from sys.v_$parameter where name ='user_dump_dest') d;
如何查看客戶端登陸的IP地址:
SQL>select sys_context('userenv','ip_address') from dual;
如何在生產(chǎn)數(shù)據(jù)庫(kù)中創(chuàng)建一個(gè)追蹤客戶端IP地址的觸發(fā)器:
SQL>create or replace trigger on_logon_trigger
after logon on database
begin
dbms_application_info.set_client_info(sys_context('userenv', 'ip_address'));
end;
REM 記錄登陸信息的觸發(fā)器
CREATE OR REPLACE TRIGGER LOGON_HISTORY
AFTER LOGON ON DATABASE --WHEN (USER='WACOS') --ONLY FOR USER 'WACOS'
BEGIN
insert into session_history
select username,sid,serial#,AUDSID,OSUSER,ACTION,SYSDATE,null,SYS_CONTEXT('USERENV','IP_ADDRESS'),TERMINAL,machine,PROGRAM
from v$session where audsid = userenv('sessionid');
END;
查詢當(dāng)前日期:
SQL> select to_char(sysdate,'yyyy-mm-dd,hh24:mi:ss') from dual;
查看所有表空間對(duì)應(yīng)的數(shù)據(jù)文件名:
SQL>select distinct file_name,tablespace_name,AUTOEXTENSIBLE from dba_data_files;
查看表空間的使用情況:
SQL>select sum(bytes)/(1024*1024) as free_space,tablespace_name
from dba_free_space group by tablespace_name;
SQL>SELECT A.TABLESPACE_NAME,A.BYTES TOTAL,B.BYTES USED, C.BYTES FREE,
(B.BYTES*100)/A.BYTES "% USED",(C.BYTES*100)/A.BYTES "% FREE"
FROM SYS.SM$TS_AVAIL A,SYS.SM$TS_USED B,SYS.SM$TS_FREE C
WHERE A.TABLESPACE_NAME=B.TABLESPACE_NAME AND A.TABLESPACE_NAME=C.TABLESPACE_NAME;
column tablespace_name format a18;
column Sum_M format a12;
column Used_M format a12;
column Free_M format a12;
column pto_M format 9.99;
select s.tablespace_name,ceil(sum(s.bytes/1024/1024))||'M' Sum_M,ceil(sum(s.UsedSpace/1024/1024))||'M' Used_M,ceil(sum(s.FreeSpace/1024/1024))||'M' Free_M, sum(s.UsedSpace)/sum(s.bytes) PTUSED
from (select b.file_id,b.tablespace_name,b.bytes,
(b.bytes-sum(nvl(a.bytes,0))) UsedSpace,
sum(nvl(a.bytes,0)) FreeSpace,(sum(nvl(a.bytes,0))/(b.bytes)) * 100 FreePercentRatio from sys.dba_free_space a,sys.dba_data_files b
where a.file_id(+)=b.file_id group by b.file_id,b.tablespace_name,b.bytes
order by b.tablespace_name) s group by s.tablespace_name
order by sum(s.FreeSpace)/sum(s.bytes) desc;
查看數(shù)據(jù)文件的hwm(可以resize的最小空間)和文件頭大小:
SELECT v1.file_name,v1.file_id,
num1 totle_space,
num3 free_space,
num1-num3 "USED_SPACE(HWM)",
nvl(num2,0) data_space,
num1-num3-nvl(num2,0) file_head
FROM
(SELECT file_name,file_id,SUM(bytes) num1 FROM Dba_Data_Files GROUP BY file_name,file_id) v1,
(SELECT file_id,SUM(bytes) num2 FROM dba_extents GROUP BY file_id) v2,
(SELECT file_id,SUM(BYTES) num3 FROM DBA_FREE_SPACE GROUP BY file_id) v3
WHERE v1.file_id=v2.file_id(+)
AND v1.file_id=v3.file_id(+);
復(fù)制代碼 代碼如下:
SQL>select t.tablespace_name, round(sum(bytes/(1024*1024)),0) ts_size
from dba_tablespaces t, dba_data_files d where t.tablespace_name = d.tablespace_name group by t.tablespace_name;
查看表空間物理文件的名稱及大小:
復(fù)制代碼 代碼如下:
SQL>select tablespace_name, file_id, file_name,round(bytes/(1024*1024),0) total_space from dba_data_files order by tablespace_name;
查看回滾段名稱及大小:
復(fù)制代碼 代碼如下:
SQL>select segment_name, tablespace_name, r.status,
(initial_extent/1024) InitialExtent,(next_extent/1024) NextExtent,
max_extents, v.curext CurExtent From dba_rollback_segs r, v$rollstat v
Where r.segment_id = v.usn(+) order by segment_name;
如何查看某個(gè)回滾段里面,跑的什么事物或者正在執(zhí)行什么sql語(yǔ)句:
復(fù)制代碼 代碼如下:
SQL>select d.sql_text,a.name
from v$rollname a,v$transaction b,v$session c,v$sqltext d
where a.usn=b.xidusn and b.addr=c.taddr and c.sql_address=
d.address and c.sql_hash_value=d.hash_value
and a.usn=1;
(備注:你要看哪個(gè),就把usn=?寫成幾就行了)
查看控制文件:
SQL>select * from v$controlfile;
查看日志文件:
SQL> col member format a50
SQL>select * from v$logfile;
如何查看當(dāng)前SQL*PLUS用戶的sid和serial#:
SQL>select sid, serial#, status from v$session where audsid=userenv('sessionid');
如何查看當(dāng)前數(shù)據(jù)庫(kù)的字符集:
SQL>select userenv('language') from dual;
SQL>select userenv('lang') from dual;
怎么判斷當(dāng)前正在使用何種SQL優(yōu)化方式:
用explain plan產(chǎn)生EXPLAIN PLAN,檢查PLAN_TABLE中ID=0的POSITION列的值。
SQL>select decode(nvl(position,-1),-1,'RBO',1,'CBO') from plan_table where id=0;
如何查看系統(tǒng)當(dāng)前最新的SCN號(hào):
SQL>select max(ktuxescnw * power(2,32) + ktuxescnb) from x$ktuxe;
在ORACLE中查找TRACE文件的腳本:
SQL>select u_dump.value || '/' || instance.value || '_ora_' ||
v$process.spid || nvl2(v$process.traceid, '_' || v$process.traceid, null ) || '.trc'"Trace File" from v$parameter u_dump cross join v$parameter instance cross join v$process join v$session on v$process.addr = v$session.paddr where u_dump.name = 'user_dump_dest' and
instance.name = 'instance_name' and v$session.audsid=sys_context('userenv','sessionid');
SQL>select d.value || '/ora_' || p.spid || '.trc' trace_file_name
from (select p.spid from sys.v_$mystat m,sys.v_$session s,
sys.v_$process p where m.statistic# = 1 and
s.sid = m.sid and p.addr = s.paddr) p,(select value from sys.v_$parameter where name ='user_dump_dest') d;
如何查看客戶端登陸的IP地址:
SQL>select sys_context('userenv','ip_address') from dual;
如何在生產(chǎn)數(shù)據(jù)庫(kù)中創(chuàng)建一個(gè)追蹤客戶端IP地址的觸發(fā)器:
復(fù)制代碼 代碼如下:
SQL>create or replace trigger on_logon_trigger
after logon on database
begin
dbms_application_info.set_client_info(sys_context('userenv', 'ip_address'));
end;
REM 記錄登陸信息的觸發(fā)器
CREATE OR REPLACE TRIGGER LOGON_HISTORY
AFTER LOGON ON DATABASE --WHEN (USER='WACOS') --ONLY FOR USER 'WACOS'
BEGIN
insert into session_history
select username,sid,serial#,AUDSID,OSUSER,ACTION,SYSDATE,null,SYS_CONTEXT('USERENV','IP_ADDRESS'),TERMINAL,machine,PROGRAM
from v$session where audsid = userenv('sessionid');
END;
查詢當(dāng)前日期:
SQL> select to_char(sysdate,'yyyy-mm-dd,hh24:mi:ss') from dual;
查看所有表空間對(duì)應(yīng)的數(shù)據(jù)文件名:
SQL>select distinct file_name,tablespace_name,AUTOEXTENSIBLE from dba_data_files;
查看表空間的使用情況:
復(fù)制代碼 代碼如下:
SQL>select sum(bytes)/(1024*1024) as free_space,tablespace_name
from dba_free_space group by tablespace_name;
SQL>SELECT A.TABLESPACE_NAME,A.BYTES TOTAL,B.BYTES USED, C.BYTES FREE,
(B.BYTES*100)/A.BYTES "% USED",(C.BYTES*100)/A.BYTES "% FREE"
FROM SYS.SM$TS_AVAIL A,SYS.SM$TS_USED B,SYS.SM$TS_FREE C
WHERE A.TABLESPACE_NAME=B.TABLESPACE_NAME AND A.TABLESPACE_NAME=C.TABLESPACE_NAME;
column tablespace_name format a18;
column Sum_M format a12;
column Used_M format a12;
column Free_M format a12;
column pto_M format 9.99;
select s.tablespace_name,ceil(sum(s.bytes/1024/1024))||'M' Sum_M,ceil(sum(s.UsedSpace/1024/1024))||'M' Used_M,ceil(sum(s.FreeSpace/1024/1024))||'M' Free_M, sum(s.UsedSpace)/sum(s.bytes) PTUSED
from (select b.file_id,b.tablespace_name,b.bytes,
(b.bytes-sum(nvl(a.bytes,0))) UsedSpace,
sum(nvl(a.bytes,0)) FreeSpace,(sum(nvl(a.bytes,0))/(b.bytes)) * 100 FreePercentRatio from sys.dba_free_space a,sys.dba_data_files b
where a.file_id(+)=b.file_id group by b.file_id,b.tablespace_name,b.bytes
order by b.tablespace_name) s group by s.tablespace_name
order by sum(s.FreeSpace)/sum(s.bytes) desc;
查看數(shù)據(jù)文件的hwm(可以resize的最小空間)和文件頭大小:
復(fù)制代碼 代碼如下:
SELECT v1.file_name,v1.file_id,
num1 totle_space,
num3 free_space,
num1-num3 "USED_SPACE(HWM)",
nvl(num2,0) data_space,
num1-num3-nvl(num2,0) file_head
FROM
(SELECT file_name,file_id,SUM(bytes) num1 FROM Dba_Data_Files GROUP BY file_name,file_id) v1,
(SELECT file_id,SUM(bytes) num2 FROM dba_extents GROUP BY file_id) v2,
(SELECT file_id,SUM(BYTES) num3 FROM DBA_FREE_SPACE GROUP BY file_id) v3
WHERE v1.file_id=v2.file_id(+)
AND v1.file_id=v3.file_id(+);
相關(guān)文章
Oracle數(shù)據(jù)庫(kù)及應(yīng)用程序優(yōu)化開發(fā)者網(wǎng)絡(luò)Oracle
Oracle數(shù)據(jù)庫(kù)及應(yīng)用程序優(yōu)化開發(fā)者網(wǎng)絡(luò)Oracle...2007-03-03CentOS系統(tǒng)上安裝配置Oracle數(shù)據(jù)庫(kù)的詳細(xì)教程
這篇文章主要介紹了CentOS系統(tǒng)上安裝配置Oracle數(shù)據(jù)庫(kù)的詳細(xì)教程,包括安裝過(guò)程中一些常見錯(cuò)誤問(wèn)題的解決,需要的朋友可以參考下2016-03-03oracle報(bào)錯(cuò)(ORA-00600)問(wèn)題處理
最近在做一個(gè)項(xiàng)目,使用的是Oracle數(shù)據(jù)庫(kù),近兩天不知道怎么回事,告警日志里總是顯示這個(gè)錯(cuò)誤(ORA-00600:internalerrorcode,arguments:[kcblasm_1],[103],[],[],[],[],[],[])度娘了一下,終于找到解決方式,分享給大家2014-08-08基于OGG實(shí)現(xiàn)Oracle實(shí)時(shí)同步MySQL的全過(guò)程
這篇文章詳細(xì)闡述了基于OGG實(shí)現(xiàn)Oracle實(shí)時(shí)同步MySQL全過(guò)程,文中通過(guò)圖文結(jié)合和代碼示例給大家講解的非常詳細(xì),對(duì)大家的學(xué)習(xí)或工作有一定的幫助,需要的朋友可以參考下2023-11-11oracle臨時(shí)表空間的作用與創(chuàng)建及相關(guān)操作詳解
Oracle可能會(huì)需要使用到一些臨時(shí)存儲(chǔ)空間,用于臨時(shí)保存解析過(guò)的查詢語(yǔ)句以及在排序過(guò)程中產(chǎn)生的臨時(shí)數(shù)據(jù),下面這篇文章主要給大家介紹了關(guān)于oracle臨時(shí)表空間的作用與創(chuàng)建及相關(guān)操作的相關(guān)資料,需要的朋友可以參考下2022-07-07Oracle設(shè)置DB、監(jiān)聽和EM開機(jī)啟動(dòng)的方法
這篇文章主要介紹了Oracle設(shè)置DB、監(jiān)聽和EM開機(jī)啟動(dòng)的方法,本文通過(guò)圖文實(shí)例相結(jié)合給大家介紹的非常詳細(xì),對(duì)大家的學(xué)習(xí)或工作具有一定的參考借鑒價(jià)值,需要的朋友可以參考下2021-04-04