查看表空间的名称及大小 <div class="codetitle"><a style="CURSOR: pointer" data="28583" class="copybut" id="copybut28583" onclick="doCopy('code28583')"> 代码如下:<div class="codebody" id="code28583"> SQL>select t.tablespace_name,round(sum(bytes/(10241024)),0) ts_size from dba_tablespaces t,dba_data_files d where t.tablespace_name = d.tablespace_name group by t.tablespace_name; 查看表空间物理文件的名称及大小: <div class="codetitle"><a style="CURSOR: pointer" data="20297" class="copybut" id="copybut20297" onclick="doCopy('code20297')"> 代码如下:<div class="codebody" id="code20297"> SQL>select tablespace_name,file_id,file_name,round(bytes/(10241024),0) total_space from dba_data_files order by tablespace_name; 查看回滚段名称及大小: <div class="codetitle"><a style="CURSOR: pointer" data="98271" class="copybut" id="copybut98271" onclick="doCopy('code98271')"> 代码如下:<div class="codebody" id="code98271"> 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语句: <div class="codetitle"><a style="CURSOR: pointer" data="37792" class="copybut" id="copybut37792" onclick="doCopy('code37792')"> 代码如下:<div class="codebody" id="code37792"> 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; 如何查看当前SQLPLUS用户的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=syscontext('userenv','sessionid'); SQL>select d.value || '/ora' || p.spid || '.trc' trace_filename 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地址的触发器: <div class="codetitle"><a style="CURSOR: pointer" data="19229" class="copybut" id="copybut19229" onclick="doCopy('code19229')"> 代码如下:<div class="codebody" id="code19229"> 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,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,AUTOEXTENSIBLE from dba_data_files; 查看表空间的使用情况: <div class="codetitle"><a style="CURSOR: pointer" data="1476" class="copybut" id="copybut1476" onclick="doCopy('code1476')"> 代码如下:<div class="codebody" id="code1476"> SQL>select sum(bytes)/(10241024) 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.BYTES100)/A.BYTES "% USED",(C.BYTES100)/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.bytes order by b.tablespace_name) s group by s.tablespace_name order by sum(s.FreeSpace)/sum(s.bytes) desc; 查看数据文件的hwm(可以resize的最小空间)和文件头大小: <div class="codetitle"><a style="CURSOR: pointer" data="28179" class="copybut" id="copybut28179" onclick="doCopy('code28179')"> 代码如下:<div class="codebody" id="code28179"> 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,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,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(+); 数据文件大小及头大小: <div class="codetitle"><a style="CURSOR: pointer" data="48069" class="copybut" id="copybut48069" onclick="doCopy('code48069')"> 代码如下:<div class="codebody" id="code48069"> SELECT v1.file_name, num1-num3 Used_space,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(+); (运行以上查询,我们可以如下信息: <div class="codetitle"><a style="CURSOR: pointer" data="14957" class="copybut" id="copybut14957" onclick="doCopy('code14957')"> 代码如下:<div class="codebody" id="code14957"> Totle_pace:该数据文件的总大小,字节为单位 Free_space:该数据文件的剩于大小,字节为单位 Used_space:该数据文件的已用空间,字节为单位 Data_space:该数据文件中段数据占用空间,也就是数据空间,字节为单位 File_Head:该数据文件头部占用空间,字节为单位) 数据库各个表空间增长情况的检查: <div class="codetitle"><a style="CURSOR: pointer" data="14293" class="copybut" id="copybut14293" onclick="doCopy('code14293')"> 代码如下:<div class="codebody" id="code14293"> SQL>select A.tablespace_name,(1-(A.total)/B.total)100 used_percent From (select tablespace_name,sum(bytes) total from dba_free_space group by tablespace_name) A,(select tablespace_name,sum(bytes) total from dba_data_files group by tablespace_name) B where A.tablespace_name=B.tablespace_name; SQL>SELECT UPPER(F.TABLESPACE_NAME) "表空间名", D.TOT_GROOTTE_MB "表空间大小(M)", D.TOT_GROOTTE_MB - F.TOTAL_BYTES "已使用空间(M)",TO_CHAR(ROUND((D.TOT_GROOTTE_MB - F.TOTAL_BYTES) / D.TOT_GROOTTE_MB 100,2),'990.99') "使用比",F.TOTAL_BYTES "空闲空间(M)", F.MAX_BYTES "最大块(M)" FROM (SELECT TABLESPACE_NAME, ROUND(SUM(BYTES) / (1024 1024),2) TOTAL_BYTES, ROUND(MAX(BYTES) / (1024 1024),2) MAX_BYTES FROM SYS.DBA_FREE_SPACE GROUP BY TABLESPACE_NAME) F, (SELECT DD.TABLESPACE_NAME,ROUND(SUM(DD.BYTES) / (1024 1024),2) TOT_GROOTTE_MB FROM SYS.DBA_DATA_FILES DD GROUP BY DD.TABLESPACE_NAME) D WHERE D.TABLESPACE_NAME = F.TABLESPACE_NAME ORDER BY 4 DESC; 查看各个表空间占用磁盘情况: <div class="codetitle"><a style="CURSOR: pointer" data="89726" class="copybut" id="copybut89726" onclick="doCopy('code89726')"> 代码如下:<div class="codebody" id="code89726"> SQL>col tablespace_name format a20; SQL>select b.file_id file_ID, b.tablespace_name tablespace_name, b.bytes Bytes,0))) used,0)) free,0))/(b.bytes)100 Percent from dba_free_space a,dba_data_files b where a.file_id=b.file_id group by b.tablespace_name,b.file_id,b.bytes order by b.file_id; 数据库对象下一扩展与表空间的free扩展值的检查: <div class="codetitle"><a style="CURSOR: pointer" data="63427" class="copybut" id="copybut63427" onclick="doCopy('code63427')"> 代码如下:<div class="codebody" id="code63427"> SQL>select a.table_name,a.next_extent,a.tablespace_name from all_tables a,max(bytes) as big_chunk from dba_free_space group by tablespace_name ) f where f.tablespace_name = a.tablespace_name and a.next_extent > f.big_chunk union select a.index_name,a.tablespace_name from all_indexes a,max(bytes) as big_chunk from dba_free_space group by tablespace_name ) f where f.tablespace_name = a.tablespace_name and a.next_extent > f.big_chunk; Disk Read最高的SQL语句的获取: <div class="codetitle"><a style="CURSOR: pointer" data="15766" class="copybut" id="copybut15766" onclick="doCopy('code15766')"> 代码如下:<div class="codebody" id="code15766"> SQL>select sql_text from (select from v$sqlarea order by disk_reads) where rownum<=5; 查找前十条性能差的sql <div class="codetitle"><a style="CURSOR: pointer" data="58750" class="copybut" id="copybut58750" onclick="doCopy('code58750')"> 代码如下:<div class="codebody" id="code58750"> SELECT FROM (SELECT PARSING_USER_ID EXECUTIONS,SORTS,COMMAND_TYPE,DISK_READS, sql_text FROM v$sqlarea ORDER BY disk_reads DESC) WHERE ROWNUM<10 ; 等待时间最多的5个系统等待事件的获取: <div class="codetitle"><a style="CURSOR: pointer" data="82913" class="copybut" id="copybut82913" onclick="doCopy('code82913')"> 代码如下:<div class="codebody" id="code82913"> SQL>select from (select from v$system_event where event not like 'SQL%' order by total_waits desc) where rownum<=5; 查看当前等待事件的会话: <div class="codetitle"><a style="CURSOR: pointer" data="10425" class="copybut" id="copybut10425" onclick="doCopy('code10425')"> 代码如下:<div class="codebody" id="code10425"> col username format a10 set line 120 col EVENT format a30 select SE.Sid,s.Username,SE.Event,se.Total_Waits,SE.Time_Waited,SE.Average_Wait from v$session S,v$session_event SE where S.Username is not null and SE.Sid=S.Sid and S.Status='ACTIVE' and SE.Event not like '%SQLNet%'; select sid,event,p1,p2,p3,wait_time,seconds_in_wait,state from v$session_wait where event not like '%message%' and event not like 'SQLNet%' and event not like '%timer%' and event != 'wakeup time manager'; 找到与所连接的会话有关的当前等待事件: <div class="codetitle"><a style="CURSOR: pointer" data="74772" class="copybut" id="copybut74772" onclick="doCopy('code74772')"> 代码如下:<div class="codebody" id="code74772"> select SW.Sid,S.Username,SW.Event,SW.Wait_Time,SW.State,SW.Seconds_In_Wait SEC_IN_WAIT from v$session S,v$session_wait SW where S.Username is not null and SW.Sid=S.Sid and SW.event not like '%SQL*Net%' order by SW.Wait_Time Desc; Oracle所有回滚段状态的检查: (编辑:李大同)
【声明】本站内容均来自网络,其相关言论仅代表作者个人观点,不代表本站立场。若无意侵犯到您的权利,请及时与联系站长删除相关内容!
|