I have a third party application using Oracle 10g database with 1 tablespace and 1 datafile. They have assigned 10GB as the size of the tablespace which is almost full now. I know we can can increase size of this.

I want to clarify what if I add one more datafile and assign 10GB space to that? Do I need to modify anything on application or will it work normally?

So all I need to do would be:

alter tablespace USER_DATA_TBS add datafile '/oradata/ovbs/user_data04.dbf' size 1000m;

Please help, I am a total beginner.


That does not increase the size of a data file, it adds one more datafile to the tablespace.

To enlarge an existing data file (for example to 20G):

alter database datafile '/oradata/ovbs/user_dataXX.dbf' resize 20g;

To add a new datafile to that tablespace (10G)

alter tablespace user_data_tbs add datafile '/oradata/ovbs/user_dataYY.dbf' size 10g;

Another option is to make the original datafile auto extensible:

alter database datafile '/oradata/ovbs/user_dataXX.dbf' autoextend on;

That way, the database will automatically extend the datafile as necessary, until the file system it resides on becomes full

The size of datafiles is totally transparent to applications. See https://docs.oracle.com/en/database/oracle/oracle-database/21/admin/managing-tablespaces.html#GUID-C207AC96-0F99-4F05-B3DD-E4A4FA17F98C for details

  • 1
    It should also be noted that most operating systems have a file size limit of 32GB, so auto extend on a file will generally only work until that file reaches the 32GB limit, or the file system becomes filled, whichever comes first.
    – pmdba
    Jan 4 '21 at 19:18

Not the answer you're looking for? Browse other questions tagged or ask your own question.