{"id":3801,"date":"2025-06-08T02:15:00","date_gmt":"2025-06-08T02:15:00","guid":{"rendered":"https:\/\/w3buddy.com\/?post_type=cposts&#038;p=3801"},"modified":"2025-06-08T16:08:33","modified_gmt":"2025-06-08T16:08:33","slug":"oracle-tablespace-management","status":"publish","type":"cposts","link":"https:\/\/w3buddy.com\/blog\/notes\/oracle-dba-d2d-tasks\/oracle-tablespace-management\/","title":{"rendered":"Oracle Tablespace Management"},"content":{"rendered":"\n<p class=\"wp-block-paragraph\">In Oracle, a <strong>tablespace<\/strong> is a logical storage unit \u2014 it groups physical datafiles where your actual data lives. Knowing the different types helps you manage space, performance, and organization more effectively.<\/p>\n\n\n\n<h3 class=\"wp-block-heading\">\ud83d\udccc Oracle Tablespace Types \u2013 Quick Reference<\/h3>\n\n\n\n<ul class=\"wp-block-list\">\n<li><strong>SYSTEM<\/strong>: Core database metadata \u2014 data dictionary, system catalogs.<\/li>\n\n\n\n<li><strong>SYSAUX<\/strong>: Supports SYSTEM \u2014 stores metadata for OEM, AWR, etc.<\/li>\n\n\n\n<li><strong>UNDO<\/strong>: Tracks changes for rollback and recovery.<\/li>\n\n\n\n<li><strong>TEMP<\/strong>: Holds temporary data (e.g., sorts, hash joins).<\/li>\n\n\n\n<li><strong>DEFAULT<\/strong>: Where user objects go by default unless specified.<\/li>\n\n\n\n<li><strong>BIGFILE<\/strong>: Single large datafile \u2014 easier to manage in big DBs.<\/li>\n\n\n\n<li><strong>SMALLFILE<\/strong>: Default type \u2014 uses multiple smaller datafiles.<\/li>\n\n\n\n<li><strong>USER<\/strong>: Custom tablespace for app\/user data \u2014 keeps things organized.<\/li>\n<\/ul>\n\n\n\n<p class=\"wp-block-paragraph\">\ud83d\udca1 Use these scripts to <strong>monitor &amp; manage tablespaces<\/strong> efficiently \u2014 essential for daily DBA tasks.<\/p>\n\n\n\n<h2 class=\"wp-block-heading\">Check All Tablespaces<\/h2>\n\n\n\n<pre class=\"wp-block-code\"><code>set line 100\nset pagesize 100\ncol TABLESPACE format a30;\ncol TOTAL_MB format 999,999,999\ncol USED_MB format 999,999,999\ncol FREE_MB format 999,999,999\ncol %_USED format 999.99\nbreak on report \ncompute sum of TOTAL_MB on report\ncompute sum of USED_MB on report\ncompute sum of FREE_MB on report\nselect          a.tablespace_name \"TABLESPACE\",\n\t\t(b.bytes\/1048576) \"TOTAL_MB\",\n\t\tnvl((c.bytes\/1048576),0) \"USED_MB\",\n\t\tnvl((d.bytes\/1048576),0) \"FREE_MB\",\n\t\tnvl((c.bytes\/b.bytes)*100,0) \"%_USED\"\nfrom            (select tablespace_name from dba_tablespaces) a,\n\t\t(select tablespace_name,bytes from sys.sm$ts_avail) b,\n\t\t(select tablespace_name,bytes from sys.sm$ts_USED) c,\n\t\t(select tablespace_name,bytes from sys.sm$ts_FREE) d\nwhere           a.tablespace_name= b.tablespace_name(+)\nand             a.tablespace_name= c.tablespace_name(+)\nand             a.tablespace_name= d.tablespace_name(+)\norder by 5 desc;<\/code><\/pre>\n\n\n\n<h2 class=\"wp-block-heading\">Check a Specific Tablespace<\/h2>\n\n\n\n<pre class=\"wp-block-code\"><code>set lines 100\nset pages 1000\ncol tspace format a30\ncolu tot_ts_size format 99999.999\ncolu free_ts_size format 99999.999\ncolu used_ts_size format 99999.999\nselect df.tablespace_name tspace,\ndf.bytes\/(1024*1024*1024) tot_ts_size_GB,\n(df.bytes\/(1024*1024*1024) -sum(fs.bytes)\/(1024*1024*1024)) Used_ts_size_GB,\nsum(fs.bytes)\/(1024*1024*1024) free_ts_size_GB,\nround(sum(fs.bytes)*100\/df.bytes) free_pct,\nround((df.bytes-sum(fs.bytes))*100\/df.bytes) used_pct1\nfrom dba_free_space fs, (select tablespace_name, sum(bytes) bytes from dba_data_files  group by tablespace_name ) df\nwhere fs.tablespace_name = df.tablespace_name and fs.tablespace_name='&amp;name'\ngroup by df.tablespace_name, df.bytes\norder by 6 desc\n\/<\/code><\/pre>\n\n\n\n<h2 class=\"wp-block-heading\">Check Tablespaces with Autoextension Enabled<\/h2>\n\n\n\n<pre class=\"wp-block-code\"><code>set linesize 100\nset pagesize 100\nselect\na.tablespace_name,\nround(SUM(a.bytes)\/(1024*1024*1024)) CURRENT_GB,\nround(SUM(decode(b.maxextend, null, A.BYTES\/(1024*1024*1024),b.maxextend*(select block_size from dba_tablespaces where tablespace_name = a.tablespace_name)\/(1024*1024*1024)))) MAX_GB,\nround(SUM(a.bytes)\/(1024*1024*1024) - round(c.Free\/1024\/1024\/1024),2) USED_GB,\nround((SUM(decode(b.maxextend, null, A.BYTES\/(1024*1024*1024),b.maxextend*(select block_size from dba_tablespaces where tablespace_name = a.tablespace_name)\/(1024*1024*1024))) - (SUM(a.bytes)\/(1024*1024*1024) -round(c.Free\/1024\/1024\/1024))),2) FREE_GB,\nround(100*(SUM(a.bytes)\/(1024*1024*1024) -round(c.Free\/1024\/1024\/1024))\/(SUM(decode(b.maxextend, null, A.BYTES\/(1024*1024*1024),b.maxextend*(select block_size from dba_tablespaces where tablespace_name = a.tablespace_name)\/(1024*1024*1024)))),2) USED_PCT\nfrom\ndba_data_files a,\nsys.filext$ b,\n(SELECT\nd.tablespace_name ,sum(nvl(c.bytes,0)) Free\nFROM\ndba_tablespaces d,\nDBA_FREE_SPACE c\nWHERE\nd.tablespace_name = c.tablespace_name(+)\ngroup by d.tablespace_name) c\nWHERE\na.file_id = b.file#(+)\nand a.tablespace_name = c.tablespace_name and a.tablespace_name='&amp;name'\nGROUP BY a.tablespace_name, c.Free\/1024\nORDER BY tablespace_name;<\/code><\/pre>\n\n\n\n<h2 class=\"wp-block-heading\">Top 20 Segments for a Given Tablespace<\/h2>\n\n\n\n<pre class=\"wp-block-code\"><code>set lines 200 pages 45\ncol OWNER for a30\ncol PARTITION_NAME for a20\ncol SEGMENT_NAME for a30\ncol SEGMENT_TYPE for a30\nselect * from (\nselect OWNER,SEGMENT_NAME,PARTITION_NAME,SEGMENT_TYPE,BYTES\/1024\/1024\/1024 size_in_gb ,TABLESPACE_NAME,RELATIVE_FNO \nfrom dba_segments\nwhere TABLESPACE_NAME like '%&amp;TS_name%'\nOrder by size_in_gb desc \n) where rownum &lt; 21;<\/code><\/pre>\n\n\n\n<h2 class=\"wp-block-heading\">Check High Water Mark<\/h2>\n\n\n\n<pre class=\"wp-block-code\"><code>-- Set display parameters\nSET lines 200 pages 100\nCOL file_name FOR a80\n\n-- Query to calculate smallest possible size (HWM), current size, and possible space savings per datafile\nSELECT \n    file_name,\n    CEIL((NVL(hwm, 1) * (SELECT block_size FROM v$datafile WHERE file# = a.file_id)) \/ 1024 \/ 1024) AS smallest_MB,\n    CEIL(blocks * (SELECT block_size FROM v$datafile WHERE file# = a.file_id) \/ 1024 \/ 1024) AS currsize_MB,\n    CEIL(blocks * (SELECT block_size FROM v$datafile WHERE file# = a.file_id) \/ 1024 \/ 1024) \n      - CEIL((NVL(hwm, 1) * (SELECT block_size FROM v$datafile WHERE file# = a.file_id)) \/ 1024 \/ 1024) AS savings_MB\nFROM \n    dba_data_files a,\n    (\n        SELECT \n            file_id,\n            MAX(block_id + blocks - 1) AS hwm\n        FROM \n            dba_extents\n        GROUP BY \n            file_id\n    ) b\nWHERE \n    a.file_id = b.file_id(+)\n    AND a.file_name LIKE '%\/path\/to\/your\/tablespace\/data%'\nORDER BY \n    4 DESC;<\/code><\/pre>\n\n\n\n<p class=\"wp-block-paragraph\"><em><strong>Note:<\/strong>&nbsp;You need to change the mount point path in the above query as needed.<\/em><\/p>\n\n\n\n<h2 class=\"wp-block-heading\">Check UNDO Tablespace Usage<\/h2>\n\n\n\n<pre class=\"wp-block-code\"><code>select df.tablespace_name \"Tablespace\", \ntotalusedspace \"Used MB\", \n(df.totalspace - tu.totalusedspace) \"Free MB\", \ndf.totalspace \"Total MB\", \nround(100 * ( (df.totalspace - tu.totalusedspace)\/ df.totalspace)) \"Pct. Free\" \nfrom \n(select tablespace_name, \nSUM (DECODE (Autoextensible,'YES', maxbytes\/ 1048576,'NO', BYTES\/ 1048576)) TotalSpace \nfrom dba_data_files \ngroup by tablespace_name) df, \n(select round(sum(bytes)\/(1024*1024)) totalusedspace, tablespace_name \nfrom dba_segments \ngroup by tablespace_name) tu \nwhere df.tablespace_name = tu.tablespace_name and df.tablespace_name='&amp;Tab' order by 5; <\/code><\/pre>\n\n\n\n<h2 class=\"wp-block-heading\">Check Datafile Details for a Tablespace<\/h2>\n\n\n\n<pre class=\"wp-block-code\"><code>Set lines 400\ncol file_name for a65\nselect FILE_NAME, TABLESPACE_NAME,BYTES\/1024\/1024, MAXBYTES\/1024\/1024 ,AUTOEXTENSIBLE from dba_data_files where TABLESPACE_NAME='&amp;TABLESPACE_NAME';<\/code><\/pre>\n\n\n\n<h2 class=\"wp-block-heading\">Verify Datafile Creation Time<\/h2>\n\n\n\n<pre class=\"wp-block-code\"><code>SET LINESIZE 200\nSET PAGESIZE 50\nSET COLSEP '    '\nCOLUMN creation_time FORMAT A20 HEADING 'Creation Time'\nCOLUMN name FORMAT A50 HEADING 'Datafile Name'\n\nSELECT TO_CHAR(CREATION_TIME, 'DD-MM-YYYY HH24:MI:SS') AS creation_time, \n       name \nFROM v$datafile \nWHERE name = 'C:\\ORACLE19C\\ORADATA\\ORCL\\SYSTEM01.DBF';<\/code><\/pre>\n\n\n\n<h2 class=\"wp-block-heading\">Add Datafiles<\/h2>\n\n\n\n<pre class=\"wp-block-code\"><code>-- Add Datafile to W3BUDDY_TEST Tablespace (Windows)\nALTER TABLESPACE W3BUDDY_TEST ADD DATAFILE 'C:\\ORCL\\ORADATA\\W3BUDDY\\W3BUDDY_TEST01.DBF' SIZE 1G;\n\n-- Add Datafile with Autoextend to W3BUDDY_TEST Tablespace (Windows)\nALTER TABLESPACE W3BUDDY_TEST ADD DATAFILE SIZE 1024M AUTOEXTEND ON MAXSIZE 32765M;\n\n-- Add Datafile with Autoextend to W3BUDDY_TEST Tablespace (Windows)\nALTER TABLESPACE W3BUDDY_TEST ADD DATAFILE 'D:\\ORCL\\ORADATA\\W3BUDDY\\DBF\\W3BUDDY_TEST_45.DBF' SIZE 100M AUTOEXTEND ON NEXT 64M MAXSIZE 31G;\n\n-- Add Datafile to W3BUDDY_TEST Tablespace (Linux)\nALTER TABLESPACE W3BUDDY_TEST ADD DATAFILE '\/var\/opt\/oracle\/oradata\/W3BUDDY\/W3BUDDY_TEST_2.DBF' SIZE 31G;\n\n-- Add Datafile with Autoextend to W3BUDDY_TEST Tablespace (Linux)\nALTER TABLESPACE W3BUDDY_TEST ADD DATAFILE '\/w3buddy\/oracle\/oradata\/W3BUDDY\/W3BUDDY_TEST_03.DBF' SIZE 1G AUTOEXTEND ON NEXT 64M MAXSIZE 20G;\n\n-- Add Tempfile to W3BUDDY_TEST Tablespace (Linux)\nALTER TABLESPACE W3BUDDY_TEST ADD TEMPFILE '\/w3buddy\/oracle\/oradata\/W3BUDDY\/W3BUDDY_TEST_TEMP_5.DBF' SIZE 20G;<\/code><\/pre>\n\n\n\n<h2 class=\"wp-block-heading\">Resize Datafiles<\/h2>\n\n\n\n<pre class=\"wp-block-code\"><code>-- Resize the datafile for W3BUDDY_TEST_DATAFILE_1.dbf in W3BUDDY_TEST tablespace\nALTER DATABASE DATAFILE '\/w3buddy\/oracle\/oradata\/W3BUDDY\/W3BUDDY_TEST_DATAFILE_1.dbf' RESIZE 31G;\n\n-- Resize the datafile for W3BUDDY_TEST_DATAFILE_2.dbf in W3BUDDY_TEST tablespace\nALTER TABLESPACE W3BUDDY_TEST ADD DATAFILE '\/w3buddy\/oracle\/oradata\/W3BUDDY\/W3BUDDY_TEST_DATAFILE_2.dbf' SIZE 30G;\n\n-- Resize the datafile for W3BUDDY_TEST_UNDOTBS.dbf in W3BUDDY_TEST tablespace\nALTER DATABASE DATAFILE '\/w3buddy\/oracle\/oradata\/W3BUDDY\/W3BUDDY_TEST_UNDOTBS.dbf' AUTOEXTEND ON MAXSIZE 25G;\n\n-- Resize the datafile for W3BUDDY_TEST_DATAFILE_3.dbf in W3BUDDY_TEST tablespace\nALTER DATABASE DATAFILE '\/w3buddy\/oracle\/oradata\/W3BUDDY\/W3BUDDY_TEST_DATAFILE_3.dbf' AUTOEXTEND ON MAXSIZE 15G;<\/code><\/pre>\n\n\n\n<h2 class=\"wp-block-heading\">Enable\/Disable Autoextend for a Datafile<\/h2>\n\n\n\n<pre class=\"wp-block-code\"><code>-- Alter database datafile to enable autoextend\nALTER DATABASE DATAFILE '\/u01\/data\/datafile_1.dbf' AUTOEXTEND ON;\n\n-- Alter database datafile to disable autoextend\nALTER DATABASE DATAFILE '\/w3buddy\/oracle\/oradata\/W3BUDDY\/datafile_2.dbf' AUTOEXTEND OFF;<\/code><\/pre>\n\n\n\n<h2 class=\"wp-block-heading\">Take a Datafile Offline\/Online<\/h2>\n\n\n\n<pre class=\"wp-block-code\"><code>Alter database datafile '\/u01\/data\/users02.dbf' offline; \nAlter database datafile '\/u01\/data\/users02.dbf' online; <\/code><\/pre>\n\n\n\n<h2 class=\"wp-block-heading\">Drop a Datafile<\/h2>\n\n\n\n<pre class=\"wp-block-code\"><code>Alter tablespace USERS drop datafile '\/u01\/data\/users02.dbf'; <\/code><\/pre>\n\n\n\n<h2 class=\"wp-block-heading\">Rename or Move a Datafile<\/h2>\n\n\n\n<pre class=\"wp-block-code\"><code>-- Alter database to move datafile to a new location\nALTER DATABASE MOVE DATAFILE '\/w3buddy\/oracle\/oradata\/W3BUDDY\/datafile_1.dbf' TO '\/w3buddy\/oracle\/oradata\/W3BUDDY\/datafile_2.dbf';<\/code><\/pre>\n\n\n\n<h2 class=\"wp-block-heading\">Create a New Tablespace<\/h2>\n\n\n\n<pre class=\"wp-block-code\"><code>-- Create tablespace W3BUDDY_TEST with a datafile (5GB size with autoextend)\nCREATE TABLESPACE W3BUDDY_TEST DATAFILE '\/w3buddy\/oracle\/oradata\/W3BUDDY\/datafile_1.dbf' SIZE 5G AUTOEXTEND ON NEXT 500M;\n\n-- Create tablespace W3BUDDY_TEST2 with a datafile (1GB size with autoextend, max size 5GB)\nCREATE TABLESPACE W3BUDDY_TEST2 DATAFILE '\/w3buddy\/oracle\/oradata\/W3BUDDY\/datafile_2.dbf' SIZE 1G AUTOEXTEND ON NEXT 64M MAXSIZE 5G;<\/code><\/pre>\n\n\n\n<h2 class=\"wp-block-heading\">Create a Bigfile Tablespace<\/h2>\n\n\n\n<pre class=\"wp-block-code\"><code>-- Create BIGFILE tablespace W3BUDDY_BIGTS with a datafile (100GB size with autoextend)\nCREATE BIGFILE TABLESPACE W3BUDDY_BIGTS DATAFILE '\/w3buddy\/oracle\/oradata\/W3BUDDY\/datafile_bigts.dbf' SIZE 100G AUTOEXTEND ON NEXT 1G;<\/code><\/pre>\n\n\n\n<h2 class=\"wp-block-heading\">Create a Tablespace on ASM Diskgroup<\/h2>\n\n\n\n<pre class=\"wp-block-code\"><code>Create tablespace DATA datafile '+DATAG' size 5G autoextend on next 500M;<\/code><\/pre>\n\n\n\n<h2 class=\"wp-block-heading\">Rename a Tablespace in Oracle Database<\/h2>\n\n\n\n<pre class=\"wp-block-code\"><code>-- Query to check the file details for FILE_ID 37\nSELECT file_id, file_name, tablespace_name \nFROM dba_data_files \nWHERE file_id = 37;\n\n-- Output:\n-- FILE_ID    FILE_NAME                                          TABLESPACE_NAME\n-- -------    -------------------------------------------------  ------------------------------\n-- 37         \/w3buddy\/oracle\/oradata\/W3BUDDY\/datafile_1.dbf      W3BUDDY_TEST\n\n-- Rename the tablespace_name from W3BUDDY_TEST to W3BUDDY_PRODUCING\nALTER TABLESPACE W3BUDDY_TEST RENAME TO W3BUDDY_PRODUCING;\n\n-- Query to verify the updated details for FILE_ID 37\nSELECT file_id, file_name, tablespace_name \nFROM dba_data_files \nWHERE file_id = 37;\n\n-- Output:\n-- FILE_ID    FILE_NAME                                           TABLESPACE_NAME\n-- ---------- --------------------------------------------------- ------------------------------\n-- 37         \/w3buddy\/oracle\/oradata\/W3BUDDY\/datafile_1.dbf       W3BUDDY_PRODUCING<\/code><\/pre>\n\n\n\n<h2 class=\"wp-block-heading\">Drop Tablespace<\/h2>\n\n\n\n<pre class=\"wp-block-code\"><code>-- Drop the tablespace W3BUDDY_TEST\nDROP TABLESPACE W3BUDDY_TEST;\n\n-- Query to check file names associated with the tablespace W3BUDDY_TEST\nSELECT file_name \nFROM dba_data_files \nWHERE tablespace_name = 'W3BUDDY_TEST';\n\n-- Drop the tablespace W3BUDDY_TEST including the physical datafiles\nDROP TABLESPACE W3BUDDY_TEST INCLUDING CONTENTS AND DATAFILES;\n\n-- Take the tablespace W3BUDDY_TEST_DATA offline\nALTER TABLESPACE W3BUDDY_TEST_DATA OFFLINE;\n\n-- Drop the tablespace W3BUDDY_TEST_DATA including contents and datafiles\nDROP TABLESPACE W3BUDDY_TEST_DATA INCLUDING CONTENTS AND DATAFILES;<\/code><\/pre>\n\n\n\n<h2 class=\"wp-block-heading\">Tablespace Growth<\/h2>\n\n\n\n<pre class=\"wp-block-code\"><code>set lines 400 pages 200\nalter session set nls_date_format='yyyy-mm-dd';\nwith t as (\nselect ss.run_time,ts.name,round(su.tablespace_size*dt.block_size\/1024\/1024\/1024,2) alloc_size_gb,\nround(su.tablespace_usedsize*dt.block_size\/1024\/1024\/1024,2) used_size_gb\nfrom\ndba_hist_tbspc_space_usage su,\n(select trunc(BEGIN_INTERVAL_TIME) run_time,max(snap_id) snap_id from dba_hist_snapshot \ngroup by trunc(BEGIN_INTERVAL_TIME) ) ss,\nv$tablespace ts,\ndba_tablespaces dt\nwhere su.snap_id = ss.snap_id\nand   su.tablespace_id = ts.ts#\nand   ts.name          = dt.tablespace_name \nand   ts.name          =upper('USERS'))\nselect e.run_time,e.name,e.alloc_size_gb,e.used_size_gb curr_used_size_gb,b.used_size_gb prev_used_size_gb,\ncase when e.used_size_gb &gt; b.used_size_gb then to_char(e.used_size_gb - b.used_size_gb)\n     when e.used_size_gb = b.used_size_gb then '***NO DATA GROWTH'\n     when e.used_size_gb &lt; b.used_size_gb then '******DATA PURGED' end variance\nfrom t e, t b\nwhere e.run_time = b.run_time + 1\norder by 1;<\/code><\/pre>\n\n\n\n<h2 class=\"wp-block-heading\">Datafile Shrink Analysis and Resizing Script<\/h2>\n\n\n\n<pre class=\"wp-block-code\"><code>set lines 1000 \nSELECT ceil( blocks*(a.BlockSize)\/1024\/1024) \"Current Size\", \n   ceil( (nvl(hwm,1)*(a.BlockSize))\/1024\/1024 ) \"Smallest Poss.\", \n   ceil( blocks*(a.BlockSize)\/1024\/1024) - \n   ceil( (nvl(hwm,1)*(a.BlockSize))\/1024\/1024 ) \"Savings\", \n   'alter database datafile '''|| file_name || ''' resize ' || \n      ceil((nvl(hwm,1)*(a.BlockSize))\/1024\/1024\/100)*100 || 'm;' \"Command\" \nFROM (SELECT a.*, p.value BlockSize FROM dba_data_files a \nJOIN v$parameter p ON p.Name='db_block_size') a \nLEFT JOIN (SELECT file_id, max(block_id+blocks-1) hwm FROM dba_extents GROUP BY file_id ) b \nON a.file_id = b.file_id \nWHERE ceil( blocks*(a.BlockSize)\/1024\/1024) - ceil( (nvl(hwm,1)*(a.BlockSize))\/1024\/1024 ) \n   &gt; 100 \/* Minimum MB it must shrink by to be considered. *\/ \nORDER BY \"Savings\" Desc;<\/code><\/pre>\n\n\n\n<p class=\"wp-block-paragraph\">This script helps <strong>identify and reclaim unused space<\/strong> from Oracle datafiles by calculating the current size, smallest possible size (based on high watermark), and the reclaimable savings. It also generates a safe <code>ALTER DATABASE DATAFILE ... RESIZE<\/code> command.<\/p>\n\n\n\n<p class=\"wp-block-paragraph\"><strong>What it shows:<\/strong><\/p>\n\n\n\n<ul class=\"wp-block-list\">\n<li><strong>Current Size<\/strong> \u2013 Actual size of the datafile in MB.<\/li>\n\n\n\n<li><strong>Smallest Poss.<\/strong> \u2013 Minimum safe size based on data usage (HWM).<\/li>\n\n\n\n<li><strong>Savings<\/strong> \u2013 Space you can reclaim.<\/li>\n\n\n\n<li><strong>Command<\/strong> \u2013 SQL to shrink the file (rounded to nearest 100MB).<\/li>\n<\/ul>\n\n\n\n<p class=\"wp-block-paragraph\"><strong>How it works:<\/strong><\/p>\n\n\n\n<ul class=\"wp-block-list\">\n<li>Pulls block size from <code>V$PARAMETER<\/code>.<\/li>\n\n\n\n<li>Uses <code>DBA_DATA_FILES<\/code> for file details.<\/li>\n\n\n\n<li>Joins with <code>DBA_EXTENTS<\/code> to get the <strong>HWM<\/strong>.<\/li>\n\n\n\n<li>Filters files with &gt;100MB unused space.<\/li>\n\n\n\n<li>Outputs sorted list by potential savings.<\/li>\n<\/ul>\n\n\n\n<p class=\"wp-block-paragraph\"><strong>Safety tips:<\/strong><\/p>\n\n\n\n<ul class=\"wp-block-list\">\n<li>Run on <strong>non-prod<\/strong> first.<\/li>\n\n\n\n<li>Files won&#8217;t shrink below the HWM.<\/li>\n\n\n\n<li>Make sure shrinking is supported by your storage\/filesystem.<\/li>\n<\/ul>\n","protected":false},"excerpt":{"rendered":"<p>In Oracle, a tablespace is a logical storage unit \u2014 it groups physical datafiles where your actual data lives. Knowing the different types helps you manage space, performance, and organization more effectively. \ud83d\udccc Oracle Tablespace Types \u2013 Quick Reference \ud83d\udca1 Use these scripts to monitor &amp; manage tablespaces efficiently \u2014 essential for daily DBA tasks. [&hellip;]<\/p>\n","protected":false},"template":"","meta":{"googlesitekit_rrm_CAowu461DA:productID":""},"categories":[952,984],"class_list":["post-3801","cposts","type-cposts","status-publish","hentry","category-notes","category-oracle-dba-d2d-tasks"],"_links":{"self":[{"href":"https:\/\/w3buddy.com\/blog\/wp-json\/wp\/v2\/cposts\/3801","targetHints":{"allow":["GET"]}}],"collection":[{"href":"https:\/\/w3buddy.com\/blog\/wp-json\/wp\/v2\/cposts"}],"about":[{"href":"https:\/\/w3buddy.com\/blog\/wp-json\/wp\/v2\/types\/cposts"}],"wp:attachment":[{"href":"https:\/\/w3buddy.com\/blog\/wp-json\/wp\/v2\/media?parent=3801"}],"wp:term":[{"taxonomy":"category","embeddable":true,"href":"https:\/\/w3buddy.com\/blog\/wp-json\/wp\/v2\/categories?post=3801"}],"curies":[{"name":"wp","href":"https:\/\/api.w.org\/{rel}","templated":true}]}}