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:
ALTER TABLE APP_DATA to APP_DATA_BAK would also work. This Oracle bug still exists in version 11.2.0.3.
Sorry, I think I typed that in wrong. Should be:
ALTER TABLE APP_DATA RENAME to APP_DATA_BAK;
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>
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.
Pretty snazzy! Just used to solve a big problem for my client.
Excellent workaround!!! You saved my day...
Post a Comment