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'));
