An example of the select statement
:
select substr(a.tablespace_name,0,15)Tablespace,
round(sum(a.blocks)/256)Total_space_MB,
round(sum(b.blocks)/256)Used_space_MB,
round(sum(c.blocks)/256)Free_space_MB
from dba_extents a, dba_extents
b, dba_free_space c
where a.tablespace_name=b.tablespace_name
and a.tablespace_name=c.tablespace_name
group by a.tablespace_name
The problem with this join
query is:
1. It takes a very very
long time to run the query/
2. The results returned
did not seems to tally when compared to running
these queries individually.
Eg, Total_space_MB seems to be bigger...etc
Denny
This may not be exactly what
you are looking for, but it will give you some
ideas and you may be able
to modify the script to get what you want.
Another source for useful scripts
is www.oramag.com. Check their archives
which goes back 2 years.
Good luck !!!
Oracleguru
www.oracleguru.net
[email protected]
set doc off pause off
/************************************************************************
*
*
* AUTHOR
: Suresh Bhat
*
* COMPANY
: Oracleguru
* FILE NAME
: analyze_tablespace_usage.sql
*
* LANGUAGE
: SQL/SQL*Plus/PL-SQL
*
* INPUT
PARAM : This script expects 1 parameter: Tablespace_name
*
* OUTPUT
PARAM: None.
*
* INPUT
FILES : None.
*
* OUTPUT
FILES: $HOME/rep/analyze_tablespace_names_TNAME.lst
*
*
where TNAME = Tablespace_name entered on
*
*
command line to run this script *
*
*
* DESCRIPTION
: This script generates tablespace usage for a
*
*
TABLESPACE name entered on the command line.
*
*
*
************************************************************************/
set pause off termout off
verify off wrap on serveroutput on size 1000000
set newpage 0 pagesize 58
linesize 80
/*
Generate tablespace
analysis report only on Wednesdays.
Uncomment the
following statements if this is what you want to do.
whenever sqlerror exit
select 1/0
from dual
where to_char(sysdate,
'day') not in ('wednesday');
*/
clear breaks
column today
new_value today noprint
column time
new_value time noprint
/*
Remove report
file if exists, otherwise in case of problem, you may
receive a print
out of an old file.
*/
host rm -f $HOME/rep/analyze_tablespace_usage_&1..lst
spool $HOME/rep/analyze_tablespace_usage_&1..lst
set feedback off
ttitle today center 'TABLE
AND INDEX SPACE USAGE IN TABLESPACE &1' -
right 'Page '
format 990 sql.pno skip 1 -
time -
skip 1 -
analyze_tablespace_usage.sql
select to_char(sysdate,
'DD-MON-YYYY') today,
to_char(sysdate, 'HH:MI:SS AM') time
from dual;
set feedback on
/*
Compute total
and unused disk space in tablespace.
*/
DECLARE
total_blocks
number;
total_bytes
number;
unused_blocks
number;
unused_bytes
number;/* Above High water mark */
last_used_extent_file_id
number;
last_used_extent_block_id
number;
last_used_block
number;
free_space
number;/* Below High water mark */
user_id
all_tables.owner%type;
object_name
all_tables.table_name%type;
object_type
varchar2 ( 5);
--
cursor
c1
is
select
owner, table_name, 'TABLE'
from
all_tables
where
tablespace_name = upper('&1')
union
select
owner, index_name, 'INDEX'
from
all_indexes
where
tablespace_name = upper('&1');
BEGIN
open c1;
LOOP
fetch c1
into user_id, object_name, object_type;
exit when c1%notfound;
dbms_space.unused_space
( user_id,
object_name,
object_type,
total_blocks,
total_bytes,
unused_blocks,
unused_bytes,
last_used_extent_file_id,
last_used_extent_block_id,
last_used_block
);
dbms_space.free_blocks
( user_id,
object_name,
object_type,
0,
free_space);
--
dbms_output.put_line
( object_type||' Name = '||user_id||'.'||object_name
);
dbms_output.put_line
( '**********'
);
dbms_output.put_line
( 'Total Bytes = '||to_char(total_bytes, '999,999,990')||
' Free Bytes above Water Mark
= '||
to_char(unused_bytes,'999,999,990') );
dbms_output.put_line
( 'Bytes to W.Mark= '||to_char(total_bytes -
unused_bytes,'999,999,990')||
' Free Bytes below Water Mark
= '||
to_char(free_space*8192, '999,999,990') );
dbms_output.put_line
(
'-------------------------------------------------------------------------
-------');
END LOOP;
END;
.
/
column object
format a26
heading 'OBJECT'
column file_id
format 99990
heading 'FILE|ID '
column block_id
format 999990
heading 'BLOCK|ID '
column blocks
format 99990
heading 'BLOCKS'
column bytes
format 9,999,999,999 heading 'BYTES'
ttitle today center 'POSSIBLE
FRAGMENTATION IN TABLESPACE &1' -
right 'Page '
format 990 sql.pno skip 1 -
time -
skip 2 -
analyze_tablespace_usage.sql
skip 2
select 'freespace'
owner, ' ' object,
file_id, block_id, blocks, bytes
bytes
from sys.dba_free_space
where tablespace_name
= upper('&1')
union
select substr(owner,
1, 15),
substr(segment_name, 1, 31),
file_id, block_id, blocks, bytes
bytes
from sys.dba_extents
where tablespace_name
= upper('&1')
order by 3, 4
/
break
on bytes on report
compute sum of bytes on report
column segment_name
format a26
column tablespace_name format
a15
ttitle today center 'SPACE
USAGE BY OBJECTS IN TABLESPACE &1' -
right 'Page '
format 990 sql.pno skip 1 -
time -
skip 2 -
analyze_tablespace_usage.sql
skip 2
select segment_name,
tablespace_name, sum(bytes) bytes
from sys.dba_extents
where tablespace_name
= '&1'
group by
segment_name, tablespace_name
/
set feedback off
column space
heading ' SPACE'
column name
heading ' INDEX NAME'
column btree_space format
9,999,999,999 heading 'TOTAL
SPACE'
column used_space
format 9,999,999,999 heading
'USED SPACE'
ttitle today center 'USED
AND FREE SPACE USAGE FOR TABLESPACE &1' -
right 'Page '
format 990 sql.pno skip 1 -
time -
skip 2 -
analyze_tablespace_usage.sql
skip 2
select 'Free Space
' space, sum(bytes )
bytes
from sys.dba_free_space
where tablespace_name
= upper('&1')
union
select 'Used Space
' space, sum(bytes )
bytes
from sys.dba_extents
where tablespace_name
= upper('&1')
/
prompt
prompt
prompt.
********** END OF REPORT *********
spool off
exit