How to fix Tablespace Usage Alerts in Oracle 19c

Channel Avatar
Comment
X
Share
How to fix Tablespace Usage Alerts in Oracle 19c
How to fix Tablespace Usage Alerts in Oracle 19c
How to fix Tablespace Usage Alerts in Oracle 19c
## Queries Used ##
— Tablespace Usage
SET LINES 300
SELECT total_ts.tablespace_name, used_ts.used_size_gb, total_ts.total_size_gb, ROUND((used_ts.used_size_gb / total_ts.total_size_gb * 100),2) percent_used
FROM
(SELECT tablespace_name,ROUND(SUM(bytes)/1024/1024/1024,2) Used_size_GB FROM dba_segments GROUP BY tablespace_name) used_ts,
(SELECT tablespace_name,ROUND(SUM(DECODE(autoextensible,’YES’,maxbytes,bytes))/1024/1024/1024,2) Total_size_GB FROM dba_data_files GROUP BY tablespace_name) total_ts
WHERE used_ts.tablespace_name total_ts.tablespace_name
ORDER by (used_ts.used_size_gb / total_ts.total_size_gb * 100) DESC;

— List of datafiles for a single TABLESPACE
SET LINES 300
COL file_name FORMAT A70
SELECT file_name, tablespace_name, ROUND(bytes/1024/1024/1024,2) file_size_gb, status,
autoextensible, ROUND(maxbytes/1024/1024/1024,2) max_size_gb, online_status
FROM dba_data_files
WHERE tablespace_name ‘&Tablespace_name’
ORDER BY file_name;

ALTER DATABASE datafile ‘/u02/oradata/DB19C1/datafile/indx01_1.dbf’ resize 100M;

ALTER DATABASE datafile ‘/u02/oradata/DB19C1/datafile/indx01_1.dbf’ autoextend OFF;

ALTER TABLESPACE INDX01 ADD DATAFILE ‘/u02/oradata/DB19C1/datafile/indx01_3.dbf’ size 10M autoextend ON maxsize 20M;

ALTER DATABASE datafile ‘/u02/oradata/DB19C1/datafile/indx01_2.dbf’ autoextend ON maxsize 200M;

show parameter db_create_file

Take the opportunity to connect and share this video with your friends and family if you find it useful.

Read Also

Leave a Reply

Your email address will not be published. Required fields are marked *