Differences

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

--- tablespaces [2019/10/08 09:51] – z0hpvk
+++ tablespaces [2025/03/08 22:24] (current) – external edit 127.0.0.1
@@ Line 80: / Line 80: @@
 ALTER DATABASE DATAFILE '/oradata/user02.dbf' RESIZE 500M;
 </code>
+==== Default Tablespaces ====
+<code SQL>
+SELECT * FROM database_properties WHERE property_name like '%TABLESPACE';
+</code>
+==== Resize Data Files ====
+<code SQL>
+-- Report on which data files can be shrunk
+set verify off pagesize 50 linesize 120
+column file_name format a60 word_wrapped
+column smallest format 999,990 heading "Smallest|Size|Poss."
+column currsize format 999,990 heading "Current|Size"
+column savings  format 999,990 heading "Poss.|Savings"
+break on report
+compute sum of savings on report
+column value new_val blksize
+select file_name,
+       ceil( (nvl(hwm,1)*&&blksize)/1024/1024 ) smallest,
+       ceil( blocks*&&blksize/1024/1024) currsize,
+       ceil( blocks*&&blksize/1024/1024) -
+       ceil( (nvl(hwm,1)*&&blksize)/1024/1024 ) savings
+from dba_data_files a,
+     ( select file_id, max(block_id+blocks-1) hwm
+         from dba_extents
+        group by file_id ) b
+where a.file_id = b.file_id(+)
+order by savings desc;
+</code>