86 - [Oracle 19C] Moving and Renaming Online Data Files

 

 

Moving and Renaming Online Data Files

show user
/*
USER is "PDB10_ADMIN"
*/



show con_name
/*
CON_NAME 
------------------------------
PDB10
*/

 

 

 

create tablespace t4
DATAFILE '/u01/app/oracle/oradata/ORCL/PDB10/t4_01.dbf' size 5m;
/*
Tablespace T4 created.


[oracle@test PDB10]$ pwd
/u01/app/oracle/oradata/ORCL/PDB10
[oracle@test PDB10]$ ll
합계 960900
drwxr-x--- 3 oracle oinstall        63 12월 11 17:07 ORCL
-rw-r----- 1 oracle oinstall 104865792 12월 11 16:04 TS2_01.DBF
-rw-r----- 1 oracle oinstall 356524032 12월 11 17:40 sysaux01.dbf
-rw-r----- 1 oracle oinstall 283123712 12월 11 17:55 system01.dbf
-rw-r----- 1 oracle oinstall   5251072 12월 11 17:58 t4_01.dbf    <<<<<<
-rw-r----- 1 oracle oinstall 134225920 12월 11 17:01 temp012023-11-11_01-06-19-635-AM.dbf
-rw-r----- 1 oracle oinstall 104865792 12월 11 17:55 undotbs01.dbf
-rw-r----- 1 oracle oinstall   5251072 12월 11 16:05 users01.dbf
[oracle@test PDB10]$
*/



SELECT DBMS_METADATA.GET_DDL('TABLESPACE','T4') FROM dual;
/*
  CREATE TABLESPACE "T4" DATAFILE 
  '/u01/app/oracle/oradata/ORCL/PDB10/t
*/

 

 

 

--NOW LET US RENAME THE  FIE t4_01.dbf TO t4_001.dbf

ALTER DATABASE MOVE DATAFILE
'/u01/app/oracle/oradata/ORCL/PDB10/t4_01.dbf'
TO
'/u01/app/oracle/oradata/ORCL/PDB10/t4_001.dbf'
/*
Database altered.



[oracle@test PDB10]$ pwd
/u01/app/oracle/oradata/ORCL/PDB10

[oracle@test PDB10]$ ll
합계 960900
drwxr-x--- 3 oracle oinstall        63 12월 11 17:07 ORCL
-rw-r----- 1 oracle oinstall 104865792 12월 11 16:04 TS2_01.DBF
-rw-r----- 1 oracle oinstall 356524032 12월 11 17:40 sysaux01.dbf
-rw-r----- 1 oracle oinstall 283123712 12월 11 17:55 system01.dbf
-rw-r----- 1 oracle oinstall   5251072 12월 11 18:01 t4_001.dbf   <<<<<
-rw-r----- 1 oracle oinstall 134225920 12월 11 17:01 temp012023-11-11_01-06-19-635-AM.dbf
-rw-r----- 1 oracle oinstall 104865792 12월 11 17:55 undotbs01.dbf
-rw-r----- 1 oracle oinstall   5251072 12월 11 16:05 users01.dbf
*/



--NOW LET MOVE t4_001.dbf TO ANOTHER LOCATION

ALTER DATABASE MOVE DATAFILE
'/u01/app/oracle/oradata/ORCL/PDB10/t4_001.dbf'
TO
'/u01/app/oracle/oradata/ORCL/PDB10/ORCL/t4_001.dbf'
/*
Database altered.



[oracle@test ORCL]$ pwd
/u01/app/oracle/oradata/ORCL/PDB10/ORCL

[oracle@test ORCL]$ ll
합계 10248
drwxr-x--- 3 oracle oinstall       22 12월 11 14:41 0BD9769D27CD39DCE063E102A8C059FB
-rw-r----- 1 oracle oinstall 10493952 12월 11 17:12 T3_02.dbf

[oracle@test ORCL]$ ll
합계 15376
drwxr-x--- 3 oracle oinstall       22 12월 11 14:41 0BD9769D27CD39DCE063E102A8C059FB
-rw-r----- 1 oracle oinstall 10493952 12월 11 17:12 T3_02.dbf
-rw-r----- 1 oracle oinstall  5251072 12월 11 18:04 t4_001.dbf <<<<<<<
[oracle@test ORCL]$
*/

 

 

 

please read this

Queries and DML and DDL operations can be performed while the data file is being moved, for example:
1-SELECT statements against tables and partitions
2-Creation of tables and indexes
3- Rebuilding of indexes

Other notes:
1- If objects are compressed while the data file is moved, the compression remains the same.
2- You do not have to shut down the database or take the data file offline while you move a data file to another
location, disk, or storage system.
3- You can omit the TO clause only 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.
4-If the REUSE option is specified, the existing file is overwritten.
note:  The REUSE keyword indicates the new file should be created even if it already exists.
5-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.