小SQL（一）

1、硬解析

selectname,valuefromv$mystata,v$statnamebwherea.statistic#=b.statistic#

andnamelike'parse count%';     

 

2、共享池

selectsql_text,version_count,parse_calls,executions

fromv$sqlareawhereupper(sql_text)like'%test%';

 

3、查看当前trace文件

SELECT      u_dump.VALUE

        || '/'

        || db_name.VALUE

        || '_ora_'

        || v$process.spid

        || NVL2 (v$process.traceid,'_' ||v$process.traceid, NULL)

        || '.trc'

           "Trace File"

 FROM            v$parameteru_dump

              CROSS JOIN

                  v$parameterdb_name

           CROSS JOIN

               v$process

        JOIN

            v$session

        ON v$process.addr =v$session.paddr

 WHERE       u_dump.name = 'user_dump_dest'

        AND db_name.name = 'db_name'

        AND v$session.audsid = SYS_CONTEXT ('userenv','sessionid');

 

4、杀掉所有oracle进程

ps -ef|grep $ORACLE_SID|grep-v ora_|grep LOCAL=NO|awk '{print $2}'|xargs kill

 

5、杀数据库进程

select* from v$session ;

alter system killsession'sid,serial#' ;

 

6、查看某用户运行的SQL语句

SELECT osuser, username,sql_text from v$session a, v$sqltext b 
where a.sql_address =b.address 
and a.username = 'BANK_USER'
order by address, piece;

 

7、查出锁定object的session的信息以及被锁定的object名

SELECT l.session_id sid,s.serial#, l.locked_mode,l.oracle_username, 
       l.os_user_name,s.machine, s.terminal,o.object_name, s.logon_time 
    FROM v$locked_object l, all_objects o, v$session s 
   WHERE l.object_id = o.object_id 
     AND l.session_id = s.sid 
ORDER BY sid, s.serial# ;

 

8、查出锁定表的session的sid, serial#,os_user_name,machine name, terminal和执行的语句
--比上面那段多出sql_text和action 

SELECT l.session_id sid,s.serial#, l.locked_mode, l.oracle_username, s.user#,
       l.os_user_name,s.machine,s.terminal,a.sql_text, a.action  
     FROM v$sqlarea a,v$session s, v$locked_object l 
    WHERE l.session_id = s.sid 
      AND s.prev_sql_addr = a.address 
ORDER BY sid, s.serial#;

 

9、查出锁定表的sid, serial#,os_user_name,machine_name, terminal，锁的type,mode 

SELECT s.sid, s.serial#,s.username, s.schemaname, s.osuser, s.process, s.machine,
       s.terminal, s.logon_time, l.type 
    FROM v$session s, v$lock l 
   WHERE s.sid = l.sid 
     AND s.username IS NOT NULL 
ORDER BY sid;

 

10、找出使用CPU多的用户session

selecta.sid,spid,status,substr(a.program,1,40) prog,a.terminal,osuser,value/60/100value
from v$session a,v$process b,v$sesstat c 
where c.statistic#=12 and c.sid=a.sid and a.paddr=b.addr order by value desc;

 

11、监控表空间的 I/O 比例

select df.tablespace_name name,df.file_name"file",f.phyrds pyr, 
f.phyblkrd pbr,f.phywrts pyw, f.phyblkwrt pbw 
from v$filestat f, dba_data_files df 
where f.file# = df.file_id 
order by df.tablespace_name; 

 

12、监控当前数据库谁在运行什么SQL语句

SELECT osuser, username,sql_text from v$session a, v$sqltext b 
where a.sql_address =b.address order by address, piece;

 

13、检查被长时间锁的对象

selecta.session_id,a.process,a.locked_mode,b.object_name,b.object_type,b.status fromv$locked_object a,dba_objects b where a.object_id=b.object_id;

 

14、查询耗资源的进程（topsession）

SELECT s.SchemanameSchema_Name,
        Decode(Sign(48 - Command),
        1, To_Char(Command), 'Action Code #'|| To_Char(Command)) Action,
        Status Session_Status, s.OsuserOs_User_Name, s.Sid, p.Spid,
        s.Serial# Serial_Num, Nvl(s.Username,'[Oracle process]') User_Name,
        s.Terminal Terminal, s.ProgramProgram, St.VALUE Criteria_Value
        FROM V$sesstat St, V$session s,V$process p
        WHERE St.Sid =s.Sid         AND St.Statistic# =To_Number('38')
        AND ('ALL' = 'ALL' OR s.Status ='ALL')
        AND p.Addr = s.Paddr
        ORDER BY St.VALUE DESC, p.Spid ASC,s.Username ASC, s.Osuser ASC

 

15、如何查看系统最大会话数

Select * from v$parameterwhere name like ‘proc%’
Show parameter processes
Select * from v$license

16、查看表空间的名称及大小

   select t.tablespace_name, round(sum(bytes/(1024*1024)),0) ts_size
    fromdba_tablespaces t, dba_data_files d
    wheret.tablespace_name = d.tablespace_name
    groupby t.tablespace_name;

17、查看表空间物理文件的名称及大小

   select tablespace_name, file_id, file_name,
   round(bytes/(1024*1024),0) total_space
    fromdba_data_files
    orderby tablespace_name;

18、查看回滚段名称及大小

   select segment_name, tablespace_name, r.status,
   (initial_extent/1024) InitialExtent,(next_extent/1024) NextExtent,
   max_extents, v.curext CurExtent
    Fromdba_rollback_segs r, v$rollstat v
    Wherer.segment_id = v.usn(+)
    orderby segment_name ;

19、查看控制文件

   select name from v$controlfile;

20、查看日志文件

   select member from v$logfile;

21、查看表空间的使用情况

   select sum(bytes)/(1024*1024) as free_space,tablespace_name
    fromdba_free_space
    groupby tablespace_name;

   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"
    FROMSYS.SM$TS_AVAIL A,SYS.SM$TS_USED B,SYS.SM$TS_FREE C
    WHEREA.TABLESPACE_NAME=B.TABLESPACE_NAME AND A.TABLESPACE_NAME=C.TABLESPACE_NAME;

22、查看数据库库对象

   select owner, object_type, status, count(*) count# from all_objects group byowner, object_type, status;

23、查看数据库的版本

   Select version FROM Product_component_version
    WhereSUBSTR(PRODUCT,1,6)='Oracle';

24、查看数据库的创建日期和归档方式

   Select Created, Log_Mode, Log_Mode From V$Database;