OraFAQ Forum: Server Administration » Tablespace fragmentation

Home » RDBMS Server » Server Administration » Tablespace fragmentation (Oracle,10g)

Show: Today's Messages :: Polls :: Message Navigator
E-mail to friend

Tablespace fragmentation [message #541955]

Fri, 03 February 2012 05:36

sathik
Messages: 196
Registered: August 2009
Location: INDIA

Senior Member

I am having 45Gb free space in tablespace. How to find the largest contiguous space or extent in the tablespace? Because am getting unable to allocate extent error?

Report message to a moderator

Re: Tablespace fragmentation [message #541958 is a reply to message #541955]

Fri, 03 February 2012 06:01

Michel Cadot
Messages: 68641
Registered: March 2007
Location: Nanterre, France, http://...

Senior Member
Account Moderator

DBA_FREE_SPACE

Regards
Michel

Report message to a moderator

Re: Tablespace fragmentation [message #541962 is a reply to message #541958]

Fri, 03 February 2012 06:07

sathik
Messages: 196
Registered: August 2009
Location: INDIA

Senior Member

This is the output I am getting. 64 MB is the largest contiguous space for this tbalespace?

SQL> select FILE_ID,BLOCK_ID,BYTES/1024/1024,BLOCKS,TABLESPACE_NAME 
from dba_free_space where tablespace_name='EDX_RPT_UNBILLEDDATA_TS_01';

   FILE_ID   BLOCK_ID BYTES/1024/1024     BLOCKS TABLESPACE_NAME
---------- ---------- --------------- ---------- ------------------------------
       700    3877513           6.875        880 EDX_RPT_UNBILLEDDATA_TS_01
       701    1523721         55.9375       7160 EDX_RPT_UNBILLEDDATA_TS_01
       307       3081          5.9375        760 EDX_RPT_UNBILLEDDATA_TS_01
       698    1964425         12.9375       1656 EDX_RPT_UNBILLEDDATA_TS_01
       699    4194185            .875        112 EDX_RPT_UNBILLEDDATA_TS_01
       700    3860105              64       8192 EDX_RPT_UNBILLEDDATA_TS_01
       701    1520009               8       1024 EDX_RPT_UNBILLEDDATA_TS_01
       307          9               8       1024 EDX_RPT_UNBILLEDDATA_TS_01
       700    3868297               8       1024 EDX_RPT_UNBILLEDDATA_TS_01
       307       1033               8       1024 EDX_RPT_UNBILLEDDATA_TS_01
       307       2057               8       1024 EDX_RPT_UNBILLEDDATA_TS_01
       698    1956233              64       8192 EDX_RPT_UNBILLEDDATA_TS_01
       700    3869321              64       8192 EDX_RPT_UNBILLEDDATA_TS_01
       701    1521033              21       2688 EDX_RPT_UNBILLEDDATA_TS_01

[Updated on: Fri, 03 February 2012 06:10] by Moderator

Report message to a moderator

Re: Tablespace fragmentation [message #541965 is a reply to message #541962]

Fri, 03 February 2012 06:10

Michel Cadot
Messages: 68641
Registered: March 2007
Location: Nanterre, France, http://...

Senior Member
Account Moderator

Keep your lines of code in 80 character width: no more than 80 characters on each line.

First step, simplest one: just use MAX.
Second step, deeper one, you have to "merge" adjacent free space extents (those where block_id+blocks = block_id of next extent).

Regards
Michel

Report message to a moderator

Re: Tablespace fragmentation [message #541968 is a reply to message #541965]

Fri, 03 February 2012 06:20

sathik
Messages: 196
Registered: August 2009
Location: INDIA

Senior Member

can you pls help me to frame the query? I am facing difficulties to frame the second step.

Report message to a moderator

Re: Tablespace fragmentation [message #541970 is a reply to message #541968]

Fri, 03 February 2012 06:34

Michel Cadot
Messages: 68641
Registered: March 2007
Location: Nanterre, France, http://...

Senior Member
Account Moderator

Execute ALTER TABLESPACE COALESCE instead then you don't have to write this query and just search for MAX.

Regards
Michel

Report message to a moderator

Previous Topic:	How to check whether database is dedicated or shared
Next Topic:	ora 01092 oracle instance terminated

Goto Forum:

-=] Back to Top [=-

[ Syndicate this forum (XML) ] [

]

Current Time: Fri Apr 19 05:17:30 CDT 2024