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
To check what tablespaces are encrypted in an Oracle database, we should query a dictionary view, DBA_TABLESPACES for sure. 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.