Database Journal
MS SQL Oracle DB2 Access MySQL PostgreSQL Sybase PHP SQL Etc SQL Scripts & Samples Links Database Forum

» Database Journal Home
» Database Articles
» Database Tutorials
MS SQL
Oracle
DB2
MS Access
MySQL
» RESOURCES
Database Tools
SQL Scripts & Samples
Links
» Database Forum
» Sitemap
Free Newsletters:
DatabaseDaily  
News Via RSS Feed


follow us on Twitter
Database Journal |DBA Support |SQLCourse |SQLCourse2
 

Featured Database Articles

Oracle

Posted December 30, 2013

Oracle Tablespace File Management Made Simple

By Mary Mikell Spence

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!

See all articles by Mary Mikell Spence



Oracle Archives

Comment and Contribute

 


(Maximum characters: 1200). You have characters left.

 

 




Latest Forum Threads
Oracle Forum
Topic By Replies Updated
Oracle Data Mining: Classification jan.hasller 0 July 5th, 07:19 AM
Find duplicates - Unique IDs Lava 5 July 2nd, 08:30 AM
no matching unique or primary key rcanter 1 April 25th, 12:32 PM
Update values of one table based on condition of values in other table using Trigger Gladiator 3 February 29th, 06:01 PM


















Thanks for your registration, follow us on our social networks to keep up-to-date