Oracle User bzw. Schema umbenennen

September 6th, 2010 Categories: Oracle
Eigentlich ist es ja nicht supported, und in einer produktiven Umgebung würde ich dazu auch nicht raten, aber in einer Test- oder Entwicklungsdatenbank kann es schon mal sinnvoll sein,
einem Oracle Schema einen neuen Namen zu geben.

Die folgende Prozedur habe ich dazu vor einiger Zeit entwickelt, und sie hat sich bisher bewährt:

create or replace procedure RenameUser(OldName varchar2, NewName varchar2, NewPassword varchar2) is
sessions number;
USER_LOGGED_IN exception;
user_id number;
pragma exception_init(USER_LOGGED_IN , -20001);
begin
  select count(*) into sessions from v$session where username = OldName and status != 'KILLED';
  if (sessions > 0) then
     raise_application_error(-20001, 'Can''t rename a user while he is logged in!');
  end if;
  select user# into user_id from user$ where name = OldName;
  update user$ set name = NewName where user# = user_id and name = OldName;
  commit;
  execute immediate 'alter system flush shared_pool';
  execute immediate 'alter user ' || NewName ||' identified by ' || NewPassword ;
end RenameUser; 

Die Neuvergabe das Passwortes ist erforderlich, da bei der Verschlüsselung des Passwortes unter Anderem auch der Benutzername verwendet wird.


Tags:

3 Responses to “Oracle User bzw. Schema umbenennen”

  1. September 20th, 2016 at 13:56
    1

    What yoou posted made a lot of sense.However, think about this, what if you
    composed a catchier title? I ain’t suggesting your content is not solid, but
    suppose you added a post title that grabbed a person’s attention? I mean Oracle User bzw.
    Schema umbenennen | Thomas Hoekstra is kinda plain. You ought
    to look at Yahoo’s home page and note hhow tey write news headlines to grab viewers to click.

    You might try adding a video orr a picture or two to grab
    readers interested about everything’ve written. In my opinion, it could make your posts a littgle bit
    more interesting.

  2. September 3rd, 2016 at 09:24
    2

    But I have experience in financial services, and I know how these conferences work.
    Let me break that down for you, into part’s you will understand
    better. 716″ at Tsukuba Super Lap and again, the same year HKS Super Bike YZF enters the Suzuka 8 Hour Endurance Road Race.

  3. Dezember 3rd, 2015 at 07:36
    3

    This article provides clear idea designed for the new visitors of blogging,
    that in fact how to do blogging and site-building.

Leave a Comment