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--------------------- Tablespace Level resize by moving to different tableapce.. -------------[PARTITION ] [SUBPARTITION ] VALIDATE STRUCTURE
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';
------------------
