RE: what is obj$.type#=10?

  • From: "Jacques Kilchoer" <Jacques.Kilchoer@xxxxxxxxx>
  • To: <oracle-l@xxxxxxxxxxxxx>
  • Date: Wed, 23 Jun 2004 14:18:17 -0700

I'm going to answer Mr. Shamsudeen, Mr. Lewis and Mr. Bobak at once.

Mr. Bobak and Mr. Shamsudeen - yes, I understand why a type# =3D 10 =
is created in a schema when the schema contains a stored procedure
depending on a public synonym (the type# 10 object will have the same
name as the public synonym). The object is created to allow invalidation
of the stored procedure in case I create an object in the schema, and
the object has the same name as the public synonym.

My question was, when I create a public synonym THAT WAS NEVER USED by
any stored procedure or other PL/SQL object, why a type# =3D 10 object
with the same name remained AFTER I dropped the public synonym? (Of
course a little thought on my part would have given me the answer to
this question since I mentioned this behaviour yesterday.)

Mr. Lewis's answer referred me to his original comment to the list
(several months ago), which I repeated only yesterday, and which was the
obvious answer to my new question from today: when you drop some objects
(not only synonyms - at least, in the past, this was valid for other
object types besides synonyms), a row remains in obj$ with type# =3D 10.
Mr. Lewis says "I don't have any idea at present why Oracle does this.",
but the theory I had heard a while ago on the list is: Oracle does this
so that Oracle doesn't waste too many object ids when running
installation scripts, that have many "drop object" statements
immediately followed by a "create object" statement.
Please see the official ORACLE-L FAQ:
To unsubscribe send email to:  oracle-l-request@xxxxxxxxxxxxx
put 'unsubscribe' in the subject line.
Archives are at
FAQ is at

Other related posts: