I have encountered an issue with the version enabled schema. I have followed below steps.
1. Created a schema (HR) with two table viz, emp and dept
2. Enabled versioning using below code
3. I had to drop the schema (HR)
4. Created the same HR schema and followed step 2 to enable versioning.
5. Below is the error.
Error starting at line 1 in command:
ORA-20131: table 'LTDEMO.EMP' is already version enabled
ORA-06512: at "WMSYS.LT", line 9150
ORA-06512: at line 2
From the above error one can imagine that there are few residues left in WMSYS related to the dropped HR schema. I can recreate HR schema with different name say HR1 and follow the next actions but I would like to repair the existing stuff. Could you please guide me to remove all the residues and repair the existing stuff please.
Please let me know if further information is required.
The error is listing the LTDEMO schema, not HR. It should not be necessary to manually cleanup the metadata in order to version the table again. If this was not an issue with using the wrong schema, you can try to run the following:
SQL> exec dbms_wm.RecoverFromDroppedUser ;
If that still doesn't help, you would need to file an SR.
Thanks for the response. I might have introduced little bit confusion in my text. Please ignore LTDEMO schema in error message and consider that it was HR schema. I will try as you suggested and let you know the results.
Edited by: Kumar on Jan 18, 2012 12:48 PM