| dba_free_space [message #525475] |
Mon, 03 October 2011 07:24  |
gkrishn
Messages: 506
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 #525496 is a reply to message #525489] |
Mon, 03 October 2011 09:13  |
gkrishn
Messages: 506
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 #525538 is a reply to message #525501] |
Mon, 03 October 2011 13:01 |
gkrishn
Messages: 506
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 |
 |
Michel Cadot
Messages: 68648
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 |
gkrishn
Messages: 506
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 
[Updated on: Tue, 04 October 2011 06:15] Report message to a moderator |
|
|
|
|
|
| Re: dba_free_space [message #525638 is a reply to message #525635] |
Tue, 04 October 2011 06:57 |
gkrishn
Messages: 506
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 #525665 is a reply to message #525651] |
Tue, 04 October 2011 09:36 |
gkrishn
Messages: 506
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.
|
|
|
|
] 
Blog
Search
Help
Members
Register
Login
Home
