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;