Differences

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

--- tablespaces [2015/07/17 14:01] – z0hpvk
+++ tablespaces [2025/03/08 22:24] (current) – external edit 127.0.0.1
@@ Line 1: / Line 1: @@
-==== Tablespaces ====
+===== Tablespaces =====
-=== Current Tablespace Usage ===
+==== 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, ROUND(a.bytes/1024/1024) AS size_mb,
+COLUMN size_mb FORMAT 999,999
-                    ROUND(a.maxbytes/1024/1024) AS maxsize_mb,
+COLUMN free_mb FORMAT 999,999
-                    ROUND(b.free_bytes/1024/1024) AS free_mb,
+COLUMN max_size_mb FORMAT 999,999
-                    ROUND((a.maxbytes-a.bytes)/1024/1024) AS growth_mb,
+COLUMN max_free_mb FORMAT 999,999
-- ROUND(((b.free_bytes+a.growth)/a.maxbytes) * 100) AS pct_used
-FROM (SELECT tablespace_name, file_id, bytes,
+SELECT tablespace_name,
-             GREATEST(bytes,maxbytes) AS maxbytes,
+       size_mb,
-             GREATEST(bytes,maxbytes)-bytes AS growth
+       free_mb,
-        FROM dba_data_files) a,
+       max_size_mb,
-     (Select file_id, SUM(bytes) AS free_bytes FROM dba_free_space GROUP BY file_id) b
+       max_free_mb,
-WHERE a.file_id = b.file_id(+)
+       TRUNC((max_free_mb/max_size_mb) * 100) AS free_pct
-)
+FROM   (
-SELECT tablespace_name,
+        SELECT a.tablespace_name,
-      sum(size_mb) SIZE_MB,
+               b.size_mb,
-      sum(maxsize_mb) MAXSIZE_MB,
+               a.free_mb,
-      sum(free_mb) FREE_MB,
+               b.max_size_mb,
-      sum(growth_mb) GROWTH_MB,
+               a.free_mb + (b.max_size_mb - b.size_mb) AS max_free_mb
-      round((sum(size_mb)-sum(free_mb)) / sum(maxsize_mb)*100,2) PERCENTAGE_USED
+        FROM   (SELECT tablespace_name,
-  from Tablespace_Data
+                       TRUNC(SUM(bytes)/1024/1024) AS free_mb
-group by tablespace_name;</code>
+                FROM   dba_free_space
-=== Changing Undo Tablespace ===
+                GROUP BY tablespace_name) a,
-== Create New Undo Tablespace ==
+               (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 ===
 <code>create undo tablespace UNDOTBS2 datafile '/u03/undotbs02.dbf' size 500M autoextend on next 50m maxsize 8G;</code>
-== Switch databases to new undo tablespace ==
+=== Switch databases to new undo tablespace ===
 <code>alter system set undo_tablespace = 'UNDOTBS2';</code>
-== Drop old undo tablespace ==
+=== Drop old undo tablespace ===
 <code>DROP TABLESPACE undotbs1 INCLUDING CONTENTS AND DATAFILES;</code>
-== Potential Issues ==
+=== Potential Issues ===
 When doing the "drop undo tablespace" command you will almost certainly hit the following Oracle error ... \\
 <code>ORA-30013: undo tablespace 'UNDOTBS1' is currently in use</code>
@@ Line 49: / Line 60: @@
 <code>/usr/sbin/lsof +L1</code>
+==== Temporary Tablespace ====
+<code> CREATE TEMPORARY TABLESPACE TEMP2 TEMPFILE '/u01/oradata/DB1/temp99.dbf' SIZE 100M; </code>
+<code> ALTER DATABASE DEFAULT TEMPORARY TABLESPACE TEMP2; </code>
+<code> DROP TABLESPACE TEMP INCLUDING CONTENTS AND DATAFILES; </code>
+If this command hangs then there are probably sessions still using the old temp tablespace. \\
+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>
+<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>