Solved

Oracle, tablespace

Posted on 2011-09-29
7
345 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 76

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 400 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
PRTG Network Monitor: Intuitive Network Monitoring

Network Monitoring is essential to ensure that computer systems and network devices are running. Use PRTG to monitor LANs, servers, websites, applications and devices, bandwidth, virtual environments, remote systems, IoT, and many more. PRTG is easy to set up & use.

 

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 76

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..

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 400 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

PRTG Network Monitor: Intuitive Network Monitoring

Network Monitoring is essential to ensure that computer systems and network devices are running. Use PRTG to monitor LANs, servers, websites, applications and devices, bandwidth, virtual environments, remote systems, IoT, and many more. PRTG is easy to set up & use.

Question has a verified solution.

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

Suggested Solutions

Title # Comments Views Activity
EXECUTE IMMEDIATE 5 72
What is the version of ojdbc6.jar 2 60
SQL Query 34 99
database upgrade 8 77
Working with Network Access Control Lists in Oracle 11g (part 1) Part 2: http://www.e-e.com/A_9074.html So, you upgraded to a shiny new 11g database and all of a sudden every program that used UTL_MAIL, UTL_SMTP, UTL_TCP, UTL_HTTP or any oth…
Note: this article covers simple compression. Oracle introduced in version 11g release 2 a new feature called Advanced Compression which is not covered here. General principle of Oracle compression Oracle compression is a way of reducing the d…
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.

809 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