SB Tablespaces tab does not show encryption algorithm for TEMPORARY or UNDO tablespaces

Toad for Oracle
1

Hi

Toad 16.1.53.1594

I can see from SB "Tablespaces" tab that which of the tablespaces are encrypted and by which algorithm.
For example SYSTEM tablespace:

CREATE TABLESPACE SYSTEM
DATAFILE 
  '+DATAC1' SIZE 2812920K AUTOEXTEND ON NEXT 32M MAXSIZE UNLIMITED
LOGGING
FORCE LOGGING
ENCRYPTION USING 'AES128'
....

But I can't say the same for the TEMPROARY or the UNDO tablespaces. They look like this:

CREATE TEMPORARY TABLESPACE TEMP2
TEMPFILE 
  '+DATAC1' SIZE 1G AUTOEXTEND ON NEXT 128M MAXSIZE 10G
ENCRYPTION ENCRYPT
TABLESPACE GROUP ''
EXTENT MANAGEMENT LOCAL UNIFORM SIZE 1M
FLASHBACK ON;

CREATE UNDO TABLESPACE UNDOTBS1
DATAFILE 
  '+DATAC1' SIZE 8G AUTOEXTEND ON NEXT 128M MAXSIZE 30G
ENCRYPTION ENCRYPT
ONLINE
RETENTION NOGUARANTEE
BLOCKSIZE 8K
FLASHBACK ON;

So I can see only "ENCRYPTION ENCRYPT"
But algorithm is missing.

When I try to modify it, then it shows AES128.
image

Regards
Raul

2

Hi Raul,

Thanks. I'll take a look at this today.

-John

3

For me, this works correctly:
CREATE TEMPORARY TABLESPACE TEMP2 TEMPFILE '+DATA' SIZE 5M AUTOEXTEND ON NEXT 1280K MAXSIZE UNLIMITED encryption encrypt;

But this throws ORA-28371 "ENCRYPTION clause and/or ENCRYPT storage option not allowed"
CREATE TEMPORARY TABLESPACE TEMP3 TEMPFILE '+DATA' SIZE 5M AUTOEXTEND ON NEXT 1280K MAXSIZE UNLIMITED ENCRYPTION USING 'AES128';

and this throws ORA-28370 "Encrypt, decrypt, or rekey not allowed"
ALTER TABLESPACE temp2 ENCRYPTION ONLINE USING 'AES192' REKEY;

All this is in 21c. So as far as I can tell, there is no bug here. Am I missing something?

4

You cannot alter TEMPORARY tablespace in that manner, need to drop ja recreate it.

What I meant, why does it show me the encryption algorithm for the CREATE TABLESPACE DDL.

Raul

5

I don't remember my exact line of thinking, but this query is returns AES128 for temp and undo. Maybe I thought that in a future version, Oracle might allow different algorithms for those types of tablespaces, so I left the door open for it.

select t.name, e.encryptionalg, e.encryptedts
from v$encrypted_tablespaces e, sys.ts$ t
where e.ts# = t.ts#
and encryptedts = 'YES'
and t.name = 'TEMP2'

What I meant, why does it show me the encryption algorithm for the CREATE TABLESPACE DDL.

In my 21c DB, Oracle allows me to change the encryption algorithm (as long as not TEMP/UNDO).

I'll make a change so that the dropdown is at least disabled in ALTER mode for temp/undo, since it can't be modified.

6

For temporary tablespace, changing encryption algorithm is not permitted, need to drop and recreate it.
For UNDO tablespaces, this is allowed, simple alter statement does that similar to other tablespaces.

What I meant, was, why the DDL does not show encryption algorithm being used:
image

Why not like this, showing user the actual encryption algorithm being used:

CREATE BIGFILE UNDO TABLESPACE UNDOTBS1
DATAFILE
'+DATAC1' SIZE 2000M AUTOEXTEND ON NEXT 4G MAXSIZE 512G
ENCRYPTION USING 'AES128' ENCRYPT
--..
;

Also, the query you are using, sys.ts$. Are you sure this is the one we should use for getting tablespace name..? (I've seen this dictionary object for the very first time)

I am not getting correct results with this one, I only get 14 rows for the whole CDB, but actually there are 24 tablespaces total for the whole CDB (cdb+all the pdb's).

This should give better results, also you can add tablespace name filter as well.

select e.con_id, t.name, e.encryptionalg, e.encryptedts from v$encrypted_tablespaces e, v$tablespace t where e.ts# = t.ts# and e.con_id = t.con_id order by con_id, name;

Regards
Raul

7

HI Raul.

Thanks for the details. I was under the impression that UNDO and TEMP had the same rules here. And you're right about the query.

-John