Here is the query that can be used
select t.name,e.encryptionalg,e.encyptedts,e.status from v$encrypted_tablespaces e,v$tablespace t where t.ts#=e.ts#(+);
We can further check the datafile encryption status of the above tablespaces using the below query
select name, encrypted from v$datafile_header where tablespace_name like '&1';
We can find the count of the datafiles which are not encrypted in the particular tablespace using the below query
select count(*) from v$datafile_header where tablespace_name like '&1' and encryped='NO';
We can find the count of the datafiles which are encrypted in the particular tablespace using the below query
select count(*) from v$datafile_header where tablespace_name like '&1' and encryped='YES';
Leave a Reply