Oracle PL/SQL Functionalities

Few New DB and Pl/sql Functionalities, hope this may help while coding.

ð Using DBMS_SYSTEM.KSDWRT to write in Alert Log File.

Used to write messages to trace files and/or alert log use

DBMS_SYSTEM.KSDWRT

(

DEST NUMBER, – 1 = Trace File, 2 = Alert Log, 3 = Both

TST VARCHAR2 – Message

);

eg:

DBMS_SYSTEM.KSDWRT (1, ‘Output to trace file’);

DBMS_SYSTEM.KSDWRT (2, ‘Output to alert log’);

DBMS_SYSTEM.KSDWRT (3, ’Output to both’);

DBMS_SYSTEM.KSDWRT (2, Messages);

ð To Flush Cache in older release of oracle :-

ALTER SESSION SET EVENTS ‘IMMEDIATE TRACE NAME FLUSH_CACHE’;

ð An undocumented feature of Oracle :-

To Bypass the buffer cache and read straight from disk using direct path reads.

alter session set "_serial_direct_read"=true;

Note:- Causes a tablespace (9i) or fast object (10g+) checkpoint, so careful on busy OLTP systems.

ð Using the undocumented function SYS_OP_MAP_NONNULL:

This function has been around for a while, but is still undocumented as of the latest Oracle database release (10gR2). Being undocumented means that it should not be used in production code without proper testing, but it does not hurt to know that it exists and explore how it works. This function makes it possible to have NULL = NULL:

EDDIE@XE> SELECT sys_op_map_nonnull (NULL) FROM DUAL;

SY

--

FF

EDDIE@XE> SELECT 'hi there' FROM DUAL WHERE sys_op_map_nonnull (NULL) = sys_op_map_nonnull (NULL);

'HITHERE

--------

hi there

EDDIE@XE> SELECT NVL (name, 'I am null') col1, NVL (id, 1) col2 FROM test WHERE sys_op_map_nonnull(name) = sys_op_map_nonnull(name);

COL1 COL2

---------- ----------

Deepak Deepak

I am null I am null

ð REVERSE Function:-

This function reverse’s the Word

Select reverse(‘DEEPAK’) from dual;

ð Using ROWTYPE in PL/SQL efficiently

CREATE TABLE CUSTOMERS (

id NUMBER,

name VARCHAR2(100),

birth DATE,

death DATE

)

PROCEDURE insert_customer IS

customer CUSTOMERS%ROWTYPE;

BEGIN

customer.id := 45;

customer.name := 'John Smith';

customer.birth := TO_DATE('1978/04/03', 'YYYY/MM/DD');

INSERT INTO CUSTOMERS VALUES customer; -- No need to insert values, we can directly pass rowtype.

END;

Configure Tablespace Encryption for Oracle 12c RAC DB.

Follow the below Steps to configure the Tablespace Encryption on Oracle 12c RAC Database.

===============================================================================================

To start with , First Create the Keystore:-

1) Creating Software Keystores

SQL> ADMINISTER KEY MANAGEMENT CREATE KEYSTORE '<Keystore Location>' IDENTIFIED BY password;

keystore altered.

Note:- This STEP was already executed by you , it seems you created the wallet using mkstore. Hence I did not execute the above step and used the wallet created by you.

2) Create Auto login Keystore

Creating an Auto-Login or Local Auto-Login Software Keystore

SQL> ADMINISTER KEY MANAGEMENT CREATE  [LOCAL] AUTO_LOGIN KEYSTORE FROM KEYSTORE '<Keystore Location>' identified by "<Keystore Password>';

keystore altered.

Note:- The auto-login software keystore can be opened from different computers from the computer where this keystore resides, but the [local] auto-login software keystore can only be opened from the computer on which it was created.

3) Open the Keystore to be used by all container Databases.

Open the Software Keystore

SQL> ADMINISTER KEY MANAGEMENT SET KEYSTORE OPEN IDENTIFIED BY "<Keystore Password>" Container=ALL;

keystore altered.

4) Set the TDE Master Encryption Key and Backup the Keystore for All container.

Set the Software TDE Master Encryption Key

SQL> ADMINISTER KEY MANAGEMENT SET KEY IDENTIFIED BY "<Keystore Password>" WITH BACKUP USING 'TDE_ENCRYPTION' Container=all;

keystore altered.

NOTE:- One can set the Encryption KEY only for particular PDB if required, by Specifying the CONTAINER=<PDB>.

5) Last but not the least Create a  ENCRYPTED TABLESPACE in PDB using below procedure:-

SQL> conn sys/<Password>@<PDB Service Name> as sysdba

Connected.

SQL> create tablespace test datafile '+DATA1' size 100m ENCRYPTION DEFAULT STORAGE (ENCRYPT);

Tablespace created.

  1* select * from v$encryption_wallet

SQL> /

WRL_TYPE             WRL_PARAMETER          STATUS                         WALLET_TYPE          WALLET_OR FULLY_BAC     CON_ID

-------------------- ---------------------- ------------------------------ -------------------- --------- --------- ----------

FILE                 <Wallet Location>        OPEN                           PASSWORD             SINGLE    NO                 0