User Tools

Site Tools


database:oracle:adjust-tablespace-size

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