Monday, March 21, 2011

space db scripts

Check whether a tablespace is fragmented and show fragmentation type.txt
=========================================================================
Check whether a tablespace is fragmented and show fragmentation type

TTI "Tablespace Fragmentation Details"

SELECT dfsc.tablespace_name tablespace_name,
DECODE (
dfsc.percent_extents_coalesced,
100,
(DECODE (
GREATEST ((SELECT COUNT (1)
FROM dba_free_space dfs
WHERE dfs.tablespace_name =
dfsc.tablespace_name), 1),
1,
'No Frag',
'Bubble Frag'
)
),
'Possible Honey Comb Frag'
)
fragmentation_status
FROM dba_free_space_coalesced dfsc
ORDER BY dfsc.tablespace_name;

TTI off



List segments that can not extend (tablespace full.txt
=======================================================

prompt Objects that cannot extend (no space in TS)

column Sname form a40 heading 'Object Name'
column Stype form a15 heading 'Type'
column Size form 9,999 heading 'Size'
column Next form 99,999 heading 'Next'
column Tname form a15 heading 'TsName'

select a.owner||'.'||a.segment_name "Sname",
a.segment_type "Stype",
a.bytes/1024/1024 "Size",
a.next_extent/1024/1024 "Next",
a.tablespace_name "TName"
from sys.dba_segments a
where a.tablespace_name not like 'T%MP%' -- Exclude TEMP tablespaces
and next_extent * 1 > ( -- Cannot extend 1x, can
change to 2x...
select max(b.bytes)
from dba_free_space b
where a.tablespace_name =
b.tablespace_name)
order by 3 desc
/

-- THIS QUERY GIVES THE SAME RESULTS, BUT IS WAY TOO SLOW
--
-- select a.owner, a.segment_name, b.tablespace_name,
-- decode(ext.extents,1,b.next_extent,
-- a.bytes*(1+b.pct_increase/100)) nextext,
-- freesp.largest
-- from dba_extents a,
-- dba_segments b,
-- (select owner, segment_name, max(extent_id) extent_id,
-- count(*) extents
-- from dba_extents
-- group by owner, segment_name
-- ) ext,
-- (select tablespace_name, max(bytes) largest
-- from dba_free_space
-- group by tablespace_name
-- ) freesp
-- where a.owner=b.owner
-- and a.segment_name=b.segment_name
-- and a.owner=ext.owner
-- and a.segment_name=ext.segment_name
-- and a.extent_id=ext.extent_id
-- and b.tablespace_name = freesp.tablespace_name
-- and decode(ext.extents,1,b.next_extent,
a.bytes*(1+b.pct_increase/100)) > freesp.largest
-- /


List tables with high water mark not equal to used blocks.txt
============================================================
set verify off
column owner format a10
column alcblks heading 'Allocated|Blocks' just c
column usdblks heading 'Used|Blocks' just c
column hgwtr heading 'High|Water' just c
break on owner skip page

select
a.owner,
a.table_name,
b.blocks alcblks,
a.blocks usdblks,
(b.blocks-a.empty_blocks-1) hgwtr
from
dba_tables a,
dba_segments b
where
a.table_name=b.segment_name
and a.owner=b.owner
and a.owner not in('SYS','SYSTEM')
and a.blocks <> (b.blocks-a.empty_blocks-1)
and a.owner like upper('&owner')||'%'
and a.table_name like upper('&table_name')||'%'
order by 1,2
/

set verify on
clear columns
clear breaks



Show database growth in Meg per month for the last year.txt
===========================================================
set pagesize 50000
tti "Database growth per month for last year"

select to_char(creation_time, 'RRRR Month') "Month",
sum(bytes)/1024/1024 "Growth in Meg"
from sys.v_$datafile
where creation_time > SYSDATE-365
group by to_char(creation_time, 'RRRR Month')
/

tti off


Show used &free space per segment type.txt
===========================================
-----------------------------------------------------------------------

---------------------------------------------------------------------------
-- Create history table...
---------------------------------------------------------------------------
drop table db_space_hist;
create table db_space_hist (
timestamp date,
total_space number(8),
used_space number(8),
free_space number(8),
pct_inuse number(5,2),
num_db_files number(5)
);

---------------------------------------------------------------------------
-- Stored proc to populate table...
---------------------------------------------------------------------------
create or replace procedure db_space_hist_proc as
begin
-- Delete old records...
delete from db_space_hist where timestamp > SYSDATE + 364;
-- Insert current utilization values...
insert into db_space_hist
select sysdate, total_space,
total_space-nvl(free_space,0) used_space,
nvl(free_space,0) free_space,
((total_space - nvl(free_space,0)) / total_space)*100
pct_inuse,
num_db_files
from ( select sum(bytes)/1024/1024 free_space
from sys.DBA_FREE_SPACE ) FREE,
( select sum(bytes)/1024/1024 total_space,
count(*) num_db_files
from sys.DBA_DATA_FILES) FULL;
commit;
end;
/
show errors

---------------------------------------------------------------------------
-- Schedule the job using the DB Job System. This section can be
removed if
-- the job is sceduled via an external scheduler.
---------------------------------------------------------------------------
declare
v_job number;
begin
select job into v_job from user_jobs where what like
'db_space_hist_proc%';
dbms_job.remove(v_job);
dbms_job.submit(v_job, 'db_space_hist_proc;', sysdate,
'sysdate+7'); -- Run every 7 days
dbms_job.run(v_job);
dbms_output.put_line('Job '||v_job||' re-submitted.');
exception
when NO_DATA_FOUND then
dbms_job.submit(v_job, 'db_space_hist_proc;', sysdate,
'sysdate+7'); -- Run every 7 days
dbms_job.run(v_job);
dbms_output.put_line('Job '||v_job||' submitted.');
end;
/

---------------------------------------------------------------------------
-- Generate a space history report...
---------------------------------------------------------------------------
select to_char(timestamp, 'DD Mon RRRR HH24:MI') "Timestamp",
total_space "DBSize (Meg)",
used_space "Free (Meg)",
free_space "Used (Meg)",
pct_inuse "% Used",
num_db_files "Num DB Files"
from db_space_hist
order by timestamp;




Show used &free space per tablespace.txt
===========================================
tti "Space Usage for Database in Meg"

SELECT Total.name "Tablespace Name",
nvl(Free_space, 0) Free_space,
nvl(total_space-Free_space, 0) Used_space,
total_space
FROM
(select tablespace_name, sum(bytes/1024/1024) Free_Space
from sys.dba_free_space
group by tablespace_name
) Free,
(select b.name, sum(bytes/1024/1024) TOTAL_SPACE
from sys.v_$datafile a, sys.v_$tablespace B
where a.ts# = b.ts#
group by b.name
) Total
WHERE Free.Tablespace_name(+) = Total.name
ORDER BY Total.name
/

tti off


Show used&free space per datafile.txt
=====================================
TTI "Allocated, Used & Free space within datafiles"

COLUMN free_space_mb format 999999.90
COLUMN allocated_mb format 999999.90
COLUMN used_mb format 999999.90

SELECT SUBSTR (df.NAME, 1, 40) file_name, df.bytes / 1024 / 1024
allocated_mb,
((df.bytes / 1024 / 1024) - NVL (SUM (dfs.bytes) / 1024 /
1024, 0))
used_mb,
NVL (SUM (dfs.bytes) / 1024 / 1024, 0) free_space_mb
FROM v$datafile df, dba_free_space dfs
WHERE df.file# = dfs.file_id(+)
GROUP BY dfs.file_id, df.NAME, df.file#, df.bytes
ORDER BY file_name;

TTI off



tablespaces with used space more than 90 percent.txt
==================================================
SELECT Total.name "Tablespace Name",
nvl(Free_space, 0) Free_space,
nvl(total_space-Free_space, 0) Used_space,
total_space,nvl(total_space-Free_space, 0)/total_space*100 "PERCENTAGE USED"
FROM
(select tablespace_name, sum(bytes/1024/1024) Free_Space
from sys.dba_free_space
group by tablespace_name) Free,
(select b.name, sum(bytes/1024/1024) TOTAL_SPACE
from sys.v_$datafile a, sys.v_$tablespace B
where a.ts# = b.ts# and b.name <> 'RBS'
group by b.name
) Total
WHERE Free.Tablespace_name(+) = Total.name
and nvl(Free_space, 0)/total_space<.1
order by Total.name;


temp space total.txt
=======================
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;



used&free space per segment type.txt
=====================================
set pagesize 50000
set line 80

col "Total Used Meg" format 999,999,990
col "Data part" format 999,999,990
col "Index part" format 999,999,990
col "LOB part" format 999,999,990
col "RBS part" format 999,999,990

tti 'Used space in Meg by segment type'

select sum(bytes)/1024/1024 "Total Used",
sum( decode( substr(segment_type,1,5), 'TABLE',
bytes/1024/1024, 0))
"Data part",
sum( decode( substr(segment_type,1,5), 'INDEX',
bytes/1024/1024, 0))
"Index part",
sum( decode( substr(segment_type,1,3), 'LOB',
bytes/1024/1024, 0))
"LOB part",
sum( decode(segment_type, 'ROLLBACK',
bytes/1024/1024, 0))
"RBS part",
sum( decode(segment_type, 'TEMPORARY',
bytes/1024/1024, 0))
"TEMP part"
from sys.dba_segments
/
tti off

tti "Total database size"

select sum(bytes)/1024/1024 "Total DB size in Meg"
from sys.v_$datafile
/
tti off

No comments:

Post a Comment