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