Re: Determine Record Creation Date Without Audit Turned On
- From: "Rich Jesse" <rjoralist@xxxxxxxxxxxxxxxxxxxxx>
- To: "oracle-l" <oracle-l@xxxxxxxxxxxxx>
- Date: Thu, 19 Jun 2008 12:02:17 -0500 (CDT)
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@xxxxxx>
> 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
>
--
//www.freelists.org/webpage/oracle-l
Other related posts:
