Check tablespaces on database
SQL>
SELECT * FROM DBA_TABLESPACES WHERE CONTENTS = 'PERMANENT';
Delete all records on tables if needs
SQL>
SELECT 'TRUNCATE TABLE ' || OWNER || '.' || TABLE_NAME || ';' FROM ALL_TABLES WHERE OWNER = 'HR';
Before movement operation, enable row movement feature
SQL>
SELECT 'ALTER TABLE ' || OWNER || '.' || TABLE_NAME || ' ENABLE ROW MOVEMENT;' FROM ALL_TABLES WHERE TABLESPACE_NAME = 'HR';
Shrink tables
SQL>
SELECT 'ALTER TABLE ' || OWNER || '.' || TABLE_NAME || ' SHRINK SPACE;' from ALL_TABLES WHERE TABLESPACE_NAME = 'HR';
If ORA-10631 error occurs;
ALTER TABLE HR.EMPLOYEES_NEW SHRINK SPACE
Error at line 99
ORA-10631: SHRINK clause should not be specified for this object
Solution:
If FUNCTION-BASED NORMAL index stores on related table, this error occurs
1- Drop the function-based index.
2- Shrink the table.
3- Recreate the index again on the table.
Define a fresh tablespace
SQL>
CREATE TABLESPACE HR_NEW DATAFILE
'/oradata/XE/data.dbf' SIZE 1G AUTOEXTEND ON NEXT 8K MAXSIZE 5G,
'/oradata/XE/data01.dbf'' SIZE 10G AUTOEXTEND ON NEXT 8K MAXSIZE 50G,
'/oradata/XE/data02.dbf SIZE 10G AUTOEXTEND ON NEXT 8K MAXSIZE 50G
LOGGING
EXTENT MANAGEMENT LOCAL AUTOALLOCATE
BLOCKSIZE 8K
SEGMENT SPACE MANAGEMENT AUTO
FLASHBACK ON;
If need, move related tablespace to a fresh tablespace
For tables:
SQL>
SELECT 'ALTER TABLE ' || OWNER || '.' || TABLE_NAME || ' MOVE TABLESPACE TS_HR_NEW;' from ALL_TABLES WHERE TABLESPACE_NAME = 'TS_HR';
For indexes:
SQL>
SELECT 'ALTER INDEX ' || OWNER || '.' || INDEX_NAME || ' REBUILD TABLESPACE TS_HR_NEW;' FROM ALL_INDEXES WHERE TABLESPACE_NAME='TS_HR';
For lob segments:
SQL>
SELECT 'ALTER TABLE ' || OWNER || '.' || TABLE_NAME || ' MOVE LOB (' || COLUMN_NAME || ')' || ' STORE AS ( TABLESPACE TS_HR_NEW);' FROM DBA_LOBS WHERE TABLESPACE_NAME = 'TS_HR'
Rebuild unusable index for other schema's indexes:
SQL>
SELECT 'ALTER INDEX '||OWNER||'.'||INDEX_NAME||' REBUILD;' FROM ALL_INDEXES WHERE STATUS='UNUSABLE' ;
Gather statistics of tables:
SQL>
SELECT 'EXEC DBMS_STATS.GATHER_TABLE_STATS(' || '''' || OWNER || '''' || ',' || '''' || TABLE_NAME || ''');' FROM ALL_TABLES WHERE TABLESPACE_NAME='TS_HR_NEW';
Check size of data files
SQL>
SELECT OWNER,
SEGMENT_NAME,
SEGMENT_TYPE,
PARTITION_NAME,
ROUND (BYTES / (1024 * 1024), 2) SIZE_MB,
TABLESPACE_NAME
FROM DBA_SEGMENTS
WHERE SEGMENT_TYPE IN ('TABLE',
'TABLE PARTITION',
'TABLE SUBPARTITION',
'INDEX',
'INDEX PARTITION',
'INDEX SUBPARTITION',
'TEMPORARY',
'LOBINDEX',
'LOBSEGMENT',
'LOB PARTITION')
AND TABLESPACE_NAME LIKE '%TS_HR%'
--AND SEGMENT_NAME LIKE 'P2010201%'
--AND partition_name LIKE 'P20100201%'
--AND segment_type = 'TABLE'
--AND OWNER = 'HR'
AND ROUND (BYTES / (1024 * 1024), 2) > 10000
ORDER BY BYTES DESC;
Ref:
https://ozsoyler.blogspot.com.tr/2017/08/eg-table-shrink-usage.html
