How to Add, Resize, Drop and Rename of a datafile in Tablespace

                         How to Add, Resize, Drop and Rename a datafile in Tablespace

In this article you will get to know step by step how we can Add, Resize, Drop and Rename of a datafile in Tablespace in Oracle.

In order to create a data file first, need to create a tablespace, To know how to create a tablespace Click here

To demonstrate, I will use USERS tablespace to Add,  Resize, Drop and Rename a data file.

First, we can check how many data files are present under USERS tablespace through below command.

Command: – select name from v$datafile;

We can see from above output only a single data file is present under USERS tablespace.

Addition of Datafile: –

Command to add a datafile: –
alter tablespace users add datafile ‘/u01/app/oracle/oradata/prod/users02.dbf’ size 50m;
The new data file has been added to my USERS tablespace.

Resize of a Datafile: –

Command to resize the datafile: – alter database datafile ‘/u01/app/oracle/oradata/prod/users02.dbf’ resize 20m;

Renaming of a Datafile: –

We have a few steps to rename a data file: –
1) Take the tablespace offline.
2) From Host need to move the data of a file from a new file which we want to rename.
3) From SQL prompt need to alter the changes.
4) Take the tablespace online.


Deletion of datafile: –

Command to drop a datafile: – alter tablespace USERS drop datafile ‘/u01/app/oracle/oradata/prod/user03.dbf

This Post Has 5 Comments

  1. Esteban

    An impressive share! I’ve just forwarded this onto a friend
    who has been conducting a little research on this.
    And he actually ordered me dinner because I stumbled
    upon it for him… lol. So let me reword this….
    Thanks for the meal!! But yeah, thanx for spending the time to
    talk about this topic here on your internet site.

Leave a Reply