how to check if oracle database is encrypted

Database encryption is a important concept these days because of security breaches . It is common practice to have database encryption enabled in Oracle database. Encryption can be present at two Level

TDE ( Transparent Data encryption) : This is setup for Table level
TSE (Tablespace encryption): This is setup for Tablespace level.

What is Oracle Transparent Data Encryption (TDE)?  

Oracle TDE allows administrators to encrypt sensitive data (i.e. Personally Identifiable Information or PII) by protecting it from unauthorized access via encryption key if storage media, backups, or datafiles are stolen.

What can you encrypt with TDE?  

You can encrypt tablespaces and/or table columns. One reason to encrypt a tablespace is that it allows for index range scans while column encryption does not. Additionally, all data stored in the tablespace are encrypted, precluding administrators from having to identify all columns requiring encryption. Tablespace encryption also allows for bulk encryption and caching performance.

Additional detail for tablespace encryption vs. column encryption can be found on Oracle’s website.

how to check if oracle database is encrypted ?

To check the wallet or Keystore in Oracle database

select WRL_PARAMETER, status, WALLET_TYPE from v$encryption_wallet;

To check the Master key in the Wallet

Select * from v$encryption_keys;

To check Encrypted column in the tables

SELECT * FROM dba_encrypted_columns;

SET LINESIZE 100
COLUMN owner FORMAT A10
COLUMN table_name FORMAT A20
COLUMN column_name FORMAT A20
SELECT * FROM dba_encrypted_columns;

To check the tablespace level encryption in Oracle database

SELECT NAME, ENCRYPTIONALG, ENCRYPTEDTS
FROM V$ENCRYPTED_TABLESPACES, V$TABLESPACE
WHERE V$ENCRYPTED_TABLESPACES.TS# = V$TABLESPACE.TS#;

To check if particular Tablespace is encrypted

SELECT tablespace_name, encrypted, status FROM dba_tablespaces where tablespace_name = "&tablespace_name"

Following are Encrypted Algorithm existing with Oracle Database

AES192: Sets the key length to 192 bits. AES is the abbreviation for Advanced Encryption Standard.
3DES168: Sets the key length to 168 bits. 3DES is the abbreviation for Triple Data Encryption Standard.
AES128: Sets the key length to 128 bits. This option is the default.
AES256: Sets the key length to 256 bits.