Thinking Out Loud

March 24, 2016

Tablespace Free w Fragmentation Info

Filed under: oracle — mdinh @ 7:43 pm

I have been conversing with Liron Amitzi on twitter about his tablespace free SQL script.

He accommodate my request to add fragmentation details so I can verify against what I have.

Looks like I did it right.

You can find Liron script at this blog – Tablespaces Free Space and Stuff

oracle@arrow:tiger:/media/sf_working/sql
$ sysdba @ts_free_space.sql

SQL*Plus: Release 11.2.0.4.0 Production on Thu Mar 24 12:35:23 2016

Copyright (c) 1982, 2013, Oracle.  All rights reserved.


Connected to:
Oracle Database 11g Enterprise Edition Release 11.2.0.4.0 - 64bit Production
With the Partitioning, OLAP, Data Mining and Real Application Testing options


TABLESPACE_NAME                   CURR_SIZE     MAX_SIZE    FREE_SIZE    USED_SIZE     PCT_FREE PCT_FREE_TOTAL  FREE_CHUNKS LARGEST_CHUNK
------------------------------ ------------ ------------ ------------ ------------ ------------ -------------- ------------ -------------
USERS                                  1024         1024      1021.38         2.62        99.74          99.74            2       1021.13
UNDOTBS1                                 80        32767        64.75        15.25        80.94          99.95           12            37
SYSAUX                                  400        32767       218.94       181.06        54.73          99.45            1        218.94
SYSTEM                                  500        32767       231.19       268.81        46.24          99.18            3           231

ARROW:(SYS@tiger):PRIMARY> @free.sql
Enter value for 1: *

                                                              Database Freespace Summary

TABLESPACE_NAME                      BLKSZ   DFCT      CT_FRAG MB_FREE_FRAG     MB_FREE     MB_TOTAL PCT_USED MAX_MB_FREE MAX_PCT_USED
----------------------------------- ------ ------ ------------ ------------ ----------- ------------ -------- ----------- ------------
*m s SYSTEM                           8192      1            3          231         231          499    53.71      32,767          .82
*m s UNDOTBS1                         8192      1           12           37          65           79    53.16      32,767          .13
*a s SYSAUX                           8192      1            1          219         219          399    45.13      32,767          .55
*m s USERS                            8192      1            2        1,021       1,021         1023      .18       1,023          .18
                                           ------                           -----------                       -----------
sum                                             4                                 1,536                            99,324

ARROW:(SYS@tiger):PRIMARY>

free.sql

set line 150 echo off verify off
ttitle -
   center  'Database Freespace Summary'  skip 2
break   on report
COMPUTE sum of mb_used on report
COMPUTE sum of mb_free on report
COMPUTE sum of max_mb_free on report
COMPUTE sum of dfct on report
COLUMN mb_used       format 99,999,999
COLUMN mb_free       format 99,999,999
COLUMN max_mb_free   format 99,999,999
COLUMN mb_free_frag  format 99,999,999
COLUMN dfct          format 99999
COLUMN blksz         format 99999
COLUMN pct_used      format 999.99
COLUMN max_pct_used  format 999.99
SELECT
  DECODE(extent_management,'LOCAL','*',' ') ||
  DECODE(segment_space_management,'AUTO','a ','m ') ||
  DECODE(allocation_type,'SYSTEM','s ','u ') ||
  fs.tablespace_name tablespace_name, block_size blksz, dfct,
  fs.nfrag                      ct_frag,
  fs.mxfrag           / 1048576 mb_free_frag,
  fs.free_bytes       / 1048576 mb_free,
  df.avail            / 1048576 mb_total,
  (df.avail-fs.mxfrag)/df.avail*100 pct_used,
  df.max_bytes        / 1048576 max_mb_free,
  (df.avail-fs.mxfrag)/df.max_bytes*100 max_pct_used
FROM dba_tablespaces ts,
  (SELECT tablespace_name, count(*) dfct,
   SUM(decode(maxbytes,0,user_bytes,greatest(maxbytes,user_bytes))) max_bytes,
   SUM(user_bytes) avail
   FROM dba_data_files
   GROUP BY tablespace_name
  ) df,
  (SELECT tablespace_name,  nvl(sum(bytes),0) free_bytes, count(bytes) nfrag, nvl(max(bytes),0) mxfrag
   FROM dba_free_space
   GROUP BY tablespace_name
  ) fs
WHERE fs.tablespace_name = ts.tablespace_name(+)
AND fs.tablespace_name   = df.tablespace_name
AND regexp_like(fs.tablespace_name,'&1','i')
ORDER BY pct_used desc
Advertisements

Leave a Comment »

No comments yet.

RSS feed for comments on this post. TrackBack URI

Leave a Reply

Fill in your details below or click an icon to log in:

WordPress.com Logo

You are commenting using your WordPress.com account. Log Out / Change )

Twitter picture

You are commenting using your Twitter account. Log Out / Change )

Facebook photo

You are commenting using your Facebook account. Log Out / Change )

Google+ photo

You are commenting using your Google+ account. Log Out / Change )

Connecting to %s

Create a free website or blog at WordPress.com.

%d bloggers like this: