Moving or Renaming Datafile in Oracle

Back to: Oracle DBA Tutorials

Moving or Renaming Datafile in Oracle

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

Moving or Renaming Datafile in Oracle

Now, we will learn moving and renaming the online datafiles. 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 a tablespace including the data file name.

Let us go ahead and create a new tablespace where we are going to mention the data file name and location.

Statement: create tablespace TEST4 datafile ‘/u01/app/oracle/oradata/JAYADB/pdbts/test_4.dbf’ size 100m;

So, the tablespace is created. Let’s see how oracle created this tablespace as we have mentioned the data file in the create statement. In order to do that we will run the DDL statement of the tablespace test4.

Query: select dbms_metadata.get_ddl(‘TABLESPACE’,’TEST4’) from dual;

We can see the create tablespace statement ran by oracle with all the other options. Let’s check the presence of the data file in the file system. We will be opening the file manager and we will check under the path /u01/app/oracle/oradata/JAYADB/PDBTS/.

We can see the test datafile is present in the path above path. So, we can see the data file name is test_4.dbf.

Step 3: Rename the data file.

Let us rename the data file from test_4.dbf to test_4_01.dbf. So, we should not change the file name in the file manager. If we change the filename in the file manager then the entire data file will be corrupted. The syntax is very simple.

Syntax: Alter database move datafile ‘/u01/app/oracle/oradata/JAYADB/PDBTS/test_4.dbf’  To /u01/app/oracle/oradata/JAYADB/PDBTS/test_4_01.dbf’;

So, we have moved the data file name from test_4.dbf to test_4_01.dbf.

Step 4: Move the data file to another location.

Until now we have tried learning to rename the datafile. Let us try to learn how to move the data file from one location to another location. So, currently, the datafile test_4_01.dbf is present in the path.

Let us go ahead and create a new folder and move the data file to that folder. We are creating a folder with the name TEST_TABLESPACE.

So, the new folder is created. We should not move the datafile directly into the file manager. There is a lot of process happening in oracle. There is logic in the database, so we have to move the data file using the SQL commands. There are a lot of data dictionaries involved with the data files. The syntax is the same as the previous but we will be giving a different path.

Syntax: Alter database move datafile ‘/u01/app/oracle/oradata/JAYADB/PDBTS/test_4.dbf’  To /u01/app/oracle/oradata/JAYADB/PDBTS/TEST_TABLESPACE/test_4_01.dbf’;

Let’s go ahead and check the folder to verify the data file.

So, the data file is now moved. Let’s go ahead and check the path of the data file.

So, the data file is now moved.

Note about Tablespaces:

Queries and DML and DDL operations can be performed while the datafile is being moved. Examples statements like

1. Select statements against tables and partitions.
2. Creation of tables and indexes
3. Rebuilding of indexes.

Note:

• If objects are compressed while the data file is moved, the compression remains the same.
• No need to shut down the database or take the data file offline while moving the data file from one location to another location, disk or storage system.
• We can omit the TO clause when an oracle-managed file is used. In this case, the DB_CREATE_FILE_DEST initialization parameter should be set to indicate the new location.
• If the REUSE option is specified, the existing file is overwritten. The REUSE keyword indicates the new file should be created even if it already exists.
• If the KEEP clause is specified, the old file will be kept after the move operation. The KEEP clause is not allowed if the source file is an oracle-managed file.

Additional Information about Tablespaces in Oracle

These are the final points about the tablespaces.

• When creating a tablespace, it is initially a read/writes tablespace. This is the default case. Once the tablespace is created then we can make it READ ONLY.
• Generally, alter tablespace statement is used to take the tablespace offline or online, add data files or temp files to it, or make it read–only tablespace.
• A tablespace can be in one of three different statuses or states:
  • READ/WRITE
  • READ-ONLY
  • OFFLINE
• Offline tablespaces are with the following options:
  • Normal
  • Temporary
  • Immediate
• System tablespace may not be taken offline.
• Add space to an existing tablespace by adding the data files to the existing tablespace or changing the size of the existing datafiles.
• Use the drop tablespace statement to drop a tablespace and its contents from the database if you no longer need its content.

This information will be helpful while working on data files.

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