database move datafile - ORACLEAGENT BLOG

Checking Tablespaces in Database

SQL> select name from v$tablespace; 

NAME
------------------------------ 
SYSTEM
SYSAUX
UNDOTBS
TEMP
DATA

Create New Tablespace

SQL> create tablespace india datafile
'/u01/app/oracle/oradata/ORACLEAGENT/india01.dbf' size 30m;

Tablespace created.
SQL> select name from v$tablespace;
 NAME
------------------------------
 SYSTEM
 SYSAUX
 UNDOTBS
 TEMP
 DATA
 INDIA

Check Datafile in whole Database

SQL> select tablespace_name,file_name from dba_data_files; 

TABLESPACE_NAME	FILE_NAME
-------------------- --------------------------------------------------
 SYSTEM		  /u01/app/oracle/oradata/ORACLEAGENT/system.dbf 
 SYSAUX	          /u01/app/oracle/oradata/ORACLEAGENT/sysaux.dbf
 UNDOTBS	  /u01/app/oracle/oradata/ORACLEAGENT/undo1.dbf 
 DATA		  /u01/app/oracle/oradata/ORACLEAGENT/data01.dbf 
 INDIA	          /u01/app/oracle/oradata/ORACLEAGENT/india01.dbf

Check Datafile's in particular Tablespace.

SQL> select file_name from dba_data_files where tablespace_name='INDIA'; 

FILE_NAME
--------------------------------------------------
/u01/app/oracle/oradata/ORACLEAGENT/india01.dbf

Check Tablespace size in particular tablespace

SQL> select sum(bytes)/1024/1024 from dba_data_files where tablespace_name='INDIA';

SUM(BYTES)/1024/1024
--------------------
2048

How to Check Tablespace size in whole Database

SQL> select tablespace_name,sum(bytes)/1024/1024 from dba_data_files group by 
tablespace_name;

TABLESPACE_NAME	SUM(BYTES)/1024/1024
--------------  --------------------
SYSTEM	          2048
INDIA	           30
SYSAUX	          2048
UNDOTBS	          2048
DATA               60

How to Resize Datafiles
	
SQL> select file_name from dba_data_files where tablespace_name='INDIA'; 

FILE_NAME
--------------------------------------------------
/u01/app/oracle/oradata/ORACLEAGENT/india01.dbf

SQL>alter database datafile '/u01/app/oracle/oradata/ORACLEAGENT/india01.dbf' 
resize 60m;

Database altered.

SQL> select sum(bytes)/1024/1024 from dba_data_files where tablespace_name='INDIA';

SUM(BYTES)/1024/1024
 
--------------------
60

Add New Datafile

SQL> select file_name from dba_data_files where tablespace_name='INDIA'; 

FILE_NAME
--------------------------------------------------
/u01/app/oracle/oradata/ORACLEAGENT/india01.dbf


SQL> alter tablespace india add datafile 
'/u01/app/oracle/oradata/ORACLEAGENT/india02.dbf' size 30m;

Tablespace altered.

SQL> select file_name from dba_data_files where tablespace_name='INDIA'; 

FILE_NAME
--------------------------------------------------
/u01/app/oracle/oradata/ORACLEAGENT/india01.dbf
/u01/app/oracle/oradata/ORACLEAGENT/india02.dbf

Rename or Relocate datafiles
   
           after the 12c method

SQL> select file_name from dba_data_files where tablespace_name='INDIA'; 

FILE_NAME
-------------------------------------------------------------------------------
/u01/app/oracle/oradata/ORACLEAGENT/india01.dbf
/u01/app/oracle/oradata/ORACLEAGENT/india02.dbf

SQL> alter database move datafile '/u01/app/oracle/oradata/ORACLEAGENT/india02.dbf' 
to '/u02/india03.dbf';

Database altered.

SQL> select file_name from dba_data_files where tablespace_name='INDIA'; 

FILE_NAME
-------------------------------------------------------------------------------
/u01/app/oracle/oradata/ORACLEAGENT/india01.dbf
/u02/india03.dbf

              before 12c method

SQL> alter tablespace india offline;
 Tablespace altered.


[oracle@training u02]$ cd $ORACLE_BASE/oradata/ORACLEAGENT 

[oracle@training ORACLEAGENT]$ ls -lrth
total 6.4G
-rw-rw----. 1 oracle oracle 501M Jul 1 22:09 temp01.dbf
-rw-rw----. 1 oracle oracle 41M Jul 1 22:09  redo3.log
-rw-rw----. 1 oracle oracle 21M Jul 2 00:03  test02.dbf
-rw-rw----. 1 oracle oracle 31M Jul 2 00:03  test01.dbf
-rw-rw----. 1 oracle oracle 41M Jul 2 00:10  redo1.log
-rw-rw----. 1 oracle oracle 41M Jul 2 00:15  data01.dbf
-rw-rw----. 1 oracle oracle 21M Jul 2 00:15  data02.dbf
-rw-rw----. 1 oracle oracle 2.1G Jul 2 00:35 system.dbf
-rw-rw----. 1 oracle oracle 61M Jul 2 00:39  india01.dbf
-rw-rw----. 1 oracle oracle 31M Jul 2 00:39  india02.dbf
-rw-rw----. 1 oracle oracle 2.1G Jul 2 00:41 sysaux.dbf
-rw-rw----. 1 oracle oracle 2.1G Jul 2 00:41 undo1.dbf
-rw-rw----. 1 oracle oracle 41M Jul 2 00:42  redo2.log
-rw-rw----. 1 oracle oracle 10M Jul 2 00:42  control_8.ctl
-rw-rw----. 1 oracle oracle 10M Jul 2 00:42  control_7.ctl 

[oracle@training ORACLEAGENT]$ mv india02.dbf /u02/india04.dbf 

[oracle@training ORACLEAGENT]$ sqlplus / as sysdba
 sqlplus / as sysdba

SQL*Plus: Release 19.0.0.0.0 - Production on Fri Jul 2 00:44:21 2021
Version 19.3.0.0.0

Copyright (c) 1982, 2019, Oracle. All rights reserved.


Connected to:
Oracle Database 19c Enterprise Edition Release 19.0.0.0.0 - Production 
Version 19.3.0.0.0

SQL> alter tablespace india rename datafile 
'/u01/app/oracle/oradata/ORACLEAGENT/india02.dbf' to '/u02/india04.dbf';

Tablespace altered.

SQL> alter tablespace india online;

Thank you for giving your valuable time to read the above information.

If you want to be updated with all our articles send us the Invitation or Follow us:

Ramkumar’s LinkedIn: https://www.linkedin.com/in/ramkumardba/
LinkedIn Group: https://www.linkedin.com/in/ramkumar-m-0061a0204/
Facebook Page: https://www.facebook.com/Oracleagent-344577549964301
Ramkumar’s Twitter: https://twitter.com/ramkuma02877110
Ramkumar’s Telegram: https://t.me/oracleageant
Ramkumar’s Facebook: https://www.facebook.com/ramkumarram8

Tag Archives: database move datafile