Differences

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

--- database:oracle:adjust-tablespace-size [2019/04/23 15:21] – [Check existing size] odefta
+++ database:oracle:adjust-tablespace-size [2025/01/02 18:22] (current) – external edit 127.0.0.1
@@ 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>