[Oracle] DBA 常用脚本二

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;
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';