Searching for a user (enhanced)

Now the same as before but using a Windows batch FOR loop.
The script iterates through the file "oracle.dat", that contains "username password SID" in every line. Then connects with sqlplus using this information and executes "busca.sql" that do and formats the search.


BUSCA.BAT

@echo off
echo Se genera el fichero "%1.out"
echo RESUMEN:
echo ========
@echo off
@echo >%1.out
FOR /F "tokens=1,2,3" %%G IN (oracle.dat) DO (sqlplus -S %%G/%%H@%%I @c:\busca.sql %1>>%1.out)

type %1.out|find "->"

REM ^ List only nontrivial results
type %1.out|find /c /i "%1"
REM ^ Count the results

ORACLE.DAT
(Information separated by ONE space. Can be configured with "option delims=xxx")

system password SID
....


BUSCA.SQL
set pagesize 0
set feed off
set serveroutput on
declare
ins varchar2(10);
hostname varchar2(40);
OK varchar2(40):=NULL;
estado varchar2(40):=NULL;

begin
select UPPER(instance) into ins from v$thread where rownum=1;
select HOST_NAME into hostname from v$instance where rownum=1;
select username,ACCOUNT_STATUS into OK,estado from dba_users where upper(username) like upper('&1');
if ok is not null then
dbms_output.put_line(rpad(ins,9,' ')||'-> '||rpad(hostname,30,' ')||' ---> '||OK||' ('||estado||')');
end if;
exception
when no_data_found then null;
end;
/
exit

EXAMPLE

C:\>busca rman
Se genera el fichero "rman.out"
RESUMEN:
========
AE02 -> aecobde9 ---> RMAN (OPEN)
AE03 -> arepbde1 ---> RMAN (OPEN)
AE05 -> aremsae4 ---> RMAN (OPEN)
PROD -> sun06.sscc.intranet.local ---> RMAN (OPEN)
RMAN -> rman ---> RMAN (OPEN)
RMAN9 -> rman ---> RMAN (OPEN)
XE -> bcnaorarman ---> RMAN (OPEN)
AOLT -> sun03 ---> RMAN (OPEN)
8

Searching for a user

It's usual to have to locate where an user resides in. Here it's a way to do it from my Windows, that have all access permisions. Another way is to install a Linux or emulator and do it with a shell script (some day...).

Result:

C:\>busca perfstat
Se genera el fichero "perfstat.out"
RESUMEN:
========
AE02 -> aecobde9 ---> PERFSTAT (OPEN)
AE03 -> arepbde1 ---> PERFSTAT (OPEN)
AE05 -> aremsae4 ---> PERFSTAT (OPEN)
AE07 -> aecobde9 ---> PERFSTAT (OPEN)
AE08 -> arepbde1 ---> PERFSTAT (OPEN)
AE09 -> arepbde1 ---> PERFSTAT (OPEN)
FINP -> sunfin ---> PERFSTAT (OPEN)
PROD -> sun06.sscc.intranet.local ---> PERFSTAT (OPEN)
RMAN -> rman ---> PERFSTAT (OPEN)
SCENTER1 -> bcnascec1p01 ---> PERFSTAT (OPEN)
AOLT -> sun03 ---> PERFSTAT (OPEN)
APP02 -> aremsar1 ---> PERFSTAT (OPEN)
APP05 -> agenbdr1 ---> PERFSTAT (OPEN)
13

And now the implementation:

C:\>type busca.bat
@echo off
echo Se genera el fichero "%1.out"
echo RESUMEN:
echo ========
echo ***************************>%1.out
echo *** P R O D U C C I O N ***>>%1.out
echo ***************************>>%1.out

sqlplus -S system/password@AE01 @c:\busca.sql %1>>%1.out
sqlplus -S system/password@AE01-10 @c:\busca.sql %1>>%1.out
....
type %1.out|find "->"
type %1.out|find /c /i "%1"


C:\>type busca.sql
set pagesize 0
set feed off
set serveroutput on
declare
ins varchar2(10);
hostname varchar2(40);
OK varchar2(40):=NULL;
estado varchar2(40):=NULL;

begin
select UPPER(instance) into ins from v$thread where rownum=1;
select HOST_NAME into hostname from v$instance where rownum=1;
select username,ACCOUNT_STATUS into OK,estado from dba_users where upper(username) like upper('&1'
);
if ok is not null then
dbms_output.put_line(rpad(ins,9,' ')||'-> '||rpad(hostname,30,' ')||' ---> '||OK||' ('||estado
||')');
end if;
exception
when no_data_found then null;
end;
/
exit