Differences

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

--- tablespaces [2019/02/04 13:30] – z0hpvk
+++ tablespaces [2025/03/08 22:24] (current) – external edit 127.0.0.1
@@ Line 68: / Line 68: @@
 Use the following SQL to find the sessions ...
 <code> SELECT USERNAME, SESSION_NUM, SESSION_ADDR FROM V$SORT_USAGE;
-SELECT SID, SERIAL#, STATUS FROM V$SESSION WHERE SERIAL#="SESSION_NUM" AND SADDR="SESSION_ADDR";</code>
+ SELECT SID, SERIAL#, STATUS FROM V$SESSION WHERE SERIAL#="SESSION_NUM" AND SADDR="SESSION_ADDR";</code>
 <code> CREATE TEMPORARY TABLESPACE TEMP TEMPFILE '/u01/oradata/DB1/temp01.dbf' SIZE 100M; </code>
 <code> ALTER DATABASE DEFAULT TEMPORARY TABLESPACE TEMP; </code>
 <code> DROP TABLESPACE TEMP2 INCLUDING CONTENTS AND DATAFILES; </code>
+To discover what the default temporary tablespace is ...
+<code> SELECT PROPERTY_VALUE FROM DATABASE_PROPERTIES WHERE PROPERTY_NAME = 'DEFAULT_TEMP_TABLESPACE'; </code>
 ==== Data File Management ====
@@ Line 77: / Line 79: @@
 ALTER TABLESPACE USERS ADD DATAFILE '/oradata/user02.dbf' SIZE 100M AUTOEXTEND ON NEXT 10M MAXSIZE 1G;
 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>