INTELLIGENT WORK FORUMS
FOR COMPUTER PROFESSIONALS

Log In

Come Join Us!

Are you a
Computer / IT professional?
Join Tek-Tips Forums!
  • Talk With Other Members
  • Be Notified Of Responses
    To Your Posts
  • Keyword Search
  • One-Click Access To Your
    Favorite Forums
  • Automated Signatures
    On Your Posts
  • Best Of All, It's Free!

*Tek-Tips's functionality depends on members receiving e-mail. By joining you are opting in to receive e-mail.

Posting Guidelines

Promoting, selling, recruiting, coursework and thesis posting is forbidden.

Jobs

How can we rename a sequence that another user owns?

How can we rename a sequence that another user owns?

(OP)
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?"

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

RE: How can we rename a sequence that another user owns?

Have the IRS order the user to rename their sequence?

RE: How can we rename a sequence that another user owns?

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

RE: How can we rename a sequence that another user owns?

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
www.syssoft-int.com

FAQ219-2884: How Do I Get Great Answers To my Tek-Tips Questions?
FAQ181-2886: How can I maximize my chances of getting an answer?

RE: How can we rename a sequence that another user owns?

(OP)
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 !

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

RE: How can we rename a sequence that another user owns?

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

RE: How can we rename a sequence that another user owns?

(OP)
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,

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

Red Flag This Post

Please let us know here why this post is inappropriate. Reasons such as off-topic, duplicates, flames, illegal, vulgar, or students posting their homework.

Red Flag Submitted

Thank you for helping keep Tek-Tips Forums free from inappropriate posts.
The Tek-Tips staff will check this out and take appropriate action.

Reply To This Thread

Posting in the Tek-Tips forums is a member-only feature.

Click Here to join Tek-Tips and talk with other members!

Resources

Close Box

Join Tek-Tips® Today!

Join your peers on the Internet's largest technical computer professional community.
It's easy to join and it's free.

Here's Why Members Love Tek-Tips Forums:

Register now while it's still free!

Already a member? Close this window and log in.

Join Us             Close