How to use commands to move various types of files in Oracle

I. Move data files:

You can use alter database or alter tablespace to move data files.

1. ALTER DATABASE

Method 1: You can use this method to move data files in any tablespace.

Step 1:

Close Database

# Sqlplus/as sysdba

SQL> shutdown immediate

Step 2:

Use the operating system command to move data files:

Move the data file 'test. ora 'from the/ora/Oracle/data1 directory to the/ora/oracle/data2 directory:

# Mv/ora/oracle/data1/test. ora/oracle/data2

Step 3:

Mount the database and use the ALTERDATABASE command to rename the data file:

SQL> STARTUP MOUNT;

SQL> ALTER DATABASE RENAME FILE '/ora/oracle/data1/test. ora' TO '/ora/oracle/data2/test. ora ';

Step 4:

Open Database

SQL> ALTER DATABASE OPEN;

SQL> SELECTNAME, STATUS FROM V $ DATAFILE;

2. ALTER TABLESPACE

Method: This method requires that the data file neither belong to the SYSTEM tablespace nor to the tablespace containing the ACTIVE rollback segment or temporary segment.

Step 1:

OFFLINE:

# Sqlplus/as sysdba

SQL> ALTER TABLESPACE test OFFLINE;

Step 2:

Use the operating system command to move data files:

Move the data file 'test. ora 'from the/ora/oracle/data1 directory to the/ora/oracle/data2 directory:

$ Mv/ora/oracle/data1/test. ora/oracle/data2

Step 3:

Use the alter tablespace command to change the data file name:

SQL> ALTER TABLESPACE test RENAME DATAFILE '/ora/oracle/data1/test. ora' TO '/ora/oracle/data2/test. ora ';

Step 4:

ONLINE:

SQL> ALTER TABLESPACE test ONLINE;

SQL> SELECT NAME, STATUS FROM V $ DATAFILE;

1 2 Next Page