| Oracle FAQ | Your Portal to the Oracle Knowledge Grid | |
 |
 | |||
| Oracle FAQ | Your Portal to the Oracle Knowledge Grid | |
|
| |||
Home -> Community -> Usenet -> c.d.o.server -> Re: Join select on dba_data_filles, dba_free_space, dba_extents etc.
Hi
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
oracleguru_at_mailcity.com
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
substr(segment_name, 1, 31),
file_id, block_id, blocks, bytes bytes
from sys.dba_extents
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
Wanni Chua <wanni_at_mbox2.singnet.com.sg> wrote in article <MPG.1152584e1c86baa398968b_at_news.singnet.com.sg>...
> Hi, > > I am trying to write a SQL to select a lists of Total space size, > Used space size and Free space size for all the tablespaces. > > 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 > > Anyone has encounterd a smiliar experience? > Why is this happening. How should I write such select query then? > > Thanks for your replies >Received on Thu Mar 11 1999 - 14:21:26 CST
 |
 |