Checking Encrypted Tablespaces in the Current Database Instance

DBA_TABLESPACES

Describes all tablespaces in the database. For example, find out if the tablespace has been encrypted, enter the following:

SELECT TABLESPACE_NAME, ENCRYPTED FROM DBA_TABLESPACES

TABLESPACE_NAME              ENC
---------------------------- ----
SYSTEM                       NO
SYSAUX                       NO
UNCOTBS1                     NO
TEMP                         NO
USERS                        NO
EXAMPLE                      NO
SECURESPACE                  YES

USER_TABLESPACES

Describes the tablespaces accessible to the current user. It has the same columns as DBA_TABLESPACES, except for the PLUGGED_IN column.

V$ENCRYPTED_TABLESPACES

Displays information about the tablespaces that are encrypted. For example:

SELECT * FROM V$ENCRYPTED_TABLESPACES;

        TS#  ENCRYPTIONALG  ENCRYPTEDTS
-----------  -------------  -----------
         6   AES128          YES

The list includes the tablespace number, its encryption algorithm, and whether its encryption is enabled or disabled.

If you want to find the name of the tablespace, use the following join operation:

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