SPACE MANAGEMENT ::

1. Table space usuage:

==============

SELECT tablespaces.tablespace_name,

         allocated.total_alloc allocated_mb,

         ROUND(allocated.total_alloc - free.total_free,2) total_used_mb,

         ROUND (free.total_free, 2) free_mb,

         ROUND (((allocated.total_alloc - free.total_free) / allocated.total_alloc) * 100, 2)

            AS actual_pct_used,

         total_extend_possible / 1024 / 1024 AS total_autoextend_possible_mb,

         ROUND (((allocated.total_alloc - free.total_free) / (allocated.total_alloc +(total_extend_possible / 1024 / 1024 ))) * 100, 2)

            AS extended_pct_used,

         DB.instance_name,

         DB.host_name,

         to_char(sysdate,'dd-Mon-yyyy HH:mi am') Run_Date

    FROM (  SELECT tablespace_name, SUM (bytes) / 1024 / 1024 total_free

              FROM dba_free_space

          GROUP BY tablespace_name) free,

         (  SELECT tablespace_name,

                   SUM (bytes) / 1024 / 1024 total_alloc,

                   SUM (GREATEST (bytes, maxbytes) - bytes) total_extend_possible

              FROM dba_data_files

          GROUP BY tablespace_name) allocated,

         (SELECT tablespace_name

            FROM dba_tablespaces

           WHERE CONTENTS NOT IN ('TEMPORARY')) tablespaces,

         ( select instance_name, host_name

         from v\$instance)           DB

   WHERE     allocated.tablespace_name(+) = tablespaces.tablespace_name

         AND free.tablespace_name(+) = tablespaces.tablespace_name

  ORDER BY 5 DESC;

select

   a.tsdf tblspace,

   b.used_space "Used Space(MB)",

   nvl(a.file_space,0) "allocated size(MB)",

   a.extn_space  as "maximum allowable (MB)",

   (a.extn_space-b.used_space) as "effective free(MB)",

   round((((a.extn_space-b.used_space)/a.extn_space)*100),2) as "%FREE"

from

   (select tablespace_name tsdf,sum(bytes)/1048576 file_space,sum(decode(maxbytes,0,bytes,maxbytes))/1048576 extn_space from dba_data_files group by tablespace_name) a,

   (select tablespace_name tsfs,(bytes/1048576) used_space from sm$ts_used) b

where

a.tsdf = b.tsfs (+) and

a.tsdf like upper('%%')

and round((((a.extn_space-b.used_space)/a.extn_space)*100),2) < 10

order by a.tsdf;

SELECT ARCHIVED_THREAD#, ARCHIVED_SEQ#, APPLIED_THREAD#, APPLIED_SEQ# FROM V$ARCHIVE_DEST_STATUS;

purge dba_recyclebin;

purge recyclebin;

select round(sum(bytes/1024/1024)) MB, segment_name from dba_segments group by segment_name order by 1 desc;

2. To check the tables size

===========================

select substr(OWNER,1,15) "OWNER",

substr(segment_NAME,1,30) "NAME",

substr(segment_type,1,12)"TYPE",

bytes "BYTES",

extents "EXTENTS"

from sys.dba_segments where

segment_type in ('INDEX','TABLE') and

bytes > 100000000 and owner not in ('SYS') order by bytes desc;

Find Tables with it's Size::

============================

SELECT a.owner,

       b.segment_name,

       b.segment_type,

       a.created,

       a.last_ddl_time,

       b.bytes / 1024 / 1024

  FROM dba_objects a, dba_segments b

 WHERE     b.segment_type = 'TABLE'

       AND a.owner = 'OTSLGWEB'

     AND b.segment_name LIKE 'CMD_%' (---provide table name)

and b.segment_name=a.object_name

3. Find Undo Tablespace size::

==============================

select

    ( select sum(bytes)/1024/1024 from dba_data_files

       where tablespace_name like 'APPS_UND%' )  allocated,

    ( select sum(bytes)/1024/1024 from dba_free_space

       where tablespace_name like 'APPS_UND%')  free,

    ( select sum(bytes)/1024/1024 from dba_undo_extents

       where tablespace_name like 'APPS_UND%') USed

from dual

/

Find who is using a UNDO or ROLL BACK segment::

================================================

SELECT TO_CHAR(s.sid)||','||TO_CHAR(s.serial#) sid_serial,

           NVL(s.username, 'None') orauser,

           s.program,

           r.name undoseg,

           t.used_ublk * TO_NUMBER(x.value)/1024||'K' "Undo"

      FROM sys.v_$rollname    r,

           sys.v_$session     s,

           sys.v_$transaction t,

           sys.v_$parameter   x

    WHERE s.taddr = t.addr

      AND r.usn   = t.xidusn(+)

     AND x.name  = 'db_block_size'

/

4. Find Free space in Temp Tablespace ::

========================================

select * from v$temp_space_header;

5. FRA Usage::

==============

set lines 120

break on report

compute sum of percent_space_used on report

compute sum of percent_space_reclaimable on report

 

select file_type

,      percent_space_used

,      percent_space_reclaimable

,      number_of_files

,      con_id

from   v$recovery_area_usage

order by 1

/

col name format a7

clear breaks

clear computes 

 

select name

,      round(space_limit / 1024 / 1024) size_mb

,      round(space_used  / 1024 / 1024) used_mb

,      decode(nvl(space_used,0),0,0,round((space_used/space_limit) * 100)) pct_used

from v$recovery_file_dest

order by name

/

select

   name,

  floor(space_limit / 1024 / 1024) "Size MB",

  ceil(space_used / 1024 / 1024) "Used MB"

from v$recovery_file_dest;

col name format a7

clear breaks

clear computes

 

select name

,      round(space_limit / 1024 / 1024) space_limit_mb

,      round(space_used  / 1024 / 1024) space_used_mb

,      percent_space_used

,      percent_space_reclaimable

,      percent_space_not_reclaimable

from v$recovery_file_dest

,    ( select sum(percent_space_reclaimable)                      percent_space_reclaimable

       ,      sum(percent_space_used)                             percent_space_used

       ,      sum(percent_space_used - percent_space_reclaimable) percent_space_not_reclaimable

       from  v$recovery_area_usage)

order by name

/

6. Datafile Resize script generator::

=====================================

select 'alter database datafile '''||file_name||''' resize 

' ||ceil( (nvl(hwm,1)*8192)/1024/1024 ) || 'm;' cmd

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(+) 

and ceil( blocks*8192/1024/1024) - ceil( (nvl(hwm,1)*8192)/1024/1024 ) > 0 

-- and tablespace_name= '&Tablespace_Name'

;

7. Top 10 Tables by row count::

================================

select * from (

  select owner, table_name, num_rows from dba_tables where 

  -- owner='FINDUR_DB' and 

  num_rows is not null order by 3 desc) 

  where rownum<=10 ;

  

8. LOBS and Table Segments:

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

set lines 500 pages 500

col column_name format a30

col segment_name format a30 

SELECT * FROM (

SELECT L.TABLE_NAME, L.COLUMN_NAME, S.SEGMENT_NAME, S.BYTES/1024/1024/1024 AS "SIZE_GB", L.TABLESPACE_NAME

FROM DBA_LOBS L, DBA_SEGMENTS S

WHERE S.SEGMENT_NAME=L.SEGMENT_NAME AND L.OWNER='FINDUR_DB'

ORDER BY S.BYTES DESC )

WHERE ROWNUM<=10 ;

9. List tablesace max_size, allocated_size, utilized and pct used 

=============================================================

set linesize 132 tab off trimspool on

set pagesize 105

set pause off

set echo off

set feedb on

column "TOTAL ALLOC (MB)" format 9,999,990.00

column "TOTAL PHYS ALLOC (MB)" format 9,999,990.00

column "USED (MB)" format  9,999,990.00

column "FREE (MB)" format 9,999,990.00

column "% USED" format 990.00

select a.tablespace_name,

       a.bytes_alloc/(1024*1024) "TOTAL ALLOC (MB)",

       a.physical_bytes/(1024*1024) "TOTAL PHYS ALLOC (MB)",

       nvl(b.tot_used,0)/(1024*1024) "USED (MB)",

       (nvl(b.tot_used,0)/a.physical_bytes)*100 "% USED"

from ( select tablespace_name,

       sum(bytes) physical_bytes,

       sum(decode(autoextensible,'NO',bytes,'YES',maxbytes)) bytes_alloc

       from dba_data_files

       group by tablespace_name ) a,

     ( select tablespace_name, sum(bytes) tot_used

       from dba_segments

       group by tablespace_name ) b

where a.tablespace_name = b.tablespace_name (+)

--and   (nvl(b.tot_used,0)/a.bytes_alloc)*100 > 10

and   a.tablespace_name not in (select distinct tablespace_name from dba_temp_files)

and   a.tablespace_name not like 'UNDO%'

order by 1 ;

10. Code to see HWM of DBF and resize them to HWM

==========================================================

--NOTE: Run the below three select statement one by one --

set verify off

set pages 1000

column file_name format a50 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

--To Check Database block size:--

column value new_val blksize

select value from v$parameter where name = 'db_block_size'

/

-- To check how much space can be reclaimed --

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(+)

/

--Script to reclaim unused space from the datafiles of respective tablespace--

set pages 0

set lines 300

column cmd format a300 word_wrapped

select 'alter database datafile '''||file_name||''' resize ' ||

       ceil( (nvl(hwm,1)*&&blksize)/1024/1024 )  || 'm;' cmd

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(+) 

  and ceil( blocks*&&blksize/1024/1024) -

      ceil( (nvl(hwm,1)*&&blksize)/1024/1024 ) > 0

/

-- this will give recommendations for space mgmt

SELECT

 'Segment Advice --------------------------'|| chr(10) ||

 'TABLESPACE_NAME : ' || tablespace_name || chr(10) ||

 'SEGMENT_OWNER : ' || segment_owner || chr(10) ||

 'SEGMENT_NAME : ' || segment_name || chr(10) ||

 'ALLOCATED_SPACE : ' || allocated_space || chr(10) ||

 'RECLAIMABLE_SPACE: ' || reclaimable_space || chr(10) ||

 'RECOMMENDATIONS : ' || recommendations || chr(10) ||

 'SOLUTION 1 : ' || c1 || chr(10) ||

 'SOLUTION 2 : ' || c2 || chr(10) ||

 'SOLUTION 3 : ' || c3 Advice

FROM

TABLE(dbms_space.asa_recommendations('TRUE', 'TRUE', 'FALSE'));