Set Wallet location in sqlnet.ora:


ENCRYPTION_WALLET_LOCATION=
( SOURCE=(METHOD=FILE)(METHOD_DATA=
(DIRECTORY=/oracle/admin/DB10G/wallet/))
)


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 scott/tiger

> 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;






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.