Alter Tablespace and Datafile in Oracle

Back to: Oracle DBA Tutorials

How to Alter Tablespace and Datafile in Oracle

In this article, I am going to discuss How to Alter Tablespace and Datafile in Oracle with Examples. Please read our previous article where we discussed Tablespace Management in Oracle.

Alter Tablespace and Datafile in Oracle.

Now, we will learn how to alter tablespace and datafile in oracle. We will be alerting the database and resizing the data file and adding a new data file and modifying the data file by reading or writing. Make sure the database and listener are up and running fine. Once the database and listener are up and running. Open SQL Developer and connect to the new pluggable database. The new pluggable database is PDBTS.

Step 1: Connect to a new pluggable database.

Connect to the pluggable database PDBTS using the admin user pdbts_Admin.

So, the connection is a success. Let’s go ahead and check the container name and the user name.

Step 2: Create tablespace and user and assign default tablespace.

Let us go ahead and create a new tablespace named TEST3.

Statement: create tablespace TEST3;

So, the tablespace is created. Now, let’s go ahead and get the DDL statement for this tablespace TEST3.

Query: select DBMS_METADATA.GET_DDL(‘TABLESPACE’,’TEST3’) from dual;

Let us go ahead and double-click on the DDL.

We can see the tablespace name TEST3 and oracle has managed the naming convention and the size of the datafile and the other parameters. So, the tablespace name is created. Let’s go ahead and check the existence of the tablespace.

Query: select TABELSAPCE_NAME, BLOCK_SIZE, STATUS, CONTENTS, LOGGING, SEGMENT_SPACE_MANAGEMENT, COMPRESS_FOR from dba_tablesapces;

We can see the tablespace is present and the status of the tablespace is ONLINE and the tablespace is available for the users. We can also check using the other data dictionary v$tablespace.

Query: select * from v$tablespace;

Let us go ahead and check the data files present inside the tablespaces.

Query: select * from v$datafile;

We can see the list of the data files. By TS# number 8 we can see the datafile for the tablespace TEST3. Let us go ahead and create a user and assign the default tablespace.

Statement: create user test_user identified by goodluck default tablespace TEST3;

As the user is created now, let us go ahead and grant the privileges to the user. Let’s go ahead and grant create session, create table, and unlimited tablespace to test_user.

Statement: grant create session, create table, unlimited tablespace to test_user;

We have granted enough privileges for the test_user.

Step 3: Create a table inside test_user.

So, in order to alter the tablespace, we have to create an example table in the schema. Let’s go ahead and create a table inside test_user.

Statement: create table test_user.emp(n number, name varchar2(200));

We can see the table is created. But there is no data inside this table. As we have created the table inside the user test_user then the default tablespace is TEST3. So, let’s go ahead and add a few rows inside the table.

Statement:

Insert into test_user.emp values(1,’abc’);

Insert into test_user.emp values(2,’def’);

Insert into test_user.emp values(3,’ghi’);

So, there is data in the table TEST3.

Step 4: Tablespace read-only.

Let us go ahead and alter the tablespace to READ ONLY. If we make the tablespace READ ONLY then the tablespace will not allow any user to add any data inside the table. Let’s try to add data to the table.

Statement: alter tablespace TEST3 READ ONLY;

So, we have altered the tablespace to read-only. Let’s go ahead and add a new row to the table TEST3. By running the alter statement we will come to know if the tablespace is set to read-only or not.

Statement: Insert into test_user.TEST3 values (4, ‘jkl’);

So, we are getting an error that displays the file cannot be modified. This is because the table EMP is present inside the schema TEST_USER. The schema TEST_USER has the default tablespace as TEST3. Let’s make the tablespace READ WRITE again.

Statement: alter tablespace TEST3 read write;

Let’s try to insert the data again.

Statement: Insert into test_user.TEST3 values (4, ‘jkl’);

We can see the data is now added to the table.

Step 5: Resize the data file.

We have learned about adding a tablespace and adding the data into the table. Let us go ahead and try to learn about resizing the data file. Let us check the data files inside the database.

Query: select * from v$datafile;

From the above image, we can see the size of the data file is 100M. By default, oracle assigns the size of the datafile as 100M. Let’s go ahead and resize the data file. The syntax to resize the datafiles is

Statement: alter database datafile ‘/complete_datafile_including_path’ resize 200M;

We can see the datafile is resized. To make sure everything is fine. Check the tablespaces in the DBA column.

Using the DBA column, we can see the tablespace TEST3 is modified to 200M. We can click on the files option above to check the datafiles as well.

From the tablespace TEST3, we can see only one data file and the data file is available for the users where the size of the data file is 199M.

Step 6: Add a new data file.

Let’s go ahead and add a new data file to the tablespace TEST3. The syntax for adding a new data file is as follows.

Syntax: alter tablespace TEST3 add datafile ‘/db_file_location/data_file_name.dbf’ size 100M;

We have to mention the location of the new data file by using the previous data file location. As we have added a new data file we got two methods to resize the data file. The first method is to resize the current data file. The second method is to add new data file.

These two methods are correct. We have to make sure the path is always correct. If the path is not correct then the data file will be corrupted. From the file manager, we can see the defile is created in the correct location.

Let us go ahead and check the number of data files that are assigned to the tablespace TEST3. We will be using v$datafile dictionary.

Query: select * from v$datafile;

We can see the new data file is added to the list of data files.

Note: we might have a doubt that if we insert a lot of data in which datafile the data is stored. Oracle manages the storing of data in datafiles.

Step 7: Check the data file using the DBA column.

Let us go ahead and open the tablespace tab in the DBA section.

If we click on TEST3. We will get the properties of the tablespace TEST3.

We can see the information about the tablespace TEST3. We can see there are a few columns mentioned above. If we click on datafiles we can see how many datafiles are associated with the tablespace TEST3.

We can see there are two data files. One datafile is of 10M and the other datafile is of 200M. The first data file name is mentioned by oracle. But the second data file is mentioned by us.

This is how we resize a data file and add a data file to the tablespace. We have also learned how to alter the tablespace to READ WRITE and READ ONLY methods.

In the next article, I am going to discuss Moving or Renaming Datafile in Oracle. Here, in this article, I try to explain How to Alter Tablespace and Datafile in Oracle with Examples. I hope you enjoy this Alter Tablespace and Datafile in Oracle article.