Oracle FAQ | Your Portal to the Oracle Knowledge Grid |
Home -> Community -> Usenet -> c.d.o.server -> Re: SQL Help
Colin Caie wrote:
>
> Hi,
> I would like to perform a query that;
>
> select blocks from dba_extents
>
> then multiplies the blocks by the value produced from;
>
> select value from v$parameter where name = 'db_block_size'.
>
> Can anyone help?.
> --
> Regards,
>
> Colin Caie
Does it have to be in a single query? If not and you are working in UNIX, try something like this:
#/bin/ksh
typeset -i Bsize=0;
Bsize=$(sqlplus -s user/pass <<-sqlEOF
set pages 0
set feedback off;
set trimout on;
whenever sqlerror exit failure;
select value from v\$parameter where name='db_block_size';
exit success;
sqlEOF
)
if [[ $? -ne 0 ]];then
print -u2 "fatal error from sqlplus";
exit 1;
fi
sqlplus -s usr/pass <<-sqlEOF
set pages 0;
set feedback off;
set trimout on;
whenever sqlerror exit failure;
select owner||'.'||segment_name||': '||blocks*${Bsize}
from dba_extents
order by owner,segment_name;
exit success;
sqlEOF
if [[ $? -ne 0 ]];then
print -u2 "fatal error from sqlplus";
exit 1;
fi
exit 0;
Received on Fri May 26 2000 - 00:00:00 CDT