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