***********************************************************************
* Description: TDE tablespace without Lob data via moving table method
* Date: 02:14 PM EST, 08/14/2017
***********************************************************************



<1> Step 1 - Bounce database into non-archivelog and session restricted mode:
     |
     |__ SQL> shutdown immediate;
     |
     |__ SQL> startup mount;
     |
     |__ SQL> alter database noarchivelog;
     |
     |__ SQL> select log_mode from v$database;
     |
     |__ SQL> alter database open;
     |
     |__ SQL> alter system enable restricted session;
     |
     |__ SQL> select logins from v$instance;
	 
	 
	 
	 
<2> Step 2 - Disable database internal jobs:
     |
     |__ SQL> select 'exec dbms_ijob.broken('||job||',true);' from dba_jobs where broken='N';

	 
	 
	 
<3> Step 3 - Create TDE secured tablespace:
     |
     |__ SQL> CREATE TABLESPACE BTB_GPS_DATA02_SEC DATAFILE '+DATA' SIZE 32767M AUTOEXTEND ON NEXT 1056M ENCRYPTION USING 'AES256' DEFAULT STORAGE(ENCRYPT);	 
	 
	 


<4> Step 4 - Run below script to prepare table/index moving commands dynamically:
     |
     |__ SQL> @BTB_GPS_DATA02.sql


                     -- Table
                     set pages 10000 lines 1000
                     set head off
                     set feedback off
                     spool BTB_GPS_DATA02_table.sql
                     select 'alter session set nls_date_format = "DD-MON-YYYY HH24:MI:SS";' from dual;
                     select 'select sysdate from dual;' from dual;
                     select 'ALTER TABLE '||t1.OWNER ||'.'||t1.table_name||' MOVE '||' TABLESPACE '|| T1.TABLESPACE_NAME ||'_SEC PARALLEL(DEGREE 4) NOLOGGING;' 
                     from dba_tables T1 where  tablespace_name='BTB_GPS_DATA02';
                     select 'select sysdate from dual;' from dual;
                     select 'spool off' from dual;
                     spool off;
                     	 
                     	 
                     -- Table Index:
                     set pages 10000 lines 1000 
                     set head off
                     set feedback off
                     spool BTB_GPS_DATA02_index.sql
                     select 'alter session set nls_date_format = "DD-MON-YYYY HH24:MI:SS";' from dual;
                     select 'select sysdate from dual;' from dual;
                     select 'ALTER INDEX '||t1.OWNER ||'.'||t1.index_name||' Rebuild '||' TABLESPACE '|| T1.TABLESPACE_NAME ||'_SEC PARALLEL(DEGREE 4) NOLOGGING;' 
                     from dba_indexes T1
                     where T1.tablespace_name='BTB_GPS_DATA02';
                     select 'select sysdate from dual;' from dual;
                     select 'spool off' from dual;
                     spool off;
                     
                     exit;	 



<5> Step 5 - Run moving table script:
     |
     |__ SQL> @BTB_GPS_DATA02_table.sql
	 
	
	
	
<6> Step 6 - Run moving index script:
     |
     |__ SQL> @BTB_GPS_DATA02_index.sql	 

	 


<7> Step 7 - Confirm the tablespace is encrypted:
     |
     |__ SQL> select tablespace_name, encrypted from dba_tablespaces where encrypted='YES' where tablespace_name = 'BTB_GPS_DATA02_SEC';	 

	 
                     TABLESPACE_NAME                ENC
                     ------------------------------ ---
                     BTB_GPS_DATA02_SEC             YES	 


				 
					 
<8> Step 8 - Change user default tablespace:
     |
     |__ SQL> alter user APP_GPS default tablespace BTB_GPS_DATA02_SEC;



	 
<9> Step 9 - Switch database back to archivelog mode and out of restricted mode:
     |
     |__ SQL> alter system disable restricted session;
     |
     |__ SQL> select logins from v$instance;
     |
     |__ SQL> shutdown immediate;
     |
     |__ SQL> startup mount;
     |
     |__ SQL> alter database archivelog;
     |
     |__ SQL> alter database open;




<10> Step 10 - Re-active database internal jobs:
     |
     |__ SQL> select 'exec dbms_ijob.broken('||job||',false);' from dba_jobs where broken='Y';