**************************************************************************
* Description: Shell script of TDE redefiniting table without Lob segment
* Date: 10:51 PM EST, 08/13/2017
**************************************************************************



spool Redef_TRNS_RQUEUE.log
select 'TABLE: TRNS_RQUEUE' from dual; 
alter session set nls_date_format = 'DD-MON-YYYY HH24:MI:SS';
select sysdate from dual;

select count(*) from APP_GPS.TRNS_RQUEUE;


--- Prework: Creating tablespace ---
CREATE TABLESPACE BTB_GPS_INDX_SEC DATAFILE '+DATA' SIZE 2G AUTOEXTEND ON NEXT 1056M ENCRYPTION USING 'AES256' DEFAULT STORAGE(ENCRYPT);
CREATE TABLESPACE BTB_GPS_DATA02_SEC DATAFILE '+DATA' SIZE 32767M AUTOEXTEND ON NEXT 1056M ENCRYPTION USING 'AES256' DEFAULT STORAGE(ENCRYPT);



--- Step 1: Creating interim table ---
select 'Step 1 - Create Table' from dual;
                                                                                      
  CREATE TABLE "APP_GPS"."TRNS_RQUEUE_SEC"                                                                                                                           
   (    "ID" NUMBER,                                                                                                                                                    
        "RECORD_LOCATOR" VARCHAR2(255),                                                                                                                                 
        "PROCESSING_PCC" VARCHAR2(100),                                                                                                                                 
        "QUEUE" VARCHAR2(100),                                                                                                                                          
        "PROCESSED_DT" DATE,                                                                                                                                            
        "STATUS" VARCHAR2(200),                                                                                                                                         
         CONSTRAINT "TRNS_RQUEUE_SEC_PK" PRIMARY KEY ("ID")                                                                                                         
  USING INDEX PCTFREE 10 INITRANS 2 MAXTRANS 255 COMPUTE STATISTICS NOLOGGING                                                                                           
  STORAGE(INITIAL 1048576 NEXT 1048576 MINEXTENTS 1 MAXEXTENTS 2147483645                                                                                               
  PCTINCREASE 0 FREELISTS 1 FREELIST GROUPS 1                                                                                                                           
  BUFFER_POOL DEFAULT FLASH_CACHE DEFAULT CELL_FLASH_CACHE DEFAULT)                                                                                                     
  TABLESPACE "BTB_GPS_INDX_SEC"  ENABLE                                                                                                                                 
   ) SEGMENT CREATION IMMEDIATE                                                                                                                                         
  PCTFREE 10 PCTUSED 40 INITRANS 1 MAXTRANS 255                                                                                                                         
 NOCOMPRESS LOGGING                                                                                                                                                     
  STORAGE(INITIAL 5242880 NEXT 10485760 MINEXTENTS 1 MAXEXTENTS 2147483645                                                                                              
  PCTINCREASE 0 FREELISTS 1 FREELIST GROUPS 1                                                                                                                           
  BUFFER_POOL DEFAULT FLASH_CACHE DEFAULT CELL_FLASH_CACHE DEFAULT)                                                                                                     
  TABLESPACE "BTB_GPS_DATA02_SEC"; 

  
  
--- Step 2: Feasible verification ---
select 'Step 2 - Feasible' from dual;
EXEC DBMS_REDEFINITION.CAN_REDEF_TABLE('APP_GPS', 'TRNS_RQUEUE');  



--- Step 3: Redefination ---
select 'Step 3 - Redefinition' from dual;                                                           
EXEC DBMS_REDEFINITION.START_REDEF_TABLE('APP_GPS', 'TRNS_RQUEUE', 'TRNS_RQUEUE_SEC');



--- Step 4: Copy Dependency ---
select 'Step 4 - Copy Dependency' from dual;

	DECLARE
       error_count pls_integer := 0;
       BEGIN
        DBMS_REDEFINITION.COPY_TABLE_DEPENDENTS('APP_GPS', 'TRNS_RQUEUE', 'TRNS_RQUEUE_SEC', dbms_redefinition.cons_orig_params, TRUE,TRUE,TRUE,FALSE, error_count);
        DBMS_OUTPUT.PUT_LINE('errors := ' || TO_CHAR(error_count));
       END;
    /


	
--- Step 5: Finish redefinition ---
select 'Step 5 - Finish' from dual; 
EXEC DBMS_REDEFINITION.FINISH_REDEF_TABLE('APP_GPS', 'TRNS_RQUEUE', 'TRNS_RQUEUE_SEC');
 
 
 
--- Step 6: Postwork --- 
select 'Step 6 - Post' from dual; 
select count(*) from APP_GPS.TRNS_RQUEUE;  
select count(*) from APP_GPS.TRNS_RQUEUE_SEC; 
  
select sysdate from dual;
spool off; 
exit;