dba_objects has three columns that store date-related information: | Name | Data type | Comment |
created | date | Corresponds to the point in time when the corresponding objects was created. |
last_ddl_time | date | Last modification (DDL statement) of the object or dependent objects. This includes grants and revokes |
timestamp | varchar2 | Last modification of the structure (specification) of the object. In the case of a view, the structure is considered changed if the number, data types, names (or order of columns?) of the returned columns change. |
created and last_ddl_time is date, the datatype of timestamp is varchar2 (!) in the format YYYY-MM-DD:HH24:MI:SS. select
obj.object_name,
obj.subobject_name,
obj.object_type,
obj.created,
obj.last_ddl_time,
to_date(obj.timestamp, 'YYYY-MM-DD:HH24:MI:SS') timestamp_,
obj.status
from
user_objects obj
where
-- obj.object_name like '%FOOBAR%' and
obj.object_name not like 'TQ84%' and
obj.object_name not like 'ERR$%' and
obj.object_type not in ('INDEX', 'LOB')
order by
obj.timestamp desc;
created <= timstamp and timstamp <= last_ddl_time, although there are some exceptions, especially in the SYS, SYSTEM, XDB, WMSYS and GSMADMIN_INTERNAL schemas: select
count(*),
owner,
object_type
from
dba_objects
where
to_date(timestamp, 'yyyy-mm-dd:hh24:mi:ss') not between created and last_ddl_time
group by
owner,
object_type
order by
owner,
object_type;