Increase tablespace size in Oracle

The database filenames are stored in the table dba_data_files

SQL> describe dba_data_files;             
 Name             NULL?    Type
---------------- ----- ---------------------------
 FILE_NAME              VARCHAR2(513)
 FILE_ID                NUMBER
 TABLESPACE_NAME        VARCHAR2(30)
 BYTES                  NUMBER
 BLOCKS                 NUMBER
 STATUS                 VARCHAR2(9)
 RELATIVE_FNO           NUMBER
 AUTOEXTENSIBLE         VARCHAR2(3)
 MAXBYTES               NUMBER
 MAXBLOCKS              NUMBER
 INCREMENT_BY           NUMBER
 USER_BYTES             NUMBER
 USER_BLOCKS            NUMBER
 ONLINE_STATUS          VARCHAR2(7)

We can see the files datasize in MB with:

SQL> select FILE_NAME,TABLESPACE_NAME,BYTES/1024/1024 Mbytes,AUTOEXTENSIBLE from dba_data_files order by TABLESPACE_NAME;

We can use the tables dba_data_files and dba_free_space to know the size and the free space of the tablespaces:

SQL> select     a.TABLESPACE_NAME,
        round(a.BYTES/(1024*1024),2) mbytes_total,
        round(b.BYTES/(1024*1024),2) mbytes_free,
        round(b.largest/(1024*1024),2) largest,
        round(((a.BYTES-b.BYTES)/a.BYTES)*100,2) percent_used
from    
        (
                select  TABLESPACE_NAME,
                        sum(BYTES) BYTES 
                from    dba_data_files 
                group   by TABLESPACE_NAME
        )
        a,
        (
                select  TABLESPACE_NAME,
                        sum(BYTES) BYTES ,
                        max(BYTES) largest 
                from    dba_free_space 
                group   by TABLESPACE_NAME
        )
        b
where   a.TABLESPACE_NAME=b.TABLESPACE_NAME;

If we want to add 10GB to a tablespace then:

ALTER TABLESPACE APP_LOG_INDX ADD DATAFILE '/u04/oradata/XXXXX/APP_log_indx_10.dbf' SIZE 10000M AUTOEXTEND OFF;

For more info please visit:
Changing Datafile Size

You still can have problems with the disk usage due to the archived logs.
But you can easily remove them with the rman tool:

$ rman TARGET /
RMAN> delete archivelog all;
Advertisements

Leave a Reply

Fill in your details below or click an icon to log in:

WordPress.com Logo

You are commenting using your WordPress.com account. Log Out / Change )

Twitter picture

You are commenting using your Twitter account. Log Out / Change )

Facebook photo

You are commenting using your Facebook account. Log Out / Change )

Google+ photo

You are commenting using your Google+ account. Log Out / Change )

Connecting to %s