Detect Tablespace Fragmentation

The following is a script that will determine how many extents of contiguous free space you have in Oracle as well as the total amount of free space you have in each tablespace. From these results you can detect how fragmented your tablespace is.

The ideal situation is to have one large free extent in your tablespace. The more extents of free space there are in the tablespace, the more likely you will run into fragmentation problems. The size of the free extents is also very important. If you have a lot of small extents (too small for any next extent size) but the total bytes of free space is large, then you may want to consider defragmentation options.

-------------------------------------

create table SPACE_TEMP (   
 TABLESPACE_NAME        CHAR(30),   
 CONTIGUOUS_BYTES       NUMBER)   
/   
   
declare   
  cursor query is select *   
          from dba_free_space   
                  order by tablespace_name, block_id;   
  this_row        query%rowtype;   
  previous_row    query%rowtype;   
total           number;   
   
begin   
  open query;   
  fetch query into this_row;   
  previous_row := this_row;   
  total := previous_row.bytes;   
  loop   
 fetch query into this_row;   
     exit when query%notfound;   
     if this_row.block_id = previous_row.block_id + previous_row.blocks then   
        total := total + this_row.bytes;   
        insert into SPACE_TEMP (tablespace_name)   
                  values (previous_row.tablespace_name);   
     else   
        insert into SPACE_TEMP values (previous_row.tablespace_name,   
               total);   
        total := this_row.bytes;   
     end if;   
previous_row := this_row;   
  end loop;   
  insert into SPACE_TEMP values (previous_row.tablespace_name,   
                           total);   
end;   
.   
/   
   
set pagesize 60   
set newpage 0   
set echo off   
ttitle center 'Contiguous Extents Report'  skip 3   
break on "TABLESPACE NAME" skip page duplicate   
spool contig_free_space.lis   
rem   
column "CONTIGUOUS BYTES"       format 999,999,999   
column "COUNT"                  format 999   
column "TOTAL BYTES"            format 999,999,999   
column "TODAY"   noprint new_value new_today format a1   
rem   
select TABLESPACE_NAME  "TABLESPACE NAME",   
       CONTIGUOUS_BYTES "CONTIGUOUS BYTES"   
from SPACE_TEMP   
where CONTIGUOUS_BYTES is not null   
order by TABLESPACE_NAME, CONTIGUOUS_BYTES desc;   
   
select tablespace_name, count(*) "# OF EXTENTS",   
         sum(contiguous_bytes) "TOTAL BYTES"    
from space_temp   
group by tablespace_name;   
   
spool off   
   
drop table SPACE_TEMP   
/   

-------------------------------------

Sample output

TABLESPACE_NAME                # OF EXTENTS  TOTAL BYTES
------------------------------ ------------ ------------
APEX_1527304513423350                     1    1,114,112
RMAN_CATALOG                              1  305,332,224
APEX_1242130650845718                     3      851,968
USERS                                    10  187,957,248
SYSAUX                                   66   88,408,064
UNDOTBS1                                  6  622,919,680
SYSTEM                                    1    5,963,776

To Check Schema Size

"
SQL> SELECT owner AS "Schema Name", 
     SUM(bytes)/1024/1024 || ' MB' AS "Total in MByte"
  2  FROM dba_segments
  3  GROUP BY owner;

Schema Name                    Total in MByte
------------------------------ ------------------
WKSYS                          7.4375 MB
MDSYS                          46.375 MB
RMAN                           8.75 MB

Schema Name                    Total in MByte
------------------------------ ------------------
ORDSYS                         10.9375 MB
SYSMAN                         115.5 MB
TESTUSER                       .0625 MB
XDB                            208.3125 MB
SYS                            965.875 MB

Schema Name                    Total in MByte
------------------------------ ------------------
WMSYS                          7.125 MB

9 rows selected.

How To Integrate SQLPLUS Command Within Shell Scripts

If you want to keep one scripts that consists of SQLPlus connection following is the method to do it.



sqlplus -s '/as sysdba' << endofcommand

SELECT SYSDATE FROM DUAL;

SELECT banner FROM v\$version;

SELECT COUNT(*) FROM v\$session WHERE username like 'SYS%';

EXIT
endofcommand

Find Out Tablespace Free Space

A database is divided into logical storage units called tablespaces, which group related data blocks, extents, and segments. For example, tablespaces commonly group together all application objects to simplify some administrative operations.

Following is the script to find out the percentage used of all tablespaces.

SELECT a.tablespace_name,
a.bytes bytes_used,
b.bytes bytes_free,
b.largest,
ROUND(((a.bytes-b.bytes)/a.bytes)*100,2) percent_used
FROM (SELECT tablespace_name,
SUM(bytes) bytes
FROM DBA_DATA_FILES
GROUP BY tablespace_name)a,
(SELECT tablespace_name,
SUM(bytes) bytes,
MAX(bytes) largest
FROM dba_free_space
GROUP BY tablespace_name) b
WHERE a.tablespace_name=b.tablespace_name
ORDER BY ((a.bytes-b.bytes)/a.bytes) DESC
/