Tablespace queries
==============
col "Tablespace" for a22
col "Used MB" for 99,999,999
col "Free MB" for 99,999,999
col "Total MB" for 99,999,999
select df.tablespace_name "Tablespace",
totalusedspace "Used MB",
(df.totalspace - tu.totalusedspace) "Free MB",
df.totalspace "Total MB",
round(100 * ( (df.totalspace - tu.totalusedspace)/ df.totalspace))
"Pct. Free" from (select tablespace_name,
round(sum(bytes) / 1048576) TotalSpace
from dba_data_files
group by tablespace_name) df,
(select round(sum(bytes)/(1024*1024)) totalusedspace, tablespace_name
from dba_segments
group by tablespace_name) tu
where df.tablespace_name = tu.tablespace_name ;
To get in detail information
=====================
REM ######################################################################
REM ## PROGRAM : tbsfree.sql
REM ##
REM ## DESCRIPTION:
REM ## This script returns the free space for all tablespaces in the
REM ## database.
REM ##
REM ## NOTE:
REM ## This script must be executed by a DBA account.
REM ##
REM ## tablespaces which have 0 free extents.
REM ## Added Runion with dba_temp_files to capture TEMP tablespaces
REM ## defined as temp_files
REM ######################################################################
ttitle on
define max_pct='90'
set echo off feedback off verify off pages 60 lines 80
col tablespace_name format a20 head 'Tablespace|Name'
col total format 9,999,999 head 'Total(MB)'
col used format 9,999,999 head 'Used(MB)'
col free format 99,999,999 head 'Free(MB)'
col pct format 999 head 'Pct|Used'
col next_extent format 999,999 head 'Next|Extent(KB)'
col Contents format a4 head 'Cont'
col Logging format a3 head 'Log'
col Extent_Management format a3 head 'Ext|Mgt'
col Allocation_Type format a5 head 'Alloc|Type'
col pct_warn format a1 head ''
ttitle -
left 'Database: ' &DB_NAME -
right &DATE_DD_MON_YY -
skip -
center 'Tablespace Free Space' -
skip 2
break on report
compute sum of total on report
compute sum of used on report
compute sum of free on report
select tbs.tablespace_name,
tot.bytes/1024/1024 total,
tot.bytes/1024/1024-sum(nvl(fre.bytes,0))/1024/1024 used,
sum(nvl(fre.bytes,0))/1024/1024 free,
(1-sum(nvl(fre.bytes,0))/tot.bytes)*100 pct,
decode(
greatest((1-sum(nvl(fre.bytes,0))/tot.bytes)*100, &max_pct),
&max_pct, '', '*'
) pct_warn,
-- tbs.next_extent/1024 next_extent,
decode(tbs.logging,'LOGGING','','NOLOGGING','NO',tbs.logging) Logging,
decode(tbs.contents,'TEMPORARY','TEMP','PERMANENT','',tbs.contents) Contents,
decode(tbs.extent_management,'DICTIONARY','','LOCAL','LMT',tbs.extent_management) Extent_Management,
decode(tbs.allocation_type,'USER','','SYSTEM','AUTO','UNIFORM','UNIFO',tbs.allocation_type) Allocation_Type
from dba_free_space fre,
(select tablespace_name, sum(bytes) bytes
from dba_data_files
group by tablespace_name) tot,
dba_tablespaces tbs
where tot.tablespace_name = tbs.tablespace_name
and fre.tablespace_name(+) = tbs.tablespace_name
group by tbs.tablespace_name, tot.bytes/1024/1024, tot.bytes, Logging, Contents, Extent_Management, Allocation_Type
--group by tbs.tablespace_name, tot.bytes/1024/1024, tot.bytes, next_extent, Logging, Contents, Extent_Management, Allocation_Type
UNION
select tbs.tablespace_name,
tot.bytes/1024/1024 total,
tot.bytes/1024/1024-sum(nvl(tfre.bytes,0))/1024/1024 used,
sum(nvl(tfre.bytes,0))/1024/1024 free,
(1-sum(nvl(tfre.bytes,0))/tot.bytes)*100 pct,
decode(
greatest((1-sum(nvl(tfre.bytes,0))/tot.bytes)*100, &max_pct),
&max_pct, '', '*'
) pct_warn,
-- tbs.next_extent/1024 next_extent,
decode(tbs.logging,'LOGGING','','NOLOGGING','NO',tbs.logging) Logging,
decode(tbs.contents,'TEMPORARY','TEMP','PERMANENT','',tbs.contents) Contents,
decode(tbs.extent_management,'DICTIONARY','','LOCAL','LMT',tbs.extent_management) Extent_Management,
decode(tbs.allocation_type,'USER','','SYSTEM','AUTO','UNIFORM','UNIFO',tbs.allocation_type) Allocation_Type
from V$TEMP_EXTENT_MAP tfre,
(select tablespace_name, sum(bytes) bytes
from dba_temp_files
group by tablespace_name) tot,
dba_tablespaces tbs
where tot.tablespace_name = tbs.tablespace_name
and tfre.tablespace_name(+) = tbs.tablespace_name
group by tbs.tablespace_name, tot.bytes/1024/1024, tot.bytes, Logging, Contents, Extent_Management, Allocation_Type
--group by tbs.tablespace_name, tot.bytes/1024/1024, tot.bytes, next_extent, Logging, Contents, Extent_Management, Allocation_Type
order by 1
/*order by 5, 1, 2*/
/
exit
Ref:
===
http://shahiddba.blogspot.com/2012/05/script-to-monitor-tablespacesdatafiles.html
Find tables which consuming more space
==============================
select owner,segment_name,segment_type,bytes/(1024*1024) size_m
from dba_segments
where tablespace_name = 'SYSTEM'
and bytes/(1024*1024) >1
order by size_m desc
==============
col "Tablespace" for a22
col "Used MB" for 99,999,999
col "Free MB" for 99,999,999
col "Total MB" for 99,999,999
select df.tablespace_name "Tablespace",
totalusedspace "Used MB",
(df.totalspace - tu.totalusedspace) "Free MB",
df.totalspace "Total MB",
round(100 * ( (df.totalspace - tu.totalusedspace)/ df.totalspace))
"Pct. Free" from (select tablespace_name,
round(sum(bytes) / 1048576) TotalSpace
from dba_data_files
group by tablespace_name) df,
(select round(sum(bytes)/(1024*1024)) totalusedspace, tablespace_name
from dba_segments
group by tablespace_name) tu
where df.tablespace_name = tu.tablespace_name ;
To get in detail information
=====================
REM ######################################################################
REM ## PROGRAM : tbsfree.sql
REM ##
REM ## DESCRIPTION:
REM ## This script returns the free space for all tablespaces in the
REM ## database.
REM ##
REM ## NOTE:
REM ## This script must be executed by a DBA account.
REM ##
REM ## tablespaces which have 0 free extents.
REM ## Added Runion with dba_temp_files to capture TEMP tablespaces
REM ## defined as temp_files
REM ######################################################################
ttitle on
define max_pct='90'
set echo off feedback off verify off pages 60 lines 80
col tablespace_name format a20 head 'Tablespace|Name'
col total format 9,999,999 head 'Total(MB)'
col used format 9,999,999 head 'Used(MB)'
col free format 99,999,999 head 'Free(MB)'
col pct format 999 head 'Pct|Used'
col next_extent format 999,999 head 'Next|Extent(KB)'
col Contents format a4 head 'Cont'
col Logging format a3 head 'Log'
col Extent_Management format a3 head 'Ext|Mgt'
col Allocation_Type format a5 head 'Alloc|Type'
col pct_warn format a1 head ''
ttitle -
left 'Database: ' &DB_NAME -
right &DATE_DD_MON_YY -
skip -
center 'Tablespace Free Space' -
skip 2
break on report
compute sum of total on report
compute sum of used on report
compute sum of free on report
select tbs.tablespace_name,
tot.bytes/1024/1024 total,
tot.bytes/1024/1024-sum(nvl(fre.bytes,0))/1024/1024 used,
sum(nvl(fre.bytes,0))/1024/1024 free,
(1-sum(nvl(fre.bytes,0))/tot.bytes)*100 pct,
decode(
greatest((1-sum(nvl(fre.bytes,0))/tot.bytes)*100, &max_pct),
&max_pct, '', '*'
) pct_warn,
-- tbs.next_extent/1024 next_extent,
decode(tbs.logging,'LOGGING','','NOLOGGING','NO',tbs.logging) Logging,
decode(tbs.contents,'TEMPORARY','TEMP','PERMANENT','',tbs.contents) Contents,
decode(tbs.extent_management,'DICTIONARY','','LOCAL','LMT',tbs.extent_management) Extent_Management,
decode(tbs.allocation_type,'USER','','SYSTEM','AUTO','UNIFORM','UNIFO',tbs.allocation_type) Allocation_Type
from dba_free_space fre,
(select tablespace_name, sum(bytes) bytes
from dba_data_files
group by tablespace_name) tot,
dba_tablespaces tbs
where tot.tablespace_name = tbs.tablespace_name
and fre.tablespace_name(+) = tbs.tablespace_name
group by tbs.tablespace_name, tot.bytes/1024/1024, tot.bytes, Logging, Contents, Extent_Management, Allocation_Type
--group by tbs.tablespace_name, tot.bytes/1024/1024, tot.bytes, next_extent, Logging, Contents, Extent_Management, Allocation_Type
UNION
select tbs.tablespace_name,
tot.bytes/1024/1024 total,
tot.bytes/1024/1024-sum(nvl(tfre.bytes,0))/1024/1024 used,
sum(nvl(tfre.bytes,0))/1024/1024 free,
(1-sum(nvl(tfre.bytes,0))/tot.bytes)*100 pct,
decode(
greatest((1-sum(nvl(tfre.bytes,0))/tot.bytes)*100, &max_pct),
&max_pct, '', '*'
) pct_warn,
-- tbs.next_extent/1024 next_extent,
decode(tbs.logging,'LOGGING','','NOLOGGING','NO',tbs.logging) Logging,
decode(tbs.contents,'TEMPORARY','TEMP','PERMANENT','',tbs.contents) Contents,
decode(tbs.extent_management,'DICTIONARY','','LOCAL','LMT',tbs.extent_management) Extent_Management,
decode(tbs.allocation_type,'USER','','SYSTEM','AUTO','UNIFORM','UNIFO',tbs.allocation_type) Allocation_Type
from V$TEMP_EXTENT_MAP tfre,
(select tablespace_name, sum(bytes) bytes
from dba_temp_files
group by tablespace_name) tot,
dba_tablespaces tbs
where tot.tablespace_name = tbs.tablespace_name
and tfre.tablespace_name(+) = tbs.tablespace_name
group by tbs.tablespace_name, tot.bytes/1024/1024, tot.bytes, Logging, Contents, Extent_Management, Allocation_Type
--group by tbs.tablespace_name, tot.bytes/1024/1024, tot.bytes, next_extent, Logging, Contents, Extent_Management, Allocation_Type
order by 1
/*order by 5, 1, 2*/
/
exit
To Check size of a particular Tablespace.
===============================
SET LINESIZE 200
select tablespace_name,
(BYTES/1024)/1024/1024 "Used Space(GB)",
total "allocated size(GB)",
maxi "maximum allowable (GB)",
maxi-(BYTES/1024)/1024/1024 "effectivefree(GB)",
--maxi-total "free(GB)",
round(((maxi-(BYTES/1024)/1024/1024)/maxi)*100,2) "% FREE",
round(((BYTES/1024/1024/1024)/maxi)*100,2) "% USED"
from
SM$TS_USED,(select sum((BYTES/1024)/1024/1024)
total,sum(greatest(decode(MAXBYTES,0,bytes,maxbytes),bytes)/1024/1024/1024) maxi from
dba_data_files where tablespace_name in ('&&tbs')) where
tablespace_name in ('&tbs');
O/p:
Enter value for tbs:
Tempfile Usage
============
SELECT A.tablespace_name tablespace, D.mb_total,
SUM (A.used_blocks * D.block_size) / 1024 / 1024 mb_used,
D.mb_total - SUM (A.used_blocks * D.block_size) / 1024 / 1024 mb_free
FROM v$sort_segment A,
(
SELECT B.name, C.block_size, SUM (C.bytes) / 1024 / 1024 mb_total
FROM v$tablespace B, v$tempfile C
WHERE B.ts#= C.ts#
GROUP BY B.name, C.block_size
) D
WHERE A.tablespace_name = D.name
GROUP by A.tablespace_name, D.mb_total;
O/p:
TABLESPACE MB_TOTAL MB_USED MB_FREE
------------------------------- ---------- ---------- ----------
TEMP 61226 0 61226
To Check name and number of datafiles added in a tablespace
================================================
select file_name,bytes from dba_data_files where tablespace_name='tbsname';
To add a datafile
============
first check if there is enough space in mount point.
df -h
alter tablespace APPS_TS_SUMMARY add datafile '/u01//ora/data/APPS_TS_SUMMARY003.dbf' size 8g;
To resize a datafile
=============
alter database USERS '/u01/oracle/data/user_02.dbf' resize 4g;
To create a new tablespace
===================
create tablespace REPD datafile '/u011/ora/REPTS01.dbf' size 8g;
To check if autoextent is on
====================
select file_name from dba_data_files where autoextensible='YES';
select file_name from dba_temp_files where autoextensible='YES';
Create a script to disable autoextent
===========================
select
'alter database datafile '''||
file_name||
' '||
' autoextend off;'
from
dba_data_files;
To check the size of database
======================
col "Database Size" format a20
col "Free space" format a20
col "Used space" format a20
select round(sum(used.bytes) / 1024 / 1024 / 1024 ) || ' GB' "Database Size"
, round(sum(used.bytes) / 1024 / 1024 / 1024 ) -
round(free.p / 1024 / 1024 / 1024) || ' GB' "Used space"
, round(free.p / 1024 / 1024 / 1024) || ' GB' "Free space"
from (select bytes
from v$datafile
union all
select bytes
from v$tempfile
union all
select bytes
from v$log) used
, (select sum(bytes) as p
from dba_free_space) free
group by free.p
/
List all Tablespaces with free space < 10% or full space> 90%
===============================================
Select a.tablespace_name,sum(a.tots/1048576) Tot_Size,
sum(a.sumb/1024) Tot_Free, sum(a.sumb)*100/sum(a.tots) Pct_Free,
ceil((((sum(a.tots) * 15) - (sum(a.sumb)*100))/85 )/1048576) Min_Add
from (select tablespace_name,0 tots,sum(bytes) sumb
from dba_free_space a
group by tablespace_name
union
Select tablespace_name,sum(bytes) tots,0 from
dba_data_files
group by tablespace_name) a
group by a.tablespace_name
having sum(a.sumb)*100/sum(a.tots) < 10
order by pct_free;
===============================================
Select a.tablespace_name,sum(a.tots/1048576) Tot_Size,
sum(a.sumb/1024) Tot_Free, sum(a.sumb)*100/sum(a.tots) Pct_Free,
ceil((((sum(a.tots) * 15) - (sum(a.sumb)*100))/85 )/1048576) Min_Add
from (select tablespace_name,0 tots,sum(bytes) sumb
from dba_free_space a
group by tablespace_name
union
Select tablespace_name,sum(bytes) tots,0 from
dba_data_files
group by tablespace_name) a
group by a.tablespace_name
having sum(a.sumb)*100/sum(a.tots) < 10
order by pct_free;
Ref:
===
http://shahiddba.blogspot.com/2012/05/script-to-monitor-tablespacesdatafiles.html
Find tables which consuming more space
==============================
select owner,segment_name,segment_type,bytes/(1024*1024) size_m
from dba_segments
where tablespace_name = 'SYSTEM'
and bytes/(1024*1024) >1
order by size_m desc
No comments :
Post a Comment