Re: Need a generic way to drop tables created by another user

Hi Todd,

Instead of dropping the table could you truncate it? If so you could then create it with an owner that would be known to all of your users and programs.

Regards

Jerry

todd_at_bi-tech.com wrote:
>
> 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
 

-- 
Jerry Gitomer		Since I know how to spell DBA I became one. 
jgitomer_at_p3.net