TDE Tablespace : Listing Encrypted Tablespaces

Question

Transparent Data Encryption

Answer 1

For TDE tablespace

encryption, the following SQL statement lists all encrypted tablespaces with their encryption
algorithm and corresponding, encrypted, data files:


SELECT t.name "TSName", e.encryptionalg "Algorithm", d.file_name "File Name" FROM v$tablespace t, v$encrypted_tablespaces e, dba_data_files d
WHERE t.ts# = e.ts# and t.name = d.tablespace_name;


SQL statement lists the table owner, tables within encrypted tablespaces, and the encryption algorithm:

SELECT a.owner "Owner", a.table_name "Table Name", e.encryptionalg "Algorithm" FROM dba_tables a, v$encrypted_tablespaces e
WHERE a.tablespace_name in (select t.name from v$tablespace t,v$encrypted_tablespaces e where t.ts# = e.ts#);