Size – Fahad Pulikkal

Step 1 oracle@astrn10: sqlplus /nolog SQL> conn /as sysdba SQL> shutdown immediate; Troubleshooting: If you are not able to get the SQL prompt, check your ORACLE_HOME, and ORACLE_SID. Step 2 SQL> exit; oracle@astrn10: mv /oracle/oradata/mydb/system01.dbf /oracle/hdb1/oradata/mydb/ Now check whether the file have been moved or not by issuing the following command: oracle@astrn10: ls /oracle/hdb1/oradata/mydb/ total 429924352 -rw-r----- 1 oracle oinstall 429924352 Feb 12 11:00 system01.dbf Now we are ready for the next step. Step 3 oracle@astrn10: sqlplus /nolog SQL> Step 4 SQL> conn /as sysdba Connected to idle instance. Step 5 SQL> startup mount; Database mounted. Step 6 SQL> alter database rename file '/oracle/oradata/mydb/system01.dbf' to '/oracle/hdb1/oradata/mydb/system01.dbf'; Database altered. Step 7 SQL> alter database open; Database opened.

dba_data_files – means total size of the data file.

The data file size is total of dba_free_space and dba_segments:
dba_data_files = dba_free_space + dba_segments

dba_segments – means used size of the data file

dba_free_space – means free size of the data file

Size Check Queries:

To get total datafiles size, free and used space in percentage:

select t2.total "TOTAL SIZE",t1.free "FREE SPACE",
 (t1.free/t2.total)*100 "FREE (%)",
 (1-t1.free/t2.total)*100 "USED (%)"
from (select sum(bytes)/1024/1024 free from dba_free_space) t1 ,
 (select sum(bytes)/1024/1024 total from dba_Data_files) t2;

To get the size of all Data files:

select sum(bytes)/1024/1024 "Data files size in MB" from dba_data_files;

To get the size of all TEMP files:

select nvl(sum(bytes),0)/1024/1024 "Temp files size in MB" from dba_temp_files;

To get the size of the on-line redo-logs:

SELECT SUM(BYTES)/1024/1024 "Redo files size in MB" FROM SYS.V_$LOG;

Total Size and free size:

select round(sum(used.bytes) / 1024 / 1024 ) || ' MB' "Database Size"
, round(free.p / 1024 / 1024) || ' MB' "Free space"
from (select bytes from v$datafile
 union all
 select bytes from v$tempfile
 union all
 select bytes from v$log) used
, (select sum(bytes) as p from dba_free_space) free
group by free.p;

Individual tablespace size:

select tablespace_name as "Tablespace Name",sum(bytes)/(1024*1024) as "Size"
from dba_data_files
 group by tablespace_name
 ORDER BY 1;

Individual tablespace used size:

select tablespace_name as "Tablespace Name",sum(bytes)/(1024*1024) as "Used Size"
from dba_segments
 GROUP BY TABLESPACE_NAME
 order by 1;

Individual tablespace free space:

select tablespace_name as "Tablespace Name", sum(bytes)/(1024*1024) as "Free Space"
 from dba_free_space
 group by tablespace_name
 order by 1;

Reference Links:

Tag: Size

Oracle Database Running Out of Space ?

Database Size