Set of tables – Creation and updates 1

[ddrillich]

Good Day,

I'm looking at six relatively small tables that I have no information about the way they are supposed to be updated. Can I find out when the rows got updated the last time?

Regards,
Dan

 

[SantaMufasa]

Dan,

The Oracle Data Dictionary automatically stores when a table was created or its structure last changed (DBA_OBJECTS.CREATED and LAST_DDL_TIME). In our application's tables, we have CREATED_DATE, CREATED_BY, UPDATED_DATE, and UPDATED_BY, which document WHO and WHEN the tables' rows were last changed and by whom.

If you thought ahead of time to turn on Oracle AUDITING, then entries would appear in SYS.AUD$. (As a DBA, you can DESCRIBE SYS.AUD$ and find out all about the AUD$ table with a Google of "Oracle AUDIT AUD$".

Mufasa
(aka Dave of Sandy, Utah, USA)
“People may forget what you say, but they will never forget how you made them feel.

 

[ddrillich]

Hi Mufasa,

I can't access for some reason the dba_objects view.

The following does work -

select * from all_objects where object_name like 'XMC%' and object_type = 'TABLE'

Many thanks.

Regards,
Dan

 

[SantaMufasa]

That means that you are not a member of the "DBA" role. Regardless of user, you will always have access to the "USER_*" and "ALL_*" data dictionary views.


Mufasa
(aka Dave of Sandy, Utah, USA)
“People may forget what you say, but they will never forget how you made them feel.

 

[ddrillich]

Thank you Mufasa.