I am trying to alter a table on another users schema.
I loggen as a dbauser and set the current_schema as the other user and when I try the alter I get the following error.
$ sqlplus dbausr/pwd
SQL> alter session set current_Schema=acct_owner_01 ;
SQL> alter table customer add deliveryoptions varchar2(15) default 'P' not null ;
alter table customer add deliveryoptions varchar2(15) default 'P' not null
ERROR at line 1:
ORA-00604: error occurred at recursive SQL level 1
ORA-00942: table or view does not exist
But, I could describe the table
I could do alter on the same table without any problem, if I omit the default clause.
SQL> alter table customer add deliveryoptions varchar2(15) ;
Does anyone know how to resolve this problem?. (in my case, I can not prefix the table with the owner name.)
try to produce SQL trace and look into it (raw file)
It displays recursive SQL calls and errors.
THIS IS A BUG.........don't use alter sesison option for this use....:-)
Try this way
Dont try putting both the constraints together I mean Default and Not null
It should work
Click Here to Expand Forum to Full Width