Skip to content

How to List Encrypted Columns

Column Level Encryption

In this use case, we have built encryption infrastructure on the database and developers encrypted some columns. Now we would like know which columns are encrypted.

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

SQL> column owner format a10;
SQL> column table_name format a20;
SQL> column column_name format a20;
SQL> select owner, table_name, column_name from all_encrypted_columns order by 1,2,3;

OWNER      TABLE_NAME           COLUMN_NAME
---------- -------------------- --------------------
ERPAPP     PAY_DAILY            DAILK351
ERPAPP     PAY_DAILY            DAILK456
ERPAPP     PAY_DAILY            DAILK661
ERPAPP     PAY_HISTO            HISTK344
ERPAPP     PAY_HISTO            HISTK411
ERPAPP     PAY_WEEKLY           WEEKK814
ERPAPP     PAY_WEEKLY           WEEKK436
ERPAPP     PAY_WEEKLY           WEEKK741

8 rows selected.

As we can see, there're 8 columns that have been encrypted in the database.

By the way, there's no actual table level encryption. However, you can alternatively move the table to the encrypted tablespace to make the whole table encrypted.

Leave a Reply

Your email address will not be published.