Need a generic way to drop tables created by another user

We have a process that will drop and re-create some tables each time it is run. These tables are shared by all users, so there is really only one copy, but we never know which user has actually created the tables. To handle this, we create PUBLIC SYNONYMs so that all users can access the tables transparently without qualification.

This works great until we run the process that wants to drop the tables. At that point the DROP TABLE statement will fail as if the table doesn't exist. It appears that a SELECT statement will observe the synonym, yet the DROP statement will not.

Is there any easy way to drop a table without knowing the creator or schema? I realize we could issue some SQL statements to read one of the system tables, determine the owner, then qualify the statement.

This is using Oracle 7 server on a UNIX box.

Thanks for any assistance you can provide, Todd Saylor
todd_at_bi-tech.com

-----== Posted via Deja News, The Leader in Internet Discussion ==----- http://www.dejanews.com/ Now offering spam-free web-based newsreading Received on Tue Mar 24 1998 - 00:00:00 CST