CODEWITHSUNDEEP
oracle-database
Hany
Hany

Reputation: 1136

Get last DDL time for Oracle table in different Schema

I am trying to find the time of the last DDL statement that has been applied on a table.

I found this solution:

Select OBJECT_NAME, LAST_DDL_TIME
From user_objects
Where OBJECT_NAME='MY_TABLE'

The problem is: I want to check this for a table which doesn't belong to my Schema.

Any suggestion please

Upvotes: 10

Views: 76417

Answers (1)

Justin Cave
Justin Cave

Reputation: 231651

Assuming you have permissions, you would just need to query the ALL_OBJECTS or DBA_OBJECTS view, i.e.

SELECT object_name, object_type, last_ddl_time
  FROM dba_objects (or all_objects)
 WHERE owner = <<owner of table>>
   AND object_name = 'MY_TABLE'

ALL_OBJECTS has information about all the objects that you have privileges on (i.e. the tables you can at least SELECT from). DBA_OBJECTS has information about all the objects in the database whether you have permission to access them or not. However, access to the DBA_OBJECTS view requires additional privileges.

Upvotes: 18

Related Questions