Essential Queries

%!!!!!Check the Database Size%

col "Database Size" format a20
col "Free space" format a20
col "Used space" format a20
select     round(sum(used.bytes) / 1024 / 1024 / 1024 ) || ' GB' "Database Size"
,     round(sum(used.bytes) / 1024 / 1024 / 1024 ) -
     round(free.p / 1024 / 1024 / 1024) || ' GB' "Used space"
,     round(free.p / 1024 / 1024 / 1024) || ' GB' "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
/


/*!!!!!Check the space used by the flashrecovery area*/

SELECT * FROM V$FLASH_RECOVERY_AREA_USAGE

/*!!!!!Check the Maximum Space limit and used Space in Flash recovery area%

select space_limit/1024/1024/1024 GB_SPACE_LIMIT,space_used/1024/1024/1024 GB_SPACE_USED from v$recovery_file_dest;


/*!!!!!CHECK THE ALLOCATED, USED, SPACE OF THE DATAFILES IN THE DATABASE WITH FILE_NAME INFORMATION;*/

SELECT SUBSTR (df.NAME, 1, 65) file_name, df.bytes / 1024 / 1024 allocated_mb,
((df.bytes / 1024 / 1024) - NVL (SUM (dfs.bytes) / 1024 / 1024, 0))
used_mb,
NVL (SUM (dfs.bytes) / 1024 / 1024, 0) free_space_mb
FROM v$datafile df, dba_free_space dfs
WHERE df.file# = dfs.file_id(+)
GROUP BY dfs.file_id, df.NAME, df.file#, df.bytes
ORDER BY file_name;

%!!!!CHECK TEMP TABLESPACE  USAGE IN MB*/

select * from (select a.tablespace_name,
sum(a.bytes/1024/1024) allocated_mb
from dba_temp_files a
where a.tablespace_name = upper('&&temp_tsname') group by a.tablespace_name) x,
(select sum(b.bytes_used/1024/1024) used_mb,
sum(b.bytes_free/1024/1024) free_mb
from v$temp_space_header b
where b.tablespace_name=upper('&&temp_tsname') group by b.tablespace_name);

%!!!!CHECK TEMP TABLESPACE  USAGE IN GB*/

select * from (select a.tablespace_name,
sum(a.bytes/1024/1024/1024) allocated_gb
from dba_temp_files a
where a.tablespace_name = upper('&&temp_tsname') group by a.tablespace_name) x,
(select sum(b.bytes_used/1024/1024/1024) used_gb,
sum(b.bytes_free/1024/1024/1024) free_gb
from v$temp_space_header b
where b.tablespace_name=upper('&&temp_tsname') group by b.tablespace_name);

%!!!!!The last datafile added to the tablespace*/

 select max(FILE_NAME ) from dba_data_files where TABLESPACE_NAME='QDB_PROD_TB_INDEX';

 select FILE_NAME, TABLESPACE_NAME from dba_data_files where TABLESPACE_NAME='S_DATA';

 select FILE_NAME, MAXBYTES/1024/1024/1024, TABLESPACE_NAME from dba_data_files where TABLESPACE_NAME='PSAPSR3';

%!!!!CHECK THE USED, ALLOCATED AND FREE SPACE OF ALL THE TABLESPACE IN THE DATABASE%%%

set lines 1000
select t1.n1 "Tablespace Name",
t2.total "Total size",
(t2.total-t1.free) "Used Size" ,
t1.free "Free space",
trunc((t1.free/t2.total)*100) "%free " ,
trunc((1-t1.free/t2.total)*100) "% used"
from
(select tablespace_name n1,trunc(sum(bytes)/1024/1024) free
from dba_free_space where tablespace_name not in ('CWMLITE','DRSYS','ODM','XDB','EXAMPLE','TOOLS')
group by tablespace_name ) t1 ,
(select trunc(sum(bytes)/1024/1024) total,tablespace_name n2
from dba_Data_files where tablespace_name not in ('CWMLITE','DRSYS','ODM','XDB','EXAMPLE','TOOLS')
group by tablespace_name) t2
where t1.n1=t2.n2;

%!!!!!Query to check tablespace size and freespace*/

col "Tablespace" for a22
col "Used MB" for 99,999,999
col "Free MB" for 99,999,999
col "Total MB" for 99,999,999

select df.tablespace_name "Tablespace",
totalusedspace "Used MB",
(df.totalspace - tu.totalusedspace) "Free MB",
df.totalspace "Total MB",
round(100 * ( (df.totalspace - tu.totalusedspace)/ df.totalspace))
"Pct. Free"
from
(select tablespace_name,
round(sum(bytes) / 1048576) TotalSpace
from dba_data_files
group by tablespace_name) df,
(select round(sum(bytes)/(1024*1024)) totalusedspace, tablespace_name
from dba_segments
group by tablespace_name) tu
where df.tablespace_name = tu.tablespace_name ;

%!!!!!Query to check the Tablespace and temp tablespaces size and free size%

select b.tablespace_name, tbs_size SizeMb, a.free_space FreeMb
from
(select tablespace_name, round(sum(bytes)/1024/1024 ,2) as free_space
from dba_free_space group by tablespace_name) a,
(select tablespace_name, sum(bytes)/1024/1024 as tbs_size
from dba_data_files group by tablespace_name
UNION
select tablespace_name, sum(bytes)/1024/1024 tbs_size
from dba_temp_files
group by tablespace_name ) b where a.tablespace_name(+)=b.tablespace_name;

%!!!!! Query to determine the max size already allocated to a tablespace%

select FILE_NAME,MAXBYTES/1024/1024/1024 from dba_data_files;

set lines 1000
col file_name format a50
select TABLESPACE_NAME,FILE_NAME,AUTOEXTENSIBLE,BYTES/1024/1024/1024, MAXBYTES/1024/1024/1024 from dba_data_files
union
select TABLESPACE_NAME,FILE_NAME,AUTOEXTENSIBLE,BYTES/1024/1024/1024, MAXBYTES/1024/1024/1024 from dba_temp_files
union
select TABLESPACE_NAME,BYTES/1024/1024/1024 from dba_free_space;


%!!!!QUERY that - SHOWS the SPACE (SIZE, FREE, USAGE) OF ALL THE TABLESPACES%

SELECT /* + RULE */  df.tablespace_name "Tablespace",
       df.bytes / (1024 * 1024) "Size (MB)",
       SUM(fs.bytes) / (1024 * 1024) "Free (MB)",
       Nvl(Round(SUM(fs.bytes) * 100 / df.bytes),1) "% Free",
       Round((df.bytes - SUM(fs.bytes)) * 100 / df.bytes) "% Used"
  FROM dba_free_space fs,
       (SELECT tablespace_name,SUM(bytes) bytes
          FROM dba_data_files
         GROUP BY tablespace_name) df
 WHERE fs.tablespace_name (+)  = df.tablespace_name
 GROUP BY df.tablespace_name,df.bytes
UNION ALL
SELECT /* + RULE */ df.tablespace_name tspace,
       fs.bytes / (1024 * 1024),
       SUM(df.bytes_free) / (1024 * 1024),
       Nvl(Round((SUM(fs.bytes) - df.bytes_used) * 100 / fs.bytes), 1),
       Round((SUM(fs.bytes) - df.bytes_free) * 100 / fs.bytes)
  FROM dba_temp_files fs,
       (SELECT tablespace_name,bytes_free,bytes_used
          FROM v$temp_space_header
         GROUP BY tablespace_name,bytes_free,bytes_used) df
 WHERE fs.tablespace_name (+)  = df.tablespace_name
 GROUP BY df.tablespace_name,fs.bytes,df.bytes_free,df.bytes_used
 ORDER BY 4 DESC;

%!!!!!TO VIEW THE MAXBYTES ALLOCATED TO A TABLESPACE DATAFILE..TRIED BY ME%

select FILE_NAME,MAXBYTES/1024/1024/1024 from dba_data_files where FILE_NAME LIKE '%/oracle/BIP/sapdata3/%';

SELECT property_value FROM database_properties WHERE property_name = 'DEFAULT_PERMANENT_TABLESPACE';