Oracle, tablespace

Posted on 2011-09-29
Last Modified: 2012-05-12
how do i find -- the  true free space within the used portion of the tablespace? After a delete which delets not only the data but also lob data..
Question by:Rao_S
Welcome to Experts Exchange

Add your voice to the tech community where 5M+ people just like you are talking about what matters.

  • Help others & share knowledge
  • Earn cash & points
  • Learn & ask questions
  • 3
  • 2
  • 2
LVL 77

Expert Comment

by:slightwv (䄆 Netminder)
ID: 36817369
Free for what?

Free for additional rows in the same table or free for other objects in the same tablespace?

If you delete rows from a table all you do is free up space for additional rows for that table.  Are you wanting to calculate how many rows can be inserted into the freed up space fbefore another Extent is allocated?


Accepted Solution

Jacobfw earned 400 total points
ID: 36817390
this script is a good example

Remember that deleting records (even ones that contain LOB columns does not free space necessarily.

This article provides an excellent overview of reclaiming space:

For LOB's, you often need to "SHRINK" the table and the LOB seperately.

Like this:

Alter table DOCUSHARE.accesstable enable row movement;
-- Check if Shrink can be performed

Alter table tablename shrink space CASCADE;

Alter table tablename modify LOB (LOB_name)  (shrink space);

Author Comment

ID: 36817406
i read about the 'shrink' and then i got confused because thee is a pre requisite that the tablespace be 'assm' and i dont knoe how the tbspace is defined on our system..
Salesforce Made Easy to Use

On-screen guidance at the moment of need enables you & your employees to focus on the core, you can now boost your adoption rates swiftly and simply with one easy tool.


Author Comment

ID: 36817417
this query shows the true free the temp tbspace... is there a query imilar to find the true used/free a tbspace..?
-- To report true free space within the used portion of the TEMPFILE:
SELECT   A.tablespace_name tablespace, D.mb_total,
         SUM (A.used_blocks * D.block_size) / 1024 / 1024 mb_used,
         D.mb_total - SUM (A.used_blocks * D.block_size) / 1024 / 1024 mb_free
FROM     v$sort_segment A,
         SELECT, C.block_size, SUM (C.bytes) / 1024 / 1024 mb_total
         FROM     v$tablespace B, v$tempfile C
         WHERE    B.ts#= C.ts#
         GROUP BY, C.block_size
         ) D
WHERE    A.tablespace_name =
GROUP by A.tablespace_name, D.mb_total;
LVL 77

Assisted Solution

by:slightwv (䄆 Netminder)
slightwv (䄆 Netminder) earned 100 total points
ID: 36817446
>>and i dont knoe how the tbspace is defined on our system..

from dba_tablespaces;

>>this query shows the true free space..

That is unallocated space.  space from deleting data will not be shown.

For unallocated free space the view you are after is dba_free_space

Assisted Solution

Jacobfw earned 400 total points
ID: 36817459
SELECT tablespace_name, extent_management, segment_space_management
FROM dba_tablespaces

Where segment_space_management = AUTO indicates ASSM


Author Closing Comment

ID: 36942226
thank you...the links given by Jacobfw!!

Featured Post

Instantly Create Instructional Tutorials

Contextual Guidance at the moment of need helps your employees adopt to new software or processes instantly. Boost knowledge retention and employee engagement step-by-step with one easy solution.

Question has a verified solution.

If you are experiencing a similar issue, please ask a related question

Background In several of the companies I have worked for, I noticed that corporate reporting is off loaded from the production database and done mainly on a clone database which needs to be kept up to date daily by various means, be it a logical…
Checking the Alert Log in AWS RDS Oracle can be a pain through their user interface.  I made a script to download the Alert Log, look for errors, and email me the trace files.  In this article I'll describe what I did and share my script.
This video shows, step by step, how to configure Oracle Heterogeneous Services via the Generic Gateway Agent in order to make a connection from an Oracle session and access a remote SQL Server database table.
This video shows how to Export data from an Oracle database using the Datapump Export Utility.  The corresponding Datapump Import utility is also discussed and demonstrated.
Suggested Courses

632 members asked questions and received personalized solutions in the past 7 days.

Join the community of 500,000 technology professionals and ask your questions.

Join & Ask a Question