Oracle-Datafile

admin Oracle ,

Hello guys,
Hope you are doing fantastic being an Oracle professional.
I wrote a blog on Oracle TABLESPACE. If you have not read that yet, I highly recommend you to click here and have a look.
In that blog, we learned how to create a Tablespace in the Oracle Database and I explained that every tablespace contains at least one datafile. So, in this blog we will learn about the datafile.

What is a Datafile?

Datafile is a storage sub-unit exists in a Tablespace in the Oracle database. Whenever we import any data into the Oracle database, it creates the tables and stores the data into the datafiles.

Next we will learn how to ADD/ALTER/DROP datafiles.

How to ADD a Datafile to an existing Tablespace?

We already have an existing Tablespace (PERM_TBLSPC) with one datafile (PERM_DF.DBF) we created while learning about the Tablespace.
We will add a new Datafile to this tablespace. First, we will check this tablespace and existing datafiles using the below query.

 

SELECT FILE_NAME,TABLESPACE_NAME FROM DBA_DATA_FILES
where TABLESPACE_NAME = 'PERM_TBLSPC';

Output

FILE_NAME                                TABLESPACE_NAME
---------------------------------------- -----------------
C:\APP\ORACLE\ORADATA\ORCL\PERM_DF.DBF   PERM_TBLSPC

Above output confirms that we have a Tablespace with only one datafile.
Let’s use ALTER TABLESPACE statement and add a new datafile to the tablespace.

 
ALTER TABLESPACE PERM_TBLSPC
ADD DATAFILE
'C:\APP\ORACLE\ORADATA\ORCL\PERM_DF2.DBF'
SIZE 50M;

Output

TABLESPACE PERM_TBLSPC altered.

You can check the new datafile by running the first snippet SELECT statement.

How to ALTER a Datafile?

We added above datafile with the size of 50MB. Let’s alter this datafile and add more 50MB memory to this datafile.
This time we will be using ALTER DATABASE statement with RESIZE keyword.

 
ALTER DATABASE 
DATAFILE
'C:\APP\ORACLE\ORADATA\ORCL\PERM_DF2.DBF'
RESIZE 100M;

Output

Database altered.

Let’s check the current size of the datafile(s).

 
SELECT FILE_NAME,TABLESPACE_NAME,
BYTES/1024/1024 as SIZE_IN_MB 
/* BYTES is a column in the table that shows the size of the datafile.*/
FROM DBA_DATA_FILES
where TABLESPACE_NAME = 'PERM_TBLSPC';

Output

FILE_NAME					TABLESPACE_NAME	SIZE_IN_MB
---------------------------------------- 	-----------------	----------
C:\APP\ORACLE\ORADATA\ORCL\PERM_DF.DBF	PERM_TBLSPC		50
C:\APP\ORACLE\ORADATA\ORCL\PERM_DF2.DBF	PERM_TBLSPC		100

Note:
Oracle database shows the datafile size in BYTES. That’s the reason I have divided it by 1024 twice to make it MEGABYTE(MB).

How to DROP a Datafile?

Until now we have learned how to add/alter a datafile. Next, we will learn how to DROP a datafile.
It is a very similar task what we did above while adding a datafile. Just replace ADD by DROP and remove SIZE keyword.

 
ALTER TABLESPACE PERM_TBLSPC
DROP DATAFILE
'C:\APP\ORACLE\ORADATA\ORCL\PERM_DF2.DBF';

Output

TABLESPACE PERM_TBLSPC altered.
 

You can query to DBA_DATA_FILES table to check if datafile has been deleted or not.

That’s all about datafile. Again, I have included very specific content to this blog to keep it easy to understand.
If you have any questions please comment below or write me an email.

If you like this blog, do share with your friends and colleagues on your social media.
For more updates join my facebook group and do like my facebook page.

Thank you,
Kapil Kumar

Sharing is caring!

You May Also Like..

Oracle Virtual Column

Hello guys, Hope you are doing well. Today, we will talk about the Oracle Virtual Column. I will try to […]

Oracle-Tablespace

Hello guys, Hope you are doing well. Today, we will talk about Oracle Tablespace and I will try to cover […]

Oracle Set Operators

Hello guys, Hope you are doing well. Today, we will talk about Oracle Operators. An operator manipulates individual data items […]

Leave a Reply

Your email address will not be published. Required fields are marked *