Heim >Datenbank >MySQL-Tutorial >Oracle DBA常用语句第1/2页

Oracle DBA常用语句第1/2页

WBOY
WBOYOriginal
2016-06-07 18:06:45857Durchsuche

Oracle DBA常用语句,对于表空间大小等查看都是不错的sql语句。

查看表空间的名称及大小
代码如下:
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;

如何查看某个回滚段里面,跑的什么事物或者正在执行什么sql语句:
代码如下:
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;

(备注:你要看哪个,就把usn=?写成几就行了)
查看控制文件:
SQL>select * from v$controlfile;
查看日志文件:
SQL> col member format a50
SQL>select * from v$logfile;
如何查看当前SQL*PLUS用户的sid和serial#:
SQL>select sid, serial#, status from v$session where audsid=userenv('sessionid');
如何查看当前数据库的字符集:
SQL>select userenv('language') from dual;
SQL>select userenv('lang') from dual;
怎么判断当前正在使用何种SQL优化方式:
用explain plan产生EXPLAIN PLAN,检查PLAN_TABLE中ID=0的POSITION列的值。
SQL>select decode(nvl(position,-1),-1,'RBO',1,'CBO') from plan_table where id=0;
如何查看系统当前最新的SCN号:
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;
如何在生产数据库中创建一个追踪客户端IP地址的触发器:
代码如下:
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 记录登陆信息的触发器
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;

查询当前日期:
SQL> select to_char(sysdate,'yyyy-mm-dd,hh24:mi:ss') from dual;
查看所有表空间对应的数据文件名:
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;

查看数据文件的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(+);

Stellungnahme:
Der Inhalt dieses Artikels wird freiwillig von Internetnutzern beigesteuert und das Urheberrecht liegt beim ursprünglichen Autor. Diese Website übernimmt keine entsprechende rechtliche Verantwortung. Wenn Sie Inhalte finden, bei denen der Verdacht eines Plagiats oder einer Rechtsverletzung besteht, wenden Sie sich bitte an admin@php.cn