ORA-03001 - while trying to rename tables owned by others - workaround

There could be situations where you may like to rename the tables owned by others, but do not have access to the password (of the schema owner) or don't want to change the password (using "by values" etc...). You could perform almost all the activities by using "ALTER SESSION SET CURRENT_SCHEMA=;", but rename table wouldn't work and following error message would be encountered:

chandra@DOE01:11g> show user
USER is "CHANDRA"
chandra@DOE01:11g> alter session set current_schema=CP_ADMIN;

Session altered.

chandra@DOE01:11g> rename APP_DATA to APP_DATA_BAK;
rename APP_DATA to APP_DATA_BAK
*
ERROR at line 1:
ORA-03001: unimplemented feature


chandra@DOE01:11g>

To get around this, we could create a simple procedure similar to the following:

create or replace procedure rename_others_tab (orig_tab_name in varchar2, new_tab_name in varchar2)
as
lv_sql_str varchar2(100);
lv_exists number(1);
begin
select count(*) into lv_exists from user_tables where table_name=orig_tab_name;
if lv_exists = 1 then
lv_sql_str := 'rename '||orig_tab_name||' to '||new_tab_name;
dbms_output.put_line (lv_sql_str);
execute immediate lv_sql_str;
else
dbms_output.put_line('ERROR:'||orig_tab_name||' does not exist!');
end if;
end;
/

Note that you would be creating the above procedure while your current_schema is set to the owner of the table.

Then:

chandra@DOE01:11g> exec rename_others_tab('APP_DATA','APP_DATA_BAK');

PL/SQL procedure successfully completed.

chandra@DOE01:11g>

The table name would be renamed....Of course, use the above procedure (work-around) with caution and when ABSOLUTELY needed.

Thought would be of use to others....