-
-
Connect to the database with SOAINFRA user and run below query to check current tablespace utilization (used, free, total space etc.).
-
select df.tablespace_name “Tablespace”,
totalusedspace “Used MB”,
(df.totalspace – tu.totalusedspace) “Free MB”,
df.totalspace “Total MB”,
round(100 * ( (df.totalspace – tu.totalusedspace)/ df.totalspace))
“Pct. Free”
from
(select tablespace_name,
round(sum(bytes) / 1048576) TotalSpace
from dba_data_files
group by tablespace_name) df,
(select round(sum(bytes)/(1024*1024)) totalusedspace, tablespace_name
from dba_segments
group by tablespace_name) tu
where df.tablespace_name = tu.tablespace_name ;
-
- Run below 3 steps with the help of DBA to reclaim unused space.
Shrink space
ALTER TABLE PREFIX_SOAINFRA.XML_DOCUMENT enable row movement;
ALTER TABLE PREFIX _SOAINFRA.XML_DOCUMENT shrink space compact;
ALTER TABLE PREFIX _SOAINFRA.XML_DOCUMENT shrink space;
ALTER TABLE PREFIX _SOAINFRA.XML_DOCUMENT disable row movement;
Deallocate unused space
ALTER TABLE PREFIX_SOAINFRA.XML_DOCUMENT DEALLOCATE UNUSED;
Rebuild index
Before you run the below command, please check what are the indexes for XML_DOCUMENT table and include the index for rebuild operation
ALTER INDEX PREFIX_SOAINFRA.DOC_STORE_PK rebuild online;
-
- Run above query again and notice the difference in free space.
-
- Run below 3 steps with the help of DBA to reclaim unused space.
Shrink space
ALTER TABLE PREFIX_SOAINFRA.XML_DOCUMENT enable row movement;
ALTER TABLE PREFIX _SOAINFRA.XML_DOCUMENT shrink space compact;
ALTER TABLE PREFIX _SOAINFRA.XML_DOCUMENT shrink space;
ALTER TABLE PREFIX _SOAINFRA.XML_DOCUMENT disable row movement;
Deallocate unused space
ALTER TABLE PREFIX_SOAINFRA.XML_DOCUMENT DEALLOCATE UNUSED;
Rebuild index
Before you run the below command, please check what are the indexes for XML_DOCUMENT table and include the index for rebuild operation
ALTER INDEX PREFIX_SOAINFRA.DOC_STORE_PK rebuild online;
-
- Run above query again and notice the difference in free space.
