New features in Oracle 11g Database - Virtual Columns

5. Virtual Columns
Virtual Columns are either created at the time of creating or modifying a table. Virtual Columns are defined by expressions or functions, which will be included as part of table structure and hence its metadata. Virtual Columns are like Views, they don't use additional disk space for its data.

U1> CREATE TABLE student_mark(
  2     stud_id NUMBER(5),
  3     stud_name VARCHAR2(15),
  4     subj1_mark NUMBER(3),
  5     subj2_mark NUMBER(3),
  6     subj3_mark NUMBER(3),
  7     tot_mark AS (subj1_mark + subj2_mark + subj3_mark));

Table created.

U1> DESCRIBE student_mark
 Name                    Null?    Type
 ----------------------- -------- ----------------
 STUD_ID                          NUMBER(5)
 STUD_NAME                        VARCHAR2(15)
 SUBJ1_MARK                       NUMBER(3)
 SUBJ2_MARK                       NUMBER(3)
 SUBJ3_MARK                       NUMBER(3)
 TOT_MARK                         NUMBER

U1> DROP TABLE student_mark;

Table dropped.

U1> CREATE TABLE student_mark(
  2     stud_id NUMBER(5),
  3     stud_name VARCHAR2(15),
  4     subj1_mark NUMBER(3),
  5     subj2_mark NUMBER(3),
  6     subj3_mark NUMBER(3),
  7     tot_mark NUMBER(4) AS (subj1_mark + subj2_mark + subj3_mark));

Table created.

U1> DESCRIBE student_mark
 Name                    Null?    Type
 ----------------------- -------- ----------------
 STUD_ID                          NUMBER(5)
 STUD_NAME                        VARCHAR2(15)
 SUBJ1_MARK                       NUMBER(3)
 SUBJ2_MARK                       NUMBER(3)
 SUBJ3_MARK                       NUMBER(3)
 TOT_MARK                         NUMBER(4)

U1> INSERT
  2     INTO student_mark(stud_id, stud_name, subj1_mark, subj2_mark, subj3_mark)
  3     VALUES(10, 'Rose', 75, 45, 66);

1 row created.

U1> INSERT
  2     INTO student_mark(stud_id, stud_name, subj1_mark, subj2_mark, subj3_mark)
  3     VALUES(20, 'Bose', 45, 79, 88);

1 row created.

U1> SELECT *
  2     FROM student_mark;

STUD_ID    STUD_NAME       SUBJ1_MARK SUBJ2_MARK SUBJ3_MARK   TOT_MARK
---------- --------------- ---------- ---------- ---------- ----------
        10 Rose                    75         45         66        186
        20 Bose                    45         79         88        212

U1> ALTER TABLE student_mark
  2     ADD avg_mark NUMBER(3) AS (((subj1_mark + subj2_mark + subj3_mark) / 3));

Table altered.

U1> DESCRIBE student_mark
 Name                    Null?    Type
 ----------------------- -------- ----------------
 STUD_ID                          NUMBER(5)
 STUD_NAME                        VARCHAR2(15)
 SUBJ1_MARK                       NUMBER(3)
 SUBJ2_MARK                       NUMBER(3)
 SUBJ3_MARK                       NUMBER(3)
 TOT_MARK                         NUMBER(4)
 AVG_MARK                         NUMBER(3)

U1> INSERT
  2     INTO student_mark(stud_id, stud_name, subj1_mark, subj2_mark, subj3_mark)
  3     VALUES(30, 'Jose', 63, 68, 72);

1 row created.

U1> SELECT *
  2 FROM student_mark;

STUD_ID    STUD_NAME       SUBJ1_MARK SUBJ2_MARK SUBJ3_MARK   TOT_MARK   AVG_MARK
---------- --------------- ---------- ---------- ---------- ---------- ----------
        10 Rose                    75         45         66        186         62
        20 Bose                    45         79         88        212         71
        30 Jose                    63         68         72        203         68

U1> ALTER TABLE student_mark DROP COLUMN avg_mark;

Table altered.

Attaching a user defined function with a virtual column:-

U1> CREATE OR REPLACE FUNCTION get_avg(stud_no IN NUMBER)
  2     RETURN NUMBER deterministic IS subj_avg NUMBER(3);
  3     BEGIN
  4         SELECT((subj1_mark + subj2_mark + subj3_mark) / 3)
  5         INTO subj_avg
  6         FROM student_mark
  7         WHERE stud_id = stud_no;
  8         RETURN(subj_avg);
  9     END;
 10 /

Function created.

U1> ALTER TABLE student_mark
 2     ADD avrg_mark AS (get_avg(stud_id));

Table altered.

U1> DESCRIBE student_mark
 Name                    Null?    Type
 ----------------------- -------- ----------------
 STUD_ID                          NUMBER(5)
 STUD_NAME                        VARCHAR2(15)
 SUBJ1_MARK                       NUMBER(3)
 SUBJ2_MARK                       NUMBER(3)
 SUBJ3_MARK                       NUMBER(3)
 TOT_MARK                         NUMBER(4)
 AVRG_MARK                        NUMBER


U1> SELECT * FROM student_mark;

STUD_ID    STUD_NAME       SUBJ1_MARK SUBJ2_MARK SUBJ3_MARK   TOT_MARK  AVRG_MARK
---------- --------------- ---------- ---------- ---------- ---------- ----------
        10 Rose                    75         45         66        186         62
        20 Bose                    45         79         88        212         71
        30 Jose                    63         68         72        203         68

Lets see what happens when we drop the function:-

U1> DROP FUNCTION get_avg;

Function dropped.

U1> SELECT *
  2 FROM student_mark;
SELECT *
*
ERROR at line 1:
ORA-00904: "U1"."GET_AVG": invalid identifier

U1> DESCRIBE student_mark
 Name                    Null?    Type
 ----------------------- -------- ----------------
 STUD_ID                          NUMBER(5)
 STUD_NAME                        VARCHAR2(15)
 SUBJ1_MARK                       NUMBER(3)
 SUBJ2_MARK                       NUMBER(3)
 SUBJ3_MARK                       NUMBER(3)
 TOT_MARK                         NUMBER(4)
 AVRG_MARK                        NUMBER

U1> CREATE OR REPLACE FUNCTION get_avg(stud_no IN NUMBER)
  2     RETURN NUMBER deterministic IS subj_avg NUMBER(3);
  3     BEGIN
  4         SELECT((subj1_mark + subj2_mark + subj3_mark) / 3)
  5         INTO subj_avg
  6         FROM student_mark
  7         WHERE stud_id = stud_no;
  8         RETURN(subj_avg);
  9     END;
 10 /

Function created.

U1> SELECT * FROM student_mark;

STUD_ID    STUD_NAME       SUBJ1_MARK SUBJ2_MARK SUBJ3_MARK   TOT_MARK  AVRG_MARK
---------- --------------- ---------- ---------- ---------- ---------- ----------
        10 Rose                    75         45         66        186         62
        20 Bose                    45         79         88        212         71
        30 Jose                    63         68         72        203         68

Partition on Virtual Columns

This enables to partition the table based on the expression or function that were used to create a virtual column. Lets take the below example:-


U1> CREATE TABLE phone_cust (
  2     cust_id NUMBER(5),
  3     name VARCHAR2(15),
  4     street VARCHAR2(15),
  5     city VARCHAR2(10),
  6     zip NUMBER(5),
  7     phone NUMBER(10),
  8     area_code AS (SUBSTR(phone,1,3)||'-'||SUBSTR(phone,4,3)));

Table created.

U1> DESCRIBE phone_cust
 Name                    Null? Type
 ----------------------- -------- ----------------
 CUST_ID                          NUMBER(5)
 NAME                             VARCHAR2(15)
 STREET                           VARCHAR2(15)
 CITY                             VARCHAR2(10)
 ZIP                              NUMBER(5)
 PHONE                            NUMBER(10)
 AREA_CODE                        VARCHAR2(7)

U1> ALTER TABLE phone_cust modify area_code AS (SUBSTR(phone, 1, 3));

Table altered.

U1> DESCRIBE phone_cust
 Name                    Null? Type
 ----------------------- -------- ----------------
 CUST_ID                          NUMBER(5)
 NAME                             VARCHAR2(15)
 STREET                           VARCHAR2(15)
 CITY                             VARCHAR2(10)
 ZIP                              NUMBER(5)
 PHONE                            NUMBER(10)
 AREA_CODE                        VARCHAR2(3)

U1> SELECT * FROM phone_cust;

   CUST_ID NAME            STREET          CITY              ZIP      PHONE ARE
---------- --------------- --------------- ---------- ---------- ---------- ---
        10 John            660 Boas Street Harrisburg      17102 7171234567 717
        20 Raju            123 Mkt Street  Lemoyne         17011 7174567891 717

U1> DROP TABLE phone_cust;

Table dropped.

U1> CREATE TABLE phone_cust(
  2     cust_id NUMBER(5),
  3     name VARCHAR2(15),
  4     street VARCHAR2(15),
  5     city VARCHAR2(10),
  6     zip NUMBER(5),
  7     phone NUMBER(10),
  8     area_code AS (SUBSTR(phone, 1, 3)))
  9   PARTITION BY range(area_code)(
 10   PARTITION p1 VALUES less than(500) TABLESPACE encr_tbs_1,
 11   PARTITION p2 VALUES less than(999) TABLESPACE encr_tbs_2)
 12   enable ROW movement;

Table created.

U1> INSERT
  2  INTO phone_cust(cust_id, name, street, city, zip, phone)
  3  VALUES(10, 'Chel', '456 Walnut St', 'Philly', '19139', 2154567891);

1 row created.

U1> INSERT
  2  INTO phone_cust(cust_id, name, street, city, zip, phone)
  3  VALUES(20, 'John', '660 Boas Street', 'Harrisburg', '17102', 7171234567);

1 row created.

U1> INSERT
  2  INTO phone_cust(cust_id, name, street, city, zip, phone)
  3  VALUES(30, 'Raju', '123 Mkt Street', 'Lemoyne', '17011', 7174567891);

1 row created.

U1> INSERT
  2  INTO phone_cust(cust_id, name, street, city, zip, phone)
  3  VALUES(40, 'Appu', '3 Bridge Trc', 'Woodbridge', '07085', 7324567891);

1 row created.

U1> INSERT
  2 INTO phone_cust(cust_id, name, street, city, zip, phone)
  3 VALUES(50, 'Babu', '5 1st Ave', 'Manhattan', '10037', 2124567891);

1 row created.

U1> INSERT
  2 INTO phone_cust(cust_id, name, street, city, zip, phone)
  3 VALUES(60, 'Rangasamy', '12 Circle Dr', 'Redlands', '92415', 9094567891);

1 row created.

U1> commit;

Commit complete.

U1> DESCRIBE phone_cust
 Name                    Null? Type
 ----------------------- -------- ----------------
 CUST_ID                          NUMBER(5)
 NAME                             VARCHAR2(15)
 STREET                           VARCHAR2(15)
 CITY                             VARCHAR2(10)
 ZIP                              NUMBER(5)
 PHONE                            NUMBER(10)
 AREA_CODE                        VARCHAR2(3)

U1> SELECT * FROM phone_cust;

   CUST_ID NAME            STREET          CITY              ZIP      PHONE ARE
---------- --------------- --------------- ---------- ---------- ---------- ---
        10 Chel            456 Walnut St   Philly          19139 2154567891 215
        50 Babu            5 1st Ave       Manhattan       10037 2124567891 212
        20 John            660 Boas Street Harrisburg      17102 7171234567 717
        30 Raju            123 Mkt Street  Lemoyne         17011 7174567891 717
        40 Appu            3 Bridge Trc    Woodbridge       7085 7324567891 732
        60 Rangasamy       12 Circle Dr    Redlands        92415 9094567891 909

U1> SELECT * FROM phone_cust PARTITION(p1);

   CUST_ID NAME            STREET          CITY              ZIP      PHONE ARE
---------- --------------- --------------- ---------- ---------- ---------- ---
        10 Chel            456 Walnut St   Philly          19139 2154567891 215
        50 Babu            5 1st Ave       Manhattan       10037 2124567891 212

U1> SELECT * FROM phone_cust PARTITION(p2);

   CUST_ID NAME            STREET          CITY              ZIP      PHONE ARE
---------- --------------- --------------- ---------- ---------- ---------- ---
        20 John            660 Boas Street Harrisburg      17102 7171234567 717
        30 Raju            123 Mkt Street  Lemoyne         17011 7174567891 717
        40 Appu            3 Bridge Trc    Woodbridge       7085 7324567891 732
        60 Rangasamy       12 Circle Dr    Redlands        92415 9094567891 909

U1> UPDATE phone_cust SET phone = 2121234567 WHERE cust_id = 20;

1 row updated.

U1> commit;

Commit complete.

U1> SELECT * FROM phone_cust;

   CUST_ID NAME            STREET          CITY              ZIP      PHONE ARE
---------- --------------- --------------- ---------- ---------- ---------- ---
        10 Chel            456 Walnut St   Philly          19139 2154567891 215
        50 Babu            5 1st Ave       Manhattan       10037 2124567891 212
        20 John            660 Boas Street Harrisburg      17102 2121234567 212
        30 Raju            123 Mkt Street  Lemoyne         17011 7174567891 717
        40 Appu            3 Bridge Trc    Woodbridge       7085 7324567891 732
        60 Rangasamy       12 Circle Dr    Redlands        92415 9094567891 909

6 rows selected.

U1> SELECT * FROM phone_cust PARTITION(p1);

   CUST_ID NAME            STREET          CITY              ZIP      PHONE ARE
---------- --------------- --------------- ---------- ---------- ---------- ---
        10 Chel            456 Walnut St   Philly          19139 2154567891 215
        50 Babu            5 1st Ave       Manhattan       10037 2124567891 212
        20 John            660 Boas Street Harrisburg      17102 2121234567 212

U1> SELECT * FROM phone_cust PARTITION(p2);

   CUST_ID NAME            STREET          CITY              ZIP      PHONE ARE
---------- --------------- --------------- ---------- ---------- ---------- ---
        30 Raju            123 Mkt Street  Lemoyne         17011 7174567891 717
        40 Appu            3 Bridge Trc    Woodbridge       7085 7324567891 732
        60 Rangasamy       12 Circle Dr    Redlands        92415 9094567891 909
 

New features in Oracle 11g Database

What's new in an Oracle 11g Database?

1. Lets check some INIT parameters first:-

MEMORY_TARGET: Specifies the Oracle system-wide usable memory. The database tunes memory to the MEMORY_TARGET value, increasing or decreasing SGA and PGA as needed.

MEMORY_MAX_TARGET: Maximum value that MEMORY_TARGET initialization parameter can use.

DIAGNOSTIC_DEST: Diagnostics for each database instance are located in this dedicated directory

2. Password is now Case Sensitive.

SQL> create user u1 identified by U1 default tablespace users quota 50m on users;

User created.

SQL> grant create session, create table, create procedure, create view to u1;

Grant succeeded.

SQL> create user u2 identified by u2 default tablespace users quota 50m on users;

User created.

SQL> grant create session, create table, create procedure, create view to u2;

Grant succeeded.

SQL> connect u1/u1
ERROR:
ORA-01017: invalid username/password; logon denied


Warning: You are no longer connected to ORACLE.

SQL> connect u1/U1
Connected.

SQL> connect u2/U2
ERROR:
ORA-01017: invalid username/password; logon denied


Warning: You are no longer connected to ORACLE.
SQL> connect u2/u2
Connected.

3. READ-ONLY Table

SQL> connect u1/U1
Connected.

SQL> create table t1 (a number) ;

Table created.

SQL> begin
   2 for x in 1..20 loop
   3 insert into t1 values (x);
   4 end loop;
   5 commit;
   6 end;
   7 /

PL/SQL procedure successfully completed.

SQL> select READ_ONLY from user_tables where table_name='T1';

REA
---
NO

1 row selected.

SQL> alter table t1 READ ONLY;

Table altered.

SQL> select READ_ONLY from user_tables where table_name='T1';

REA
---
YES

1 row selected.

SQL> insert into t1 values (99);
insert into t1 values (99)
*
ERROR at line 1:
ORA-12081: update operation not allowed on table "U1"."T1"

SQL> alter table t1 READ WRITE;

Table altered.

SQL> insert into t1 values (99);

1 row created.

SQL> commit;

Commit complete.

4. Tablespace Encryption

As we all know, in an Oracle database all the data are finally stored in the datafiles, what if the datafiles containing sensitive data were stolen? there are always chances that these datafiles can either be disseminated or attached to a different database and the sensitive data can be exposed or revealed.

In order to overcome this, Oracle introduced Transparent Data Encryption (TDE), in Oracle 10g version, a column of a table can be encrypted based on a (master) key, and that key is not stored within the same database, but externally in an Oracle wallet.

Now in Oracle 11g, you can encrypt an entire tablespace, i.e., a whole table or all the tables in an encrypted tablespace using tablespace encryption.

Tablespace encryption is supported on all data stored in an encrypted tablespace including internal large objects (LOBs) such as BLOBs and CLOBs. But data stored externally, like BFILE data is not encrypted.

If a table is created with BFILE column in an encrypted tablespace, then this BFILE column will not be encrypted, but the rest of the table will be encrypted.

Configure wallet location by setting the ENCRYPTION_WALLET_LOCATION parameter in sqlnet.ora:-

ENCRYPTION_WALLET_LOCATION=(SOURCE=(METHOD=FILE)(METHOD_DATA=(DIRECTORY=C:\oracle\ora11\admin\ORA11\wallet)))

Tablespace Level
ENCRYPTION algorithms supported in Oracle11g are 3DES168, AES128, AES192 and AES256

SYS> ALTER SYSTEM SET ENCRYPTION KEY IDENTIFIED BY "b1b1";

System altered.

SYS> CREATE TABLESPACE ENCR_TBS_1
   2     DATAFILE 'C:\ORACLE\ORADATA\ORA11\ENCR_TBS_01.DBF' SIZE 20M
   3     AUTOEXTEND ON NEXT 1M MAXSIZE 128M
   4     EXTENT MANAGEMENT LOCAL UNIFORM SIZE 1M
   5     SEGMENT SPACE MANAGEMENT AUTO
   6     ENCRYPTION
   7     DEFAULT STORAGE(ENCRYPT);

Tablespace created.

SYS> CREATE TABLESPACE ENCR_TBS_2
   2     DATAFILE 'C:\ORACLE\ORADATA\ORA11\ENCR_TBS_02.DBF' SIZE 20M
   3     AUTOEXTEND ON NEXT 1M MAXSIZE 128M
   4     EXTENT MANAGEMENT LOCAL UNIFORM SIZE 1M
   5     SEGMENT SPACE MANAGEMENT AUTO
   6     ENCRYPTION USING 'AES256'
   7     DEFAULT STORAGE(ENCRYPT);

Tablespace created.

SQL> select * from V$ENCRYPTED_TABLESPACES;

       TS# ENCRYPT ENC
---------- ------- ---
         5 AES128  YES
         6 AES256  YES

SYS> select tablespace_name,encrypted from dba_tablespaces;

TABLESPACE_NAME     ENC
----------------    ---
SYSTEM              NO
SYSAUX              NO
UNDOTBS1            NO
TEMP                NO
USERS               NO
TEST_TS1            NO
ENCR_TBS_1          YES
ENCR_TBS_2          YES

8 rows selected.

SQL> desc DBA_ENCRYPTED_COLUMNS
Name                Null?    Type
------------------- -------- -------------
OWNER NOT           NULL     VARCHAR2(30)
TABLE_NAME          NOT NULL VARCHAR2(30)
COLUMN_NAME         NOT NULL VARCHAR2(30)
ENCRYPTION_ALG               VARCHAR2(29)
SALT                         VARCHAR2(3)

SQL> col owner for a5
SQL> col table_name for a10
SQL> col column_name for a14
SQL> select * from DBA_ENCRYPTED_COLUMNS;

OWNER TABLE_NAME COLUMN_NAME    ENCRYPTION_ALG                SAL
----- ---------- -------------- ----------------------------- ---
U1    CUST       CRED_CARD_NUM  AES 192 bits key              NO
U1    CUST_2     CRED_CARD_NUM  3 Key Triple DES 168 bits key NO

SYS> CREATE TABLE CUST_1(
   2     cust_id VARCHAR2(4),
   3     cust_name VARCHAR2(10),
   4     cred_card_num NUMBER(16))
   5     TABLESPACE ENCR_TBS_1;

Table created.

Above table is created in an encrypted tablespace, hence all data in this table is stored encrypted on the disk.

SYS> insert into CUST_1 values (30,'Kate',1234567812345678);

1 row created.

SYS> insert into CUST_1 values (40,'Deep',5678123456781234);

1 row created.

SYS> commit;

Commit complete.

SYS> col CRED_CARD_NUM for 9999999999999999
SYS> select * from CUST_1;

CUST CUST_NAME  CRED_CARD_NUM
---- ---------- -----------------
30   Kate       1234567812345678
40   Deep       5678123456781234

SYS> alter system set encryption wallet close;

System altered.

C:\>cd C:\oracle\ora11\admin\ORA11\wallet

C:\oracle\ora11\admin\ORA11\wallet>C:\oracle\ora11\BIN\orapki wallet export -wallet . -dn "CN=oracle" -request oracle.req -pwd "b1b1"

Table Level
SQL> ALTER SYSTEM SET ENCRYPTION KEY IDENTIFIED BY "b1b1";

System altered.

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

A wallet is automatically opened when you set or reset the master encryption key. Once you set the encryption, you will be see a file created in the above DIRECTORY location.

SQL> ALTER SYSTEM SET ENCRYPTION WALLET CLOSE ;

System altered.

SQL> ALTER SYSTEM SET ENCRYPTION WALLET OPEN IDENTIFIED BY "b1b1";

System altered.

Creating a table with an encrypted column using the default AES192 algorithm

SQL> conn u1/u1
Connected.
SQL> create table CUST (
   2     cust_id VARCHAR2(4),
   3     cust_name VARCHAR2(10),
   4     cred_card_num NUMBER(16) ENCRYPT
   5     );

Table created.

U1> desc cust
Name                Null?    Type
------------------- -------- --------------------
CUST_ID                      VARCHAR2(4)
CUST_NAME                    VARCHAR2(10)
CRED_CARD_NUM                NUMBER(16) ENCRYPT

U1> insert into CUST values (10,'Tom',1234567812345678);

1 row created.

U1> insert into CUST values (20,'Sam',5678123456781234);

1 row created.

U1> commit;

Commit complete.

U1> CREATE INDEX cust_card_idx ON CUST (cred_card_num);
CREATE INDEX cust_card_idx ON CUST (cred_card_num)
*
ERROR at line 1:
ORA-28338: cannot encrypt indexed column(s) with salt

U1> ALTER TABLE CUST MODIFY (cred_card_num ENCRYPT NO SALT);

Table altered.

U1> CREATE INDEX cust_card_idx ON CUST (cred_card_num);

Index created.

U1> col CRED_CARD_NUM for 9999999999999999
U1> select * from CUST;

CUST CUST_NAME  CRED_CARD_NUM
---- ---------- -----------------
10   Tom        1234567812345678
20   Sam        5678123456781234

Creating a table with an encrypted column using NO SALT parameter

U1> create table CUST_2 (
  2     cust_id VARCHAR2(4),
  3     cust_name VARCHAR2(10),
  4     cred_card_num NUMBER(16) ENCRYPT NO SALT
  5     );

Table created.

U1> insert into CUST_2 select * from CUST;

2 rows created.

U1> commit;

Commit complete.

U1> select * from CUST_2;

CUST CUST_NAME  CRED_CARD_NUM
---- ---------- -----------------
10   Tom        1234567812345678
20   Sam        5678123456781234

Adding Salt to an Encrypted Column

U1> ALTER TABLE CUST_2 MODIFY (cred_card_num ENCRYPT SALT);

Table altered.

Removing Salt to an Encrypted Column

U1> ALTER TABLE CUST_2 MODIFY (cred_card_num ENCRYPT NO SALT);

Table altered.

U1> ALTER TABLE CUST_2 REKEY;

Table altered.

U1> ALTER TABLE CUST_2 REKEY USING '3DES168';

Table altered.

Creating a table with an encrypted column using 3DES168 algorithm

create table CUST_3 (
    cust_id VARCHAR2(4),
    cust_name VARCHAR2(10),
    cred_card_num NUMBER(16) ENCRYPT USING '3DES168'
    );

Adding and modifying encrypted columns

ALTER TABLE CUST_3 ADD (cust_addr VARCHAR2(15) ENCRYPT);

ALTER TABLE CUST_3 modify (cust_name ENCRYPT);

Disable Column Encryption

ALTER TABLE CUST_3 MODIFY (cust_name DECRYPT);

Manually Creating an Oracle 11g Database

Creating an Oracle 11g Database in Windows using command line.

Creating a 11g database is just the same as 10g.

INIT.ORA.Below are the contents of my init.ora:-

db_name='ORA11'
memory_target=1G
processes = 150
audit_file_dest='C:\oracle\ora11\admin\adump'
audit_trail ='db'
db_block_size=8192
db_domain=''
db_recovery_file_dest=C:\oracle\ora11\admin\ORA11\flash_recovery_area
db_recovery_file_dest_size=2G
diagnostic_dest=C:\oracle\ora11\admin\ORA11\diagnostic_dest
dispatchers='(PROTOCOL=TCP) (SERVICE=ORA11XDB)'
open_cursors=300
remote_login_passwordfile='EXCLUSIVE'
undo_tablespace='UNDOTBS1'
control_files = ("C:\oracle\oradata\ORA11\ORA11_CONTROL1.ora", "C:\oracle\oradata\ORA11\ORA11_CONTROL2.ora")
compatible ='11.1.0'

Set Environment Variables:-

set ORACLE_SID=ORA11
set ORACLE_HOME=C:\oracle\ora11

Create Oracle service

C:\oracle\ora11\bin\oradim.exe -new -sid ORA11 -startmode m -INTPWD oracle -PFILE "C:\oracle\ora11\database\initORA11.ora"

Instance created.
Error while deleting value, OS Error = 2

Create Database

C:\>sqlplus /nolog

SQL*Plus: Release 11.1.0.6.0 - Production on Mon Dec 10 15:25:47 2007

Copyright (c) 1982, 2007, Oracle. All rights reserved.

SQL> connect sys/oracle as sysdba
Connected to an idle instance.

SQL> startup nomount pfile="C:\oracle\ora11\database\initORA11.ora"

ORACLE instance started.

Total System Global Area 644468736 bytes
Fixed Size 1335108 bytes
Variable Size 171966652 bytes
Database Buffers 465567744 bytes
Redo Buffers 5599232 bytes

CREATE DATABASE ORA11
USER SYS IDENTIFIED BY ORACLE
USER SYSTEM IDENTIFIED BY ORACLE
DATAFILE 'C:\oracle\oradata\ORA11\SYSTEM01.DBF' SIZE 325M REUSE AUTOEXTEND ON NEXT 10240K MAXSIZE UNLIMITED
SYSAUX DATAFILE 'C:\oracle\oradata\ORA11\SYSAUX01.DAT' SIZE 120M REUSE AUTOEXTEND ON NEXT 5M MAXSIZE 2048M
DEFAULT TABLESPACE USERS DATAFILE 'C:\oracle\oradata\ORA11\USERS01.DBF' SIZE 50M REUSE AUTOEXTEND ON MAXSIZE UNLIMITED
DEFAULT TEMPORARY TABLESPACE TEMP TEMPFILE 'C:\oracle\oradata\ORA11\TEMP01.DBF' SIZE 40M REUSE AUTOEXTEND ON NEXT 640K MAXSIZE UNLIMITED
UNDO TABLESPACE "UNDOTBS1" DATAFILE 'C:\oracle\oradata\ORA11\UNDOTBS01.DBF'
SIZE 200M REUSE AUTOEXTEND ON NEXT 5120K MAXSIZE UNLIMITED
CHARACTER SET WE8MSWIN1252
NATIONAL CHARACTER SET AL16UTF16
LOGFILE 'C:\oracle\oradata\ORA11\REDO01.LOG' SIZE 100M REUSE,
'C:\oracle\oradata\ORA11\REDO02.LOG' SIZE 100M REUSE,
'C:\oracle\oradata\ORA11\REDO03.LOG' SIZE 100MREUSE
EXTENT MANAGEMENT LOCAL
MAXLOGFILES 32
MAXLOGMEMBERS 4
MAXLOGHISTORY 100
MAXDATAFILES 254
MAXINSTANCES 1;

@C:\oracle\ora11\rdbms\admin\catalog.sql
@C:\oracle\ora11\rdbms\admin\catproc.sql

SQL> connect system/ORACLE as sysdba
Connected.

@C:\oracle\ora11\sqlplus\admin\pupbld.sql

SQL> select program from v$session;

PROGRAM
-----------------------
sqlplus.exe
ORACLE.EXE (q001)
ORACLE.EXE (CJQ0)
ORACLE.EXE (q000)
ORACLE.EXE (W000)
ORACLE.EXE (QMNC)
ORACLE.EXE (FBDA)
ORACLE.EXE (SMCO)
ORACLE.EXE (MMNL)
ORACLE.EXE (MMON)
ORACLE.EXE (RECO)
ORACLE.EXE (SMON)
ORACLE.EXE (CKPT)
ORACLE.EXE (LGWR)
ORACLE.EXE (DBW0)
ORACLE.EXE (MMAN)
ORACLE.EXE (DIA0)
ORACLE.EXE (PSP0)
ORACLE.EXE (DBRM)
ORACLE.EXE (DIAG)
ORACLE.EXE (VKTM)
ORACLE.EXE (PMON)

22 rows selected.

Drop Database

SQL> connect sys/oracle as sysdba
Connected.
SQL> shutdown immediate;
Database closed.
Database dismounted.
ORACLE instance shut down.
SQL> startup mount exclusive;
ORACLE instance started.

Total System Global Area 644468736 bytes
Fixed Size 1335108 bytes
Variable Size 171966652 bytes
Database Buffers 465567744 bytes
Redo Buffers 5599232 bytes
Database mounted.
SQL> alter system enable restricted session;

System altered.

SQL> drop DATABASE ;

Database dropped.

Disconnected from Oracle Database 11g Enterprise Edition Release 11.1.0.6.0 - Production
With the Partitioning, OLAP, Data Mining and Real Application Testing options

or

SQL> STARTUP mount RESTRICT EXCLUSIVE;
ORACLE instance started.

Total System Global Area 644468736 bytes
Fixed Size 1335108 bytes
Variable Size 171966652 bytes
Database Buffers 465567744 bytes
Redo Buffers 5599232 bytes
Database mounted.

SQL> drop DATABASE ;

Operation 203 succeeded.

Version

SQL> select * from v$version;

BANNER
--------------------------------------------------------------------------------
Oracle Database 11g Enterprise Edition Release 11.1.0.6.0 - Production
PL/SQL Release 11.1.0.6.0 - Production
CORE 11.1.0.6.0 Production
TNS for 32-bit Windows: Version 11.1.0.6.0 - Production
NLSRTL Version 11.1.0.6.0 - Production

5 rows selected.

Following messages were noted in Alert.ora

Tue Dec 11 08:36:23 2007
Warning: chk_tab_prop - table doesn't exist :SYS.KUPC$DATAPUMP_QUETAB
Warning: chk_tab_prop - table doesn't exist :SYS.AQ$_KUPC$DATAPUMP_QUETAB_S
Warning: chk_tab_prop - table doesn't exist :SYS.AQ$_KUPC$DATAPUMP_QUETAB_I
Warning: chk_tab_prop - table doesn't exist :SYS.AQ$_KUPC$DATAPUMP_QUETAB_H
Warning: chk_tab_prop - table doesn't exist :SYS.AQ$_KUPC$DATAPUMP_QUETAB_T
Warning: chk_tab_prop - table doesn't exist :SYS.AQ$_KUPC$DATAPUMP_QUETAB_G
Warning: chk_tab_prop - table doesn't exist :SYS.AQ$_KUPC$DATAPUMP_QUETAB_P
Warning: chk_tab_prop - table doesn't exist :SYS.AQ$_KUPC$DATAPUMP_QUETAB_D
Tue Dec 11 08:39:11 2007
SERVER COMPONENT id=CATPROC: timestamp=2007-12-11 08:39:11