[Oracle] DBA 常用脚本一

桃扇骨 2022-08-08 12:50 193阅读 0赞

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 TYPE = 'PROCEDURE' AND LINE = 1  
UNION  
SELECT NAME SORT1, LINE SORT2, TEXT  
FROM USER\_SOURCE  
WHERE TYPE = 'PROCEDURE' AND LINE>1  
UNION  
SELECT NAME SORT1, 999999 SORT2,'/'  
FROM USER\_SOURCE  
WHERE TYPE = 'PROCEDURE' AND LINE = 1  
ORDER BY 1,2;  
  
select 'Remark Build function definitions' from  
dual;  
  
SELECT NAME SORT1, LINE SORT2,  
'Create or Replace ' || TEXT  
FROM USER\_SOURCE  
WHERE TYPE = 'FUNCTION' AND LINE = 1  
UNION  
SELECT NAME SORT1, LINE SORT2, TEXT  
FROM USER\_SOURCE  
WHERE TYPE = 'FUNCTION' AND LINE > 1  
UNION  
SELECT NAME SORT1, 999999 SORT2,'/'  
FROM USER\_SOURCE  
WHERE TYPE = 'FUNCTION' AND LINE = 1  
ORDER BY 1,2;  
spool off

3: 查看视图源码 Show views, Extract the code for views from the database

SET HEADING OFF  
SET ECHO OFF  
SET FEEDBACK OFF  
SET PAGESIZE 0  
set numwidth 10  
  
SELECT uv.view\_name SORT1, 0 SORT2, 0 SORT3,  
0 SORT4,  
'create or replace view '||uv.view\_name ||' ('  
from dba\_views uv  
WHERE uv.owner = upper('&2')  
and uv.view\_name = upper('&1')  
union all  
SELECT utc.view\_name SORT1, utc.column\_id  
SORT2, 0 SORT3, 0 SORT4,utc.column\_name  
from dba\_tab\_columns  
WHERE utc.owner = upper('&2')  
and utc.table\_name = upper('&1')  
and utc.column\_id = 1  
union all  
SELECT utc.view\_name SORT1, utc.column\_id  
SORT2, 0 SORT3, 0 SORT4,  
' , '||utc.column\_name  
from dba\_tab\_columns  
WHERE utc.owner = upper('&2')  
and utc.table\_name = upper('&1')  
and utc.column\_id <> 1  
SELECT uv.view\_name SORT1,999 SORT2,0  
SORT3,0 SORT4,' )'  
from dba\_views uv  
WHERE uv.owner = upper('&2')  
and uv.view\_name = upper('&1')  
ORDER BY 1, 2, 3, 4;  
SELECT uv.text  
from dba\_views uv  
WHERE uv.owner = upper('&2')  
and uv.view\_name = upper('&1');  
SELECT uv.view\_name SORT1,999 SORT2,0  
SORT3,0 SORT4,' ;'  
from dba\_views uv  
WHERE uv.owner = upper('&2')  
and uv.view\_name = upper('&1');

4: 监控文件IO性能 Monitor File I/O

This script takes a snapshot of v$filestats at the current time and saves it. It then waits 10 seconds and takes another snapshot and reports on the delta.

Code:

col name for a50  
set linesize 132  
set pages 666  
\-- drop temporary table  
drop table jh$filestats;  
create table jh$filestats as  
select file\#, PHYBLKRD, PHYBLKWRT  
from v$filestat;  
prompt Waiting......  
exec dbms\_lock.sleep(10);  
  
prompt NOTE: Only the top 10 files...  
select \* from (  
select df.name, fs.phyblkrd - t.phyblkrd "Reads",fs.PHYBLKWRT - t.PHYBLKWRT "Writes",(fs.PHYBLKRD+fs.PHYBLKWRT) - (t.PHYBLKRD+t.PHYBLKWRT) "Total IO"  
from v$filestat fs, v$datafile df, jh$filestats t  
where df.file\# = fs.file\# and t.file\# = fs.file\#  
and (fs.PHYBLKRD+fs.PHYBLKWRT) - (t.PHYBLKRD+t.PHYBLKWRT) > 0  
order by "Total IO" desc )  
where rownum <= 10  
/

5: 获取表中2个最大值Finds the two highest salaries from table

(1). Finds the two highest salaries from table "emp".

Code:  
select a.empno,a.sal  
from emp a  
where 2>  
(  
select count(\*)  
from emp  
where sal>a.sal  
);

(2). Finds the two lowest salaries from table "emp".

Code:  
select a.empno,a.sal  
from emp a  
where 2>  
(  
select count(\*)  
from emp  
where sal<a.sal);

6: 给定父表查询出它的子表Finds Child Tables for a given parent table

set echo off  
set verify off  
accept xTable prompt 'Enter Table Name: '  
TTITLE LEFT 'Child Tables for the table: '&xTABLE  
break on TABLE\_NAME  
SELECT B.TABLE\_NAME, C.COLUMN\_NAME,  
C.POSITION  
FROM USER\_CONSTRAINTS A,  
USER\_CONSTRAINTS B,  
USER\_CONS\_COLUMNS C  
WHERE  
A.CONSTRAINT\_NAME=  
B.R\_CONSTRAINT\_NAME  
AND  
A.TABLE\_NAME = C.TABLE\_NAME  
AND  
A.CONSTRAINT\_NAME = C.CONSTRAINT\_NAME  
AND  
A.TABLE\_NAME = UPPER('&xTable')  
ORDER BY B.TABLE\_NAME, C.POSITION;  
  
TTITLE LEFT 'Parent tables for the table: '&xTable  
  
SELECT A.TABLE\_NAME, C.COLUMN\_NAME,  
C.POSITION  
FROM USER\_CONSTRAINTS A,  
USER\_CONSTRAINTS B,  
USER\_CONS\_COLUMNS C  
WHERE  
A.CONSTRAINT\_NAME=B.R\_CONSTRAINT\_NAME  
AND  
B.TABLE\_NAME = C.TABLE\_NAME  
AND  
B.CONSTRAINT\_NAME = C.CONSTRAINT\_NAME  
AND  
B.TABLE\_NAME = UPPER('&xTable')  
ORDER BY A.TABLE\_NAME, C.POSITION;

7: 查看约束的相关信息 Returns information about the constraint

Script prompts for a constraint name then it returns information about the constraint.

Code:  
\-- displays constraint info  
\-- created by Noah Monsey  
\-- 02/02/2000  
set lines 120  
set serveroutput on size 100000  
set scan on  
clear buffer  
set verify off  
set feedback off  
declare  
  
l\_constraint\_name varchar2(30);  
l\_constraint\_type varchar2(1);  
l\_table\_name varchar2(30);  
l\_search\_condition long;  
l\_related\_constraint varchar2(30);  
l\_related\_table varchar2(30);  
l\_rcolumn varchar2(30);  
l\_column varchar2(30);  
l\_query varchar2(400);  
l\_column\_count integer;  
  
cursor lc\_constraint (p\_constraint\_name varchar2) is  
select \*  
from dba\_constraints  
where constraint\_name = p\_constraint\_name;  
  
cursor lc\_related\_constraint(p\_r\_constraint\_name varchar2)  
is  
select table\_name from dba\_constraints  
where constraint\_name = p\_r\_constraint\_name;  
  
cursor lc\_columns (p\_constraint\_name varchar2) is  
select column\_name from dba\_cons\_columns  
where constraint\_name = p\_constraint\_name;  
begin  
dbms\_output.enable(1000000);  
for rec\_constraint in  
lc\_constraint(upper('&constraint\_name'))  
loop  
l\_constraint\_type := null;  
l\_table\_name := null;  
l\_related\_constraint := null;  
l\_search\_condition := null;  
l\_constraint\_type :=  
rec\_constraint.constraint\_type;  
l\_table\_name :=  
rtrim(rec\_constraint.table\_name);  
l\_related\_constraint :=  
rec\_constraint.r\_constraint\_name;  
l\_search\_condition :=  
rec\_constraint.search\_condition;  
l\_constraint\_name :=  
rec\_constraint.constraint\_name;  
l\_column\_count := 0;  
dbms\_output.put\_line(  
);  
if l\_constraint\_type = 'P' then  
dbms\_output.put\_line(l\_constraint\_name || ' is a primary key constraint on table ' || l\_table\_name);  
open lc\_columns(l\_constraint\_name);  
fetch lc\_columns into l\_column;  
dbms\_output.put\_line('Column ' ||  
l\_column);  
close lc\_columns;  
end if;  
if l\_constraint\_type = 'U' then  
dbms\_output.put\_line(l\_constraint\_name || ' is a unique constraint on table ' || l\_table\_name);  
for rec\_columns in  
lc\_columns(l\_constraint\_name)  
loop  
dbms\_output.put\_line('Column ' ||  
rec\_columns.column\_name);  
end loop;  
end if;  
if l\_constraint\_type = 'C' then  
dbms\_output.put\_line(l\_constraint\_na  
me || ' is a check constraint ' ||  
rtrim(l\_table\_name) || ' where '||  
l\_search\_condition);  
end if;  
if l\_constraint\_type = 'R' then  
open  
lc\_related\_constraint(l\_related\_constr  
aint);  
fetch lc\_related\_constraint into  
l\_related\_table;  
dbms\_output.put\_line('Table  
'||l\_table\_name ||' referential  
constraint '|| l\_related\_constraint ||'  
failed because ');  
l\_query := ' select ' ;  
l\_column\_count := 0;  
open lc\_columns(l\_constraint\_name);  
fetch lc\_columns into l\_column;  
l\_query := l\_query || ' ' || rtrim(l\_column);  
if l\_column\_count > 1 then  
l\_query := l\_query || ',';  
end if;  
l\_column\_count := l\_column\_count +1;  
dbms\_output.put\_line('Column'||l\_column);  
close lc\_columns;  
l\_query := l\_query || 'from'|| l\_table\_name || ' minus select ' ;  
l\_column\_count := 0;  
dbms\_output.put\_line('Table'|| l\_related\_table||' is missing related values ');  
open  
lc\_columns(l\_related\_constraint);  
fetch lc\_columns into l\_rcolumn;  
l\_query := l\_query || ' ' ||  
rtrim(l\_rcolumn);  
if l\_column\_count > 1 then  
l\_query := l\_query || ',';  
end if;  
l\_column\_count := l\_column\_count +1;  
dbms\_output.put\_line('Column'||l\_rcolumn);  
close lc\_columns;  
l\_query := l\_query || ' from ' || rtrim(l\_related\_table) ||';';  
dbms\_output.put\_line(l\_query);  
close lc\_related\_constraint;  
end if;  
if l\_constraint\_type not in ('C','P','U','R') then  
dbms\_output.put\_line(l\_table\_name||'  
has a unknown constraint type');  
end if;  
end loop;  
end;  
/  
set feedback on

8: 找出表中重复的记录Find out the duplicate records in a Table

Code:  
SELECT primary\_key FROM table\_name  
MINUS  
(  
SELECT DISTINCT primary\_key  
FROM table\_name  
)  
  
You can also use \* instead of the primary\_key.  
18. Description: This Script will help you to find out which Database you are presently  
Connected to? If You are working on multiple Databases on different Servers using SQL\*Net or Net8.  
Code:  
  
select distinct A.name DATABASE,  
B.machine SERVER  
from V$database A, V$session B  
where b.username is null  
/