bos (broadcast oracle select)

USAGE

This is a Ruby script usefull to lunch a SQL statement to several Oracle instances

USAGE: bos [--sid|-s XXX] [--sfile|-S XXX] [--query|-q XXX] [--qfile|-Q XXX] [--user|-u XXX] [--ufile|-U XXX] [param1] [...]

where

[--sid|-s XXX]: XXX is a oracle instance
[--sfile|-S XXX]: XXX is a file with the list of Oracle databases
[--query|-q XXX]: XXX is a SQL statement
[--qfile|-Q XXX]: XXX is a file with inside a SQL statement
[--user|-u XXX]: XXX is username/password of the Oracle user
[--ufile|-U XXX]: XXX is a file with the list of username/password

param1, param2, ... are the parameters (like "#P1#=95") used inside the SQL statement

Examples:

bos --sfile "/makalu/sapbasis/scripts/prod.txt" --query "select mandt from sapr3.t000" --ufile users

bos --sid C11 --query "select mandt from sapr3.t000" --user sapr3/sap

Examples of SQL script:

----------------------------------------------------------- extents.sqlp -----------------------------------------------------------

select
	SEGMENT_NAME,
	TABLESPACE_NAME,
	EXTENTS,
	MAX_EXTENTS,
	MAX_EXTENTS - EXTENTS free,
	NEXT_EXTENT,
	round(EXTENTS / MAX_EXTENTS * 100, 1)     perc
from
	dba_segments
where
	owner='SAPR3' and
	MAX_EXTENTS - EXTENTS <= #P1#
order by
	SEGMENT_NAME

------------------------------------------------------------ ts.sqlp ------------------------------------------------------------

select
	u.tablespace_name,
	round(u.used_mb, 2),
	round(f.free_mb, 2),
	round(u.used_mb + f.free_mb, 2) size_mb,
	round((u.used_mb * 100) /(u.used_mb + f.free_mb), 1) || '%' perc
from
	(
	select
		tablespace_name,
		sum(bytes) / 1024 / 1024 used_mb
	from
		dba_segments
	group by
		tablespace_name
	) u, (
	select
		tablespace_name,
		sum(bytes) / 1024 / 1024  free_mb
	from
		dba_free_space
	group by
		tablespace_name
	) f
where
	u.tablespace_name = f.tablespace_name and
	round((u.used_mb * 100) /(u.used_mb + f.free_mb), 1) > #P1# and
	round(f.free_mb, 2) < #P2#

------------------------------------------------------------------------ next.sql ------------------------------------------------------------------------

select
	s.SEGMENT_NAME,
	s.TABLESPACE_NAME,
	s.EXTENTS,
	s.NEXT_EXTENT
from
	dba_segments s
where
	s.owner='SAPR3' and
	s.TABLESPACE_NAME <> 'PSAPTEMP' and
	not exists (
		select 1 from
			dba_free_space f
		where
			f.tablespace_name = s.tablespace_name
		and
			f.BYTES > s.NEXT_EXTENT
	)

------------------------------------------------------------------------

Example of sfile:

P00
P01
P02
P03
P04
PTB
G20
G18

Example of ufile:

sapr3/sap
system/manager

REQUIREMENTS

I've tested this library with a Debian Linux 3.0 and

• ruby 1.6.7
• rubi-dbi

HISTORY

• 2002-09-23 (Matteo)

I have ported my previous orasql.pl Perl script to Ruby.

There is also a new
parameter: --user (and -ufile)

AUTHOR

E-MAIL: matteo.redaelli@libero.it

WEB: http://digilander.iol.it/reda

LICENSE

This package is free software; you can redistribute it and/or modify it under the same terms as Perl itself, i.e., under the terms of the "Artistic License" or the "GNU General Public License".

The C library at the core of this Perl module can additionally be redistributed and/or modified under the terms of the "GNU Library General Public License".

DISCLAIMER

This package is distributed in the hope that it will be useful, but WITHOUT ANY WARRANTY; without even the implied warranty of MERCHANTABILITY or FITNESS FOR A PARTICULAR PURPOSE.

See the "GNU General Public License" for more detail