| record created date [message #58654] |
Tue, 23 September 2003 22:45  |
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 #58657 is a reply to message #58654] |
Wed, 24 September 2003 07:35  |
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 |
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 #58681 is a reply to message #58678] |
Thu, 25 September 2003 08:48 |
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
|
|
|
|
] 
Blog
Search
Help
Members
Register
Login
Home
