On Wednesday 11 July 2001 09:11, Jesse, Rich wrote:
>
> The problem I'm having is that the generated file contains "CONNECT
> <schema>" commands for every schema that has an indexed table. Does
> everybody just gather up all the passwords to all the schemas and manually
Rich,
Use a script to change the password for each user
and then change it back to whatever it was.
I've included one below that works well on unix.
It requires 2 scripts, both included at the end
of this post. The method used has the advantage
of saving the commands to put the correct password
in a script in /tmp should something go wrong and
your main script crashes without resetting the password.
> If the file was
> created so that the schema was prefixed to the TABLE instead of the INDEX,
> I could have one CONNECT SYSTEM/MANAGER at the start and it would all work
> fine.
Two words for that: Learn Perl.
There's nothing better for changing scripts
> You can't create an index in schema "B" for a table in schema "A"
> anyway, right?
>
Sure you can.
> Anyone have any cool workarounds for this in 8.1.7? A cool
> grep/awk/sed/perl script to move the schema prefixes from index to table in
> the INDEXFILE-generated script, perhaps?
Oh, looky there. You mentioned Perl.
If I weren't going to be late for work, I'd do this now. Maybe
later.
Jared
----------------------------------------------
-- become_user.sql
rem avoid messing up an account if something does not work!
whenever sqlerror exit
set pages 0 feed on verify off echo off term on
col bu_user new_value user_to_become noprint
col global_name new_value gname noprint
prompt bu.sql will save a users old password
prompt and assign a new one so that you
prompt ( the DBA ) can log in. Run the tmp
prompt script to restore the old password.
prompt
prompt
prompt User to become:
set term off feed off
select '&&1' bu_user from dual;
select global_name from global_name;
@@tmpfile
set term off feed off
define reset_script=&&_tmp_file_name_..reset.sql
spool &&reset_script
select 'alter user &&user_to_become identified by values ' || '''' ||
password || '''' || ';'
from dba_users
where username = upper('&&user_to_become')
/
prompt set feed on echo off pause off term on
select 'prompt Please remove &&reset_script after you are done' from dual;
prompt prompt
spool off
!chmod 640 &&reset_script
alter user &&user_to_become identified by dbatest
/
whenever sqlerror continue
set term on feed on
connect &&user_to_become/dbatest@&&gname
prompt
prompt The password for &&user_to_become@&&gname will be changed to 'dbatest'
prompt The script to restore the password is &&reset_script
prompt
prompt I am resetting the users password to its original setting now.
prompt
@&&reset_script
undef gname
undef 1
undef user_to_become
----------------------------------------------
-- tmpfile.sql
-- create a temporary file name
-- it will be of the form /tmp/<INSTANCE>.<USER>.<HSECS>
-- the temp filename will be in the variable _tmp_file_name_
-- you must be able to select from v$timer for this to work
-- use rmfile to remove tmp files
set verify off feed off
set echo off pause off feed off term off
var tmpstamp_ varchar2(30);
var dbname_ varchar2(8);
declare
tmpsecs number;
secs varchar2(9);
sec_len integer := 0;
begin
select hsecs into tmpsecs from v$timer;
--dbms_output.enable(1000000);
--dbms_output.put_line('secs: ' || secs);
select to_char(tmpsecs)
into :tmpstamp_
from dual;
select lower(substr(global_name,1,instr(global_name,'.')-1))
into :dbname_
from global_name;
end;
/
col tmpstamp_ noprint new_value timestamp
col filetmp_ noprint new_value _tmp_file_name_
col dbname_ noprint new_value instance
select :tmpstamp_ tmpstamp_, :dbname_ dbname_ from dual;
select
'/tmp/' ||
'&&instance' ||
'.' ||
lower(user) || '.' ||
'×tamp' filetmp_
from dual
/
set term on
----------------------------------------------
--
Please see the official ORACLE-L FAQ: http://www.orafaq.com
--
Author: Jared Still
INET: [EMAIL PROTECTED]
Fat City Network Services -- (858) 538-5051 FAX: (858) 538-5051
San Diego, California -- Public Internet access / Mailing Lists
--------------------------------------------------------------------
To REMOVE yourself from this mailing list, send an E-Mail message
to: [EMAIL PROTECTED] (note EXACT spelling of 'ListGuru') and in
the message BODY, include a line containing: UNSUB ORACLE-L
(or the name of mailing list you want to be removed from). You may
also send the HELP command for other information (like subscribing).
