[Webinar] Streamline your web hosting managementRegister Today

x
?
Solved

Oracle, tablespace

Posted on 2011-09-29
7
Medium Priority
?
375 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
The new generation of project management tools

With monday.com’s project management tool, you can see what everyone on your team is working in a single glance. Its intuitive dashboards are customizable, so you can create systems that work for you.

 

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

Free Tool: Path Explorer

An intuitive utility to help find the CSS path to UI elements on a webpage. These paths are used frequently in a variety of front-end development and QA automation tasks.

One of a set of tools we're offering as a way of saying thank you for being a part of the community.

Question has a verified solution.

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

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…
This post first appeared at Oracleinaction  (http://oracleinaction.com/undo-and-redo-in-oracle/)by Anju Garg (Myself). I  will demonstrate that undo for DML’s is stored both in undo tablespace and online redo logs. Then, we will analyze the reaso…
This video shows syntax for various backup options while discussing how the different basic backup types work.  It explains how to take full backups, incremental level 0 backups, incremental level 1 backups in both differential and cumulative mode a…
This video shows how to configure and send email from and Oracle database using both UTL_SMTP and UTL_MAIL, as well as comparing UTL_SMTP to a manual SMTP conversation with a mail server.
Suggested Courses

612 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