version enabled

[m2001331]

Hi,

I tried to drop a user from a database,but i got the following error:-

ERROR at line 1:
ORA-00604: error occurred at recursive SQL level 1
ORA-20031: All version enabled tables owned by 'PAY_MASTER' must be disabled
first.
ORA-06512: at "SYS.WM_ERROR", line 288
ORA-06512: at "SYS.WM_ERROR", line 305
ORA-06512: at "SYS.NO_VM_DROP_PROC", line 40
ORA-06512: at line 3

I understand that once version is enable i will not be able to directly disable it.How can I disable the version so that i can drop the user.
I am aware that there is a 'purge' method but i do not know how to use it.
please advice.
thanks.

 

[karluk]

I haven't tried it, but your error message strongly suggests that you need to run dbms_wm.disableversioning('table_name') for each version enabled table owned by 'PAY_MASTER'. You can use force=true if necessary, if you have any workspaces that contained modified data.

 

[m2001331]

Hi,
I tried to exec disable versioning -
SQL> exec dbms_wm.disableversioning('ACCOUNT_LT');
BEGIN dbms_wm.disableversioning('ACCOUNT_LT'); END;

*
ERROR at line 1:
ORA-20132: table 'ACCOUNT_LT' is not version enabled
ORA-06512: at "SYS.WM_ERROR", line 288
ORA-06512: at "SYS.WM_ERROR", line 305
ORA-06512: at "SYS.LT", line 695
ORA-06512: at "SYS.LT", line 8114
ORA-06512: at line 1
but i got the above error.
and when i tried to drop the table i got the following message
SQL> drop table ACCOUNT_LT cascade constraints;
drop table ACCOUNT_LT cascade constraints
*
ERROR at line 1:
ORA-00604: error occurred at recursive SQL level 1
ORA-20061: versioned objects have to be version disabled before being dropped
ORA-06512: at "SYS.WM_ERROR", line 288
ORA-06512: at "SYS.WM_ERROR", line 297
ORA-06512: at "SYS.NO_VM_DROP_PROC", line 60
ORA-06512: at line 3

please advice.
thanks.

 

[karluk]

Are you doing the disabling from a different schema? You get the same error message both if the table exists and is not version enabled, and also if the table doesn't exist at all. That means if you are disabling using an id other than the table owner, you have to specify the owner.table_name. Something like

exec dbms_wm.disableversioning('PAY_MASTER.ACCOUNT_LT');

 

[m2001331]

Hi,
I executed the disable versioning from the owner and i still got the same results.
SQL> exec dbms_wm.disableversioning('ACCOUNT_LT');
BEGIN dbms_wm.disableversioning('ACCOUNT_LT'); END;

*
ERROR at line 1:
ORA-20132: table 'ACCOUNT_LT' is not version enabled
ORA-06512: at "SYS.WM_ERROR", line 288
ORA-06512: at "SYS.WM_ERROR", line 305
ORA-06512: at "SYS.LT", line 695
ORA-06512: at "SYS.LT", line 8114
ORA-06512: at line 1

any other way for me to get over this problem/
thanks.

 

[karluk]

Please query user_wm_versioned_tables to list the names of all your versioned enabled tables.

 

[m2001331]

Hi,
I queried the user_wm_versioned_tables as / as sysdba and also as wmsys and no records were retrieved.I have no idea on how to resolve this.

SQL> conn wmsys/password
Connected.
SQL> select * from user_wm_versioned_tables ;

no rows selected

SQL> conn / as sysdba
Connected.
SQL> select * from user_wm_versioned_tables ;

no rows selected

thanks.

 

[karluk]

Well, I'm not sure what's going on. My thought was that your "cascade constraints" option was generating the error. You would be unable to drop a table that was linked by a foreign key to a version enabled table. That would, indeed, cause an error, but unfortunately the error message is different than what you are seeing.

Just to be thorough, I would query dba_wm_versioned_tables as sys, to see if anything in your database is version enabled.