Solved

Oracle, tablespace

Posted on 2011-09-29
7
343 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)
Comment Utility
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
Comment Utility
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
Comment Utility
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
Comment Utility
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
Comment Utility
>>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
Comment Utility
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
Comment Utility
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.

Join & Write a Comment

Configuring and using Oracle Database Gateway for ODBC Introduction First, a brief summary of what a Database Gateway is.  A Gateway is a set of driver agents and configurations that allow an Oracle database to communicate with other platforms…
From implementing a password expiration date, to datatype conversions and file export options, these are some useful settings I've found in Jasper Server.
Via a live example show how to connect to RMAN, make basic configuration settings changes and then take a backup of a demo database
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.

771 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

Need Help in Real-Time?

Connect with top rated Experts

12 Experts available now in Live!

Get 1:1 Help Now