Oracle DBA 常用Scripts（第一部分）！

　　以下是我在一些网站上一点点节选出来的！我还会继续整理！先来一部分！大家觉得好，我会在贴！
　　网站太多了请到www.orakle.com各网站的索引都有！
　　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

查看本文来源