One of the most common tasks for any DBA is managing the space used by the database. This, in turn, translates to managing space on the server. In the early days of Oracle, this could be quite a task as it was considered a ‘best practice’ to use as few extents as possible to cut down on fragmentation. Also, there were some pretty strong limitations on file sizes, which were enforced at the OS level. Today, we have locally managed tablespaces and automatic segment management. These options, coupled with the large file sizes that the typical operating system offers, have freed the DBA from from micromanaging each extent, segment, and file in the database.
How Big Should My Tablespace Be?
When creating a new tablespace, often one makes space calculations based on information from developers and other application experts. Often you’re dealing with a purchased package and only have the vendor’s recommendations to go by. These recommendations or calculations can result in vastly over-allocated space. A calculation may show that within the first year the tablespace will need be 100Gb to hold all of the data. Or perhaps, the developer simply requests a 100Gb tablespace based on his or her knowledge of the data. In any event, you’ve got a tablespace request and, in turn, you request your friendly Unix SA to allocate 100Gb to a filesystem so that this 100Gb tablespace can be accommodated.
SQL> create tablespace appdata datafile ‘/u01/oradata/appdata01.dbf’ size 20g, 2 ‘/u01/oradata/appdata02.dbf’ size 20g, 3 ‘/u01/oradata/appdata03.dbf’ size 20g, 4 ‘/u01/oradata/appdata04.dbf’ size 20g, 5 ‘/u01/oradata/appdata05.dbf’ size 20g ; Tablespace created.
As time goes on, you receive no space alerts for this tablespace, but like a good DBA you log in and check on the freespace from time to time. After a year, you find only 20Gb occupied. The tables have not grown at the rate that was expected and you have tied up 80Gb unnecessarily.
Autoextensible Files
So, what do we do when a tablespace is requested but there is no real good idea of how much data may be going into it? To the rescue come autoextensible files.
SQL> create tablespace mytbs datafile 2 ‘/u01/oradata/mytbs01.dbf’ size 1g autoextend on next 1g maxsize 20g, 3 ‘/u01/oradata/mytbs02.dbf’ size 1g autoextend on next 1g maxsize 20g, 4 ‘/u01/oradata/mytbs03.dbf’ size 1g autoextend on next 1g maxsize 20g, 5 ‘/u01/oradata/mytbs04.dbf’ size 1g autoextend on next 1g maxsize 20g, 6 ‘/u01/oradata/mytbs05.dbf’ size 1g autoextend on next 1g maxsize 20g ; Tablespace created.
This time, we have created a tablespace that is 5Gb in size with the capability to grow to 100Gb. Monitoring the filesystem at the OS level is now just as effective as monitoring the free space in the tablespace itself. In many ways it is MORE effective as tablespace freespace may be made up of many small unusable chunks (in which case, you may need to reorg—but that’s another topic).
Checking Available Space
When checking for the available space, you now need to distinguish between “allocated” space versus “physically allocated” space. I’ve found that this trips people up as the tendency is to look at dba_free_space. In our example above, we have allocated 100Gb, but we have only physically allocated 5Gb. The below query looks at dba_free_space and reflects only the physically allocated space.
SQL> l 1 select a.tablespace_name, a.bytes_alloc, nvl(b.tot_free, 0) tot_free, 2 a.bytes_alloc-nvl(b.tot_free,0) tot_used, nvl(b.max_free_extent,0) max_free_extent, 3 ((a.bytes_alloc-nvl(b.tot_free,0))/a.bytes_alloc)*100 pct_used 4 from ( select tablespace_name, sum(bytes) bytes_alloc 5 from dba_data_files 6 group by tablespace_name ) a, 7 ( select tablespace_name, sum(bytes) tot_free, max(bytes) max_free_extent 8 from dba_free_space 9 group by tablespace_name ) b, 10 dba_tablespaces c 11 where a.tablespace_name = b.tablespace_name (+) 12 and a.tablespace_name = c.tablespace_name 13 union 14 select a.tablespace_name, sum(bytes) bytes_alloc, null, null, null, null 15 from dba_temp_files a, dba_tablespaces c 16 where a.tablespace_name = c.tablespace_name 17 group by a.tablespace_name, c.status 18* order by 1 SQL> / TABLESPACE_NAME BYTES_ALLOC TOT_FREE TOT_USED MAX_FREE_EXTENT PCT_USED ---------------------- -------------------- ---------------- -------------------- ---------------- -------- MYTBS 5,368,709,120 5,363,466,240 5,242,880 1,072,693,248 .10 SYSAUX 786,432,000 53,870,592 732,561,408 45,088,768 93.15 SYSTEM 377,487,360 5,439,488 372,047,872 5,242,880 98.56 TEMP 20,971,520 UNDOTBS1 89,128,960 78,249,984 10,878,976 63,963,136 12.21 USERS 104,857,600 102,170,624 2,686,976 102,105,088 2.56
Here we will show the allocated versus the physically allocated space:
SQL> l 1 select a.tablespace_name, 2 a.bytes_alloc/(1024*1024) "TOTAL ALLOC (MB)", 3 a.physical_bytes/(1024*1024) "TOTAL PHYS ALLOC (MB)", 4 nvl(b.tot_used,0)/(1024*1024) "USED (MB)", 5 (nvl(b.tot_used,0)/a.bytes_alloc)*100 "% USED" 6 from ( select tablespace_name, 7 sum(bytes) physical_bytes, 8 sum(decode(autoextensible,'NO',bytes,'YES',maxbytes)) bytes_alloc 9 from dba_data_files 10 group by tablespace_name ) a, 11 ( select tablespace_name, sum(bytes) tot_used 12 from dba_segments 13 group by tablespace_name ) b 14 where a.tablespace_name = b.tablespace_name (+) 15* and a.tablespace_name not in (select distinct tablespace_name from dba_temp_files) SQL> / TABLESPACE_NAME TOTAL ALLOC (MB) TOTAL PHYS ALLOC (MB) USED (MB) % USED ---------------------- ---------------- --------------------- ------------- ------- MYTBS 102,400.00 5,120.00 0.00 0.00 SYSAUX 32,767.98 750.00 697.63 2.13 SYSTEM 600.00 360.00 353.81 58.97 UNDOTBS1 32,767.98 85.00 9.38 0.03 USERS 11,264.00 100.00 1.56 0.01
Note the total allocated space is 100Gb while the physically allocated space is 5Gb. Let’s put something in our tablespace and see what things look like:
SQL> create table mmtest (x number) storage (initial 4g next 128m) tablespace mytbs; Table created. SQL> @tsinfo TABLESPACE_NAME BYTES_ALLOC TOT_FREE TOT_USED MAX_FREE_EXTENT PCT_USED ---------------------- -------------------- ---------------- -------------------- ---------------- -------- MYTBS 5,368,709,120 1,068,498,944 4,300,210,176 264,241,152 80.10 SYSAUX 786,432,000 53,870,592 732,561,408 45,088,768 93.15 SYSTEM 377,487,360 5,439,488 372,047,872 5,242,880 98.56 TEMP 20,971,520 UNDOTBS1 89,128,960 78,249,984 10,878,976 63,963,136 12.21 USERS 104,857,600 102,170,624 2,686,976 102,105,088 2.56 SQL> @tsalloc TABLESPACE_NAME TOTAL ALLOC (MB) TOTAL PHYS ALLOC (MB) USED (MB) % USED ---------------------- ---------------- --------------------- ------------- ------- MYTBS 102,400.00 5,120.00 4,096.00 4.00 SYSAUX 32,767.98 750.00 697.63 2.13 SYSTEM 600.00 360.00 353.81 58.97 UNDOTBS1 32,767.98 85.00 9.38 0.03 USERS 11,264.00 100.00 1.56 0.01
If we look at things from a strictly “physical” point of view (using the 1st query), the tablespace appears to be 80% full. However, if we take the autoextensible files into account (using the 2nd query), we can see that the tablespace is only 4% full! The tablespace can continue to fill and to grow and we need only monitor the filesystem. At some point, of course, additional files may need to be added, but you can follow the same strategy there:
SQL> alter tablespace mytbs add datafile ‘/u01/oradata/mytbs06.dbf’ size 1g autoextend on next 1g maxsize unlimited ; Tablespace altered.
Monitor the Files—Not the Free Space
There are no hard and fast rules for the initial size you give the file and at what size you specify the ‘next’ parameter. Often choosing a smaller number (100M) will make more sense. Choosing ‘maxsize unlimited’ is often the simplest choice. Don’t worry, Oracle doesn’t actually grow the file ad infinitum if you specify ‘unlimited’. The maxsize is OS dependant and is limited by the maximum operating system file size. The easiest way to be certain what it is exactly is to query the database:
SQL> l
1 select file_id, file_name, bytes/1024/1024 mbytes, autoextensible, maxbytes/1024/1024 max_mbytes,
2 increment_by*block_size/1024/1024 increment_by_mbytes,
3 substr(file_name, instr(file_name,'/',-1)+1, length(file_name)) fn
4 from dba_data_files f, dba_tablespaces t
5 where f.tablespace_name = trim(upper('&&ts'))
6 and f.tablespace_name = t.tablespace_name
7 union
8 select file_id, file_name, bytes/1024/1024 mbytes, autoextensible, maxbytes/1024/1024 max_mbytes,
9 increment_by*block_size/1024/1024 increment_by_mbytes,
10 substr(file_name, instr(file_name,'/',-1)+1, length(file_name)) fn
11 from dba_temp_files f, dba_tablespaces t
12 where f.tablespace_name = trim(upper('&&ts'))
13 and f.tablespace_name = t.tablespace_name
14* order by file_id
SQL> /
Enter value for ts: mytbs
FILE_ID FILE_NAME MBYTES AUT MAX_MBYTES INCREMENT_BY_MBYTES
---------- ----------------------------- ------------ --- ---------- -------------------
12 /u01/oradata/mytbs01.dbf 1,024 YES 20,480 1,024
13 /u01/oradata/mytbs01.dbf 1,024 YES 20,480 1,024
14 /u01/oradata/mytbs01.dbf 1,024 YES 20,480 1,024
15 /u01/oradata/mytbs01.dbf 1,024 YES 20,480 1,024
16 /u01/oradata/mytbs01.dbf 1,024 YES 20,480 1,024
17 /u01/oradata/mytbs01.dbf 1,024 YES 32,768 1,024
So, the new file that was added later with ‘maxsize unlimited’ actually has a maximum size 32Gb.
Using this strategy with autoextensible files can simplify your life as a DBA. There are DBAs out there that believe that this is a lazy way of managing tablespaces–and space in general. However, I believe that it just makes sense. So often we have no way of knowing how much space is going to be required in a given tablespace. Over-allocating the space up front is a waste. Using it as you need it is smart!
