oracle dba常用,Oracle DBA常用语句

最新推荐文章于 2021-04-10 23:35:14 发布

姜海船没船

最新推荐文章于 2021-04-10 23:35:14 发布

阅读量92

点赞数

文章标签： oracle dba常用

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,