Re: Determine Record Creation Date Without Audit Turned On

Unless the INSERT statements for the apps that maintain that table assume a static column list:

CREATE TABLE my_table (abc NUMBER);
INSERT INTO my_table VALUES (42);
COMMIT;
alter table my_table add ( row_create_date date default sysdate ); INSERT INTO my_table VALUES (42);

ORA-00947: not enough values

And, as I'm finding out, there are 3rd-party products that get upset when a table has it's structure altered without it's knowledge. Sometimes not upset enough to throw an error, but upset enough to mangle some data. Perhaps it's a Java thing...

Rich

> On Wed, Jun 18, 2008 at 7:44 AM, Langston, Chris <Chris.Langston_at_aa.com>
> wrote:
>
>> All,
>>
>> We have a user that needs to do cleanup on a table in a 10.2 instance
>> and wants to remove rows in a table based on when the record was created
>> but there is no creation date as part of the record entry. Without
>> having auditing turned on, is there a way to do determine this from the
>> data dictionary tables and, if so, which ones. I'm a rather new DBA and
>> not well versed in Oracle's data dictionary tables. All of my searching
>> for keeps directing me to information about auditing.
>>
>
> There's a simple way to set this up for future use.
>
> alter table my_table add ( row_create_date date default sysdate )
>
> Obviously this will not work for old data, but may be useful in the
> near future for cleaning up data.
>
> And 30 days from now, all rows with a null value for this column will be 30+
> days old.
>
> --
> Jared Still
> Certifiable Oracle DBA and Part Time Perl Evangelist
>

--
http://www.freelists.org/webpage/oracle-l