Results 1 to 8 of 8
  1. #1
    Join Date
    May 2004
    Posts
    95

    Unanswered: execute command as SYS

    Hello everybody,

    I got a procedure that copies data from a schema to another, so far no problem, because I granted select previleges to the destination schema.

    The problem I'm having, is that I need to run a 'ALTER SYSTEM' command, and this is only done by user SYS. Is there anyway for me to execute a part of that procedure as user SYS, without giving certain priviledges to the owner of the procedure????

    Many thanks.

  2. #2
    Join Date
    Jun 2004
    Location
    Liverpool, NY USA
    Posts
    2,509
    What "alter system" command are you running. I know many ways to copy data without having to issue "ALTER system" commands.
    Bill
    You do not need a parachute to skydive. You only need a parachute to skydive twice.

  3. #3
    Join Date
    May 2004
    Posts
    95
    ok..

    I have to kill all sessions for the user that I want to copy from, so that the data doesn't suffer any change while copying.

    So my solution is to kill all sessions before the copy.

    Thanks.

  4. #4
    Join Date
    Aug 2003
    Location
    Where the Surf Meets the Turf @Del Mar, CA
    Posts
    7,776
    Provided Answers: 1
    Is there anyway for me to execute a part of that procedure as user SYS, without giving certain priviledges to the owner of the procedure????
    Put in a package owned by SYS & GRANT EXECUTE on that package
    You can lead some folks to knowledge, but you can not make them think.
    The average person thinks he's above average!
    For most folks, they don't know, what they don't know.
    Good judgement comes from experience. Experience comes from bad judgement.

  5. #5
    Join Date
    May 2004
    Posts
    95
    many many thanks.....

    a simple and clean solution, and that will allows another possibilities.

  6. #6
    Join Date
    Jan 2004
    Location
    Croatia, Europe
    Posts
    4,094
    Provided Answers: 4
    Quote Originally Posted by xixo
    I have to kill all sessions for the user that I want to copy from, so that the data doesn't suffer any change while copying.

    So my solution is to kill all sessions before the copy.
    Can you guarantee that the same user (or many of them connecting to the same schema) won't connect again during the copying process and spoil your perfect plan?

  7. #7
    Join Date
    May 2004
    Posts
    95
    the access is changed before killing the sessions, so that if the users access the data, they get an invalid entry.

  8. #8
    Join Date
    Dec 2003
    Posts
    1,074
    You could use FLASHBACK query (in 10g +) to go after data as of a specific time. Something like:

    Code:
    declare
     l_now timestamp;
    begin
     l_now := current_timestamp;
    
     insert into current_schema.table1
     select * from other_schema.table1 as of timestamp(l_now);
    
     insert into current_schema.table2
     select * from other_schema.table2 as of timestamp(l_now);
    
    end;
    I can't think of another way that you could guarantee that multiple tables would be consistent otherwise, unless you wanted to get into the complexity of using expdp, which has a CONSISTENT parameter (which also uses FLASHBACK anyway, so maybe there's no performance gain).

    --=cf

Posting Permissions

  • You may not post new threads
  • You may not post replies
  • You may not post attachments
  • You may not edit your posts
  •