Table resize, Index Resize, partition or sub partition table space resize :

Table resize, Index Resize, partition or sub partition table space resize :

Please Note: Take backup of either schema and/or table before doing any changes. No backup means no recovery as simple as that..
Backup is must, backup is must.. Test this in non-prod first - no direct use in prod db.

------------------------- Table Resize --------------

 


execute DBMS_STATS.GATHER_SCHEMA_STATS(user, cascade=>true);



select count(*), sum(bytes) from user_extents where segment_name='T';



alter table t deallocate unused;



select count(*), sum(bytes) from user_extents where segment_name='T';





select table_name, ROW_MOVEMENT from user_tables where table_name='T';



ALTER TABLE t ENABLE ROW MOVEMENT ;

ALTER TABLE T SHRINK SPACE CASCADE;



alter table  t   disable row movement;  -- as per previous table row_movement value



select count(*), sum(bytes) from user_extents where segment_name='T';

------------------- Index Resize -----------------------

 



analyze index i_t validate structure;

select blocks, lf_blks, btree_space, pct_used from index_stats;

alter index i_t rebuild online;

select blocks, lf_blks, btree_space, pct_used from index_stats;

--for partitions
--ANALYZE INDEX   [PARTITION ] [SUBPARTITION ] VALIDATE STRUCTURE

--------------------- Tablespace Level resize by moving to different tableapce.. -------------

In this exmaple : It is considered like

Each partition has its own tablesapce.
No tablesapce being shared between partitions.

 


CREATE TABLE T

(id            NUMBER,

 created_date  DATE)

PARTITION BY RANGE (created_date)

(PARTITION part_2016 VALUES LESS THAN (TO_DATE('01/01/2017', 'DD/MM/YYYY')) TABLESPACE users,

 PARTITION part_2017 VALUES LESS THAN (TO_DATE('01/01/2018', 'DD/MM/YYYY')) TABLESPACE users);





 INSERT INTO t

SELECT level,

         CASE

         WHEN MOD(level,2) = 0 THEN TO_DATE('05/09/2016', 'DD/MM/YYYY')

         ELSE TO_DATE('04/05/2017', 'DD/MM/YYYY')

       END

FROM   dual

CONNECT BY level <= 1000000;

COMMIT;





execute DBMS_STATS.GATHER_SCHEMA_STATS(user, cascade=>true);

EXEC DBMS_STATS.gather_table_stats(USER, 'T',cascade=>true);



col table_name for a30

col partition_name for a30

SELECT table_name,        partition_name,        num_rows FROM   user_tab_partitions where table_name='T' ;



 --- Note down the table space size being altered...




select count(*), sum(bytes) from user_extents where segment_name='T';



SELECT TABLE_NAME,PARTITION_NAME,TABLESPACE_NAME  FROM USER_TAB_PARTITIONS WHERE TABLE_NAME='T';



CREATE TABLESPACE test5



Move partition/subpartition   to new TABLESPACE



ALTER TABLE T MOVE PARTITION part_2016 ONLINE TABLESPACE test5 UPDATE INDEXES PARALLEL;



Rebuild partition/subpartition indexes to new TABLESPACE



ALTER INDEX T_P REBUILD PARTITION part_2016 TABLESPACE test5 PARALLEL ;



drop old tablespace , and rename new tablespace name back to old tablespace

Please note : Ensure no objects exists in the tablesapce before tablespace being dropped, one can check existence of objects using below queries.
To find objects belonging to a tablespace:

select distinct segment_type from dba_segments where tablespace_name='USERS';
OR
select   owner, segment_name, TABLESPACE_NAME from dba_segments where tablespace_name='USERS' 
union
select   owner, table_name, TABLESPACE_NAME from dba_tables where tablespace_name='USERS' 
union
select  owner, index_name, TABLESPACE_NAME from dba_indexes where tablespace_name='USERS';

SELECT UT.TABLESPACE_NAME "TABLESPACE", COUNT (US.SEGMENT_NAME) "NUM SEGMENTS"
   FROM USER_TABLESPACES UT, USER_SEGMENTS US
   WHERE UT.TABLESPACE_NAME = US.TABLESPACE_NAME
   AND ut.tablespace_name='USERS'
   GROUP BY (UT.TABLESPACE_NAME)
   ORDER BY COUNT (US.SEGMENT_NAME) DESC;



DROP TABLESPACE USERS INCLUDING CONTENTS AND DATAFILES;

ALTER TABLE T MODIFY DEFAULT ATTRIBUTES FOR PARTITION part_2016 TABLESPACE test5;

ALTER INDEX  T_P MODIFY DEFAULT ATTRIBUTES FOR PARTITION part_2016 TABLESPACE test5;

ALTER TABLESPACE test5 RENAME TO USERS;

-- check index status for all the partitions , table sizes , indexes sizes etc..

The other way of doing is redefinition



--------- Using dbms_redefinition:

--- TABLE LEVEL ------- 12C FEATURE

begin

dbms_redefinition.redef_table (

uname=>USER,

tname=>'PAR_TABLE',

index_tablespace => 'USERS',

table_part_tablespace=>'USERS');

end;

/

------------------------------ TABLE PARTITION LEVEL -------------

Creating   a non partitioned table:

create table  nopar_table( id number,ts timestamp,value varchar2(30)) TABLESPACE TEST5;



--- create all indexes on this non partition table exactly same as the indexes on the partitioned table


create index  nopar_table_ts on  nopar_table(ts) tablespace TEST5;





BEGIN

DBMS_REDEFINITION.CAN_REDEF_TABLE(

UNAME => USER,

TNAME => 'PAR_TABLE',

PART_NAME => 'P0');

END;

/



BEGIN

DBMS_REDEFINITION.START_REDEF_TABLE(

UNAME => USER,

ORIG_TABLE => 'PAR_TABLE',

INT_TABLE => 'NOPAR_TABLE',

PART_NAME => 'P0' );

END;

/







BEGIN

DBMS_REDEFINITION.FINISH_REDEF_TABLE (

UNAME => USER,

ORIG_TABLE => 'PAR_TABLE',

INT_TABLE => 'NOPAR_TABLE',

PART_NAME => 'P0');

END;

/





-- INCASE IF THERE ARE ERRORS DURING THE REDEFINATION..................

BEGIN

DBMS_REDEFINITION.ABORT_REDEF_TABLE (

UNAME => USER,

ORIG_TABLE => 'PAR_TABLE',

INT_TABLE => 'NOPAR_TABLE',

PART_NAME => 'P0');

END;

/

--- Note: This can be called after the START_REDEF_TABLE Procedure but before the FINISH_REDEF_TABLE Procedure is called.





select TABLE_NAME, PARTITION_NAME, TABLESPACE_NAME,NUM_ROWS from user_tab_partitions where table_name='PAR_TABLE';

select index_name, PARTITION_NAME, TABLESPACE_NAME,status from user_ind_partitions where index_name in (select index_name from user_indexes where table_name='PAR_TABLE');

select index_name, tablespace_name, status from user_indexes where table_name='PAR_TABLE';



------------------