Tuesday, September 8, 2009

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....

6 comments:

Anonymous said...

ALTER TABLE APP_DATA to APP_DATA_BAK would also work. This Oracle bug still exists in version 11.2.0.3.

Anonymous said...

Sorry, I think I typed that in wrong. Should be:

ALTER TABLE APP_DATA RENAME to APP_DATA_BAK;

Chandra Pabba said...

Hello, Yes, ALTER TABLE would work if you are executing that statement while logged in as the schema owner. If you attempt to run while you are logged as a different user, you would get the following:

SQL> show user

USER is "SYS"

SQL> alter table app_data.cp_data rename to app_data.cp_data_back;

alter table app_data.cp_data rename to app_data.cp_data_back

*

ERROR at line 1:

ORA-14047: ALTER TABLE|INDEX RENAME may not be combined with other operations





SQL>

Vit Spinka said...

ALTER TABLE works, as long as you don't specify schema name in the "TO" clause.
You don't even need to set current_schema for ALTER TABLE RENAME.

Michael Fontana said...

Pretty snazzy! Just used to solve a big problem for my client.

Priyank Pandey said...

Excellent workaround!!! You saved my day...