CODEWITHSUNDEEP
oracle-database
maseed ilyas
maseed ilyas

Reputation: 1

Hi all, Is there a query driven way to find out who created a particular table in oracle?

I have a specific schema in which i need to find who created certain tables. I already have table created date from dba_objects but i also need the username who created the table. Is there anyway to get this information through a query in oracle.

Upvotes: 0

Views: 970

Answers (1)

MplsDBA
MplsDBA

Reputation: 86

You cannot identify the user who issued the create table statement retroactively unless you have previously enabled Unified Auditing and have the ORA_SECURECONFIG policy enabled:

select * from audit_unified_enabled_policies where policy_name = 'ORA_SECURECONFIG';

If it is enabled, simply look at unified_audit_trail, it should have the data you are looking for:

select dbusername 
from unified_audit_trail
where action_name = 'CREATE TABLE' 
and object_schema = '<target_schema>' 
and object_name = '<tablename>';

This policy I believe is not enabled by default but can easily be enabled for future needs:

audit policy ora_secureconfig;

Upvotes: 2

Related Questions