Differences

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

--- tablespaces [2016/12/05 11:30] – z0hpvk
+++ tablespaces [2025/03/08 22:24] (current) – external edit 127.0.0.1
@@ Line 1: / Line 1: @@
 ===== Tablespaces =====
 ==== Current Tablespace Usage ====
-<code>set linesize 140
+<code SQL>SET PAGESIZE 140 LINESIZE 200
-WITH Tablespace_Data as
-(
+COLUMN used_pct FORMAT A11
-SELECT tablespace_name,
+COLUMN size_mb FORMAT 999,999
-       ROUND(a.bytes/1024/1024) AS size_mb,
+COLUMN free_mb FORMAT 999,999
-       ROUND(a.maxbytes/1024/1024) AS maxsize_mb,
+COLUMN max_size_mb FORMAT 999,999
-       ROUND(b.free_bytes/1024/1024) AS free_mb,
+COLUMN max_free_mb FORMAT 999,999
-       ROUND((a.maxbytes-a.bytes)/1024/1024) AS growth_mb,
-- ROUND(((b.free_bytes+a.growth)/a.maxbytes) * 100) AS pct_used
+SELECT tablespace_name,
-FROM (SELECT tablespace_name, file_id, bytes,
+       size_mb,
-             GREATEST(bytes,maxbytes) AS maxbytes,
+       free_mb,
-             GREATEST(bytes,maxbytes)-bytes AS growth
+       max_size_mb,
-        FROM dba_data_files) a,
+       max_free_mb,
-     (Select file_id, SUM(bytes) AS free_bytes
+       TRUNC((max_free_mb/max_size_mb) * 100) AS free_pct
-        FROM dba_free_space GROUP BY file_id) b
+FROM   (
-WHERE a.file_id = b.file_id(+)
+        SELECT a.tablespace_name,
-)
+               b.size_mb,
-SELECT tablespace_name,
+               a.free_mb,
-      sum(size_mb) SIZE_MB,
+               b.max_size_mb,
-      sum(maxsize_mb) MAXSIZE_MB,
+               a.free_mb + (b.max_size_mb - b.size_mb) AS max_free_mb
-      sum(free_mb) FREE_MB,
+        FROM   (SELECT tablespace_name,
-      sum(growth_mb) GROWTH_MB,
+                       TRUNC(SUM(bytes)/1024/1024) AS free_mb
-      round((sum(size_mb)-sum(free_mb)) / sum(maxsize_mb)*100,2) PERCENTAGE_USED
+                FROM   dba_free_space
-  from Tablespace_Data
+                GROUP BY tablespace_name) a,
-group by tablespace_name;</code>
+               (SELECT tablespace_name,
+                       TRUNC(SUM(bytes)/1024/1024) AS size_mb,
+                       TRUNC(SUM(GREATEST(bytes,maxbytes))/1024/1024) AS max_size_mb
+                FROM   dba_data_files
+                GROUP BY tablespace_name) b
+        WHERE  a.tablespace_name(+) = b.tablespace_name
+       )
+ORDER BY tablespace_name;
+</code>
 ==== Changing Undo Tablespace ====
 === Create New Undo Tablespace ===
@@ Line 59: / 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 ====
+<code>
+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>