Friday, April 7, 2017

How to generate CREATE TABLESPACE statements for both encrypted and non-encrypted tablespaces

I used the following statement when I was supposed to pre-create encrypted tablespaces as part of a migration project. We were supposed to move a database using TDE from one server to another, from AIX to Linux. The database wasn't big so import could be used.

The statement will pre-create all tablespaces as they were in the old database with the exception of SYSTEM, SYSAUX etc.

SELECT 'create bigfile tablespace ' ||T.NAME || ' datafile ''/u02/oradata/proddb01' || LOWER(T.NAME) || '.dbf'' size 256M autoextend on next 128M maxsize unlimited extent management local segment space management auto encryption using ''' || E.ENCRYPTIONALG || ''' default storage(encrypt);' 
FROM  V$TABLESPACE T, V$ENCRYPTED_TABLESPACES E
WHERE T.TS# = E.TS#
UNION
SELECT 'create bigfile tablespace ' ||T.NAME || ' datafile ''/u02/oradata/proddb01' || LOWER(F.NAME) || ''' size 256M autoextend on next 128M maxsize unlimited extent management local segment space management auto;' 
FROM  V$TABLESPACE T, V$DATAFILE F
WHERE T.TS# = F.TS#
AND T.NAME NOT IN ('SYSTEM','SYSAUX','UNDOTBS1','USERS')
AND F.TS# NOT IN (SELECT TS# FROM V$ENCRYPTED_TABLESPACES);

No comments:

Post a Comment