Go Premium for a chance to win a PS4. Enter to Win

x
?
Solved

Oracle, tablespace

Posted on 2011-09-29
7
Medium Priority
?
372 Views
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..
0
Comment
Question by:Rao_S
  • 3
  • 2
  • 2
7 Comments
 
LVL 78

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?

0
 
LVL 7

Accepted Solution

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

http://gavinsoorma.com/2009/07/script-tablespace-free-space-and-fragmentation/

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

This article provides an excellent overview of reclaiming space:
http://www.dba-oracle.com/t_reclaiming_disk_space.htm

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 CHECK;

Alter table tablename shrink space CASCADE;

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

Author Comment

by:Rao_S
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..
0
Independent Software Vendors: We Want Your Opinion

We value your feedback.

Take our survey and automatically be enter to win anyone of the following:
Yeti Cooler, Amazon eGift Card, and Movie eGift Card!

 

Author Comment

by:Rao_S
ID: 36817417
this query shows the true free space..in the temp tbspace... is there a query imilar to find the true used/free space...in 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   B.name, C.block_size, SUM (C.bytes) / 1024 / 1024 mb_total
         FROM     v$tablespace B, v$tempfile C
         WHERE    B.ts#= C.ts#
         GROUP BY B.name, C.block_size
         ) D
WHERE    A.tablespace_name = D.name
GROUP by A.tablespace_name, D.mb_total;
0
 
LVL 78

Assisted Solution

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

select TABLESPACE_NAME, SEGMENT_SPACE_MANAGEMENT, EXTENT_MANAGEMENT
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
0
 
LVL 7

Assisted Solution

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

Where segment_space_management = AUTO indicates ASSM

0
 

Author Closing Comment

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

Featured Post

What does it mean to be "Always On"?

Is your cloud always on? With an Always On cloud you won't have to worry about downtime for maintenance or software application code updates, ensuring that your bottom line isn't affected.

Question has a verified solution.

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

How to Unravel a Tricky Query Introduction If you browse through the Oracle zones or any of the other database-related zones you'll come across some complicated solutions and sometimes you'll just have to wonder how anyone came up with them.  …
Shell script to create broker configuration file using current broker Configuration, solely for purpose of backup on Linux. Script may need to be modified depending on OS-installation. Please deploy and verify the script in a test environment.
This video explains at a high level with the mandatory Oracle Memory processes are as well as touching on some of the more common optional ones.
This video shows how to Export data from an Oracle database using the Original Export Utility.  The corresponding Import utility, which works the same way is referenced, but not demonstrated.
Suggested Courses

926 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