By Todd Bottger-Oracle on Apr 28, 2014
Database administrators who manage multiple Oracle Databases may need to take a quick inventory of the TDE encrypted objects in a given database. Taking an inventory may be necessary not only for fulfilling day-to-day DBA responsibilities but also for attestation as part of periodic IT security audits.
Fortunately, listing out the TDE encrypted objects in an Oracle Database is straightforward using simple SQL queries. These queries leverage the built-in DBA_* views, and you need to have the correct database privileges to use them (e.g. have DBA role, hold individual select privileges, be logged in as SYSDBA, etc.):
Below are example queries that use these DBA_* views to answer basic questions about what TDE encrypted objects exist in the database:
- What tables contain TDE encrypted columns?
sql> select table_name, column_name from dba_encrypted_columns;
- What tables are stored in TDE encrypted tablespaces?
sql> select a.table_name, a.tablespace_name from dba_tables a, dba_tablespaces b where a.tablespace_name = b.tablespace_name and b.encrypted = 'YES';
- What indexes are stored in TDE encrypted tablespaces?
sql> select a.index_name, a.tablespace_name from dba_indexes a, dba_tablespaces b where a.tablespace_name = b.tablespace_name and b.encrypted = 'YES' and index_name not like 'SYS_IL%';
- What are all of the TDE encrypted objects including tables, indexes and columns?
[Use a combination of the above queries]