熱點推薦:
您现在的位置: 電腦知識網 >> 編程 >> Oracle >> 正文

Oracle DBA 常用Scripts(第一部分)!

2013-11-13 12:45:24  來源: Oracle 

  以下是我在一些網站上一點點節選出來的!我還會繼續整理!先來一部分!大家覺得好我會在貼!
  網站太多了請到各網站的索引都有!
  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
    Copyright © 2005-2013 電腦知識網 Computer Knowledge   All rights reserved.