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