Skip to content

How to Check Encrypted Tablespace in Oracle

  • by

Tablespace Level Encryption

In this use case, DBA has done encryption infrastructure and create some encrypted tablespaces in the database. Now we would like to put some tables into one of the encrypted tablespaces, but we don't know which tablespaces are encrypted.

DBA_TABLESPACES

Here is the query statement that can check which tablespaces are encrypted.

SQL> select tablespace_name from dba_tablespaces where encrypted = 'YES';

TABLESPACE_NAME
------------------------------
ENC_TBS01
ENC_TBS02

As we can see, there're 2 tablespaces that can be used for our table.

V$ENCRYPTED_TABLESPACES

To check the algorithm of encryption for those tablespaces, we may query V$ENCRYPTED_TABLESPACES.

SQL> select t.name tablespace_name, e.encryptionalg algo from v$tablespace t, v$encrypted_tablespaces e where t.ts# = e.ts# order by 1;

TABLESPACE_NAME                ALGO
------------------------------ -------
ENC_TBS01                      AES128
ENC_TBS02                      AES256

A little off-topic, do you know that why we don't use ALL_TABLESPACES dictionary view? You can take a look.

Leave a Reply

Your email address will not be published. Required fields are marked *