|
|||||||||||||||||||||||||||||||||||||||||||||||||||
![]() |
|
Auditing
Oracle DDL with the data dictionary When monitoring
activity within an Oracle database, the Oracle administrator needs to know
those times when a table or index was created. Oracle has a new
column called CREATED with the DBA_OBJECTS view that can be used to
display the data that an object was created. This is a critical
report in a production environment when an audit of DDL must be kept. Here is a script to
produce a great management report showing all tables and indexes that were
created within the past 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; This elegant report
gives complete statistics for all recent create DDL statements. Here
is a sample of the report. Date/time Object Object created Type Name ------------ -------------------- ------------------------------------- 02-FEB-27 07 TABLE ORACHECK_FS_TEMP 02-FEB-26 10 TABLE GL_TRANSLATION_INTERIM 02-FEB-26 10 INDEX GL_TRANSLATION_INTERIM_N1 02-FEB-26 10 SYNONYM GL_TRANSLATION_INTERIM 02-FEB-26 10 INDEX GL_POSTING_INTERIM_N1 02-FEB-26 10 SYNONYM GL_POSTING_INTERIM 02-FEB-26 10 INDEX GL_POSTING_INTERIM_N2 02-FEB-26 10 TABLE GL_POSTING_INTERIM 02-FEB-25 08 PROCEDURE MC_LOAD_RESOURCE 02-FEB-20 15 VIEW MC_MPS_BREAKUP 02-FEB-15 13 DATABASE LINK TEST2.WORLD 02-FEB-15 12 DATABASE LINK TEST.WORLD If you like Oracle
tuning, you might enjoy my latest book “Oracle Tuning: The DefinitiveReference” by Rampant TechPress. (I don’t think
it is right to charge a fortune for books!) and you can buy it right now
at this link: http://www.rampant-books.com/book_1002_oracle_tuning_definitive_reference_2nd_ed.htm
|
|
||||||||||||||||||||||||||||||||||||||||||||||||
|