Menu
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
816
Use the dbms_metadata package to get the DDL of any object of the DB. Also, You can format the output using dbms_metadata. set_transform_param . See Oracle documentation for more information on it.
The last DDL time is easy: select last_ddl_time from user_objects where object_name = :tab; As you're finding, if you've not got auditing, last DML write time is a little trickier...
LAST_DDL_TIME is the last time the table had a DDL (structure) change applied, but does NOT include DML (data).
and rownum = 1; will get the "last" record. It is the ONLY way.
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.
152
If you love us? You can donate to us via Paypal or buy me a coffee so we can maintain and grow! Thank you!
Donate Us With
