'IT & 인생'에 해당되는 글 473건

set lines 200 pages 200

set pagesize 66

set pause on

set verify off

set feed off

clear screen

column sid heading 'sid' format 999

column username heading 'username' format a8

column pgm heading 'program' format a25

column terminal heading 'terminal' format a8

column pga heading 'PGA session memory' format a11

column uga heading 'UGA session memory' format a11

column pga_sum heading 'SUM PGA mem' format a12

column uga_sum heading 'SUM UGA mem' format a12

column pga_max heading 'Max PGA session memory' format a15

column uga_max heading 'Max UGA session memory' format a15

column pga_m_sum heading 'Sum Max PGA session memory' format a11

column uga_m_sum heading 'Sum Max UGA session memory' format a11

spool sess.txt

ttitle '**********< Program Global Area >**********'

ttitle '1. Current pga, uga session memory'

select a.sid, a.username, substr(a.program, 1, 25) as pgm, a.terminal,

max(decode(c.name, 'session pga memory', trunc(value/1000)||'K', 0)) pga,

max(decode(c.name, 'session uga memory', trunc(value/1000)||'K', 0)) uga

from v$session a, v$sesstat b, v$statname c

where a.sid = b.sid

and b.statistic# = c.statistic#

and c.name like 'session%'

group by a.sid, a.username, substr(a.program, 1, 25), a.terminal;

ttitle '2. Sum of current pga, uga session memory'

select 'Current PGA, UGA session memory SUM:' as sum,

sum(decode(c.name, 'session pga memory', trunc(value/1000),0))||'K' pga_sum,

sum(decode(c.name, 'session uga memory', trunc(value/1000),0))||'K' uga_sum

from v$session a, v$sesstat b, v$statname c

where a.sid = b.sid

and b.statistic# = c.statistic#

and c.name like 'session%';

ttitle '3. Max(peak) pga, pga session memory'

select a.sid, a.username, substr(a.program, 1, 25) as pgm, a.terminal,

max(decode(c.name, 'session pga memory max', trunc(value/1000)||'K', 0)) pga_max,

max(decode(c.name, 'session uga memory max', trunc(value/1000)||'K', 0)) uga_max

from v$session a, v$sesstat b, v$statname c

where a.sid = b.sid

and b.statistic# = c.statistic#

and c.name like 'session%'

group by a.sid, a.username, substr(a.program, 1, 25), a.terminal;

ttitle '4. Sum of max(peak) pga, uga session memory'

select 'Max(peak) PGA, UGA session memory SUM:' as sum,

sum(decode(c.name, 'session pga memory max', trunc(value/1000), 0))||'K' pga_m_sum,

sum(decode(c.name, 'session uga memory max', trunc(value/1000), 0))||'K' uga_m_sum

from v$session a, v$sesstat b, v$statname c

where a.sid = b.sid

and b.statistic# = c.statistic#

and c.name like 'session%';

spool off

exit

GRANT EXECUTE ON DBMS_OBFUSCATION_TOOLKIT TO system;

GRANT EXECUTE ON DBMS_CRYPTO TO system;

create or replace function TYS_Encrypt (input_string VARCHAR2) RETURN RAW

IS

   -- select DBMS_CRYPTO.RANDOMBYTES (num_key_bytes) from dual; --> key_bytes_raw

   encrypted_raw      RAW (2000);             -- stores encrypted binary text

   key_bytes_raw      RAW (32) := HEXTORAW('6E266456572A3E4C79D677BAC5144A645DF911CE07F18AEFF57C3E8560AE1672');  --암호화 키

   encryption_type    PLS_INTEGER := DBMS_CRYPTO.ENCRYPT_AES256 + DBMS_CRYPTO.CHAIN_CBC + DBMS_CRYPTO.PAD_PKCS5;  --암호화 알고리즘

BEGIN

     encrypted_raw := DBMS_CRYPTO.ENCRYPT

      (src => UTL_I18N.STRING_TO_RAW (input_string,  'AL32UTF8'),

       typ => encryption_type,

       key => key_bytes_raw);

   return encrypted_raw;

END;

/

[PACKAGE]

CREATE OR REPLACE PACKAGE SCOTT.CRYPTO_AES256

IS

/******************************************************************************

  암호화

 ******************************************************************************/

 FUNCTION ENC_AES ( input_string IN VARCHAR2

 ) RETURN VARCHAR2;

/******************************************************************************

  복호화

 ******************************************************************************/

 FUNCTION DEC_AES (  encrypted_raw IN VARCHAR2

 ) RETURN VARCHAR2;                       

END CRYPTO_AES256;

/

CREATE OR REPLACE PACKAGE BODY SCOTT.CRYPTO_AES256

IS

/******************************************************************************

  암호화

 ******************************************************************************/

 FUNCTION ENC_AES ( input_string IN VARCHAR2

 ) RETURN VARCHAR2

 IS

   encrypted_raw      RAW (2000);             -- 암호화된 RAW타입 데이터

   key_bytes_raw      RAW (32);               -- 암호화 KEY (32RAW => 32Byte => 256bit)

   encryption_type    PLS_INTEGER :=          -- 암호화 알고리즘 선언

                            DBMS_CRYPTO.ENCRYPT_AES256

                          + DBMS_CRYPTO.CHAIN_CBC

                          + DBMS_CRYPTO.PAD_PKCS5;

    BEGIN

        key_bytes_raw := UTL_I18N.STRING_TO_RAW('12345678901234567890123456789012', 'AL32UTF8');

        encrypted_raw := DBMS_CRYPTO.ENCRYPT

            (

                src => UTL_I18N.STRING_TO_RAW (input_string,  'AL32UTF8'),

                typ => encryption_type,

                key => key_bytes_raw

            );

        -- 에러 방지를 위해 base64_encode로 인코딩 처리..

        -- ORA-06502: PL/SQL: numeric or value error: hex to raw conversion error

        RETURN UTL_RAW.CAST_TO_VARCHAR2(UTL_ENCODE.BASE64_ENCODE(encrypted_raw));

    END ENC_AES;

/******************************************************************************

  복호화

 ******************************************************************************/

 FUNCTION DEC_AES (  encrypted_raw IN VARCHAR2

 ) RETURN VARCHAR2

IS

   output_string      VARCHAR2 (200);         -- 복호화된 문자열

   decrypted_raw      RAW (2000);             -- 복호화된 raw타입 데이터

   key_bytes_raw      RAW (32);               -- 256bit 암호화 key

   encryption_type    PLS_INTEGER :=          -- 복호화 알고리즘 선언

                            DBMS_CRYPTO.ENCRYPT_AES256

                          + DBMS_CRYPTO.CHAIN_CBC

                          + DBMS_CRYPTO.PAD_PKCS5;

    BEGIN

        key_bytes_raw := UTL_I18N.STRING_TO_RAW('12345678901234567890123456789012', 'AL32UTF8');

        decrypted_raw := DBMS_CRYPTO.DECRYPT

            (

                -- 에러 방지를 위해 base64_decode로 인코딩 처리..

                -- ORA-06502: PL/SQL: numeric or value error: hex to raw conversion error

                src => UTL_ENCODE.BASE64_DECODE(UTL_RAW.CAST_TO_RAW(encrypted_raw)),

                typ => encryption_type,

                key => key_bytes_raw

            );

       output_string := UTL_I18N.RAW_TO_CHAR (decrypted_raw, 'AL32UTF8');

       RETURN output_string;

    END DEC_AES;                       

END CRYPTO_AES256;

/

[Funtion]

create FUNCTION ENC_AES ( input_string IN VARCHAR2

 ) RETURN VARCHAR2

 IS

 /******************************************************************************

  암호화

 ******************************************************************************/

   encrypted_raw      RAW (2000);             -- 암호화된 RAW타입 데이터

   key_bytes_raw      RAW (32);               -- 암호화 KEY (32RAW => 32Byte => 256bit)

   encryption_type    PLS_INTEGER :=          -- 암호화 알고리즘 선언

                            DBMS_CRYPTO.ENCRYPT_AES256

                          + DBMS_CRYPTO.CHAIN_CBC

                          + DBMS_CRYPTO.PAD_PKCS5;

    BEGIN

        key_bytes_raw := UTL_I18N.STRING_TO_RAW('12345678901234567890123456789012', 'AL32UTF8');

        encrypted_raw := DBMS_CRYPTO.ENCRYPT

            (

                src => UTL_I18N.STRING_TO_RAW (input_string,  'AL32UTF8'),

                typ => encryption_type,

                key => key_bytes_raw

            );

        -- 에러 방지를 위해 base64_encode로 인코딩 처리..

        -- ORA-06502: PL/SQL: numeric or value error: hex to raw conversion error

        RETURN UTL_RAW.CAST_TO_VARCHAR2(UTL_ENCODE.BASE64_ENCODE(encrypted_raw));

    END ENC_AES;

 /

 create FUNCTION DEC_AES (  encrypted_raw IN VARCHAR2

 ) RETURN VARCHAR2

IS

/******************************************************************************

  복호화

 ******************************************************************************/

   output_string      VARCHAR2 (200);         -- 복호화된 문자열

   decrypted_raw      RAW (2000);             -- 복호화된 raw타입 데이터

   key_bytes_raw      RAW (32);               -- 256bit 암호화 key

   encryption_type    PLS_INTEGER :=          -- 복호화 알고리즘 선언

                            DBMS_CRYPTO.ENCRYPT_AES256

                          + DBMS_CRYPTO.CHAIN_CBC

                          + DBMS_CRYPTO.PAD_PKCS5;

    BEGIN

        key_bytes_raw := UTL_I18N.STRING_TO_RAW('12345678901234567890123456789012', 'AL32UTF8');

        decrypted_raw := DBMS_CRYPTO.DECRYPT

            (

                -- 에러 방지를 위해 base64_decode로 인코딩 처리..

                -- ORA-06502: PL/SQL: numeric or value error: hex to raw conversion error

                src => UTL_ENCODE.BASE64_DECODE(UTL_RAW.CAST_TO_RAW(encrypted_raw)),

                typ => encryption_type,

                key => key_bytes_raw

            );

       output_string := UTL_I18N.RAW_TO_CHAR (decrypted_raw, 'AL32UTF8');

       RETURN output_string;

    END DEC_AES;                       

/

export EDITOR=vi

echo "======================================="

echo "===Please Select the Instance Number==="

echo "======================================="

echo "1) TESTDB"

echo "2) STARTDB"

echo "3) ABCDB"

echo "What number do you want?"

read number

case $number in

        "1")

                export ORACLE_SID=TESTDB

                echo "Thank you for selecting TESTDB"

                ;;

        "2")

                export ORACLE_SID=STARTDB

                echo "Thank you for selecting STARTDB"

                ;;

        "3")

                export ORACLE_SID=ABCDB

                echo "Thank you for selecting ABCDB"

                ;;

        *)

                exit;;

esac