######### Tablespace size #####
https://www.oraask.com/2021/04/check-tablespace-usage-in-oracle.html
select t.tablespace, t.totalspace as " Totalspace(MB)",
round((t.totalspace-nvl(fs.freespace,0)),2) as "Used Space(MB)",
nvl(fs.freespace,0) as "Freespace(MB)",
round(((t.totalspace-nvl(fs.freespace,0))/t.totalspace)*100,2) as "%Used",
round((nvl(fs.freespace,0)/t.totalspace)*100,2) as "% Free"
from
(select round(sum(d.bytes)/(1024*1024)) as totalspace,d.tablespace_name tablespace
from dba_data_files d
group by d.tablespace_name) t,
(select round(sum(f.bytes)/(1024*1024)) as freespace,f.tablespace_name tablespace
from dba_free_space f
group by f.tablespace_name) fs
where t.tablespace=fs.tablespace (+)
order by "% Free";
#### Datafile ###
https://www.thegeekstuff.com/2016/10/oracle-tablespace-datafile/
SELECT file_id, file_name, bytes/1024/1024 size_mb, maxbytes, autoextensible,increment_by,status
FROM dba_data_files
WHERE tablespace_name = 'THEGEEKSTUFF';
#####
select name,state,total_mb,free_mb from v$asm_diskgroup;
alter tablespace tablespace_name add datafile '+DATA' size 100M autoextend on;
alter tablespace tablespace_name add datafile '+DATA' size 127G;
### SQL query returns the name of the tablespace that contains HR schema
select distinct tablespace_name from dba_segments where owner='HR';
##### To check Database total space.
select sum(bytes/1024/1024/1024) "Database Size(GB)" from dba_data_files;
###### Check Used space in DATABASE.
select sum(bytes/1024/1024/1024) "Database Used Space(GB)" from dba_segments;
##### To check Free space in DATABASE.
select sum(bytes/1024/1024/1024) "Database Free space(GB)" from dba_free_space;
#### How many datafile contains a Tablespace ###
select tablespace_name, file_name, status from dba_data_files order by 1;
select tablespace_name, count(*) data_file_count from dba_data_files group by tablespace_name;
##### Temp tablespace ####
Check the temp usage by sessions
SELECT b.tablespace,
ROUND(((b.blocks*p.value)/1024/1024),2)||'M' AS temp_size,
a.inst_id as Instance,
a.sid||','||a.serial# AS sid_serial,
NVL(a.username, '(oracle)') AS username,
a.program, a.status, a.sql_id
FROM gv$session a, gv$sort_usage b, gv$parameter p
WHERE p.name = 'db_block_size' AND a.saddr = b.session_addr
AND a.inst_id=b.inst_id AND a.inst_id=p.inst_id
ORDER BY temp_size desc;
Find the temp tablespace usage
select a.tablespace_name tablespace,
d.TEMP_TOTAL_MB,
sum (a.used_blocks * d.block_size) / 1024 / 1024 TEMP_USED_MB,
d.TEMP_TOTAL_MB - sum (a.used_blocks * d.block_size) / 1024 / 1024 TEMP_FREE_MB
from v$sort_segment a,
(
select b.name, c.block_size, sum (c.bytes) / 1024 / 1024 TEMP_TOTAL_MB
from v$tablespace b, v$tempfile c
where b.ts#= c.ts#
group by b.name, c.block_size
) d
where a.tablespace_name = d.name
group by a.tablespace_name, d.TEMP_TOTAL_MB;
Check detail of size and free space in TEMP
SELECT * FROM dba_temp_free_space;
Ref : https://smarttechways.com/2021/03/09/find-the-temp-usage-by-sessions-in-oracle/
##### Undo Tablespace #####
Check the undo tablespace total, free and used space(Size in MB) in Ora
SELECT a.tablespace_name,
SIZEMB,
USAGEMB,
(SIZEMB - USAGEMB) FREEMB
FROM ( SELECT SUM (bytes) / 1024 / 1024 SIZEMB, b.tablespace_name
FROM dba_data_files a, dba_tablespaces b
WHERE a.tablespace_name = b.tablespace_name AND b.contents like 'UNDO'
GROUP BY b.tablespace_name) a,
( SELECT c.tablespace_name, SUM (bytes) / 1024 / 1024 USAGEMB
FROM DBA_UNDO_EXTENTS c
WHERE status <> 'EXPIRED'
GROUP BY c.tablespace_name) b
WHERE a.tablespace_name = b.tablespace_name;
Check undo usage by User or schema
select u.tablespace_name tablespace, s.username, u.status, sum(u.bytes)/1024/1024 sum_in_mb, count(u.segment_name) seg_cnts
from dba_undo_extents u, v$transaction t , v$session s
where u.segment_name = '_SYSSMU' || t.xidusn || '$' and t.addr = s.taddr
group by u.tablespace_name, s.username, u.status order by 1,2,3;
Ref : https://smarttechways.com/2021/02/09/check-the-undo-tablespace-usage-in-oracle/
No comments:
Post a Comment