Welcome Guest ( Log In | Register )


 
 
 
 
 
 

 
 
Oracle 

Performance Tuning Reference poster
 
Oracle training in Linux 

commands
 
Oracle training Weblogic Book
 
Easy Oracle Jumpstart
 
Oracle training & performance tuning books
 
Burleson Consulting Remote DB Administration
 
 
 
Reply to this topicStart new topic
> No free block from USERS tbs where is showing 25G free space...
Swati Desai
post Feb 22 2012, 04:51 AM
Post #1


Advanced Member
***

Group: Members
Posts: 122
Joined: 8-January 08
Member No.: 15,826



Hi All,

In my 11gR2 database, have one users tablespace which is showing total MB-32767 and freeMB - 26353. Now when I run below query to findout free block , it is not showing any block as a free.... I run below sql many times for different TBS and able to reclaim the space from free block...
=============================================
define m_tablespace = 'USERS'

select
file_id,
block_id,
block_id + blocks - 1 end_block,
owner,
segment_name,
segment_type
from
dba_extents
where
tablespace_name = '&m_tablespace'
union all
select
file_id,
block_id,
block_id + blocks - 1 end_block,
'free' owner,
'free' segment_name,
null segment_type
from
dba_free_space
where
tablespace_name = '&m_tablespace'
order by
1,2
/



==============================================
I am not able to identify how to reclaim the space from USERS tablespace. Can anyone please help me?


Thanks in advance.
Go to the top of the page
 
+Quote Post
burleson
post Feb 22 2012, 06:15 AM
Post #2


Advanced Member
***

Group: Members
Posts: 11,431
Joined: 26-January 04
Member No.: 13



Hi Swati,

>> I run below sql many times for different TBS and able to reclaim the space from free block...

First, use this script to get tablespace free space, your query is incorrect:

http://www.dba-oracle.com/t_free_space_script.htm

You need to download the script collection:

http://www.dba-oracle.com/oracle_scripts.htm

********************************************
>> how to reclaim the space from USERS tablespace.

Can you make a scheduled downtime to:

1 - Redo the tablespace with smaller data files?

2 - Export and re-pack the table data into the new tablespace?

Within the tablespace, objects are scattered throughout the tablespace and corresponding datafiles.

There are several types of fragmentation, most common the "honeycomb" fragmentation and the "bubble" fragmentation, where it is more difficult to reclaim wasted disk space.

Note that you can remove bubble fragmentation by using locally-managed tablespaces, and see these notes on Oracle data file fragmentation.



Please read: http://www.dba-oracle.com/t_reclaiming_disk_space.htm


--------------------
Hope this helps. . .

Donald K. Burleson
Oracle Press author
Author of Oracle Tuning: The Definitive Reference
Go to the top of the page
 
+Quote Post

Reply to this topicStart new topic
1 User(s) are reading this topic (1 Guests and 0 Anonymous Users)
0 Members:

 

Lo-Fi Version Time is now: 2nd September 2014 - 01:42 AM