以下是我在一些網(wǎng)站上一點點節(jié)選出來的！我還會繼續(xù)整理！先來一部分！大家覺得好，我會在貼！
網(wǎng)站太多了請到www.orakle.com各網(wǎng)站的索引都有！
Oracle DBA 常用 Script
1.Description: This script will display the active user
and the rollback segment being used in the database.
Code:
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. Description: 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.
3. Description: 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 VARCH
