[Oracle] DBA 常用脚本一

1: 查看回滚段中活动的事务 Active Transactions in Rollback Segments
column rr heading 'RB Segment' format a18
column us heading 'Username' format a15
column os heading 'OS User' format a10
column te heading 'Terminal' format a10
SELECT r.name rr,
nvl(s.username,'no transaction') us,
s.osuser os,
s.terminal te
from
v$lock l,
v$session s,
v$rollname r
WHERE
l.sid = s.sid(+) AND
trunc(l.id1/65536) = r.usn AND
l.type = 'TX' AND
l.lmode = 6
ORDER BY r.name
/
 
2: 查看包体源码 Show Package Extract package and package body source from the database
COL SORT1 NOPRINT
COL SORT2 NOPRINT
COL SORT3 NOPRINT
COL SORT4 NOPRINT
BREAK ON SORT1 SKIP 1
set linesize 120
SET HEADING OFF
SET ECHO OFF
SET FEEDBACK OFF
SET PAGESIZE 0
SPOOL bldpack.sql
select 'set echo on ' from dual;
select 'spool bldpack.lst' from dual;
select 'Remark Build package definitions' from dual;
SELECT NAME SORT1, LINE SORT2,
'Create or Replace ' || TEXT
from USER_SOURCE
WHERE TYPE = 'PACKAGE' AND LINE = 1
UNION
SELECT NAME SORT1, LINE SORT2,TEXT
from USER_SOURCE
WHERE TYPE = 'PACKAGE' AND LINE > 1
UNION
SELECT NAME SORT1, 999999 SORT2, '/'
from USER_SOURCE
WHERE TYPE = 'PACKAGE' AND LINE =1
ORDER BY 1,2;
spool off
SPOOL bldpbdy.sql
select 'set echo on ' from dual;
select 'spool bldpbdy.lst' from dual;
select 'Remark Build package body definitions' from dual;
SELECT NAME SORT1, LINE SORT2,'Createor
Replace '||TEXT
from USER_SOURCE
WHERE TYPE = 'PACKAGE BODY' AND LINE = 1
UNION
SELECT NAME SORT1, LINE SORT2,TEXT
from USER_SOURCE
WHERE TYPE = 'PACKAGE BODY' AND LINE > 1
UNION
SELECT NAME SORT1, 999999 SORT2,'/'
from USER_SOURCE
WHERE TYPE = 'PACKAGE BODY' AND LINE = 1
ORDER BY 1,2;
spool off
SPOOL bldproc.sql
select 'set echo on ' from dual;
select 'spool bldproc.lst' from dual;
select 'Remark Build procedure definitions' from
dual;
SELECT NAME SORT1, LINE SORT2,
'Create or Replace ' || TEXT
from USER_SOURCE
WHERE