Home » RDBMS Server » Server Administration » dba_free_space (11gR2)
dba_free_space [message #525475] Mon, 03 October 2011 07:24 Go to next message
gkrishn
Messages: 491
Registered: December 2005
Location: Putty a dark screen
Senior Member
I am little confused about this view


DBA_FREE_SPACE:  lists the free extents in all tablespaces

Column            Datatype       NULL        Description 
_______________   ____________   ________    ___________________
TABLESPACE_NAME   VARCHAR2(30)   NOT NULL    Name of the tablespace containing the extent 
FILE_ID           NUMBER         NOT NULL    ID number of the file containing the extent 
BLOCK_ID          NUMBER         NOT NULL    Starting block number of the extent 
BYTES             NUMBER                     Size of the extent in bytes 
BLOCKS            NUMBER         NOT NULL    Size of the extent in Oracle blocks 
RELATIVE_FNO      NUMBER         NOT NULL    Relative file number of the first extent block 





It list free spaces outside segments which are free and available in Tablespace ?
or
Free spaces(free extents) available withing all segments ?

Can someone please explain. Thanks
Re: dba_free_space [message #525489 is a reply to message #525475] Mon, 03 October 2011 08:19 Go to previous messageGo to next message
Michel Cadot
Messages: 59812
Registered: March 2007
Location: Nanterre, France, http://...
Senior Member
Account Moderator
Quote:
It list free spaces outside segments which are free and available in Tablespace ?

Yes.

Regards
Michel
Re: dba_free_space [message #525496 is a reply to message #525489] Mon, 03 October 2011 09:13 Go to previous messageGo to next message
gkrishn
Messages: 491
Registered: December 2005
Location: Putty a dark screen
Senior Member
"DBA_FREE_SPACE:  lists the free extents in all tablespaces"


Hi , then why oracle call it extent ? when it is outside segment ,i thought it will be just blocks no more logical grouping as extents. Can you pls explain.

Thanks
Re: dba_free_space [message #525501 is a reply to message #525496] Mon, 03 October 2011 10:19 Go to previous messageGo to next message
Michel Cadot
Messages: 59812
Registered: March 2007
Location: Nanterre, France, http://...
Senior Member
Account Moderator
Quote:
why oracle call it extent ?

Because IT defines it as it. They are FREE extends that is logical groups of blocks that are not allocated.

Regards
Michel
Re: dba_free_space [message #525538 is a reply to message #525501] Mon, 03 October 2011 13:01 Go to previous messageGo to next message
gkrishn
Messages: 491
Registered: December 2005
Location: Putty a dark screen
Senior Member
Wht determine the size of that logical group ? (logical groups of blocks that are not allocated) .

Or is it just a group of all "contiguous" free blocks (what ever the number that is) ?
Re: dba_free_space [message #525539 is a reply to message #525538] Mon, 03 October 2011 13:08 Go to previous messageGo to next message
Michel Cadot
Messages: 59812
Registered: March 2007
Location: Nanterre, France, http://...
Senior Member
Account Moderator
Quote:
Wht determine the size of that logical group ?

History of the space inside the tablespace and smon activity.

Quote:
Or is it just a group of all "contiguous" free blocks (what ever the number that is) ?

Amount determined by what I've said above, there can be contiguous free extents.

Regards
Michel

[Edit: missing word]

[Updated on: Tue, 04 October 2011 06:28]

Report message to a moderator

Re: dba_free_space [message #525633 is a reply to message #525475] Tue, 04 October 2011 06:11 Go to previous messageGo to next message
gkrishn
Messages: 491
Registered: December 2005
Location: Putty a dark screen
Senior Member
Thanks Mike.

I was trying to solve this issue i am facing.But still no luck with it.

ERROR at line 1:
ORA-01653: unable to extend table MYSCHEMA.MY_TABLE_EMP by 128 in tablespace MYTBS01



There is 40GB+ free in tablespace MYTBS01

I ran segment advisor on this tablespace, but there is no segment that i can shrink .(May be not relevant in this
scenario since its looking for free extents outside MYSCHEMA.MY_TABLE_EMP segment and i have 40GB+ free).

So from the theory ,it look like fragmentation outside segments, in free space.

#so i ran @tfstsfgm.sql script from metalink to list free extent sizes ,
This is list of large free extents in descending order.
TABLESPACE NAME                CONTIGUOUS BYTES
------------------------------ ----------------
MYTBS01                             2,031,616
MYTBS01                             2,031,616
MYTBS01                             2,031,616
MYTBS01                             2,031,616
MYTBS01                             1,966,080
MYTBS01                             1,966,080
MYTBS01                             1,966,080
MYTBS01                             1,966,080
MYTBS01                             1,900,544
MYTBS01                             1,900,544



#MYSCHEMA.MY_TABLE_EMP next extent size i changed to 512KB (just tried though its using ASSM) .
DATPRD:ERCDR9> alter table "MYSCHEMA"."MY_TABLE_EMP" STORAGE(NEXT 524288);


Table altered.

## Now it is this .
  STORAGE(INITIAL 65536 NEXT 524288 MINEXTENTS 1 MAXEXTENTS 2147483645
  PCTINCREASE 0 FREELISTS 1 FREELIST GROUPS 1 BUFFER_POOL DEFAULT FLASH_


## Still no luck.

DATPRD:ERCDR9> alter table "MYSCHEMA"."MY_TABLE_EMP" allocate extent;
alter table "MYSCHEMA"."MY_TABLE_EMP" allocate extent
*
ERROR at line 1:
ORA-01653: unable to extend table MYSCHEMA.MY_TABLE_EMP by 128 in tablespace MYTBS01



## I want oracle use a small next extent
or
I want to make large extents available to oracle (though i dnt know the size).


Any tips ? Please dont ask me to refer oracle documentation, two days i am on it Smile

[Updated on: Tue, 04 October 2011 06:15]

Report message to a moderator

Re: dba_free_space [message #525635 is a reply to message #525633] Tue, 04 October 2011 06:35 Go to previous messageGo to next message
Michel Cadot
Messages: 59812
Registered: March 2007
Location: Nanterre, France, http://...
Senior Member
Account Moderator
select tablespace_name, count(*) 
from dba_segments 
where owner='MYSCHEMA' and segment_name='MY_TABLE_EMP'
group by tablespace_name
/
select bytes, count(*)
from dba_extents
where owner='MYSCHEMA' and segment_name='MY_TABLE_EMP'
group by bytes
order by bytes
/
select extent_management, allocation_type, min_extlen
from dba_tablespaces
where tablespace_name = 'MYTBS01'
/

Regards
Michel
Re: dba_free_space [message #525638 is a reply to message #525635] Tue, 04 October 2011 06:57 Go to previous messageGo to next message
gkrishn
Messages: 491
Registered: December 2005
Location: Putty a dark screen
Senior Member
MYDB> select tablespace_name, count(*) 
  2  from dba_segments 
  3  where owner='MYSCHEMA' and segment_name='MY_TABLE_EMP'
  4  group by tablespace_name
  5  /

TABLESPACE_NAME                  COUNT(*)
------------------------------ ----------
MYTBS01                               1

MYDB> select bytes, count(*)
  2  from dba_extents
  3  where owner='MYSCHEMA' and segment_name='MY_TABLE_EMP'
  4  group by bytes
  5  order by bytes
  6  /

     BYTES   COUNT(*)
---------- ----------
     65536         16
   1048576          3

MYDB> select extent_management, allocation_type, min_extlen
  2  from dba_tablespaces
  3  where tablespace_name = 'MYTBS01'
  4  /

EXTENT_MAN ALLOCATIO MIN_EXTLEN
---------- --------- ----------
LOCAL      SYSTEM         65536


Re: dba_free_space [message #525651 is a reply to message #525638] Tue, 04 October 2011 07:46 Go to previous messageGo to next message
Michel Cadot
Messages: 59812
Registered: March 2007
Location: Nanterre, France, http://...
Senior Member
Account Moderator
As you are in SYSTEM allocation type, NEXT EXTENT parameter is not taken into account.
Given the extents you already have the next one MUST be of 1MB.

Regards
Michel
Re: dba_free_space [message #525665 is a reply to message #525651] Tue, 04 October 2011 09:36 Go to previous message
gkrishn
Messages: 491
Registered: December 2005
Location: Putty a dark screen
Senior Member
Another strange thing is , this table has 0 records.so an insert should take extents from the below listed free
extents withing this segments

MYDB> select bytes, count(*)
  2  from dba_extents
  3  where owner='MYSCHEMA' and segment_name='MY_TABLE_EMP'
  4  group by bytes
  5  order by bytes
  6  /

     BYTES   COUNT(*)
---------- ----------
     65536         16
   1048576          3


No solution yet.
Previous Topic: dba_segment
Next Topic: Is it possible to write Trigers on Data Dictionary Tables & Views?
Goto Forum:
  


Current Time: Thu Nov 27 11:46:22 CST 2014

Total time taken to generate the page: 0.08546 seconds