Register for Sangam 2011 at AIOUG

Don't miss chance to meet ACEs and some great Oracle DBA and Developer sessions.

Sangam 2011.

See you all there !!

Create a table with only scale specification for a column

Hi All,

Just came across a peculiar way of specifying only the column scale and not its precision, so thought of sharing with all. It looks pretty simple but could be a handy tip for table designs.

Scenario: Create a table CONT_EMPS where Salary column must store values upto 2 places of decimals (i.e. scale is 2). Here, note that no precision has been specified for the Salary column. Lets see how to do it.

SQL> CREATE TABLE CONT_EMPS
2 (EMPID NUMBER,
3 EMPNAME VARCHAR2(256),
4 SAL NUMBER(*,2))
5 /
Table created.

SQL> DESC CONT_EMPS
Name         Null?    Type
------------ -------- ----------------------------
EMPID                 NUMBER
EMPNAME               VARCHAR2(256)
SAL                   NUMBER(38,2)

When I specify '*' in place of precision, Oracle treats it as the maximum precision allowed for the column. Additionally, this convention doesn't applies to PL/SQL data type declaration.
 
Bye for now.
Saurabh

Related articles

• PL/SQL Part-1 (oramasters.com)


• SQL SERVER - How to work with Row Constructors , inserted and deleted tables and Handle Result Set (sqlexplore.wordpress.com)


• jOOQ-meta. A "hard-core SQL" proof of concept (lukaseder.wordpress.com)


• BISQL # 34 :What is Identity IN SQL {Identity Part - I} (sqlmentalist.wordpress.com)


• SQL Tuning or SQL Optimization (premaseem.wordpress.com)


• Pivot Data in SQL and Display in a Flex Advanced Data Grid Part One (boozydoo.wordpress.com)


• SQL injection (nuzhatg.wordpress.com)

Oracle ACE Nominations

Just to share with you all

Oracle ACE Nominations.

PL/Scope and PL/SQL Hierarchical Profiler in Oracle 11g

Hello world,

Ever since the release of Oracle 11gR2 in 2009 under the theme "Consolidate. Compress. Control", its new features have always been the talk of the conferences and meetings. But besides the compression, control and other development features, there were several other features which have got less weightage. I shall present my walkthrough experience in the below link (which has been published by Exforsys).

PL/Scope and DBMS_HPROF

Hope you enjoy reading the complete story and post your feedback and comments.

Thanks, bye

Saurabh

 

Related articles

• New book: Expert PL/SQL Practices for Oracle Developers and DBAs (dulcianonit.wordpress.com)


• Oracle 11g SecureFiles (sbhoracle.wordpress.com)


• Result Caching feature in Oracle 11g (sbhoracle.wordpress.com)

Enable Encryption for SecureFiles

Last week, I was trying to enable ENCRYPTION property for Oracle 11g Secure File. But I couldn’t find sufficient information on the web. However, I have tried it now and would like to demonstrate on my blog.

Suppose I have a table EMPLOYEES where employees image (BLOB) has to be loaded into the database. You want to compress it and encrypt it as per AES192 encryption standard.

 Let us see how to do it. 

1. Compression can be easily enabled for the BLOB as below

ALTER TABLE EMPLOYEES
MODIFY LOB (IMAGE)
(
COMPRESS HIGH
)
/

Table altered.

2. Enabling the encryption feature for the IMAGE column in EMPLOYEES table

The TDE is an encryption system which encrypts the columns value with a confidential key. An encrypted key can hold multiple encrypted columns of same table. There exists second level of security where the column keys are again encrypted with database’s master key. But note that none of the keys are stored in database. They reside within an Oracle wallet. An encryption wallet has to be set as an external security module before enabling the feature for the LOB column.

Step 1: Create a Folder C:\ExternalSecurity\Wallets\ on the Oracle database server to store the Transparent Data Encryption (TDE) wallet.



Step 2: Edit the SQLNET.ora file at \\ORACLE_HOME\NETWORK\ADMIN location on the server. Include the ENCRYPTION_WALLET_LOCATION to indicate the location of the TDE wallet.

ENCRYPTION_WALLET_LOCATION=
(SOURCE=(METHOD=FILE)
(METHOD_DATA= (DIRECTORY= C:\ExternalSecurity\Wallets)
)
)



Step 3: Open the command prompt on the server machine to reload the server

Reload the Listener using RELOAD command




Step 4: Now login as DBA

Set the encryption key along with its password

SQL> ALTER system SET ENCRYPTION KEY IDENTIFIED BY "wallet";

System altered.

The ALTER SYSTEM command above keeps the wallet open until the database is up. If the database is down and restarted subsequently, the wallet has to be explicitly opened.

The error message raised below is not an error since the wallet gets automatically opened while setting

SQL> ALTER system SET ENCRYPTION WALLET OPEN IDENTIFIED BY "wallet";
ALTER system SET ENCRYPTION WALLET OPEN IDENTIFIED BY "wallet"
*
ERROR at line 1:
ORA-28354: wallet already open

Step 5: Login as SCOTT user

Enter user-name: SCOTT/TIGER
SQL> ALTER TABLE EMPLOYEES
2 MODIFY
3 (
4 IMAGE ENCRYPT USING 'AES192'
5 );

Table altered.

SQL> SELECT *
2 FROM user_encrypted_columns;

TABLE_NAME COLUMN_NAME ENCRYPTION_ALG SAL INTEGRITY_AL
---------- -------------- ----------------------------- --- ------------
EMPLOYEES IMAGE AES 192 bits key YES SHA-1

Step 6: Verify the server wallet location. Check the ENCRYPTED WALLET LOCATION directory. You can find the ewallet.p12 filer created.



Note that the table carrying encrypted column(s) cannot participate in conventional export or import process. But data pump rescues the situation by supporting encrypted exports and imports.