Home » RDBMS Server » Server Administration » record created date
record created date [message #58654] Tue, 23 September 2003 22:45 Go to next message
BIRENDER KUMAR
Messages: 18
Registered: May 2003
Junior Member
Hi expert,

Is there any way to find out record creation date from
oracle without including user define column as create_date with defalut value as sysdate.
pls in detail.

Thanx 4 ur time,
Re: record created date [message #58656 is a reply to message #58654] Wed, 24 September 2003 06:08 Go to previous messageGo to next message
Mahesh Rajendran
Messages: 10706
Registered: March 2002
Location: oracleDocoVille
Senior Member
Account Moderator
I dont think so
Re: record created date [message #58657 is a reply to message #58654] Wed, 24 September 2003 07:35 Go to previous messageGo to next message
Thiru
Messages: 1089
Registered: May 2002
Senior Member
Did you try a row level insert trigger and a log table ?
or are you totally against creating additional objects to track data inserts ?

You may also be able to track data modifications(although not in very detail) through Oracle Log_Miner.

Thiru
Re: record created date : WORKSPACE MANAGER [message #58678 is a reply to message #58654] Thu, 25 September 2003 08:19 Go to previous messageGo to next message
Thiru
Messages: 1089
Registered: May 2002
Senior Member
Kumar,
I just remembered of a feature that will do the trick,without having to create 'user defined' columns or triggers : 9i Workspace manager ofcourse !

Demo:

SQL> drop table t;

Table dropped.

SQL> create table t(x int primary key,y varchar2(10));

Table created.

SQL> execute dbms_wm.enableVersioning('T','VIEW_WO_OVERWRITE');

PL/SQL procedure successfully completed.

SQL> insert into t values(1,'test');

1 row created.

SQL> commit;

Commit complete.

SQL> update t set y='TEST';

1 row updated.

SQL> commit;

Commit complete.

SQL> select x,y,user_name,type_of_change,createtime from t_hist;

X Y
---------- ----------
USER_NAME
-----------------------------------------------------------------------------------------------------
T CREATETIM
- ---------
1 test
THIRU
I 25-SEP-03

1 TEST
THIRU
U 25-SEP-03

I=Insert, U= Update

Is'nt that fantastic ? You'll fall in love with Oracle all over again !

I havent worked with this yet,just couple of tests.I believe there are some limitations in using this.You can refer to the doc for more details. Although you are not creating additional objecs to support this auditing,versioning but Oracle creates a lot of objects,I believe to support this and obviously takes up space ,becos it not only stores the audit info,but also the actual data values.

Let me know how this works out for you.
Thiru
Re: record created date : WORKSPACE MANAGER [message #58680 is a reply to message #58678] Thu, 25 September 2003 08:25 Go to previous messageGo to next message
Mahesh Rajendran
Messages: 10706
Registered: March 2002
Location: oracleDocoVille
Senior Member
Account Moderator
Niceee!

Re: record created date : WORKSPACE MANAGER [message #58681 is a reply to message #58678] Thu, 25 September 2003 08:48 Go to previous message
Thiru
Messages: 1089
Registered: May 2002
Senior Member
Just found out that you cant drop a table that is 'version enabled'. I had to disable workspace manager versioning inorder to drop the table !

SQL> drop table t;
drop table t
*
ERROR at line 1:
ORA-00942: table or view does not exist

SQL> execute dbms_wm.DisableVersioning('T');

PL/SQL procedure successfully completed.

SQL> drop table t;

Table dropped.

Test,test and test before implementing a new feature into production..

-Thiru
Previous Topic: Listner Help...
Next Topic: Create Loggin via-commands.
Goto Forum:
  


Current Time: Fri Jan 27 14:57:48 CST 2023