There is a script named "audit_ddl.sql" online at
http://www.EvDBT.com/tools.htm for enabling only those audits related
to CREATE, DROP, ALTER, TRUNCATE, REPLACE, etc commands. Also, it
creates a DBMS_JOB job to delete records from SYS.AUD$ older than 45
days. Obviously, you may want to make changes to or remove altogether
some of this. Feel free to review it and see if might meet your
(future) DDL auditing needs? Of course, you'll need to bounce the
database to set AUDIT_TRAIL to either TRUE (DB), DB, or OS, if it is
currently set to NONE...
Hope this helps...
consultant - Evergreen Database Technologies, Inc.
P.O. Box 630791, Highlands Ranch CO 80163-0791
website = http://www.EvDBT.com/
email = Tim@xxxxxxxxx
mobile = +1-303-885-4526
fax = +1-303-484-3608
Yahoo IM = tim_evdbt
Dan Norris wrote:
If you want to know in the future, I think the audit
events can capture that information for you, but you'd have to enable
auditing and then AUDIT CREATE TABLE;
As for hindsight, it may be possible to find out with logminer, but I'm
not positive on that. I agree--the data dictionary isn't going to help
you at all (except aud$, if enabled).
On Thu, Nov 20, 2008 at 11:55 AM, Goulet,
If memory is serving correctly there is no way to find
out who created the table. The data dictionary only has information on
the current owner and the object's name. By passing out the "create
any table" system privilege you've lost control of who actually did it
unless you have auditing turned on. In my databases no one other than
DBA's have any of the "any" privileges.
Senior Oracle DBA
transmitted in this communication is intended only for the person or
entity to which it is addressed and may contain confidential and/or
privileged material. Any review, retransmission, dissemination or other
use of, or taking of any action in reliance upon, this information by
persons or entities other than the intended recipient is prohibited. If
you received this in error, please destroy any copies, contact the
sender and delete the material from any computer.
How do I determine which user actually
created a table?
I have a table named ???? under schema
A. I have numerous schemas/users that have CREATE ANY
TABLE system privilege. I have the OBJECT_ID of the
How do I join USER$, TAB$, and
so on to find the actual user that created the table?
Windows Live Hotmail now works up to 70% faster. Sign up today.