Oracle 常用脚本1

 

select * from v$database;

select * from v$datafile;

 

 

--查询锁
SELECT RPAD (oracle_username, 10) o_name, session_id SID,
       DECODE (locked_mode,
               0, 'None',
               1, 'Null',
               2, 'Row share',
               3, 'Row Execlusive',
               4, 'Share',
               5, 'Share Row Exclusive',
               6, 'Exclusive'
              ) lock_type,
       object_name, xidusn, xidslot, xidsqn
  FROM v$locked_object, all_objects
 WHERE v$locked_object.object_id = all_objects.object_id;

select s.terminal,s.machine,s.program,sid,s.serial#,
    a.oracle_username, a.process, o.owner, o.object_id, o.object_name, a.locked_mode
from v$session s, v$locked_object a, dba_objects o
where s.sid=a.session_id
and o.object_id=a.object_id 

 

 

SET LINESIZE 100 s.inst_id,
       s.sid,
       s.serial#,
       p.spid,
       s.username,
 ;


                                                                                  COLUMN spid FORMAT A10
COLUMN username FORMAT A10
COLUMN program FORMAT A45

SELECT       s.program
FROM   gv$session s
       JOIN gv$process p ON p.addr = s.paddr AND p.inst_id = s.inst_id
WHERE  s.type !='BACKGROUND'   INST_ID        SID    SERIAL# SPID       USERNAME   PROGRAM
---------- ---------- ---------- ---------- ---------- ---------------------------------------------130153859       TEST       sqlplus@oel5-11gr2.localdomain (TNS V1-V3)
         1232873834       SYS        sqlplus@oel5-11gr2.localdomain (TNS V1-V3)
         1403874663                  oracle@oel5-11gr2.localdomain (J000)
         1381254665                  oracle@oel5-11gr2.localdomain (J001)