PostgreSQL: Script to Kill Sessions

pg_terminate_backend() AND pg_cancel_backend()

1) kill all connections  of a database name.

-- List all available databases

postgres=>\l

postgres=> SELECT pg_terminate_backend(pg_stat_activity.pid)
           FROM   pg_stat_activity
           WHERE  datname = 'DATABASE NAME'
              AND pid <> pg_backend_pid();

2) kill all connections  of a specified user name.

postgres=> SELECT pg_terminate_backend(pg_stat_activity.pid)
             FROM pg_stat_activity
            WHERE usename = 'james'
              AND pid <> pg_backend_pid();

3)kill all sessions of current database.

postgres=> SELECT pg_terminate_backend(pg_stat_activity.pid)
             FROM pg_stat_activity
            WHERE datname = current_database()
              AND pid <> pg_backend_pid();

4) kill a connections  of a specified pid.

postgres=> SELECT pg_terminate_backend(pid);

5) kill a running SQL of a session, but does not kill the session.

postgres=> SELECT pg_cancel_backend( pid );

It is useful when a running SQL is taking long time than expected.

Advertisements
Posted in pg_cancel_backend(), SQL Scripts for DBA | Tagged , , , | Leave a comment

Drop a Role with Dependent Objects in PostgreSQL

Try to drop a role/user in PostgreSQL, and get below errors:

postgres=>drop role james_read;
ERROR: role "james_read" cannot be dropped because some objects depend on it
DETAIL: privileges for table user1.tab1
privileges for table table user1.tab1
privileges for sequence user1.seq1
...
..
.

SOLUTION

1)Revoke objects privileges from this role/user which is to be dropped:

postgres=> select grantee,table_schema,table_name,privilege_type
            from information_schema.role_table_grants
           where grantee='james_read' 
           order by table_name,privilege_type;
grantee    | table_schema | table_name | privilege_type
-----------+--------------+------------+----------------
james_read |    user1     | tab1       | SELECT
james_read |    user1     | tab2       | SELECT
...
..
.

postgres=> select 'revoke '||privilege_type ||' on '||
           table_schema||'.'||table_name||' from '||grantee||' ;'
from information_schema.role_table_grants
where grantee='james_read' ;

-----------------------------------------------------------------------------
 revoke SELECT on user1.tab1 from james_read ;
 revoke SELECT on user1.tab2 from james_read ;
...
..
.

OR just ...

postgres=>REVOKE ALL PRIVILEGES ON ALL TABLES IN SCHEMA user1 
          FROM james_read; 

postgres=>REVOKE ALL PRIVILEGES ON ALL SEQUENCES IN SCHEMA user1
          FROM james_read; 

postgres=>alter default privileges in schema james revoke select  
          on tables from james_read;

postgres=>alter default privileges in schema james revoke select ,  
          usage on sequences from james_read;

2) Revoke depended roles.

\du to find dependent roles

postgres=>\du
postgres=>revoke other_role from james_read;

3) Drop the role.

postgres=> drop role james_read;
ERROR:  role "james_read" cannot be dropped because some objects depend on it
DETAIL:  privileges for default privileges on new sequences belonging to role james in schema james
privileges for default privileges on new relations belonging to role james in schema james

postgres=> ALTER DEFAULT PRIVILEGES FOR ROLE james IN SCHEMA james REVOKE ALL ON TABLES FROM james_read;
ALTER DEFAULT PRIVILEGES
postgres=> ALTER DEFAULT PRIVILEGES FOR ROLE james IN SCHEMA james REVOKE ALL ON SEQUENCES FROM james_read;
ALTER DEFAULT PRIVILEGES
postgres=> drop role james_read;
DROP ROLE
postgres=>
Posted in Privileges | Tagged , , , , | Leave a comment

Create Materialised View Log on the Remote Database

DB1 is a local database, on which the materialised views are to be created. DB2 is a remote database, on which materialised view logs are created .

The high level steps are:

  • on local database DB1, create a database link between DB1 and DB2 database.
  • On the remote database DB2, create materialised logs on the tables of DB2.
  • On local database DB1, create materialised views with Fast Refresh option , etc.
  • On Local database DB1, run a complete refresh on your materialised views:          SQL>dbms_snapshot.refresh(‘schema_name.mview_name’,’c’);
  • Then on local database Db1 again ,  refresh the materialised  view using the Fast Refresh option: dbms_snapshot.refresh(‘schema_name.mview_name’,’f’);
Posted in Configuration, Performance Tunning | Tagged , , , | Leave a comment

AWS DMS Validation Pending for Some Tables

While running an AWS DMS task, some tables records are fully loaded into target database. From Table Statistics, it shows some tables validations are pending forever without progressing at all.

Check “Migration task logs”, it just says :

[TASK_MANAGER]I:All tables are loaded.waiting for validation to finish... 
                                     (replicationtask.c:1234)

RESOLUTION

  •  The following permission is required when validating tables with LOBs.
SQL>grant execute on sys.dbms_crypto to <dms_endpoint_user>;
Posted in AWS | Tagged , , , , , , | Leave a comment

ORA-02800: Requests timed out from Oracle Scheduler Job

Get below errors from running an Oracle scheduler job:

SQL> exec dbms_scheduler.run_job(job_name=>'BATCH_JOB', 
                                 use_current_session=>false);
BEGIN dbms_scheduler.run_job(job_name=>'BATCH_JOB', 
                                 use_current_session=>false); END;

*
ERROR at line 1:
ORA-02800: Requests timed out
ORA-06512: at "SYS.DBMS_ISCHED", line 196
ORA-06512: at "SYS.DBMS_SCHEDULER", line 486
ORA-06512: at line 1

WORKAROUND

1)check parameter to make sure not zero.

SQL> show parameter job_queue_processes

NAME                                 TYPE     VALUE
-------------------------------- ------------ ------------------
job_queue_processes                integer    100

2) Bounce the database due to the bugs.

3) Rerun the job successfully.

SQL> exec dbms_scheduler.run_job(job_name=>'BATCH_JOB', 
use_current_session=>false);

PL/SQL procedure successfully completed.

 

Posted in dbms_scheduler | Tagged , , , , , , , | Leave a comment

SEQUENCE in PostgreSQL

1)Create Sequence:

CREATE [ TEMPORARY | TEMP ] SEQUENCE [ IF NOT EXISTS ] name 
[ AS data_type ] 
[ INCREMENT [ BY ] increment ] [ MINVALUE minvalue | NO MINVALUE ] 
[ MAXVALUE maxvalue | NO MAXVALUE ] 
[ START [ WITH ] start ] [ CACHE cache ] [ [ NO ] CYCLE ] 
[ OWNED BY { table_name.column_name | NONE } ]


CREATE SEQUENCE serial START 101;

2) Select the next number from this sequence:

SELECT nextval('serial');

 nextval
---------
     101

SELECT nextval('serial');

 nextval
---------
     102

3)Sequence Manipulation Functions

-- Return value most recently obtained with nextval 
select currval('serial') ;
-----
102
(1 row)

--Return value most recently obtained with nextval for any sequence
select lastval();
lastval
---------
102
(1 row)

-- Advance sequence and return new value
select nextval('serial') ;
-----
103
(1 row)

-- Set sequence's current value
select setval('serial', 1);
setval
--------
1
(1 row)

--setval
SELECT setval('serial', 42); Next nextval will return 43
SELECT setval('serial', 42, true); Same as above
SELECT setval('serial', 42, false); Next nextval will return 42

4) Sequence naming:

nextval('serial')   operates on sequence serial
nextval('SERIAL')   operates on sequence serial
nextval('"Serial"') operates on sequence Serial

5) Alter sequence:

ALTER SEQUENCE [ IF EXISTS ] name 
[ AS data_type ] 
[ INCREMENT [ BY ] increment ] 
[ MINVALUE minvalue | NO MINVALUE ] [ MAXVALUE maxvalue | NO MAXVALUE ] 
[ START [ WITH ] start ] 
[ RESTART [ [ WITH ] restart ] ] 
[ CACHE cache ] [ [ NO ] CYCLE ] 
[ OWNED BY { table_name.column_name | NONE } ] 

ALTER SEQUENCE [ IF EXISTS ] name OWNER TO { new_owner | CURRENT_USER 
| SESSION_USER } 
ALTER SEQUENCE [ IF EXISTS ] name RENAME TO new_name 
ALTER SEQUENCE [ IF EXISTS ] name SET SCHEMA new_schema

6) Grant Privileges On Sequence.

GRANT { { USAGE | SELECT | UPDATE }
    [, ...] | ALL [ PRIVILEGES ] }
    ON { SEQUENCE sequence_name [, ...] | ALL SEQUENCES IN SCHEMA schema_name [, ...] } 
TO role_specification [, ...] [ WITH GRANT OPTION ]

where role_specification can be: 
[ GROUP ] role_name | PUBLIC | CURRENT_USER | SESSION_USER

7)Drop sequence:

DROP SEQUENCE [ IF EXISTS ] name [, ...] [ CASCADE | RESTRICT ]

DROP SEQUENCE serial;

8) Views about sequences:

PG_SEQUENCES:

Name Type References Description
schemaname name pg_namespace.nspname Name of schema containing sequence
sequencename name pg_class.relname Name of sequence
sequenceowner name pg_authid.rolname Name of sequence’s owner
data_type regtype pg_type.oid Data type of the sequence
start_value bigint Start value of the sequence
min_value bigint Minimum value of the sequence
max_value bigint Maximum value of the sequence
increment_by bigint Increment value of the sequence
cycle boolean Whether the sequence cycles
cache_size bigint Cache size of the sequence
last_value bigint The last sequence value written to disk. If caching is used, this value can be greater than the last value handed out from the sequence. Null if the sequence has not been read from yet. Also, if the current user does not have USAGE or SELECT privilege on the sequence, the value is null.

PG_SEQUENCE:

pg_sequence Columns

Name Type References Description
seqrelid oid pg_class.oid The OID of the pg_class entry for this sequence
seqtypid oid pg_type.oid Data type of the sequence
seqstart int8 Start value of the sequence
seqincrement int8 Increment value of the sequence
seqmax int8 Maximum value of the sequence
seqmin int8 Minimum value of the sequence
seqcache int8 Cache size of the sequence
seqcycle bool Whether the sequence cycles
Posted in Objects, PostgreSQL | Tagged , , , , , , , , , | Leave a comment

ORA-29283: invalid file operation ORA-06512: at “SYS.UTL_FILE”, line 536 ORA-29283: invalid file operation

Connected to Oracle database through service name, then get  errors by running the below sample code.  There is no problem to run the sample code while connecting to database through IPC.

$sqlplus jamesh/****@DBTEST

SQL>DECLARE
    l_output utl_file.file_type;
BEGIN
    l_output := utl_file.fopen( 'MY_DIR', 'test.txt', 'w' );
    utl_file.fclose( l_output ); 
END;
/

*
ERROR at line 1:
ORA-29283: invalid file operation
ORA-06512: at "SYS.UTL_FILE", line 536
ORA-29283: invalid file operation
ORA-06512: at line 4
$sqlplus  / as sysdba
SQL> DECLARE
     l_output utl_file.file_type;
BEGIN
     l_output := utl_file.fopen( 'MY_DIR', 'test.txt', 'w' );
     utl_file.fclose( l_output );
END;

 /

PL/SQL procedure successfully completed.

CAUSES

New group “apex” is just added into oracle user for being able to read/write MY_DIR directory.

$ id oracle
uid=100(oracle)gid=200(oinstall)groups=200(oinstall),300(dba),400(apex)

RESOLUTION

Bounce the listener to make the changes effective.

$ ls -ltr|grep apex
drwxrwx---. 6 apexuser apex 4096 May 3 16:08 apex

$lsnrctl stop

$lsnrctl start

$sqlplus jamesh/****@DBTEST

SQL>DECLARE
   l_output utl_file.file_type;
BEGIN
   l_output := utl_file.fopen( 'MY_DIR', 'test.txt', 'w' );
   utl_file.fclose( l_output ); 
END;
/

PL/SQL procedure successfully completed.
Posted in SQL Net, User Privileges and Roles | Tagged , , , | Leave a comment

Heterogeneous Database Connections – Oracle to PostgreSQL

There are two ways to use Heterogeneous Services,  one is via ODBC which does not require a licence,  or you can use the licensed Gateways.

This article describes how to set up the Oracle configurations using ODBC , and then you can create database link between Oracle database and AWS PostgreSQL RDS.

1) Install and verify packages:

$ rpm -qa | grep -i odbc|sort
postgresql-odbc-09.03.0100-2.el7.x86_64
unixODBC-2.3.1-11.el7.i686
unixODBC-2.3.1-11.el7.x86_64
unixODBC-devel-2.3.1-11.el7.i686
unixODBC-devel-2.3.1-11.el7.x86_64

2)Set up the listener to have a service for the Heterogeneous Service.  Set up a new SID Description in the SID_LIST in the listener.ora file :

(SID_DESC=
(SID_NAME=<HS_Name>)
(ORACLE_HOME=<Oracle Home>)
(PROGRAM=<Program Name>)
)

Where
<HS_Name>      –    Is the chosen name used to identify the Heterogeneous Service
<Oracle Home> –    Is the ORACLE_HOME used for the Heterogeneous Service
<Program Name>   –    This depends on the version of the database
For 10g and below the program name should be hsodbc,
and for 11g and above should be dg4odbc

SID_LIST_LISTENER =
...
..
.
(SID_DESC =
  (SID_NAME=PG)
  (ORACLE_HOME=/u01/app/oracle/product/12.1.0/dbhome_1)
  (PROGRAM=dg4odbc)
)

$lsnrctl reload 

-- or -- 

$lsnrctl stop
$lsnrctl start

3) Before accessing ODBC data source,  you must configure the Oracle database to enable communication with the heterogeneous service over Oracle Net (tnsnames.ora):

PG =
(DESCRIPTION =
  (ADDRESS = (PROTOCOL = TCP)(HOST = HOSTOFLISTENER)(PORT = 1521))
  (CONNECT_DATA =
    (SID = PG)
  )
  (HS = OK )
)

4) Edit initPG.ora under $ORACLE_HOME/hs/admin/

$cd  /u01/app/oracle/product/12.1.0/dbhome_1/hs/admin

$ cat initPG.ora
# This is a sample agent init file that contains the HS parameters that are
# needed for the Database Gateway for ODBC

#
# HS init parameters
#
HS_FDS_CONNECT_INFO = PG
HS_FDS_TRACE_LEVEL = on
HS_FDS_SHAREABLE_NAME = /usr/lib64/psqlodbc.so
HS_LANGUAGE = AMERICAN_AMERICA.WE8ISO8859P9
#HS_LANGUAGE = AMERICAN_AMERICA.UTF8

#
# ODBC specific environment variables
#
set ODBCINI= /etc/odbc.ini


#
# Environment variables required for the non-Oracle system
#
#set <envvar>=<value>

5) Edit /etc/odbc.ini

$ cat /etc/odbc.ini
[PG]
Description = PG
Driver = /usr/lib64/psqlodbcw.so
ServerName = host_pg
Username = dblinkuser
Password = dblinkuserpass
Port = 1521
Database = pgdb
Trace = yes
TraceFile = /tmp/odbctrace.txt

[Default]
Driver = /usr/lib64/libodbcpsqlS.so

6)Create postgreSQL user with SELECT privilege on tables.

create user dblinkuser encrypted password 'dblinkuserpass';

grant usage on schema “testschema” to dblinkuser;

grant select on "testschema"."tbl"_test to dblinkuser;

7) Create Oracle database link to PostgreSQL RDS instance.

SQL>CREATE DATABASE LINK PG CONNECT TO "dblinkuser"
IDENTIFIED BY 'dblinkuserpass'
USING 'PG';

8) Test with UnixODBC:

$ isql pg
+---------------------------------------+
| Connected!                            |
|                                       |
| sql-statement                         |
| help [tablename]                      |
| quit                                  |
|                                       |
+---------------------------------------+
SQL> select count(*) from "testschema"."tbl_test";
+---------------------+
| count               |
+---------------------+
|2357                 |
+---------------------+
SQLRowCount returns 1
1 rows fetched
SQL>


9) Test the database link :

SQL>select count(*) from “testschema”.”tbl_test”@PG;

 COUNT(*)
----------
2357
Posted in Client, Migration | Tagged , , , , , , , , , | Leave a comment

Data Encryption and Decryption in Oracle

1)DBMS_OBFUSCATION_TOOLKIT package:

Encryption Function:

SQL> CREATE OR REPLACE FUNCTION encrypt (p_text IN VARCHAR2, p_key VARCHAR2 )
 RETURN RAW
 IS
 lc_text VARCHAR2(32767) := p_text;
 lr_key RAW(255) := UTL_RAW.cast_to_raw(p_key);
 lt_enc_text RAW(32767);
BEGIN
lc_text := RPAD( lc_text, (TRUNC(LENGTH(lc_text)/8)+1)*8, CHR(0) );
DBMS_OBFUSCATION_TOOLKIT.desencrypt(input => UTL_RAW.cast_to_raw(lc_text),
                                      key => lr_key,
                           encrypted_data => lt_enc_text);
RETURN lt_enc_text;
END;
 /

Function created.

SQL> SELECT ENCRYPT('HELLO WORLD !','1234567890') from dual;

ENCRYPT('HELLOWORLD!','1234567890')
----------------------------------------------
F9D853C25CAB740D930D7DCA709D0994

SQL>

Decryption Function:

SQL> CREATE OR REPLACE FUNCTION decrypt (p_raw IN RAW, p_key VARCHAR2 )
RETURN VARCHAR2 IS
 lc_decrypted VARCHAR2(32767);
 lc_return_dec VARCHAR2(32767);
 lr_key RAW(255) := UTL_RAW.cast_to_raw(p_key);
 BEGIN
  DBMS_OBFUSCATION_TOOLKIT.desdecrypt(input => p_raw,
                                        key => lr_key,
                             decrypted_data => lc_decrypted);
   lc_return_dec := UTL_RAW.cast_to_varchar2(lc_decrypted);
  RETURN RTRIM( lc_return_dec, CHR(0) );
END; 
/

Function created.


SQL> SELECT decrypt('F9D853C25CAB740D930D7DCA709D0994','1234567890')
     FROM   dual;

DECRYPT('F9D853C25CAB740D930D7DCA709D0994','1234567890')
--------------------------------------------------------------
HELLO WORLD !

2) DBMS_CRYPTO Package:

set serveroutput on;
DECLARE
  l_text VARCHAR2(32767) :='1234-5678-9012-3456';
  l_key RAW(128) := UTL_I18N.STRING_TO_RAW('0123456789', 'AL32UTF8');
  l_encrypted_raw RAW(2048);
  l_decrypted_raw RAW(2048);
BEGIN
  dbms_output.put_line('Original : ' || l_text );
  l_encrypted_raw := dbms_crypto.encrypt(
            src =>  UTL_I18N.STRING_TO_RAW (l_text , 'AL32UTF8'),
            typ =>  dbms_crypto.des_cbc_pkcs5,
            key =>  l_key
                                        );
dbms_output.put_line('Encrypted ( CHAR ) : ' ||
                UTL_I18N.RAW_TO_CHAR(l_encrypted_raw, 'AL32UTF8'));
 
dbms_output.put_line('Encrypted ( HEX ) : ' ||
                RAWTOHEX(l_encrypted_raw));
 
 l_decrypted_raw := dbms_crypto.decrypt(
                                src => l_encrypted_raw,
                                typ => dbms_crypto.des_cbc_pkcs5,
                                key => l_key);

 dbms_output.put_line('Decrypted : '|| 
                UTL_I18N.RAW_to_CHAR(l_decrypted_raw,'AL32UTF8'));
 END;

   /

Original : 1234-5678-9012-3456
Encrypted ( CHAR ) : .n?k.z????L??E??AmdM
Encrypted ( HEX ) : 2E1F6EAE186B2E7A9DBCFA834C3FBD45A6B0C3806D64044D
Decrypted : 1234-5678-9012-3456

PL/SQL procedure successfully completed.

DBMS_CRYPTO Block Cipher Suites

Name Description
DES_CBC_PKCS5 ENCRYPT_DES + CHAIN_CBC+ PAD_PKCS5
DES3_CBC_PKCS5 ENCRYPT_3DES + CHAIN_CBC + PAD_PKCS5
Posted in Security | Tagged , , , , , , , , | Leave a comment

Cryptographic Hash Functions in Oracle

1) ORA_HASH() Function.

ORA_HASH( expr, max_bucket, seed_value)

  • The optional max_bucket argument determines the maximum bucket value returned by the hash function. You can specify any value between 0 and 4294967295. The default is 4294967295.
  • The optional seed_value argument enables Oracle to produce many different results for the same set of data. Oracle applies the hash function to the combination of expr and seed_value. You can specify any value between 0 and 4294967295. The default is 0.

ORA_HASH function returns a NUMBER value.

SQL>  select ora_hash('Hello World !') from dual;

ORA_HASH('HELLOWORLD!')
-----------------------
             3256043700

2) STANDARD_HASH ( expr, ‘method’), which is available from 12.1 onward.

Valid method algorithms are SHA1SHA256SHA384SHA512, and MD5. If you omit this argument, then SHA1 is used.

SQL> select STANDARD_HASH ( 'Hello World !') from dual;

STANDARD_HASH('HELLOWORLD!')
----------------------------------------
F95BE4341EDDB92DDE5F14D045978F9A4EC6C1A6

SQL> select STANDARD_HASH ( 'Hello World !', 'SHA1') from dual;

STANDARD_HASH('HELLOWORLD!','SHA1')
----------------------------------------
F95BE4341EDDB92DDE5F14D045978F9A4EC6C1A6

SQL> select STANDARD_HASH ( 'Hello World !', 'MD5') from dual;

STANDARD_HASH('HELLOWORLD!','MD5
--------------------------------
B9BE3EF4018BE19F248F6F8E63B9E006

SQL> select STANDARD_HASH ( 'Hello World !', 'SHA256') from dual;

STANDARD_HASH('HELLOWORLD!','SHA256')
----------------------------------------------------------------
07F2BDEF34ED16E3A1BA0DBB7E47B8FD981CE0CCB3E1BFE564D82C423CBA7E47

SQL> select STANDARD_HASH ( 'Hello World !', 'SHA384') from dual;

STANDARD_HASH('HELLOWORLD!','SHA384')
--------------------------------------------------------------------------------
67E60F9CE837CAA3CA82550F0DFCBDE1B8B8A7C1605FA8D115BCC2314204FD95F5F607306622C38C
0205DE7DF6D426D8


SQL> select STANDARD_HASH ( 'Hello World !', 'SHA512') from dual;

STANDARD_HASH('HELLOWORLD!','SHA512')
--------------------------------------------------------------------------------
FEAB0028F1142D420A1425D1DD5B518225B4523AA1CFF63385ECE3411318819F5EC83042CCB79D81
F20E4A243866886CA3AE3026153ACFF8E126C0E89631502E

3) DBMS_OBFUSCATION_TOOLKIT.MD5() function

SQL> CREATE OR REPLACE FUNCTION toolkit_md5 (v_input VARCHAR2)
RETURN dbms_obfuscation_toolkit.varchar2_checksum
AS
BEGIN
      RETURN dbms_obfuscation_toolkit.md5(input_string => v_input);
END toolkit_md5;
/

Function created.

SQL> select
RAWTOHEX( UTL_RAW.CAST_TO_RAW( toolkit_md5('Hello World !'))) from dual;

RAWTOHEX(UTL_RAW.CAST_TO_RAW(TOOLKIT_MD5('HELLOWORLD!')))
--------------------------------------------------------------------------------
B9BE3EF4018BE19F248F6F8E63B9E006

4) DBMS_CRYPTO.HASH() function

 The predefined constants HASH_MD4, HASH_MD5 and HASH_SH1 were already available in previous Oracle releases. In Oracle 12c, three additional constants for different lengths of SHA-2 (256, 384 and 512 bits) were added:
HASH_MD4   CONSTANT PLS_INTEGER := 1;
HASH_MD5   CONSTANT PLS_INTEGER := 2;
HASH_SH1   CONSTANT PLS_INTEGER := 3;
HASH_SH256 CONSTANT PLS_INTEGER := 4;
HASH_SH384 CONSTANT PLS_INTEGER := 5;
HASH_SH512 CONSTANT PLS_INTEGER := 6;
CREATE OR REPLACE 
FUNCTION CRYPTO_HASH (v_input VARCHAR2, v_hash_typ BINARY_INTEGER) 
RETURN RAW DETERMINISTIC
AS
   PRAGMA UDF;
BEGIN
   RETURN dbms_crypto.hash(utl_raw.cast_to_raw(v_input), v_hash_typ);
END CRYPTO_HASH ;
/

Function created.

-- MD4
--
SQL>  select CRYPTO_HASH('Hello World !',1) from dual;

CRYPTO_HASH('HELLOWORLD!',1)
--------------------------------------------------------------------------------
BBA87E44C59377A32F26033CC0A9203B


-- MD5
-- 
SQL>  select CRYPTO_HASH('Hello World !',2) from dual;

CRYPTO_HASH('HELLOWORLD!',2)
--------------------------------------------------------------------------------
B9BE3EF4018BE19F248F6F8E63B9E006

-- SH1
SQL>  select CRYPTO_HASH('Hello World !',3) from dual;

CRYPTO_HASH('HELLOWORLD!',3)
--------------------------------------------------------------------------------
F95BE4341EDDB92DDE5F14D045978F9A4EC6C1A6

-- SH256
--

SQL> select CRYPTO_HASH('Hello World !',4) from dual;

CRYPTO_HASH('HELLOWORLD!',4)
--------------------------------------------------------------------------------
07F2BDEF34ED16E3A1BA0DBB7E47B8FD981CE0CCB3E1BFE564D82C423CBA7E47

-- SH384
--

SQL>  select CRYPTO_HASH('Hello World !',5) from dual;

CRYPTO_HASH('HELLOWORLD!',5)
--------------------------------------------------------------------------------
67E60F9CE837CAA3CA82550F0DFCBDE1B8B8A7C1605FA8D115BCC2314204FD95F5F607306622C38C
0205DE7DF6D426D8

-- SH512
--

SQL>  select CRYPTO_HASH('Hello World !',6) from dual;

CRYPTO_HASH('HELLOWORLD!',6)
--------------------------------------------------------------------------------
FEAB0028F1142D420A1425D1DD5B518225B4523AA1CFF63385ECE3411318819F5EC83042CCB79D81
F20E4A243866886CA3AE3026153ACFF8E126C0E89631502E
Posted in Security | Tagged , , , , , , , , , , , , , , , | Leave a comment