[Oracle] DBA 常用脚本二

- 日理万妓 2022-08-08 12:51 198阅读 0赞

9: 修改SQL提示符 Change SQL prompt

When you connect to sqlplus, you see the the following sql prompt. SQL>  
By using this sql in the glogin.sql, you will see a prompt similar to the following:  
**SCOTT@DB-01>**

Code:

The following code works on Oracle 8i (8.1.5, 8.1.6, 8.1.7).  
You have to insert the following line of code in glogin.sql which is usually found in $ORACLE\_HOME/sqlplus/admin  
  
set termout off  
set echo off  
define X=NotConnected  
define Y=DBNAME  
  
Column Usr New\_Value X  
Column DBName New\_Value Y  
Select SYS\_CONTEXT('USERENV','SESSION\_USER') Usr From Dual;  
  
\--- The following does not work in 8.1.5 but works --- in 8.1.6 or above  
Select SYS\_CONTEXT('USERENV','DB\_NAME') DBNAME From Dual;  
  
\--- If you are using 8.1.5, use this .  
Select Global\_Name DBNAME from Global\_Name;  
  
set termout on  
set sqlprompt '&X@&Y> '  
  
Please note that this method will work only when you make a new sql plus session because when you make a new sql plus session, then only glogin.sql is executed.

10: 位运算函数bitwise functions for numbers

I got sick and tired of not having bitwise functions for numbers in Oracle so I went ahead and wrote my own.

Code:  
CREATE OR REPLACE PACKAGE bitwise IS  
FUNCTION hexconverter (pi\_number IN NUMBER)  
RETURN CHAR;  
  
FUNCTION hexconverter (pi\_hexstr IN CHAR)  
RETURN NUMBER;  
  
FUNCTION bitand (pi\_num1 IN NUMBER, pi\_num2 IN NUMBER)  
RETURN NUMBER;  
  
FUNCTION bitor (pi\_num1 IN NUMBER, pi\_num2 IN NUMBER)  
RETURN NUMBER;  
  
FUNCTION bitxor (pi\_num1 IN NUMBER, pi\_num2 IN NUMBER)  
RETURN NUMBER;  
END bitwise;  
/  
  
CREATE OR REPLACE PACKAGE BODY bitwise  
IS  
FUNCTION numtohexchar (pi\_number IN NUMBER)  
RETURN CHAR  
IS  
v\_hextoreturn CHAR (1);  
BEGIN  
IF pi\_number = 0 THEN  
v\_hextoreturn := '0';  
ELSIF pi\_number = 1 THEN  
v\_hextoreturn := '1';  
ELSIF pi\_number = 2 THEN  
v\_hextoreturn := '2';  
ELSIF pi\_number = 3 THEN  
v\_hextoreturn := '3';  
ELSIF pi\_number = 4 THEN  
v\_hextoreturn := '4';  
ELSIF pi\_number = 5 THEN  
v\_hextoreturn := '5';  
ELSIF pi\_number = 6 THEN  
v\_hextoreturn := '6';  
ELSIF pi\_number = 7 THEN  
v\_hextoreturn := '7';  
ELSIF pi\_number = 8 THEN  
v\_hextoreturn := '8';  
ELSIF pi\_number = 9 THEN  
v\_hextoreturn := '9';  
ELSIF pi\_number = 10 THEN  
v\_hextoreturn := 'A';  
ELSIF pi\_number = 11 THEN  
v\_hextoreturn := 'B';  
ELSIF pi\_number = 12 THEN  
v\_hextoreturn := 'C';  
ELSIF pi\_number = 13 THEN  
v\_hextoreturn := 'D';  
ELSIF pi\_number = 14 THEN  
v\_hextoreturn := 'E';  
ELSIF pi\_number = 15 THEN  
v\_hextoreturn := 'F';  
ELSE  
raise\_application\_error (-20000, 'Invalid value', TRUE);  
END IF;  
  
RETURN v\_hextoreturn;  
END numtohexchar;  
  
FUNCTION hexchartonum (pi\_hexchar IN CHAR)  
RETURN NUMBER  
IS  
v\_numtoreturn NUMBER (2);  
BEGIN  
IF pi\_hexchar = '0' THEN  
v\_numtoreturn := 0;  
ELSIF pi\_hexchar = '1' THEN  
v\_numtoreturn := 1;  
ELSIF pi\_hexchar = '2' THEN  
v\_numtoreturn := 2;  
ELSIF pi\_hexchar = '3' THEN  
v\_numtoreturn := 3;  
ELSIF pi\_hexchar = '4' THEN  
v\_numtoreturn := 4;  
ELSIF pi\_hexchar = '5' THEN  
v\_numtoreturn := 5;  
ELSIF pi\_hexchar = '6' THEN  
v\_numtoreturn := 6;  
ELSIF pi\_hexchar = '7' THEN  
v\_numtoreturn := 7;  
ELSIF pi\_hexchar = '8' THEN  
v\_numtoreturn := 8;  
ELSIF pi\_hexchar = '9' THEN  
v\_numtoreturn := 9;  
ELSIF pi\_hexchar = 'A' THEN  
v\_numtoreturn := 10;  
ELSIF pi\_hexchar = 'B' THEN  
v\_numtoreturn := 11;  
ELSIF pi\_hexchar = 'C' THEN  
v\_numtoreturn := 12;  
ELSIF pi\_hexchar = 'D' THEN  
v\_numtoreturn := 13;  
ELSIF pi\_hexchar = 'E' THEN  
v\_numtoreturn := 14;  
ELSIF pi\_hexchar = 'F' THEN  
v\_numtoreturn := 15;  
ELSE  
raise\_application\_error (-20000, 'Invalid value', TRUE);  
END IF;  
  
RETURN v\_numtoreturn;  
END hexchartonum;  
  
FUNCTION hexconverter (pi\_number IN NUMBER)  
RETURN CHAR  
IS  
i NUMBER;  
v\_digit NUMBER (2);  
v\_hexstr VARCHAR2 (16);  
BEGIN  
v\_hexstr := ;  
FOR i IN REVERSE 0 .. 15  
LOOP  
v\_digit := MOD (TRUNC (pi\_number / POWER (16, i)), 16);  
v\_hexstr := v\_hexstr || numtohexchar (v\_digit);  
END LOOP;  
  
RETURN v\_hexstr;  
END hexconverter;  
  
FUNCTION hexconverter (pi\_hexstr IN CHAR)  
RETURN NUMBER  
IS  
i NUMBER;  
v\_digit NUMBER (2);  
v\_numtoreturn NUMBER;  
v\_hexstr16 CHAR (16);  
BEGIN  
v\_hexstr16 := LPAD (pi\_hexstr, 16, '0');  
v\_numtoreturn := 0;  
  
FOR i IN 0 .. 16  
LOOP  
V\_digit := hexchartonum (SUBSTR (v\_hexstr16, i, 1));  
v\_numtoreturn := v\_numtoreturn + v\_digit \* POWER (16, 16 - i);  
END LOOP;  
  
RETURN v\_numtoreturn;  
END hexconverter;  
  
FUNCTION bitand (pi\_num1 IN NUMBER, pi\_num2 IN NUMBER)  
RETURN NUMBER  
IS  
v\_hex1 CHAR (16);  
v\_hex2 CHAR (16);  
v\_raw1 RAW (8);  
v\_raw2 RAW (8);  
v\_rawresult RAW (8);  
v\_hexresult VARCHAR2 (16);  
v\_numresult NUMBER;  
BEGIN  
v\_hex1 := hexconverter (pi\_num1);  
v\_hex2 := hexconverter (pi\_num2);  
v\_raw1 := HEXTORAW (v\_hex1);  
v\_raw2 := HEXTORAW (v\_hex2);  
v\_rawresult := UTL\_RAW.bit\_and (v\_raw1, v\_raw2);  
v\_hexresult := RAWTOHEX (v\_rawresult);  
v\_numresult := hexconverter (v\_hexresult);  
RETURN v\_numresult;  
END bitand;  
  
FUNCTION bitor (pi\_num1 IN NUMBER, pi\_num2 IN NUMBER)  
RETURN NUMBER  
IS  
v\_hex1 CHAR (16);  
v\_hex2 CHAR (16);  
v\_raw1 RAW (8);  
v\_raw2 RAW (8);  
v\_rawresult RAW (8);  
v\_hexresult VARCHAR2 (16);  
v\_numresult NUMBER;  
BEGIN  
v\_hex1 := hexconverter (pi\_num1);  
v\_hex2 := hexconverter (pi\_num2);  
v\_raw1 := HEXTORAW (v\_hex1);  
v\_raw2 := HEXTORAW (v\_hex2);  
v\_rawresult := UTL\_RAW.bit\_or (v\_raw1, v\_raw2);  
v\_hexresult := RAWTOHEX (v\_rawresult);  
v\_numresult := hexconverter (v\_hexresult);  
RETURN v\_numresult;  
END bitor;  
  
FUNCTION bitxor (pi\_num1 IN NUMBER, pi\_num2 IN NUMBER)  
RETURN NUMBER  
IS  
v\_hex1 CHAR (16);  
v\_hex2 CHAR (16);  
v\_raw1 RAW (8);  
v\_raw2 RAW (8);  
v\_rawresult RAW (8);  
v\_hexresult VARCHAR2 (16);  
v\_numresult NUMBER;  
BEGIN  
v\_hex1 := hexconverter (pi\_num1);  
v\_hex2 := hexconverter (pi\_num2);  
v\_raw1 := HEXTORAW (v\_hex1);  
v\_raw2 := HEXTORAW (v\_hex2);  
v\_rawresult := UTL\_RAW.bit\_xor (v\_raw1, v\_raw2);  
v\_hexresult := RAWTOHEX (v\_rawresult);  
v\_numresult := hexconverter (v\_hexresult);  
RETURN v\_numresult;  
END bitxor;  
END bitwise;  
/

11: 检查数据库警告日志 Check error messages in alert log file

This Script search for Oracle error messages in last 100 lines in the alert log file , send a email message to concerned and keep log to a file.  
You should pass name of ORACLE\_SID as a parameter.  
For eg: Ck\_alerlog FINL

Code:  
\#!/usr/bin/sh  
\# Script Type: Shell (Bourne)  
\#  
\# Script name: ck\_alertlog.sh  
\#  
\# Date: Thu 06 06 2000  
\# Author: Thomas Kuruvilla  
\# Comments: Script checks last 100 lines of  
\# the alert log for specific  
\# Oracle errors, then pages or e-mails depending \#on the error.  
\# Parameter: ORACLE\_SID  
\# -------------------  
\# Revision Log  
\#  
\# 00/00/00 : Name of modifier - description  
\#of Modifications  
\#  
\#--------------------------------------------------  
\#-------------------------  
SID=$1  
DIR=/usr/local/bin/dbabin  
ORACLE\_SID=$SID; export ORACLE\_SID  
ORACLE\_HOME=\`grep -v "^\[\#\]" /var/opt/oracle/oratab|grep $ORACLE\_SID|cut -d: -f2  
export ORACLE\_HOME  
cd $ORACLE\_HOME/../..  
  
ALERT\_DEST=\`pwd\`  
ALERT\_DEST=$ALERT\_DEST/admin/$SID/bdump  
LD\_LIBRARY\_PATH=$ORACLE\_HOME/lib:$LD\_LIBRARY\_PATH; export LD\_LIBRARY\_PATH  
PATH=$ORACLE\_HOME/bin:$PATH; export PATH  
COLLECTOR=\`tail -100 $ALERT\_DEST/alert\_$SID.log |egrep "ORA-255 |ORA-255:|ORA-0255|ORA-214|ORA-214:|ORA-00214|ORA-270|ORA-270:|ORA-00270|ORA-272|ORA-272:|ORA-00272|ORA-600|ORA-600:|ORA-00600|ORA-1122|ORA-1122:|ORA-01122|ORA-1578|ORA1578:|ORA-01578|ORA-1628|ORA-1628:|ORA-01628|ORA-1630|ORA-1630:|ORA-01630|ORA1631|ORA-1631:|ORA-01631|ORA-1632|ORA-1632:|ORA-01632|ORA-1650|ORA-1650:|ORA01650|ORA-1652|ORA-1652:|ORA-01652|ORA-1653|ORA-1653:|ORA-01653|ORA-1654|ORA1654:|ORA-01654|ORA-1655|ORA-1655:|ORA-01655"\`  
for i in $COLLECTOR  
do  
ORAERR=\`echo $i | grep ORA-...\`  
if \[ $ORAERR \] then  
i=\`echo $i | awk '  
\{  
print $1  
\}'\`  
y=\`cat $DIR/tmpalert$SID.log|egrep -ch$i\`  
if \[ $y -ge 2 \] then  
continue  
else  
echo $i Paged at \`date '+%m/%d/%y %H:%M'\` >> $DIR/tmpalert$SID.log  
echo "Oracle errors in alert log on $SID" | /usr/bin/mailx -s "check aler\_$SID.log" [xxx@myorg.com][xxx_myorg.com];  
fi;  
fi;  
\--Done

12: 导出表数据 Export table data into text file

Creating a ascii text file of oracle table data with pipe delimited columns.

Code:  
\#!/bin/ksh  
\#First line in the .txt file is column names  
USR=username/password  
echo "set feedback off/n set pagesize 0/n  
select 'XXTT' || table\_name from user\_tables ;" | sqlplus -s $USR | grep '^XXTT' | cut -c5- |  
while read TABL  
do  
STR="nothing"  
STR1="nothing"  
echo "desc $TABL/n" | sqlplus -s $USR | tail +3 | grep "^\[A-Z,a-z\]" | awk '\{print $1\}' |  
while read COLUM  
do  
if \[ "$STR" = "nothing" \] then  
STR=$COLUM  
STR1=$COLUM  
else  
STR="$STR || '|' || $COLUM"  
STR1="$STR1|$COLUM"  
fi  
Done  
echo "$STR1" > $TABL.txt  
echo "set feedback off/nset pagesize 0/nset linesize 2000/nselect $STR from $TABL;"| sqlplus -s $USR >> $TABL.txt  
Done

13: 查看数据库文件架构和大小 map out the database

Maps out the database physically with sizes and paths of all the physical files .It will give the names and sizes of rollback segments also..

Code:  
spool dbmap.rpt  
start title132.sql "Database Layout " "dbmap.sql"  
prompt ================================  
prompt Tablespace/Datafile Listing  
prompt ================================  
prompt  
prompt  
column "Location" format A60;  
column "Tablespace Name" format A15;  
column "Size(M)" format 999,990;  
  
break on "Tablespace Name" skip 1 nodup;  
compute sum of "Size(M)" on "Tablespace Name";  
SELECT tablespace\_name "Tablespace Name",  
file\_name "Location", bytes/1048576"Size(M)"  
FROM sys.dba\_data\_files  
Order by tablespace\_name;  
Prompt  
Prompt ================================  
Prompt Redo Log Listing  
Prompt ================================  
Prompt  
Prompt  
column "Group" format 999;  
column "File Location" format A50;  
column "Bytes (M)" format 99,990;  
  
break on "Group" skip 1 nodup;  
  
Select a.group\# "Group", b.member "File  
Location", (a.bytes/1024) "Bytes (K)"  
From v$log a, v$logfile b  
Where a.group\# = b.group\#  
Order by 1,2;  
Prompt  
Prompt ================================  
Prompt Control File Listing  
Prompt ================================  
Prompt  
Prompt  
column name format A80 heading "CONTROL FILE  
NAME"  
column status format a10 heading "STATUS"  
Select name,status  
From v$controlfile;  
Prompt  
Prompt ================================  
Prompt Rollback Listing  
Prompt ================================  
Prompt  
Prompt  
column "Segment Name" format A15;  
column "Tablespace" format A15;  
Column "Initial (M)" Format 99,990;  
Column "Next (M)" Format 99,990;  
column "Min Ext." FORMAT 9999;  
column "Max Ext." FORMAT 99999999999;  
column "Status" Format A7;  
  
Select segment\_name "Segment Name",  
tablespace\_name "Tablespace",  
(initial\_extent/1024)/1024 "Initial (M)",  
(next\_extent/1024)/1024 "Next (M)",  
min\_extents "Min Ext.",  
max\_extents "Max Ext.",  
status "Status"  
From sys.dba\_rollback\_segs  
Order by tablespace\_name, segment\_name;  
Spool Off;

14: 检测可能出现的“表空间扩展失败”情况. Detects possible next extent failures

Select  
substr(sg.tablespace\_name,1,30)Tablespace,  
substr(sg.segment\_name,1,30) Object,  
sg.extents extents,  
sg.next\_extent next,  
max(sp.bytes) available  
From dba\_free\_space sp, dba\_segments sg  
Where sp.tablespace\_name = sg.tablespace\_name  
Having max(sp.bytes) < sg.next\_extent  
Group by sg.tablespace\_name,  
sg.segment\_name, sg.extents, sg.next\_extent  
Order by 1,2;

15: 查看数据库运行时长. Displays DB up time

Displays DB start time, and calculates how long the DB has been up.

Code:  
set head off pages 0  
Select  
'Current System time and date is '||  
To\_Char(sysdate,'HH24:MI:SS " on " ddth  
FMMonth YYYY')||chr(10)||'Database  
'||NAME||' has been up since '||  
To\_Char(To\_Date(a.value||b.value,'JSSSSS')  
,'HH24:MI:SS " on " ddth FMMonth YYYY')  
||chr(10)||' ( Which is  
'||Trunc(sysdate-to\_date(a.value||b.value,'JSSSSS'))||' days '||To\_Char(To\_Date(trunc(86400\*(  
(sysdate-to\_date(a.value||b.value,'JSSSSS')  
)-trunc(sysdate-to\_date(a.value||b.value,'JSSSS'  
)))),'SSSSS')  
,'HH24 "hours" MI "mins and" SS "seconds )"')  
from v$instance a,v$instance b,v$database  
where a.key='STARTUP TIME - JULIAN'  
and b.key='STARTUP TIME - SECONDS';

16: 监控用户和用户执行的SQL命令 Monitor the user

Displays the user and the text of the statement the user is executing

Code:  
SELECT OSUSER,SERIAL\#,SQL\_TEXT  
FROM V$SESSION, V$SQL  
WHERE  
V$SESSION.SQL\_ADDRESS=V$SQL.ADDRESS AND V$SESSION.STATUS = 'ACTIVE';

[xxx_myorg.com]: mailto:xxx@myorg.com