DBMS_REDEFINITION_PARTITION

-- Dawid Mocek
-- Reorganize single partition using new tablespace with compression
-- Req: options_flag => DBMS_REDEFINITION.cons_use_pk

-- DB 11.2.0.3 has an error in dbms_redefinition package - it does not clear properly temporary data in dictionary/views,
-- so trying to redefine next object will throw an exception

-- --
-- AS SYS
-- chg @part_name
BEGIN
  DBMS_REDEFINITION.CAN_REDEF_TABLE( uname => 'xxxx_xxx', tname => 'LOG_EVENT', options_flag => DBMS_REDEFINITION.cons_use_pk, part_name => 'P_12_2015');
END;
/

-- AS SYS
-- chg @TBSPC_xxxx_xxx_INT_PAR_TMP filename
-- CREATE TABLESPACE "TBSPC_xxxx_xxx_INT_TMP" DATAFILE '+DATA/DB/DATAFILE/xxxx_xxx_log/int_tmp.dbf' SIZE 4G REUSE AUTOEXTEND OFF LOGGING ONLINE PERMANENT BLOCKSIZE 8192 EXTENT MANAGEMENT  LOCAL AUTOALLOCATE  SEGMENT SPACE MANAGEMENT AUTO  DEFAULT COMPRESS ;
CREATE TABLESPACE "TBSPC_xxxx_xxx_INT_PAR_TMP" DATAFILE '+DATA/DB/DATAFILE/xxxx_xxx_log/event122015c.dbf' SIZE 4G REUSE AUTOEXTEND OFF LOGGING ONLINE PERMANENT BLOCKSIZE 8192 EXTENT MANAGEMENT  LOCAL AUTOALLOCATE  SEGMENT SPACE MANAGEMENT AUTO DEFAULT COMPRESS ;
-- CREATE TABLESPACE "TBSPC_xxxx_xxx_INT_IDX_TMP" DATAFILE '+DATA/DB/DATAFILE/xxxx_xxx_log/idx_log_event.dbf' SIZE 4G REUSE AUTOEXTEND OFF LOGGING ONLINE PERMANENT BLOCKSIZE 8192 EXTENT MANAGEMENT  LOCAL AUTOALLOCATE  SEGMENT SPACE MANAGEMENT AUTO DEFAULT COMPRESS ;

-- AS SYS
-- ALTER USER yyyy quota unlimited ON TBSPC_xxxx_xxx_INT_IDX_TMP;
ALTER USER yyyy quota unlimited ON TBSPC_xxxx_xxx_INT_PAR_TMP;
-- ALTER USER yyyy quota unlimited ON TBSPC_xxxx_xxx_INT_TMP;

-- ALTER USER zzzz quota unlimited ON TBSPC_xxxx_xxx_INT_IDX_TMP;
ALTER USER zzzz quota unlimited ON TBSPC_xxxx_xxx_INT_PAR_TMP;
-- ALTER USER zzzz quota unlimited ON TBSPC_xxxx_xxx_INT_TMP;

-- ALTER USER wwww quota unlimited ON TBSPC_xxxx_xxx_INT_IDX_TMP;
ALTER USER wwww quota unlimited ON TBSPC_xxxx_xxx_INT_PAR_TMP;
-- ALTER USER wwww quota unlimited ON TBSPC_xxxx_xxx_INT_TMP;


-- AS USER
CREATE TABLE "xxxx_xxx"."INT_LOG_EVENT"
  (
   [...]
  )
  TABLESPACE TBSPC_xxxx_xxx_INT_PAR_TMP;

-- AS SYS
-- Start the redefinition process using /* rowid */ cons_use_pk
-- @part_name
BEGIN
  DBMS_REDEFINITION.START_REDEF_TABLE( uname => 'xxxx_xxx', orig_table => 'oooo_oooo', int_table => 'INT_LOG_EVENT', col_mapping => NULL, options_flag => DBMS_REDEFINITION.cons_use_pk, part_name => 'P_12_2015');
END;
/


-- AS USER
-- Manually create any local indexes on the interim table
CREATE INDEX "xxxx_xxx"."INT_LOG_EVENT_EVENT_CODE" ON "xxxx_xxx"."INT_LOG_EVENT"
  (
    "EVENT_CODE"
  )
  PCTFREE 10 INITRANS 2 MAXTRANS 255 COMPUTE STATISTICS TABLESPACE "TBSPC_xxxx_xxx_INT_IDX_TMP" ;

CREATE INDEX "xxxx_xxx"."INT_LOG_EVENT_EVENT_NAME" ON "xxxx_xxx"."INT_LOG_EVENT"
  (
    "EVENT_NAME"
  )
  PCTFREE 10 INITRANS 2 MAXTRANS 255 COMPUTE STATISTICS TABLESPACE "TBSPC_xxxx_xxx_INT_IDX_TMP" ;

CREATE INDEX "xxxx_xxx"."INT_LOG_EVENT_EVENT_TIME" ON "xxxx_xxx"."INT_LOG_EVENT"
  (
    "EVENT_TIME"
  )
  PCTFREE 10 INITRANS 2 MAXTRANS 255 COMPUTE STATISTICS TABLESPACE "TBSPC_xxxx_xxx_INT_IDX_TMP" ;

CREATE INDEX "xxxx_xxx"."INT_LOG_EVENT_E_SERVICE_CODE" ON "xxxx_xxx"."INT_LOG_EVENT"
  (
    "E_SERVICE_CODE"
  )
  PCTFREE 10 INITRANS 2 MAXTRANS 255 COMPUTE STATISTICS TABLESPACE "TBSPC_xxxx_xxx_INT_IDX_TMP" ;

CREATE INDEX "xxxx_xxx"."INT_LOG_EVENT_L_TIME" ON "xxxx_xxx"."INT_LOG_EVENT"
  (
    "L_TIME"
  )
  PCTFREE 10 INITRANS 2 MAXTRANS 255 COMPUTE STATISTICS TABLESPACE "TBSPC_xxxx_xxx_INT_IDX_TMP" ;

CREATE UNIQUE INDEX "xxxx_xxx"."INT_LOG_EVENT_PK" ON "xxxx_xxx"."INT_LOG_EVENT"
  (
    "L_ID"
  )
  PCTFREE 10 INITRANS 2 MAXTRANS 255 COMPUTE STATISTICS TABLESPACE "TBSPC_xxxx_xxx_INT_IDX_TMP" ;

-- AS SYS
-- Optionally synchronize the interim table.
-- @part_name
BEGIN
  DBMS_REDEFINITION.SYNC_INTERIM_TABLE( uname => 'xxxx_xxx', orig_table => 'LOG_EVENT', int_table => 'INT_LOG_EVENT', part_name => 'P_12_2015');
END;
/

-- AS SYS
-- Complete the redefinition.
-- @param part_name
BEGIN
  DBMS_REDEFINITION.FINISH_REDEF_TABLE( uname => 'xxxx_xxx', orig_table => 'LOG_EVENT', int_table => 'INT_LOG_EVENT', part_name => 'P_12_2015');
END;
/

-- AS SYS
-- abort
-- @param part_name
BEGIN
  DBMS_REDEFINITION.ABORT_REDEF_TABLE ( uname => 'xxxx_xxx', orig_table => 'LOG_EVENT', int_table => 'INT_LOG_EVENT', part_name => 'P_12_2015');
END;
/

-- AS SYS
-- !!! Be sure that object has been redefined !!!
SELECT partition_name,
  tablespace_name
FROM dba_tab_partitions
WHERE table_name = 'LOG_EVENT';

-- AS SYS/USER
-- optional rebuild indexes
SELECT 'ALTER INDEX '
  ||INDEX_NAME
  ||' REBUILD'
FROM user_indexes
WHERE table_name='LOG_EVENT';


-- AS SYS
-- chg !!! tablespace name !!!
-- DROP TABLESPACE TBSPC_xxxx_xxx_LOG_EVENT122015 INCLUDING CONTENTS AND DATAFILES;

-- AS SYS
-- chg  !!! names !!!
-- ALTER TABLESPACE TBSPC_xxxx_xxx_INT_PAR_TMP RENAME TO TBSPC_xxxx_xxx_LOG_EVENT122015;

-- AS SYS
-- chg datafile name and size
-- ALTER DATABASE DATAFILE '+DATA/db/datafile/xxxx_xxx/event122015c.dbf' RESIZE 137M;

-- AS SYS
-- chg partiton name
-- select count(l_id) from xxxx_xxx.log_event partition(P_12_2015);

-- AS SYS
-- chg tablespace name
--  ALTER TABLESPACE TBSPC_xxxx_xxx_LOG_EVENT122015 READ ONLY ;


-- AS SYS
-- sprawdz status wnętrzności bazy

-- select * from v$db_object_cache ;
-- select OWNER,OBJECT_NAME,OBJECT_TYPE,STATUS from dba_objects where OWNER='xxxx_xxx';
-- select * from sys.snap_loadertime$;
-- select * from all_objects where object_id = ??;
-- select * from all_objects ao, sys.snap_loadertime$ sl where ao.object_id = sl.TABLEOBJ#;
-- select * from sys.sumpartlog$;

-- AS USER
-- bypass ora-error
 CREATE materialized VIEW mdummy TABLESPACE TBSPC_TEST NOLOGGING refresh complete
AS
SELECT * FROM xxxx_xxx.log_event PARTITION(P_12_2015);

 EXEC DBMS_MVIEW.refresh('mdummy', 'cf');
 DROP MATERIALIZED VIEW mdummy;

 SELECT v.SQL_TEXT,
           v.PARSING_SCHEMA_NAME,
           v.FIRST_LOAD_TIME,
           v.DISK_READS,
           v.ROWS_PROCESSED,
           v.ELAPSED_TIME,
           v.service
      FROM gv$sql v
WHERE TO_DATE(v.FIRST_LOAD_TIME,'YYYY-MM-DD hh24:mi:ss')>ADD_MONTHS(TRUNC(SYSDATE,'MM'),-2) AND v.sql_text LIKE '%V_LOG_ALL%';


SELECT * FROM xxxx_xxx.log_event PARTITION(P_12_2015);


ALTER database datafile '+DATA/db/datafile/xxxx_xxx_log/event012016.dbf' resize 8G;


SELECT * FROM xxxx_xxx.log_event PARTITION(p_01_2016) WHERE ROWNUM < 500 ORDER BY l_time DESC;