Wednesday, January 30, 2019

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

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

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




2 comments: