Ask the Expert

Estimating extent size

I'm new at administrating a database (8.1.7 on Win2000). I have a tablespace I use to keep tables with large amounts of data (large for us). The data file has grown to 4 GB in size (auto extend ON). The tablespace is locally managed with uniform extent size of 200 MB. I think the file has grown to this size because of the extent size. How do I determine an extent size for this tablespace? Also, should I use smaller multiple datafiles for this tablespace?

    Requires Free Membership to View

Well, you answered your own question actually. You said "the tablespace is locally managed with uniform extent size of 200 MB". So, your extent size is 200 MB. The reason the datafile has grown to 4 GB is not because of the extent size, but because of data being added that requires more extents to be allocated. It may be that you have extents that are too big and so attempting to grab 200 MB at a time for an extent is way too much, so you may want to check into that. Locally managed tablespaces are very efficiently managed overall. If you want to limit the file size, do so and turn autoextend off. You can then create additional datafiles as needed and limit them to a specific size.
For example:
SQL> create tablespace users2
  2  datafile 'D:oracleoradata3user2.dbf' size 200M
  3  autoextend off
  4  extent management local uniform size 10M;

If what you really want to know is how much data is stored in the allocated space, try this query to get a list of all your tablespaces and how much space is allocated vs. how much is actually used for each:

column FileNum     format 999     heading 'File|Num'
column FileName     format A60      heading 'File Name' 
column Tablespace   format A30      heading 'Tblspace'
column Allocated    format 9,999    heading 'Size|(MB)' 
column Used         format 9,999    heading 'Used|(MB)'
column TotalFree    format 9,999    heading 'Free|(MB)'
column status       format A3       heading 'STA'

column oneMB noprint new_value MB

compute sum of Allocated on tablespace 
compute sum of Allocated on report 
compute sum of Used      on tablespace 
compute sum of Used      on report 
compute sum of TotalFree on tablespace 
compute sum of TotalFree on report 

select substr(to_char(x.file_id,999), 1,4)     FileNum,
       x.file_name                             FileName,
       x.tablespace_name                       Tablespace,
       x.bytes/(1024*1024)                     Allocated,
       (x.bytes-sum(y.bytes))/(1024*1024)      Used,
       substr(x.status,1,3)                    status
from sys.dba_data_files x , 
     sys.dba_free_space  y 
where x.file_id = y.file_id 
group by substr(to_char(x.file_id,999), 1,4),
         x.file_name,
         x.tablespace_name,
         x.bytes,
         x.status  
order by 1,2,3;

This was first published in November 2003

There are Comments. Add yours.

 
TIP: Want to include a code block in your comment? Use <pre> or <code> tags around the desired text. Ex: <code>insert code</code>

REGISTER or login:

Forgot Password?
By submitting you agree to receive email from TechTarget and its partners. If you reside outside of the United States, you consent to having your personal data transferred to and processed in the United States. Privacy
Sort by: OldestNewest

Forgot Password?

No problem! Submit your e-mail address below. We'll send you an email containing your password.

Your password has been sent to: