Q

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? 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

Dig deeper on Oracle database design and architecture

Pro+

Features

Enjoy the benefits of Pro+ membership, learn more and join.

Have a question for an expert?

Please add a title for your question

Get answers from a TechTarget expert on whatever's puzzling you.

You will be able to add details on the next page.

0 comments

Oldest 

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:

SearchDataManagement

SearchBusinessAnalytics

SearchSAP

SearchSQLServer

TheServerSide

SearchDataCenter

SearchContentManagement

SearchFinancialApplications

Close