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