Using script to find DDL statement statistics

When monitoring the behavior of an Oracle database, the database administrator needs to know the details of the creation of a table or index, such as dates and times.

In the Dba_objects view, Oracle provides a new column called created that administrators can use to display the data at the time of object creation. This column can be used as an authentication report when the DDL statements are required to be audited in actual development.

The following script generates a good management report that shows all the tables and indexes that were created in the last 14 days.

Alter session set nls_date_format= ' Yy-mon-dd HH24 ';
Set pages 999

Column C1 heading ' date/time|created '
Column C2 heading ' object| Type ' Format A20
Column C3 heading ' object| Name ' Format A40

Select
Created C1,
Object_type C2,
object_name C3
From
Dba_objects
where
Created > sysdate-14
ORDER BY
Created Desc;

The following report returns the complete statistics for the most recently created DDL statement. The following is a sample report.

Date/time Object
Created Type Name
------------     --------------------                   ---------------------------
02-feb-27 Modified TABLE Oracheck_fs_temp
02-feb-26 TABLE Gl_translation_interim
02-feb-26 INDEX gl_translation_interim_n1
02-feb-26 synonym Gl_translation_interim
02-feb-26 INDEX gl_posting_interim_n1
02-feb-26 synonym Gl_posting_interim
02-feb-26 INDEX gl_posting_interim_n2
02-feb-26 TABLE Gl_posting_interim
02-feb-25 PROCEDURE Mc_load_resource
02-feb-20 VIEW Mc_mps_breakup
02-feb-15 DATABASE LINK TEST2. World
02-feb-15 DATABASE LINK TEST. World