database:oracle:adjust-tablespace-size
Table of Contents
Adjust tablespace size to free up space
Check how much space from datafiles are occupied for a specific tablespace
Replace <TBS_NAME> with the actual tablespace to check:
SELECT df.tablespace_name, df.file_name, df.bytes, SUM(e.bytes) AS used_bytes FROM dba_data_files df LEFT JOIN dba_extents e ON df.file_id = e.file_id WHERE df.tablespace_name='<TBS_NAME>' GROUP BY df.tablespace_name, df.file_name, df.bytes
Output:
DATA D:\ORACLE\ORADATA\DATA1.DBF 32212254720 32211206144 DATA D:\ORACLE\ORADATA\DATA2.DBF 32212254720 4697620480 ...
Check existing tablespace size
SELECT a.file_name,round(a.bytes/1024/1024) totalsize,b.freesize FROM dba_data_files a, (SELECT file_id,round(SUM(bytes/1024/1024)) freesize FROM dba_free_space GROUP BY file_id ) b WHERE a.file_id=b.file_id(+)
FILENAME TOTALSIZE FREESIZE /home/user/app/user/oradata/userorcl/pdborcl/USER_DATA_WORK.DBF 128 103 /home/user/app/user/oradata/userorcl/pdborcl/USER_LOB_WORK.DBF 256 247 ...
Free up space
ALTER DATABASE DATAFILE '/home/user/app/user/oradata/userorcl/pdborcl/USER_DATA_WORK.DBF' RESIZE 50K
database/oracle/adjust-tablespace-size.txt · Last modified: 2023/09/01 16:14 by odefta