Datafiles

<- System

Add

ALTER TABLESPACE <TableSpaceName>
ADD DATAFILE 'DatafilePathAndName'
SIZE <nnnnM|nnnnG>;

SQLPLUS> ALTER TABLESPACE users ADD DATAFILE '/u01/oradata/DB1/users02.dbf' SIZE 25M;
A BFTS can only have 1 datafile.

Display

 COL tablespace_name FORMAT a25
 COL file_name       FORMAT a60

-- Database Size

 SELECT  round((a.data_size + b.temp_size + c.redo_size)/power(1000,3)) "TOTAL 
 (gb)"
 FROM ( SELECT sum(bytes) data_size FROM dba_data_files ) a,
      ( SELECT sum(bytes) temp_size FROM dba_temp_files ) b,
      ( SELECT sum(bytes) redo_size FROM sys.v_$log     ) c;

-- File_name, Tablespace_name and Size

 SELECT file_name, tablespace_name, bytes
 FROM dba_data_files
 ORDER BY file_name, tablespace_name, bytes;

-- Datafiles that Cannot Be AutoExtended

 SELECT tablespace_name, file_name, AutoExtensible, MaxBytes, Increment_By
 FROM dba_data_files
 WHERE AutoExtensible != 'YES'
 ORDER BY 1,2;

-- Temp Tablespace Files

 -- Files
 set pages 999
 set lines 400
 col TABLESPACE_NAME format a12 HEAD 'TS'
 col FILE_NAME       format a50
 SELECT d.TABLESPACE_NAME, d.FILE_NAME, d.BYTES/1024/1024 SIZE_MB,
        d.AUTOEXTENSIBLE, d.MAXBYTES/1024/1024 MAXSIZE_MB, 
        d.INCREMENT_BY*(v.BLOCK_SIZE/1024)/1024 INCREMENT_BY_MB
 FROM dba_temp_files d, v$tempfile v
 WHERE d.FILE_ID = v.FILE#
 ORDER by d.TABLESPACE_NAME, d.FILE_NAME;

 -- Size Used
 SELECT round(sum(bytes)/power(1000,3)) "Temp_Tablespace (gb)" 
 FROM dba_temp_files;

-- Redo Log Files

 SELECT round(sum(bytes)/power(1000,2)) "Redo_Logs (mb)" 
 FROM sys.v_$log;

Drop

ALTER TABLESPACE <TablespaceName> DROP DATAFILE 'datafile_path_and_name'; 

 ALTER TABLESPACE users DROP DATAFILE '/u01/oradata/DB1/users02.dbf';

Move

1. ALTER TABLESPACE <TablespaceName> OFFLINE NORMAL;
2. Copy source_datafile(s) to destination_datafile(s) location.
3. ALTER DATABASE RENAME FILE <'Source_Datafile(s)_Location'>
   TO <'Destination_Datafile(s)_Location'>;
4. ALTER TABLESPACE <TablespaceName> ONLINE;

 ALTER TABLESPACE users OFFLINE NORMAL;
 OS> cp /u02/oradata/DB1/users.dbf /u06/oradata/DB1/users.dbf
 ALTER DATABASE RENAME FILE '/u02/oradata/DB1/users.dbf'
       TO '/u06/oradata/DB1/users.dbf';
 ALTER TABLESPACE users ONLINE;

Delete the original\source datafile at OS level after appropriate confirmation that all is working OK for your environment.

Resize

-- Standard

ALTER DATABASE DATAFILE 'datafile_path_and_name' RESIZE <nnnnM|nnnnG>; 

 ALTER DATABASE DATAFILE '/u01/DB1/users.dbf' RESIZE 1024M;

-- Temp Tablespace Files

ALTER DATABASE TEMPFILE 'tempfile_path_and_name' RESIZE <nnnnM|nnnnG>; 

 ALTER DATABASE TEMPFILE '/u01/DB1/temp01.dbf' RESIZE 1g;

Online Move Session Example

Requires Oracle 19c or later.

-- Check HW detected and Ensure Not Already Mounted
   lsblk

-- Create Partition & Filesystem
   parted /dev/sde
     (parted) mklabel gpt
     (parted) unit TB
     (parted) mkpart primary 0.00TB 3TB
     (parted) print
     (parted) quit
   lsblk

-- Format New Partition as ext4
   mkfs.ext4 /dev/sde1
     ...
     Allocating group tables: done
     Writing inode tables: done
     Creating journal (262144 blocks): done
     Writing superblocks and filesystem accounting information: done

   lsblk

-- Create and Mount Mount Point
   cd /
   mkdir /u05
   mount /dev/sde1 /u05
   df -h
   umount -l /u05

   -- Get UUID of Device: 
   blkid /dev/sde1

   -- vi /etc/fstab
   Ex: UUID=422508d3-a612-4909-ad03-8a0994d3f1fa /u05 ext4 defaults 0 0

   -- Mount using /etc/fstab and Disk Using UUID
      mount -a
      df -h

   -- QC Can Create File
      touch /u05/test42.txt
      ls -l /u05/test42.txt
      rm /u05/test42.txt -f
      --umount -l /u05

-- Cfg for Oracle
chown -R oracle:oinstall /u05
ls -l /u05

mkdir -p /u05/oradata
chown -R oracle:oinstall /u05/oradata
chmod -R 775 /u05/oradata
ls -l /u05

-- Cfg to Move TS
> Existing: /u02/oradata/MYDB/datafile

On BOTH
mkdir -p /u05/oradata/MYDB/datafile
chown -R oracle:oinstall /u05/oradata
chmod -R 775 /u05/oradata

-- chk DG (DG will auto-handle the move on SB)
SHOW PARAMETER standby_file_management;  -- should be AUTO

-- Chk datafile Path
SELECT file#, name, bytes FROM  v$datafile WHERE  LOWER(name) LIKE '%indexes%';

-- Move TS
ALTER DATABASE MOVE DATAFILE
  '/u02/oradata/MYDB/datafile/o1_mf_indexes_mwowvndl_.dbf'
  TO '/u05/oradata/MYDB/datafile/indexes01.dbf'
  KEEP;

  -- Monitor Move
  SELECT ROUND(sofar/1024/1024/1024,1) AS sofar_gb,
       ROUND(totalwork/1024/1024/1024,1) AS total_gb,
       ROUND(sofar*100/NULLIF(totalwork,0),1) AS pct
  FROM   v$session_longops
  WHERE  opname='Online data file move' AND target='21';

  SELECT ROUND(sofar/1024/1024/1024,1) AS sofar_gb,ROUND(totalwork/1024/1024/1024,1) AS total_gb,ROUND(sofar*100/NULLIF(totalwork,0),1) AS pct FROM v$session_longops WHERE  opname='Online data file move' AND target='21';

-- Validate
SELECT name FROM v$datafile WHERE file#=21;

-- Cleanup the old file
rm -f /u02/oradata/MYDB/datafile/o1_mf_indexes_mwowvndl_.dbf

<- System