Re: Importing indexes with INDEXFILE in imp

From: Jared Still <jkstill_at_cybcon.com>
Date: Fri, 13 Jul 2001 06:45:18 -0700
Message-ID: <F001.00349C2D.20010713063637@fatcity.com>

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)  || '.' ||
        '&timestamp' filetmp_

from dual
/

set term on

-- 
Please see the official ORACLE-L FAQ: http://www.orafaq.com
-- 
Author: Jared Still
  INET: jkstill_at_cybcon.com

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: ListGuru_at_fatcity.com (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).

Received on Fri Jul 13 2001 - 08:45:18 CDT