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

　　以下是我在一些網站上一點點節選出來的！我還會繼續整理！先來一部分！大家覺得好我會在貼！
　　網站太多了請到各網站的索引都有！
　　Oracle DBA 常用 Script
　　Description: This script will display the active user
　　and the rollback segment being used in the database
　　Code:
　　column rr heading RB Segment format a
　　column us heading Username format a
　　column os heading OS User format a
　　column te heading Terminal format a
　　
　　SELECT rname rr
　　nvl(susernameno transaction) us
　　sosuser os
　　sterminal te
　　FROM
　　v$lock l
　　v$session s
　　v$rollname r
　　WHERE
　　lsid = ssid(+) AND
　　trunc(lid/) = rusn AND
　　ltype = TX AND
　　llmode =
　　ORDER BY rname
　　/
　　 Description: When you connect to sqlplus you see the the following sql prompt SQL>
　　By using this sql in the gloginsql you will see a prompt similar to the following:
　　SCOTT@DB>
　　Code:
　　
　　The following code works on Oracle i ( )
　　You have to insert the following line of code in gloginsql 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(USERENVSESSION_USER) Usr From Dual;
　　
　　 The following does not work in but works in or above
　　Select SYS_CONTEXT(USERENVDB_NAME) DBNAME From Dual;
　　
　　 If you are using 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 gloginsql is executed
　　 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_num IN NUMBER pi_num IN NUMBER)
　　RETURN NUMBER;
　　
　　FUNCTION bitor (pi_num IN NUMBER pi_num IN NUMBER)
　　RETURN NUMBER;
　　
　　FUNCTION bitxor (pi_num IN NUMBER pi_num 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 ();
　　BEGIN
　　IF pi_number = THEN
　　v_hextoreturn := ;
　　ELSIF pi_number = THEN
　　v_hextoreturn := ;
　　ELSIF pi_number = THEN
　　v_hextoreturn := ;
　　ELSIF pi_number = THEN
　　v_hextoreturn := ;
　　ELSIF pi_number = THEN
　　v_hextoreturn := ;
　　ELSIF pi_number = THEN
　　v_hextoreturn := ;
　　ELSIF pi_number = THEN
　　v_hextoreturn := ;
　　ELSIF pi_number = THEN
　　v_hextoreturn := ;
　　ELSIF pi_number = THEN
　　v_hextoreturn := ;
　　ELSIF pi_number = THEN
　　v_hextoreturn := ;
　　ELSIF pi_number = THEN
　　v_hextoreturn := A;
　　ELSIF pi_number = THEN
　　v_hextoreturn := B;
　　ELSIF pi_number = THEN
　　v_hextoreturn := C;
　　ELSIF pi_number = THEN
　　v_hextoreturn := D;
　　ELSIF pi_number = THEN
　　v_hextoreturn := E;
　　ELSIF pi_number = THEN
　　v_hextoreturn := F;
　　ELSE
　　raise_application_error ( Invalid value TRUE);
　　END IF;
　　
　　RETURN v_hextoreturn;
　　END numtohexchar;
　　
　　FUNCTION hexchartonum (pi_hexchar IN CHAR)
　　RETURN NUMBER
　　IS
　　v_numtoreturn NUMBER ();
　　BEGIN
　　IF pi_hexchar = THEN
　　v_numtoreturn := ;
　　ELSIF pi_hexchar = THEN
　　v_numtoreturn := ;
　　ELSIF pi_hexchar = THEN
　　v_numtoreturn := ;
　　ELSIF pi_hexchar = THEN
　　v_numtoreturn := ;
　　ELSIF pi_hexchar = THEN
　　v_numtoreturn := ;
　　ELSIF pi_hexchar = THEN
　　v_numtoreturn := ;
　　ELSIF pi_hexchar = THEN
　　v_numtoreturn := ;
　　ELSIF pi_hexchar = THEN
　　v_numtoreturn := ;
　　ELSIF pi_hexchar = THEN
　　v_numtoreturn := ;
　　ELSIF pi_hexchar = THEN
　　v_numtoreturn := ;
　　ELSIF pi_hexchar = A THEN
　　v_numtoreturn := ;
　　ELSIF pi_hexchar = B THEN
　　v_numtoreturn := ;
　　ELSIF pi_hexchar = C THEN
　　v_numtoreturn := ;
　　ELSIF pi_hexchar = D THEN
　　v_numtoreturn := ;
　　ELSIF pi_hexchar = E THEN
　　v_numtoreturn := ;
　　ELSIF pi_hexchar = F THEN
　　v_numtoreturn := ;
　　ELSE
　　raise_application_error ( Invalid value TRUE);
　　END IF;
　　
　　RETURN v_numtoreturn;
　　END hexchartonum;
　　
　　FUNCTION hexconverter (pi_number IN NUMBER)
　　RETURN CHAR
　　IS
　　i NUMBER;
　　v_digit NUMBER ();
　　v_hexstr VARCHAR ();
　　BEGIN
　　v_hexstr := ;
　　FOR i IN REVERSE
　　LOOP
　　v_digit := MOD (TRUNC (pi_number / POWER ( i)) );
　　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 ();
　　v_numtoreturn NUMBER;
　　v_hexstr CHAR ();
　　BEGIN
　　v_hexstr := LPAD (pi_hexstr );
　　v_numtoreturn := ;
　　
　　FOR i IN
　　LOOP
　　V_digit := hexchartonum (SUBSTR (v_hexstr i ));
　　v_numtoreturn := v_numtoreturn + v_digit * POWER ( i);
　　END LOOP;
　　
　　RETURN v_numtoreturn;
　　END hexconverter;
　　
　　FUNCTION bitand (pi_num IN NUMBER pi_num IN NUMBER)
　　RETURN NUMBER
　　IS
　　v_hex CHAR ();
　　v_hex CHAR ();
　　v_raw RAW ();
　　v_raw RAW ();
　　v_rawresult RAW ();
　　v_hexresult VARCHAR ();
　　v_numresult NUMBER;
　　BEGIN
　　v_hex := hexconverter (pi_num);
　　v_hex := hexconverter (pi_num);
　　v_raw := HEXTORAW (v_hex);
　　v_raw := HEXTORAW (v_hex);
　　v_rawresult := UTL_RAWbit_and (v_raw v_raw);
　　v_hexresult := RAWTOHEX (v_rawresult);
　　v_numresult := hexconverter (v_hexresult);
　　RETURN v_numresult;
　　END bitand;
　　
　　FUNCTION bitor (pi_num IN NUMBER pi_num IN NUMBER)
　　RETURN NUMBER
　　IS
　　v_hex CHAR ();
　　v_hex CHAR ();
　　v_raw RAW ();
　　v_raw RAW ();
　　v_rawresult RAW ();
　　v_hexresult VARCHAR ();
　　v_numresult NUMBER;
　　BEGIN
　　v_hex := hexconverter (pi_num);
　　v_hex := hexconverter (pi_num);
　　v_raw := HEXTORAW (v_hex);
　　v_raw := HEXTORAW (v_hex);
　　v_rawresult := UTL_RAWbit_or (v_raw v_raw);
　　v_hexresult := RAWTOHEX (v_rawresult);
　　v_numresult := hexconverter (v_hexresult);
　　RETURN v_numresult;
　　END bitor;
　　
　　FUNCTION bitxor (pi_num IN NUMBER pi_num IN NUMBER)
　　RETURN NUMBER
　　IS
　　v_hex CHAR ();
　　v_hex CHAR ();
　　v_raw RAW ();
　　v_raw RAW ();
　　v_rawresult RAW ();
　　v_hexresult VARCH
From:http://tw.wingwit.com/Article/program/Oracle/201311/16529.html