Переопределение Z#IMS_OUT_REQUEST для экономии места : ЦФТ-Банк

create tablespace IMS datafile '/db1/oradata/EKKSUP/ims.dbf' size 1G autoextend on next 1g maxsize 10G;
alter user ibs quota unlimited on IMS;
truncate table ibs.Z#NVB_IMS_OUT_REQ;
alter table ibs.Z#NVB_IMS_OUT_REQ move tablespace IMS;
select dbms_lob.substr((SELECT DBMS_METADATA.GET_DDL('TABLE','Z#NVB_IMS_OUT_REQ','IBS') from dual),4000,1) from dual;

CREATE TABLE "IBS"."Z#NVB_IMS_OUT_REQ_NEW" 
("ID" NUMBER, 
"SN" NUMBER, 
"SU" NUMBER, 
"CLASS_ID" VARCHAR2(16), 
"STATE_ID" VARCHAR2(16), 
"C_ABONENT" NUMBER, 
"C_PUSH_TIME" DATE, 
"C_PUSH_USER" VARCHAR2(30), 
"C_CLOB_VAL" CLOB, 
"C_OR_CLOB_VAL" CLOB, 
"C_MESS_ID" VARCHAR2(100), 
"C_OBJ_ID" VARCHAR2(128), 
"C_EXT_CLASS" VARCHAR2(32), 
"C_EVENT" VARCHAR2(255), 
"C_LINKED_OBJ" NUMBER NOT NULL ENABLE, 
"C_FILENAME" VARCHAR2(128), 
"C_PACK_TYPE" NUMBER, 
"C_BLOB_VAL" BLOB, 
"C_CORR_ID" VARCHAR2(100), 
"C_INTERFACE_NS" VARCHAR2(100), 
"C_INTERFACE" VARCHAR2(100), 
"C_ERR_TEXT" VARCHAR2(600), 
"C_OBJ_CLASS" VARCHAR2(100), 
"C_HTTP_STATUS" NUMBER, 
"C_USER_ID" NUMBER, 
"C_TICKET" VARCHAR2(40), 
"C_ORG" NUMBER) 
TABLESPACE "IMS"
LOB ("C_CLOB_VAL") STORE AS SECUREFILE (TABLESPACE "IMS" COMPRESS HIGH DEDUPLICATE)
LOB ("C_OR_CLOB_VAL") STORE AS SECUREFILE (TABLESPACE "IMS" COMPRESS HIGH DEDUPLICATE) 
LOB ("C_BLOB_VAL") STORE AS SECUREFILE (TABLESPACE "IMS" COMPRESS HIGH DEDUPLICATE)
PARTITION BY RANGE (C_PUSH_TIME) INTERVAL (NUMTOYMINTERVAL (1,'MONTH'))(PARTITION oldims VALUES LESS THAN (TO_DATE('01-01-2018', 'DD-MM-YYYY')) );

exec dbms_redefinition.can_redef_table('IBS', 'Z#NVB_IMS_OUT_REQ'); 
alter session force parallel dml parallel 32;
alter session force parallel query parallel 32;

exec DBMS_REDEFINITION.START_REDEF_TABLE('IBS','Z#NVB_IMS_OUT_REQ','Z#NVB_IMS_OUT_REQ_NEW');
declare error_count pls_integer := 0; begin dbms_redefinition.copy_table_dependents('IBS','Z#NVB_IMS_OUT_REQ','Z#NVB_IMS_OUT_REQ_NEW',1,true,true,true,true,error_count);end;
exec dbms_redefinition.finish_redef_table('IBS','Z#NVB_IMS_OUT_REQ','Z#NVB_IMS_OUT_REQ_NEW');

DECLARE
   sql_stmt   VARCHAR2 (100);
BEGIN
   FOR c_rec IN (SELECT index_name FROM dba_indexes WHERE table_name = 'Z#NVB_IMS_OUT_REQ' and partitioned='NO')
   LOOP
      sql_stmt:='ALTER INDEX IBS.' || c_rec.index_name || ' rebuild compress tablespace IMS';
       EXECUTE IMMEDIATE sql_stmt;
   END LOOP;
END;
/

alter table ibs.Z#NVB_IMS_OUT_REQ parallel 32;
alter table ibs.Z#IMS_OUT_REQUEST parallel 32;
insert into ibs.Z#NVB_IMS_OUT_REQ select /*+parallel(source 32) */ * from ibs.Z#IMS_OUT_REQUEST;