1. Bounced the DB
2. Connected to Sys
3. Checked, No User is connected
4. Checked, no object is INVALID under Sys User
5. Executed the procedure TEST_PRC1 using SYS user
6. Got same error message again.
Why are you creating a table as SYS? - because I faced this issue with a schema, then thought to give it a try with sys first. What is the issue if I create a test table using sys?
Why are you using a proc and dynamic sql to create a table? - This same proc is running fine on other databases, then why not on this? I am able to create table but not able to execute the proc. this issue is limited to this database only.
The detection of a deadlock will cause an entry to be put into the alert log detailing the associated trace file. --found "Global Enqueue Services Deadlock detected. More info in file" error msg in alert log.
Look in that trace file for information related to both sides of the deadlock.