How to Add Datafile in Oracle database with ASM

🔹 Topic: How to Add Datafile in Oracle Database Using ASM

In this video, I explain step-by-step how to add a datafile in Oracle Database when ASM (Automatic Storage Management) is used.

✅ Commands used:-


space.sql
set pages 100
COL "% USED" FORMAT A10
SELECT S.TABLESPACE_NAME,TOTAL "TOTAL_SPACE",FREE "FREE_SPACE",TO_CHAR(((TOTAL-FREE)/TOTAL)*100,99.999) AS "% USED"
FROM (SELECT TABLESPACE_NAME,SUM(BYTES)/1024/1024 AS TOTAL FROM DBA_DATA_FILES GROUP BY TABLESPACE_NAME)S,
(SELECT TABLESPACE_NAME,SUM(BYTES)/1024/1024 AS FREE FROM DBA_FREE_SPACE GROUP BY TABLESPACE_NAME) F
WHERE S.TABLESPACE_NAME=F.TABLESPACE_NAME(+) ORDER BY "% USED" DESC ;

COL "% USED" FORMAT A10
SELECT TABLESPACE_NAME,TOTAL AS "TOTAL_SPACE",FREE AS "FREE_SPACE",TO_CHAR(((TOTAL-FREE)/TOTAL)*100,99.999) AS "% USED"
FROM (SELECT TABLESPACE_NAME,SUM((BYTES_USED+BYTES_FREE)/1024/1024) AS TOTAL,SUM(BYTES_USED/1024/1024)AS USED
,SUM(BYTES_FREE/1024/1024) AS FREE FROM V$TEMP_SPACE_HEADER GROUP BY TABLESPACE_NAME) ORDER BY "% USED" DES


=======

set lines 199
set col FILE_NAME for a70
select FILE_ID,FILE_NAME,TABLESPACE_NAME,BYTES/1024/1024 "SIZE-MB" from dba_data_files where TABLESPACE_NAME='USERS';

========

alter tablespace USERS add datafile '+DATA_REPO' size 100M;


This video is useful for:
✔ Oracle DBA Freshers
✔ Experienced DBAs
✔ Interview preparation
✔ Real-time production support learning

📌 Commands explained practically with clear logic.

📧 For queries & training:
rashoratech@gmail.com

🎯 Channel: Rash OraTech
🔔 Subscribe for Oracle DBA, ASM, RAC, Data Guard & Performance Tuning

#OracleDBA #Oracle19c #ASM #AddDatafile #CDB #PDB #OracleDatabase