How can we rename a sequence that another user owns? 1

[SantaMufasa]

There is no "ALTER SEQUENCE <name> RENAME..." syntax, and "RENAME <old_name> to <new_name>" does not allow a schema qualification. So my Oracle brain teaser for the day is, "How can we rename a sequence that another user owns without connecting as the owner?"

Mufasa
(aka Dave of Sandy, Utah, USA)
“People may forget what you say, but they will never forget how you made them feel.

 

[carp]

Have the IRS order the user to rename their sequence?

 

[Thargy]

I suppose inspecting its parameters, dropping it and then recreating it with the new name and identical parameters is out of the question?
Is it mandatory to rename, or will achieving the same effect as renaming suffice?


Regards

T

 

[fredericofonseca]

ALTER SESSION SET CURRENT_SCHEMA statement probably - but for some commands, the original session needs to be a sysdba type of user

Regards

Frederico Fonseca
SysSoft Integrated Ltd

http://www.syssoft-int.com

FAQ219-2884
FAQ181-2886

 

[fredericofonseca]

nah. ignore.. won't work gives ORA-03001: unimplemented feature

Workaround -- which would require strict security.

ALTER SESSION SET CURRENT_SCHEMA = HR;
create or replace procedure hrrename_others_seq (orig_seq_name in varchar2, new_seq_name in varchar2)
as
lv_sql_str varchar2(100);
lv_exists number(1);
begin

lv_sql_str := 'rename '||orig_seq_name||' to '||new_seq_name;
dbms_output.put_line (lv_sql_str);
execute immediate lv_sql_str;
end;

exec hrrename_others_seq('seqfred','seqfred2');
drop procedure hrrename_others_seq


Regards

Frederico Fonseca
SysSoft Integrated Ltd

http://www.syssoft-int.com

FAQ219-2884
FAQ181-2886

 

[SantaMufasa]

Carp: HeHeHe !

Thargy: Although it is a possible workaround, the downsides include the fact that all of the synonyms and permissions to the old sequence name, associated to other users disappear. Although replacement synonyms and permissions could be recreated...what a mess. (Carp's solution would actually be easier. <grin>)

Frederico: Clever, reasonable workaround. Hava

!


Mufasa
(aka Dave of Sandy, Utah, USA)
“People may forget what you say, but they will never forget how you made them feel.

 

[Beilstwh]

Why not setup a synonym on the original sequence using the new required name? Then everything works.

Bill
Lead Application Developer
New York State, USA

 

[SantaMufasa]

That is a thought, Bill, but the original request came from auditors that uncovered a sequence name that did not comply with naming standards, thus the need for the rename on the base object. But, yes, under "normal" circumstances, your synonym idea would be satisfactory.

Thanks,

Mufasa
(aka Dave of Sandy, Utah, USA)
“People may forget what you say, but they will never forget how you made them feel.