Oracle DBA 常用Scripts（第一部分）！-数据库-IT落伍者

以下是我在一些网站上一点点节选出来的！我还会继续整理！先来一部分！大家觉得好我会在贴！

网站太多了请到各网站的索引都有！

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

FUNCTION numtohexchar (pi_number IN NUMBER)

RETURN CHAR

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

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

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

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

v_hex CHAR ();

v_raw RAW ();

v_rawresult RAW ();

v_hexresult VARCHAR ();

v_numresult NUMBER;

BEGIN

v_hex := hexconverter (pi_num);

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

v_hex CHAR ();

v_raw RAW ();

v_rawresult RAW ();

v_hexresult VARCHAR ();

v_numresult NUMBER;

BEGIN

v_hex := hexconverter (pi_num);

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

v_hex CHAR ();

v_raw RAW ();

v_rawresult RAW ();

v_hexresult VARCH