Differences

This shows you the differences between two versions of the page.

--- database:oracle:adjust-tablespace-size [2019/04/23 18:16] – created odefta
+++ database:oracle:adjust-tablespace-size [2023/09/01 16:14] (current) – odefta
@@ Line 1: / Line 1: @@
 ====== Adjust tablespace size to free up space ======
-Check existing size:
+===== Check how much space from datafiles are occupied for a specific tablespace =====
+Replace <TBS_NAME> with the actual tablespace to check:
+<code sql>
+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
+</code>
+Output:
+<code>
+DATA	D:\ORACLE\ORADATA\DATA1.DBF	32212254720	32211206144
+DATA	D:\ORACLE\ORADATA\DATA2.DBF	32212254720	4697620480
+...
+</code>
+===== Check existing tablespace size =====
 <code sql>
  select a.file_name,round(a.bytes/1024/1024) totalsize,b.freesize
@@ Line 15: / Line 35: @@
 /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
+...
 </code>
+===== Free up space =====
+<code sql>
+ALTER DATABASE DATAFILE '/home/user/app/user/oradata/userorcl/pdborcl/USER_DATA_WORK.DBF' RESIZE 50K
+</code>