MANUAL DATABASE CREATION IN 11G ON WINDOW INVIRONMENT

MANUAL DATABASE CREATION IN 11G ON WINDOW INVIRONMENT
=========================================================

Create Directory folder under admin, oradata, and flash_recovery_area

D:\app\oracle\admin\nashim\pfile\
D:\app\oracle\admin\nashim\udump
D:\app\oracle\admin\nashim\cdump
D:\app\oracle\admin\nashim\dpdump
D:\app\oracle\admin\nashim\adump

D:\app\oracle\oradata\nashim
D:\app\oracle\flash_recovery_area\nashim


Startup the database to nomount stage with sample parameter file

sample pfile:---
=============

nashim.__db_cache_size=213909504
nashim.__java_pool_size=4194304
nashim.__large_pool_size=4194304
nashim.__oracle_base='D:\app\oracle'#ORACLE_BASE set from environment
nashim.__pga_aggregate_target=251658240
nashim.__sga_target=369098752
nashim.__shared_io_pool_size=0
nashim.__shared_pool_size=138412032
nashim.__streams_pool_size=0
*.audit_file_dest='D:\app\oracle\admin\nashim\adump'
*.audit_trail='db'
*.compatible='11.2.0.0.0'
*.control_files='D:\app\oracle\oradata\nashim\control01.ctl',
'D:\app\oracle\flash_recovery_area\nashim\control02.ctl'
*.db_block_size=8192
*.db_domain=''
*.db_name='nashim'
*.db_recovery_file_dest='D:\app\oracle\flash_recovery_area'
*.db_recovery_file_dest_size=4039114752
*.diagnostic_dest='D:\app\oracle'
*.dispatchers='(PROTOCOL=TCP) (SERVICE=nashimXDB)'
*.memory_target=620756992
*.open_cursors=300
*.processes=150
*.remote_login_passwordfile='EXCLUSIVE'
*.undo_tablespace='UNDOTBS1'

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

Create Instance service in window environment

Deletion:-
===========

C:\Windows\system32>oradim -delete -sid nashim
Instance deleted.

Creation:-
===========

C:\Windows\system32>oradim -NEW -SID nashim -STARTMODE AUTO -usrpwd manager -PFI
LE D:\app\oracle\admin\nashim\pfile\initnashim.ora


Instance created.

C:\Windows\system32>
C:\Windows\system32>set ORACLE_SID=nashim

C:\Windows\system32>set ORACLE_UNQNAME=nashim

C:\Windows\system32>sqlplus sys as sysdba

SQL*Plus: Release 11.2.0.1.0 Production on Mon Dec 2 12:57:25 2013

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

Enter password:

Connected to:
Oracle Database 11g Enterprise Edition Release 11.2.0.1.0 - Production
With the Partitioning, OLAP, Data Mining and Real Application Testing options

SQL> select status from v$instance;

STATUS
------------
STARTED

SQL> show parameter instance;

NAME                                 TYPE        VALUE
------------------------------------ ----------- ------------------------------
active_instance_count                integer
cluster_database_instances           integer     1
instance_groups                      string
instance_name                        string      nashim
instance_number                      integer     0
instance_type                        string      RDBMS
open_links_per_instance              integer     4
parallel_instance_group              string
parallel_server_instances            integer     1
SQL> show parameter control

NAME                                 TYPE        VALUE
------------------------------------ ----------- ------------------------------
control_file_record_keep_time        integer     7
control_files                        string      D:\APP\ORACLE\ORADATA\NASHIM\C
                                                 ONTROL01.CTL, D:\APP\ORACLE\FL
                                                 ASH_RECOVERY_AREA\NASHIM\CONTR
                                                 OL02.CTL
control_management_pack_access       string      DIAGNOSTIC+TUNING
SQL> ed
Wrote file afiedt.buf

  1   CREATE DATABASE nashim
  2      USER SYS IDENTIFIED BY manager
  3      USER SYSTEM IDENTIFIED BY manager
  4      LOGFILE GROUP 1 ('d:\app\oracle\oradata\nashim\redo01.log') SIZE 100M,
  5      GROUP 2 ('d:\app\oracle\oradata\nashim\redo02.log') SIZE 100M,
  6      GROUP 3 ('d:\app\oracle\oradata\nashim\redo03.log') SIZE 100M
  7      MAXLOGFILES 5
  8      MAXLOGMEMBERS 5
  9      MAXLOGHISTORY 1
 10      MAXDATAFILES 100
 11      MAXINSTANCES 1
 12      CHARACTER SET US7ASCII
 13      NATIONAL CHARACTER SET AL16UTF16
 14      DATAFILE 'd:\app\oracle\oradata\nashim\system01.dbf' SIZE 325M REUSE
 15      EXTENT MANAGEMENT LOCAL
 16      SYSAUX DATAFILE 'd:\app\oracle\oradata\nashim\sysaux01.dbf' SIZE 325M R
EUSE
 17      DEFAULT TEMPORARY TABLESPACE temp1
 18      TEMPFILE 'd:\app\oracle\oradata\nashim\temp01.dbf'
 19      SIZE 20M REUSE
 20      UNDO TABLESPACE UNDOTBS1
 21      DATAFILE 'd:\app\oracle\oradata\nashim\undotbs01.dbf'
 22*     SIZE 200M REUSE AUTOEXTEND ON MAXSIZE UNLIMITED
SQL> /

Database created.

SQL>
SQL> @?/rdbms/admin/catalog.sql

SQL> @?/rdbms/admin/catproc.sql
SQL> create spfile from pfile='d:\app\oracle\admin\nashim\pfile\initnashim.ora';

SQL> shutdown immediate
SQL> startup

    Create password files.

C:\> export ORACLE_SID=nashim
C:\> orapwd file=${ORACLE_HOME}\database\orapw${ORACLE_SID} password=sys entries=10

ORACLE 11G MANUAL DB CREATION

Oracle 11g Manual Database Creation.

    Create necessary directories.

    $ mkdir -p /u01/app/oracle/admin/nashim/adump/
    $ mkdir -p /u01/app/oracle/oradata/nashim/
    $ mkdir -p /u01/app/oracle/flash_recovery_area/nashim/

    Startup the database to nomount stage with sample parameter file.

    --Sample Parameter File
    ----------------------------------------------------------------------------
    nashim.__db_cache_size=213909504
    nashim.__java_pool_size=4194304
    nashim.__large_pool_size=4194304
    nashim.__oracle_base='/u01/app/oracle'#ORACLE_BASE set from environment
    nashim.__pga_aggregate_target=251658240
    nashim.__sga_target=369098752
    nashim.__shared_io_pool_size=0
    nashim.__shared_pool_size=138412032
    nashim.__streams_pool_size=0
    *.audit_file_dest='/u01/app/oracle/admin/nashim/adump'
    *.audit_trail='db'
    *.compatible='11.2.0.0.0'
    *.control_files='/u01/app/oracle/oradata/nashim/control01.ctl',
    '/u01/app/oracle/flash_recovery_area/nashim/control02.ctl'
    *.db_block_size=8192
    *.db_domain=''
    *.db_name='nashim'
    *.db_recovery_file_dest='/u01/app/oracle/flash_recovery_area'
    *.db_recovery_file_dest_size=4039114752
    *.diagnostic_dest='/u01/app/oracle'
    *.dispatchers='(PROTOCOL=TCP) (SERVICE=nashimXDB)'
    *.memory_target=620756992
    *.open_cursors=300
    *.processes=150
    *.remote_login_passwordfile='EXCLUSIVE'
    *.undo_tablespace='UNDOTBS1'
    ----------------------------------------------------------------------------


    $ export ORACLE_SID=nashim
    $ sqlplus / as sysdba

    SQL> startup nomount pfile='/u01/initnashim.ora';

    --Execute the Database Creation Script.


    CREATE DATABASE nashim
    USER SYS IDENTIFIED BY sys
    USER SYSTEM IDENTIFIED BY sys
    LOGFILE GROUP 1 ('/u01/app/oracle/oradata/nashim/redo01.log') SIZE 100M,
    GROUP 2 ('/u01/app/oracle/oradata/nashim/redo02.log') SIZE 100M,
    GROUP 3 ('/u01/app/oracle/oradata/nashim/redo03.log') SIZE 100M
    MAXLOGFILES 5
    MAXLOGMEMBERS 5
    MAXLOGHISTORY 1
    MAXDATAFILES 100
    MAXINSTANCES 1
    CHARACTER SET US7ASCII
    NATIONAL CHARACTER SET AL16UTF16
    DATAFILE '/u01/app/oracle/oradata/nashim/system01.dbf' SIZE 325M REUSE
    EXTENT MANAGEMENT LOCAL
    SYSAUX DATAFILE '/u01/app/oracle/oradata/nashim/sysaux01.dbf' SIZE 325M REUSE
    DEFAULT TEMPORARY TABLESPACE temp1
    TEMPFILE '/u01/app/oracle/oradata/nashim/temp01.dbf'
    SIZE 20M REUSE
    UNDO TABLESPACE UNDOTBS1
    DATAFILE '/u01/app/oracle/oradata/nashim/undotbs01.dbf'
    SIZE 200M REUSE AUTOEXTEND ON MAXSIZE UNLIMITED;


    Run additional scripts


    SQL>@?/rdbms/admin/catalog.sql
    SQL>@?/rdbms/admin/catproc.sql


    Create Spfile for the database.

    SQL> create spfile from pfile='/u01/initnashim.ora';

    File created.

    SQL> shutdown immediate
    SQL> startup

    Create password files.

    $ export ORACLE_SID=nashim
    $ orapwd file=${ORACLE_HOME}/dbs/orapw${ORACLE_SID} password=sys entries=10

CREATING ASM ON LINUX

$su root
$mkdir /asmdisk
$dd if=/dev/zero of=/asmdisk/disk1 bs=1024 count=1000
$dd if=/dev/zero of=/asmdisk/disk2 bs=1024 count=1000
$dd if=/dev/zero of=/asmdisk/disk3 bs=1024 count=1000
$dd if=/dev/zero of=/asmdisk/disk4 bs=1024 count=1000
$dd if=/dev/zero of=/asmdisk/disk5 bs=1024 count=1000
$chmod 777 /asmdisk/disk*
$/sbin/losetup /dev/loop1 /asmdisk/disk1
$/sbin/losetup /dev/loop2 /asmdisk/disk2
$/sbin/losetup /dev/loop3 /asmdisk/disk3
$/sbin/losetup /dev/loop4 /asmdisk/disk4
$/sbin/losetup /dev/loop5 /asmdisk/disk5
$raw /dev/raw/raw1 /dev/loop1
$raw /dev/raw/raw2 /dev/loop2
$raw /dev/raw/raw3 /dev/loop3
$raw /dev/raw/raw4 /dev/loop4
$raw /dev/raw/raw5 /dev/loop5
$cd /dev/raw
$ls -ltr
$chown -R oracle:oinstall *
$chmod 666 *
$ls -ltr
--$get /etc/rc.local
$exit
$cd $ORACLE_HOME/bin
$su root
$localconfig add
$dbca

What is PL/SQL

What is PL/SQL and what is it used for?

PL/SQL is Oracles Procedural Language extension to SQL. PL/SQLs language syntax, structure and data types are similar to that of ADA. The PL/SQL language includes object oriented programming techniques such as encapsulation, function overloading, and information hiding (all but inheritance). PL/SQL is commonly used to write data-centric programs to manipulate data in an Oracle database.

Should one use PL/SQL or Java to code procedures and triggers?

Internally the Oracle database supports two procedural languages, namely PL/SQL and Java. This leads to questions like "Which of the two is the best?" and "Will Oracle ever desupport PL/SQL in favour of Java?”
Many Oracle applications are based on PL/SQL and it would be difficult of Oracle to ever desupport PL/SQL. In fact, all indications are that PL/SQL still has a bright future ahead of it. Many enhancements are still being made to PL/SQL. For example, Oracle 9iDB supports native compilation of Pl/SQL code to binaries.
PL/SQL and Java appeal to different people in different job roles. The following table briefly describes the difference between these two language environments:
PL/SQL:
• Data centric and tightly integrated into the database.
• Proprietary to Oracle and difficult to port to other database systems.
• Data manipulation is slightly faster in PL/SQL than in Java.
• Easier to use than Java (depending on your background).
Java:
• Open standard, not proprietary to Oracle.
• Incurs some data conversion overhead between the Database and Java type systems.
• Java is more difficult to use (depending on your background).

How can one see if somebody modified any code?

Code for stored procedures, functions and packages is stored in the Oracle Data Dictionary. One can detect code changes by looking at the LAST_DDL_TIME column in the USER_OBJECTS dictionary view. Example:
SELECT OBJECT_NAME,
TO_CHAR(CREATED, DD-Mon-RR HH24:MI) CREATE_TIME,
TO_CHAR(LAST_DDL_TIME, DD-Mon-RR HH24:MI) MOD_TIME,
STATUS
FROM USER_OBJECTS
WHERE LAST_DDL_TIME > &CHECK_FROM_DATE;

How can one search PL/SQL code for a string/ key value?

The following query is handy if you want to know where a certain table, field or expression is referenced in your PL/SQL source code.

SELECT TYPE, NAME, LINE
FROM USER_SOURCE
WHERE UPPER(TEXT) LIKE %&KEYWORD%;

How can one keep a history of PL/SQL code changes?

 One can build a history of PL/SQL code changes by setting up an AFTER CREATE schema (or database) level trigger (available from Oracle 8.1.7). This way one can easily revert to previous code should someone make any catastrophic changes. Look at this example:

CREATE TABLE SOURCE_HIST -- Create history table
AS SELECT SYSDATE CHANGE_DATE, USER_SOURCE.*
FROM USER_SOURCE WHERE 1=2;

CREATE OR REPLACE TRIGGER change_hist -- Store code in hist table
AFTER CREATE ON SCOTT.SCHEMA -- Change SCOTT to your schema name
DECLARE
BEGIN
if DICTIONARY_OBJ_TYPE in (PROCEDURE, FUNCTION,
PACKAGE, PACKAGE BODY, TYPE) then
-- Store old code in SOURCE_HIST table
INSERT INTO SOURCE_HIST
SELECT sysdate, user_source.* FROM USER_SOURCE
WHERE TYPE = DICTIONARY_OBJ_TYPE
AND NAME = DICTIONARY_OBJ_NAME;
end if;
EXCEPTION
WHEN OTHERS THEN
raise_application_error(-20000, SQLERRM);
END;
/
show errors

Can one print to the screen from PL/SQL?

One can use the DBMS_OUTPUT package to write information to an output buffer. This buffer can be displayed on the screen from SQL*Plus if you issue the SET SERVEROUTPUT ON; command.

For example:
set serveroutput on
begin
dbms_output.put_line(Look Ma, I can print from PL/SQL!!!);
end;
/

DBMS_OUTPUT is useful for debugging PL/SQL programs. However, if you print too much, the output buffer will overflow. In that case, set the buffer size to a larger value, eg.: set serveroutput on size 200000

If you forget to set serveroutput on type SET SERVEROUTPUT ON once you remember, and then EXEC NULL;. If you havent cleared the DBMS_OUTPUT buffer with the disable or enable procedure, SQL*Plus will display the entire contents of the buffer when it executes this dummy PL/SQL block.

Note that DBMS_OUTPUT doesnt print blank or NULL lines. To overcome this problem, SET SERVEROUTPUT ON FORMAT WRAP; Look at this example with this option first disabled and then enabled:

SQL> SET SERVEROUTPUT ON
SQL> begin
2 dbms_output.put_line(The next line is blank);
3 dbms_output.put_line();
4 dbms_output.put_line(The above line should be blank);
5 end;
6 /

The next line is blank
The above line should be blank

SQL> SET SERVEROUTPUT ON FORMAT WRAP
SQL> begin
2 dbms_output.put_line(The next line is blank);
3 dbms_output.put_line();
4 dbms_output.put_line(The above line should be blank);
5 end;
6 /

The next line is blank
The above line should be blank

Can one call DDL statements from PL/SQL?

One can call DDL statements like CREATE, DROP, TRUNCATE, etc. from PL/SQL by using the "EXECUTE IMMEDATE" statement. Users running Oracle versions below 8i can look at the DBMS_SQL package.

begin
EXECUTE IMMEDIATE CREATE TABLE X(A DATE);
end;

NOTE: The DDL statement in quotes should not be terminated with a semicolon.

Can one use dynamic SQL statements from PL/SQL?

Starting from Oracle8i one can use the "EXECUTE IMMEDIATE" statement to execute dynamic SQL and PL/SQL statements (statements created at run-time). Look at these examples. Note that statements are NOT terminated by semicolons:

EXECUTE IMMEDIATE CREATE TABLE x (a NUMBER);
-- Using bind variables...
sql_stmt := INSERT INTO dept VALUES (:1, :2, :3);
EXECUTE IMMEDIATE sql_stmt USING dept_id, dept_name, location;
-- Returning a cursor...
sql_stmt := SELECT * FROM emp WHERE empno = :id;
EXECUTE IMMEDIATE sql_stmt INTO emp_rec USING emp_id;

One can also use the older DBMS_SQL package (V2.1 and above) to execute dynamic statements. Look at these examples:

CREATE OR REPLACE PROCEDURE DYNSQL AS
cur integer;
rc integer;
BEGIN
cur := DBMS_SQL.OPEN_CURSOR;
DBMS_SQL.PARSE(cur, CREATE TABLE X (Y DATE), DBMS_SQL.NATIVE);
rc := DBMS_SQL.EXECUTE(cur);
DBMS_SQL.CLOSE_CURSOR(cur);
END;
/

More complex DBMS_SQL example using bind variables:

CREATE OR REPLACE PROCEDURE DEPARTMENTS(NO IN DEPT.DEPTNO%TYPE) AS
v_cursor integer;
v_dname char(20);
v_rows integer;
BEGIN
v_cursor := DBMS_SQL.OPEN_CURSOR;
DBMS_SQL.PARSE(v_cursor, select dname from dept where deptno > :x, DBMS_SQL.V7);
DBMS_SQL.BIND_VARIABLE(v_cursor, :x, no);
DBMS_SQL.DEFINE_COLUMN_CHAR(v_cursor, 1, v_dname, 20);
v_rows := DBMS_SQL.EXECUTE(v_cursor);
loop
if DBMS_SQL.FETCH_ROWS(v_cursor) = 0 then
exit;
end if;
DBMS_SQL.COLUMN_VALUE_CHAR(v_cursor, 1, v_dname);
DBMS_OUTPUT.PUT_LINE(Deptartment name: ||v_dname);
end loop;
DBMS_SQL.CLOSE_CURSOR(v_cursor);
EXCEPTION
when others then
DBMS_SQL.CLOSE_CURSOR(v_cursor);
raise_application_error(-20000, Unknown Exception Raised: ||sqlcode|| ||sqlerrm);
END;
/

What is the difference between %TYPE and %ROWTYPE?

The %TYPE and %ROWTYPE constructs provide data independence, reduces maintenance costs, and allows programs to adapt as the database changes to meet new business needs.
%ROWTYPE is used to declare a record with the same types as found in the specified database table, view or cursor. Example:

DECLARE
v_EmpRecord emp%ROWTYPE;

%TYPE is used to declare a field with the same type as that of a specified tables column. Example:

DECLARE
v_EmpNo emp.empno%TYPE;

What is the result of comparing NULL with NULL?

NULL is neither equal to NULL, nor it is not equal to NULL. Any comparison to NULL is evaluated to NULL. Look at this code example to convince yourself.

declare
a number := NULL;
b number := NULL;
begin
if a=b then
dbms_output.put_line(True, NULL = NULL);
elsif a<>b then
dbms_output.put_line(False, NULL <> NULL);
else
dbms_output.put_line(Undefined NULL is neither = nor <> to NULL);
end if;
end;

 -===============

Rewrite complex subqueries with temporary tables Oracle created the global temporary table (GTT) and the SQL WITH operator to help divide-and-conquer complex SQL sub-queries (especially those with WHERE clause subqueries, SELECT clause scalar subqueries and FROM clause in-line views). Tuning SQL with temporary tables (and materializations in the WITH clause) can result in amazing performance improvements. Use minus instead of EXISTS subqueries Some say that using the minus operator instead of NOT IN and NOT Exists will result in a faster execution plan. Use SQL analytic functions The Oracle analytic functions can do multiple aggregations (e.g. rollup by cube) with a single pass through the tables, making them very fast for reporting SQL. Re-write NOT EXISTS and NOT EXISTS subqueries as outer joins In many cases of NOT queries (but ONLY where a column is defined as NULL), you can re-write the uncorrelated subqueries into outer joins with IS NULL tests. Note that this is a non-correlated sub-query, but it could be re-written as an outer join. select book_key from book where book_key NOT IN (select book_key from sales); Below we combine the outer join with a NULL test in the WHERE clause without using a sub-query, giving a faster execution plan. select b.book_key from book b, sales s where b.book_key = s.book_key(+) and s.book_key IS NULL; Index your NULL values If you have SQL that frequently tests for NULL, consider creating an index on NULL values. To get around the optimization of SQL queries that choose NULL column values (i.e. where emp_name IS NULL), we can create a function-based index using the null value built-in SQL function to index only on the NULL columns. Leave column names alone Never do a calculation on an indexed column unless you have a matching function-based index (a.k.a. FBI). Better yet, re-design the schema so that common where clause predicates do not need transformation with a BIF: where salary*5 > :myvalue where substr(ssn,7,4) = "1234" where to_char(mydate,mon) = "january" Avoid the use of NOT IN or HAVING Instead, a NOT EXISTS subquery may run faster (when appropriate). Avoid the LIKE predicate Always replace a "like" with equality, when appropriate. Never mix data types If a WHERE clause column predicate is numeric, do not to use quotes. For char index columns, always use quotes. There are mixed data type predicates: where cust_nbr = "123" where substr(ssn,7,4) = 1234 Dont fear full-table scans Not all OLTP queries are optimal when they uses indexes. If your query will return a large percentage of the table rows, a full-table scan may be faster than an index scan. This depends on many factors, including your configuration (values for db_file_multiblock_read_count, db_block_size), query parallelism and the number of table/index blocks in the buffer cache. Use those aliases Always use table aliases when referencing columns.

ORACLE WALLET

E:\oracle\product\wlt

E:\oracle\product\10.2.0\admin\student_wallet

Setup

In order to show the encryption working we need to open a datafile in a HEX editor. Rather than trying to open a huge datafile, it makes sense to create a small file for this test.

CONN sys/password AS SYSDBA

CREATE TABLESPACE tde_test

  DATAFILE '/u01/oradata/DB10G/tde_test.dbf' SIZE 128K

  AUTOEXTEND ON NEXT 64K;

Next, create a user with with a quota on the new tablespace.

CREATE USER test IDENTIFIED BY test DEFAULT TABLESPACE tde_test;

ALTER USER test QUOTA UNLIMITED ON tde_test;

GRANT CONNECT TO test;

GRANT CREATE TABLE TO test;

Normal Column

First we will prove that the data from a normal column can be seen from the OS. To do this create a test table and insert some data.

CONN test/test

CREATE TABLE tde_test (

  id    NUMBER(10),

  data  VARCHAR2(50)

)

TABLESPACE tde_test;

INSERT INTO tde_test (id, data) VALUES (1, 'This is a secret!');

COMMIT;

Then flush the buffer cache to make sure the data is written to the datafile.

CONN sys/password AS SYSDBA

ALTER SYSTEM FLUSH BUFFER_CACHE;

Open the datafile using a HEX editor (like UltraEdit) and the sentence "This is a secret!" is clearly visible amongst all the non-printable characters.

Encrypted Column

Before attempting to create a table with encrypted columns, a wallet must be created to hold the encryption key. The search order for finding the wallet is as follows:

1. If present, the location specified by the ENCRYPTION_WALLET_LOCATION parameter in the sqlnet.ora file.
2. If present, the location specified by the WALLET_LOCATION parameter in the sqlnet.ora file.
3. The default location for the wallet ($ORACLE_BASE/admin/$ORACLE_SID/wallet).

Although encrypted tablespaces can share the default database wallet, Oracle recommend you use a separate wallet for transparent data encryption functionality by specifying the ENCRYPTION_WALLET_LOCATION parameter in the sqlnet.ora file. To accomplish this we add the following entry into the sqlnet.ora file on the server and make sure the specified directory has been created.

ENCRYPTION_WALLET_LOCATION=

  (SOURCE=(METHOD=FILE)(METHOD_DATA=

    (DIRECTORY=/u01/app/oracle/admin/DB10G/encryption_wallet/)))

The following command creates and opens the wallet.

CONN sys/password AS SYSDBA

ALTER SYSTEM SET ENCRYPTION KEY AUTHENTICATED BY "myPassword";

Wallets must be reopened after an instance restart and can be closed to prevent access to encrypted columns.

ALTER SYSTEM SET WALLET OPEN IDENTIFIED BY "myPassword";

ALTER SYSTEM SET WALLET CLOSE;

Create a test table with an encrypted column and insert some data. Using the ENCRYPT clause on its own is the same as using the ENCRYPT USING 'AES192' clause, as AES192 is the default encryption method.

 CONN test/test

DROP TABLE tde_test;

PURGE RECYCLEBIN;

CREATE TABLE tde_test (

  id    NUMBER(10),

  data  VARCHAR2(50) ENCRYPT

)

TABLESPACE tde_test;

INSERT INTO tde_test (id, data) VALUES (1, 'This is a secret!');

COMMIT;

Flush the buffer cache to make sure the data is written to the datafile.

CcreONN sys/password AS SYSDBA

ALTER SYSTEM FLUSH BUFFER_CACHE;

When the file is opened using a HEX editor only non-printable characters are present. The test sentence cannot be seen anywhere, but the data is still clearly visible from a database connection.

SELECT * FROM tde_test;

        ID DATA

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

         1 This is a secret!

1 row selected.

Performance

There is a performance overhead associated with the encryption/decryption process. The following tables are used in a performance comparison.

CONN test/test

CREATE TABLE tde_test_1 (

  id    NUMBER(10),

  data  VARCHAR2(50)

)

TABLESPACE tde_test;

CREATE TABLE tde_test_2 (

  id    NUMBER(10),

  data  VARCHAR2(50) ENCRYPT

)

TABLESPACE tde_test;

The following script uses these tables to compare the speed of regular and encrypted inserts and regular and decrypted queries. Each test repeats 1000 times, with the timings reported in 100ths of a second.

SET SERVEROUTPUT ON SIZE UNLIMITED

DECLARE

  l_loops  NUMBER := 1000;

  l_data   VARCHAR2(50);

  l_start  NUMBER;

BEGIN

  EXECUTE IMMEDIATE 'TRUNCATE TABLE tde_test_1';

  EXECUTE IMMEDIATE 'TRUNCATE TABLE tde_test_2';

 

  l_start := DBMS_UTILITY.get_time;

  FOR i IN 1 .. l_loops LOOP

    INSERT INTO tde_test_1 (id, data)

    VALUES (i, 'Data for ' || i);

  END LOOP;

  DBMS_OUTPUT.put_line('Normal Insert   : ' || (DBMS_UTILITY.get_time - l_start));

 

  l_start := DBMS_UTILITY.get_time;

  FOR i IN 1 .. l_loops LOOP

    INSERT INTO tde_test_2 (id, data)

    VALUES (i, 'Data for ' || i);

  END LOOP;

  DBMS_OUTPUT.put_line('Encrypted Insert: ' || (DBMS_UTILITY.get_time - l_start));

  l_start := DBMS_UTILITY.get_time;

  FOR i IN 1 .. l_loops LOOP

    SELECT data

    INTO   l_data

    FROM   tde_test_1

    WHERE  id = i;

  END LOOP;

  DBMS_OUTPUT.put_line('Normal Query    : ' || (DBMS_UTILITY.get_time - l_start));

 

  l_start := DBMS_UTILITY.get_time;

  FOR i IN 1 .. l_loops LOOP

    SELECT data

    INTO   l_data

    FROM   tde_test_2

    WHERE  id = i;

  END LOOP;

  DBMS_OUTPUT.put_line('Decrypted Query : ' || (DBMS_UTILITY.get_time - l_start));

END;

/

Normal Insert   : 31

Encrypted Insert: 45

Normal Query    : 42

Decrypted Query : 58

PL/SQL procedure successfully completed.

SQL>

The results clearly demonstrate that encrypted inserts and decrypted queries are slower than their normal counterparts.

External Tables

External tables can be encrypted in a similar way to regular tables. First, we make sure the default data pump directory is available to the test user.

CONN sys/password AS SYSDBA

GRANT READ, WRITE ON DIRECTORY data_pump_dir TO test;

Next, we create the external table as a copy of an existing table, using the ENCRYPT clause.

CONN test/test

CREATE TABLE tde_test_1_ext (

  id,

  data ENCRYPT IDENTIFIED BY "myPassword"

)

ORGANIZATION EXTERNAL

(

  TYPE ORACLE_DATAPUMP

  DEFAULT DIRECTORY data_pump_dir

  location ('tde_test_1_ext.dmp')

)

AS

SELECT id,

       data

FROM   tde_test_1;

Views

The %_ENCRYPTED_COLUMNS views are used to display information about encrypted columns.

SET LINESIZE 100

COLUMN owner FORMAT A15

COLUMN tble_name FORMAT A15

COLUMN column_name FORMAT A15

SELECT * FROM dba_encrypted_columns;

OWNER           TABLE_NAME                     COLUMN_NAME     ENCRYPTION_ALG                SAL

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

TEST            TDE_TEST_2                     DATA            AES 192 bits key              YES

TEST            TDE_TEST_1_EXT                 DATA            AES 192 bits key              YES

2 rows selected.

SQL>