Extend a tablespace

This is the first thing and Oracle Database Administrator needs to know.

First, check with this query tablespace occupation state of all tablespaces in this database:

set linesize 170
set pages 60
column  pct_used format 999.9       heading "%|Used" 
column  ts_name    format a33     heading "Tablespace Name" 
column  Mbytes   format 999,999,999,999    heading "MBytes" 
column  used    format 999,999,999,999   heading "Used" 
column  free    format 999,999,999,999  heading "Free" 
column  largest    format 999,999,999,999  heading "Largest" 
break   on report 
compute sum of Mbytes on report 
compute sum of free on report 
compute sum of used on report


select nvl(b.tablespace_name,nvl(a.tablespace_name,'UNKNOWN')) ts_name
, kbytes_alloc/1024      Mbytes
, (kbytes_alloc-nvl(kbytes_free,0))/1024     used
, nvl(kbytes_free,0)/1024      free
, ((kbytes_alloc-nvl(kbytes_free,0))/kbytes_alloc)*100 pct_used
, nvl(largest,0)/1024       largest
from (select sum(bytes)/1024   Kbytes_free
 ,  max(bytes)/1024  largest
 ,  tablespace_name
 from   dba_free_space
 group by tablespace_name)   a
, (select  sum(bytes)/1024  Kbytes_alloc
 ,  tablespace_name
 from  dba_data_files
 group by tablespace_name)   b
where a.tablespace_name (+) = b.tablespace_name
order by 5,1;

in my case, it gives me

Tablespace Name                             MBytes             Used             Free   Used          Largest
--------------------------------- ---------------- ---------------- ---------------- ------ ----------------
ODIDEMO                                        300                3              297    1.1              180
OGG_STB                                        100                1               99    1.3               99
OGG_IAS_IAU                                     60                2               58    2.7               58
CUBE_STB                                        10                1                9   12.5                9
LCMPROD_STB                                     10                1                9   12.5                9
LCMQA_STB                                       10                1                9   12.5                9
LCM_STB                                         10                1                9   12.5                9
PROD_STB                                        10                1                9   12.5                9
UNDOTBS1                                       330               70              260   21.3              153
LCMPROD_ODI_USER                               200               46              155   22.8              155
LCMQA_ODI_USER                                 200               46              155   22.8              155
LCM_ODI_USER                                   200               61              139   30.3              139
CUBE_ODI_USER                                  200               66              134   32.9              134
PROD_ODI_USER                                  200               73              127   36.4              127
OGG_ODI_USER                                   200               76              124   37.9              124
OGG_IAS_OPSS                                    60               38               22   64.0               22
USERS                                          230              161               69   70.1                8
MITIREP_1203                                   450              401               49   89.2               48
SYSTEM                                         550              502               48   91.3               48
SYSAUX                                         820              775               45   94.6               42
                                  ---------------- ---------------- ----------------
sum                                          4,150            2,328            1,823

So i´m going to resize SYSAUX tablespace that is at 94.6% occupation.
As you can see in the below query, tablespace SYSAUX has 1 datafile located in /u01/app/oracle/oradata/ORCL/..

SQL> set linesize 140
set pages 130
column  FILE_NAME    format a100
select FILE_ID, FILE_NAME, BYTES/1024/1024, AUTOEXTENSIBLE from dba_data_files where tablespace_name like 'SYSAUX';SQL> SQL> SQL>

   FILE_ID FILE_NAME                                                                                            BYTES/1024/1024 AUT
---------- ---------------------------------------------------------------------------------------------------- --------------- ---
         2 /u01/app/oracle/oradata/ORCL/sysaux.dbf                                                                          820 NO

...so we should check if this filesystem has enough free space to resize this datafile.

[oracle@ODIGettingStarted ~]$ df -h /u01/app/oracle/oradata/ORCL/
Filesystem            Size  Used Avail Use% Mounted on
/dev/sdb               15G   13G  1.1G  93% /u01

and with df unix command we can see that this filesystem is at 93% occupation and has 1.1Gb free. In this point we have two options: Add more space to an existing datafile or add another file to the tablespace. First option is simpler but second option allow us to add another datafile in another filesystem if the current filesystem is full or nearly full.

1- Resize the current datafile:

SQL> alter database datafile 2 resize 900M;

Database altered.

SQL>

or

SQL> alter database datafile '/u01/app/oracle/oradata/ORCL/sysaux.dbf' resize 900M;

Database altered.

Both previous sentences are same.

2- Add another datafile to tablespace:

so lets see where can i place new datafile in my system:

[oracle@ODIGettingStarted ~]$ df -h
Filesystem            Size  Used Avail Use% Mounted on
/dev/mapper/vg_odigettingstarted-lv_root
                       37G  6.5G   28G  19% /
tmpfs                 2.2G  453M  1.8G  21% /dev/shm
/dev/sda1             485M   55M  405M  12% /boot
/dev/sdb               15G   13G  1.1G  93% /u01
/dev/sdc               10G    3G    7G  29% /u02
/dev/sdd               10G    3G    7G  29% /u03

So i can see that on u02 or u03 disks i had a lot of space, so i´m going to add the new datafile in /u02

SQL>  alter tablespace SYSAUX add datafile '/u02/app/oracle/oradata/ORCL/sysaux_02.dbf' size 100M;

Tablespace altered.

So, we can see new datafile added with this query:

SQL> select FILE_ID, FILE_NAME, BYTES/1024/1024, AUTOEXTENSIBLE from dba_data_files where tablespace_name like 'SYSAUX';

   FILE_ID FILE_NAME                                                              BYTES/1024/1024 AUT
---------- ---------------------------------------------------------------------- --------------- ---
         2 /u01/app/oracle/oradata/ORCL/sysaux.dbf                                            900 NO
        21 /u02/app/oracle/oradata/ORCL/sysaux_02.dbf                                         100 NO