Database Administration (MOSC)

MOSC Banner

Reclaiming free space at tablespace / datafile level after re-organization of segment

edited Jan 13, 2012 1:29AM in Database Administration (MOSC) 2 commentsAnswered

Hi,

We have a 10.2.0.4 production database (Standard Edition) and in it has a table called PROCESS (with LOB column MESG)
that grew to appx 46 GB and after that a lot of data was purged from that table.

The table data plus the LOB segment is stored in table space APPS_DATA

select tablespace_name,sum(bytes)/1024/1024/1024,sum(maxbytes)/1024/1024/1024
from dba_data_files where tablespace_name='APPS_DATA' group by tablespace_name;

Tablespace_name   sum(bytes)/1024/1024/1024       sum(maxbytes)/1024/1024/1024
APPS_DATA                       65.17                           79.95

The following queries confirmed that table PROCESS has a lot of free space (appx 40 GB) in APPS_DATA tablespace


SELECT table_name, column_name, segment_name, a.bytes/1024/1024 MB

Howdy, Stranger!

Log In

To view full details, sign in to My Oracle Support Community.

Register

Don't have a My Oracle Support Community account? Click here to get started.

Category Leaderboard

Top contributors this month

New to My Oracle Support Community? Visit our Welcome Center

MOSC Help Center