This discussion is archived
1 2 3 Previous Next 34 Replies Latest reply: Oct 21, 2013 3:52 AM by yxes2013 Go to original post RSS
  • 16. Re: scn_to_timestamp(DBMS_FLASHBACK.GET_SYSTEM_CHANGE_NUMBER)
    Justin Cave Oracle ACE
    Currently Being Moderated

    I should also point out that if you only care about the date at which the row was inserted (rather than tracking the date at which the row was modified), you don't even need a trigger.  You can simply add a column with a DEFAULT value

     

    SQL> create table foo( col1 number, insert_date date default sysdate );
    Table created.
    SQL> insert into foo( col1 ) values( 1 );
    1 row created.
    SQL> select * from foo;
          COL1 INSERT_DA
    ---------- ---------
             1 21-OCT-13

     

    Justin

  • 17. Re: scn_to_timestamp(DBMS_FLASHBACK.GET_SYSTEM_CHANGE_NUMBER)
    Hemant K Chitale Oracle ACE
    Currently Being Moderated

    ROWDEPENDENCIES is a table attribute.  You can see if it has been enabled by running DBMS_METADATA.GET_DDL.

     

    Example :  The second table does not have ROWDEPENDENCIES enabled.

     

    SQL> create table hkc_test_emp (emp_id number, emp_name varchar2(30)) rowdependencies;

    Table created.

    SQL> set long 100000
    SQL> select dbms_metadata.get_ddl('TABLE','HKC_TEST_EMP') from dual;

    DBMS_METADATA.GET_DDL('TABLE','HKC_TEST_EMP')
    --------------------------------------------------------------------------------

      CREATE TABLE "HEMANT"."HKC_TEST_EMP"
       (    "EMP_ID" NUMBER,
            "EMP_NAME" VARCHAR2(30)
       ) SEGMENT CREATION DEFERRED
      PCTFREE 10 PCTUSED 40 INITRANS 1 MAXTRANS 255
    NOCOMPRESS LOGGING
      TABLESPACE "HEMANT" ROWDEPENDENCIES


    1 row selected.

    SQL>

    SQL> create table hkc_test_emp_norowd (emp_id number, emp_name varchar2(30));

    Table created.

    SQL> select dbms_metadata.get_ddl('TABLE','HKC_TEST_EMP_NOROWD') from dual;

    DBMS_METADATA.GET_DDL('TABLE','HKC_TEST_EMP_NOROWD')
    --------------------------------------------------------------------------------

      CREATE TABLE "HEMANT"."HKC_TEST_EMP_NOROWD"
       (    "EMP_ID" NUMBER,
            "EMP_NAME" VARCHAR2(30)
       ) SEGMENT CREATION DEFERRED
      PCTFREE 10 PCTUSED 40 INITRANS 1 MAXTRANS 255
    NOCOMPRESS LOGGING
      TABLESPACE "HEMANT"


    1 row selected.

    SQL>

     

    Hemant K Chitale

  • 18. Re: scn_to_timestamp(DBMS_FLASHBACK.GET_SYSTEM_CHANGE_NUMBER)
    Hemant K Chitale Oracle ACE
    Currently Being Moderated

    ORA_ROWSCN relies on the *commit* time, not the INSERT time.

     

    Another thing you must note is that SCN_TO_TIMESTAMP is always an approximation. (according to the documentation : "The usual precision of the result value is 3 seconds")

     

    EMP_IDs 1 and 2, inserted at different times in the session are committed together.  Similarly, EMP_IDs 3 and 4 are inserted at different times but commited together.

     

    SQL> insert into hkc_test_emp values (1,'HEMANT') ;

    1 row created.

    SQL> insert into hkc_test_emp values (2,'LARRY E') ;

    1 row created.

    SQL> commit;

    Commit complete.

    SQL> select scn_To_timestamp(ora_rowscn), emp_id, emp_name from hkc_test_emp;

    SCN_TO_TIMESTAMP(ORA_ROWSCN)                                                    EMP_ID EMP_NAME
    --------------------------------------------------------------------------- ---------- --------------
    21-OCT-13 01.50.02.000000000 PM                                                      1 HEMANT
    21-OCT-13 01.50.02.000000000 PM                                                      2 LARRY E

    2 rows selected.

     

    SQL> set time on
    13:50:40 SQL>
    13:50:46 SQL>
    13:50:58 SQL>
    13:51:08 SQL> insert into hkc_test_emp values (3,'ABC');

    1 row created.

    13:51:20 SQL>
    13:52:26 SQL> insert into hkc_test_emp values (4,'XYZ');

    1 row created.

    13:52:39 SQL>
    13:53:10 SQL> commit;

    Commit complete.

    13:53:14 SQL> select scn_To_timestamp(ora_rowscn), emp_id, emp_name from hkc_test_emp order by emp_id;

    SCN_TO_TIMESTAMP(ORA_ROWSCN)                                                    EMP_ID EMP_NAME
    --------------------------------------------------------------------------- ---------- -------------------
    21-OCT-13 01.50.02.000000000 PM                                                      1 HEMANT
    21-OCT-13 01.50.02.000000000 PM                                                      2 LARRY E
    21-OCT-13 01.53.11.000000000 PM                                                      3 ABC
    21-OCT-13 01.53.11.000000000 PM                                                      4 XYZ

    4 rows selected.

    13:53:20 SQL>

     

    Hemant K Chitale

  • 19. Re: scn_to_timestamp(DBMS_FLASHBACK.GET_SYSTEM_CHANGE_NUMBER)
    yxes2013 Newbie
    Currently Being Moderated

    Thanks Justin,

     

    But we are not allowed to touch the table as this belongs to a 3rd party apps. Any error resulting to alteration of their tables will not be supported by them.

  • 20. Re: scn_to_timestamp(DBMS_FLASHBACK.GET_SYSTEM_CHANGE_NUMBER)
    yxes2013 Newbie
    Currently Being Moderated

    Thanks Hemant Sir,

     

     

     

     

    SQL> select dbms_metadata.get_ddl('TABLE','EMP','HR') from dual;

     

     

    DBMS_METADATA.GET_DDL('TABLE','EMP','HR')

    --------------------------------------------------------------------------------

     

     

      CREATE TABLE "HR"."EMP"

       (    "MSGTYPE" NUMBER(*,0),

            "FLIPPED_MSG

     

     

    Why is not displaying everything?

     

     

    By the way is still can not understand this:

     

    SCOTT@MYDB> select ora_rowscn,empno,ename from emp;

     

     

    ORA_ROWSCN      EMPNO ENAME

    ---------- ---------- ----------

       4443685       7369 SMITH

       4443685       7499 ALLEN

       4443685       7521 WARD

       4443685       7566 JONES

       4443685       7654 MARTIN

       4443685       7698 BLAKE

       4443685       7782 CLARK

       4443685       7788 SCOTT

       4443685       7839 KING

       4443685       7844 TURNER

       4443685       7876 ADAMS

       4443685       7900 JAMES

       4443685       7902 FORD

       4443685       7934 MILLER

     

    If my first query ends up with 'JONES'. How do I query next 'MARTIN' and up?

     

    Thanks...

  • 21. Re: scn_to_timestamp(DBMS_FLASHBACK.GET_SYSTEM_CHANGE_NUMBER)
    Nicolas.Gasparotto Oracle ACE
    Currently Being Moderated

    TO ALL HERE STOP FEEDING THE TROLL, YXES IS REFUSING TO READ PROPERLY WHAT WAS PREVIOUSLY SUGGESTED, THIS VERY DISCUSSION IS JUST YET AN OTHER PROOF (IF IT WAS NEEDED) AND A FOLLOWING OF PREVIOUS THREAD OF LAST WEEK:

    https://forums.oracle.com/thread/2594218

    OUT THERE, I POINTED OUT A BLOG ENTRY WHICH EXPLAINED VERY WELL THE CASE IS ASKING ABOUT RIGHT NOW !!!!!!!!!!!!!!!!!!!!!!!!!!

     

    YXES,

    !!!!!!!!!!!!!!! STOP IT NOW !!!!!!!!!!!!!!!

     

    Nicolas.

  • 22. Re: scn_to_timestamp(DBMS_FLASHBACK.GET_SYSTEM_CHANGE_NUMBER)
    Hemant K Chitale Oracle ACE
    Currently Being Moderated

    DBMS_METADATA.GET_DDL returns a CLOB.  In sqlplus, you have to SET LONG to a large value  to see the output.

     

    Hemant K Chitale

  • 23. Re: scn_to_timestamp(DBMS_FLASHBACK.GET_SYSTEM_CHANGE_NUMBER)
    yxes2013 Newbie
    Currently Being Moderated

    Ok fine, stopping now, Im afraid....   Before you can lock the thread

  • 24. Re: scn_to_timestamp(DBMS_FLASHBACK.GET_SYSTEM_CHANGE_NUMBER)
    yxes2013 Newbie
    Currently Being Moderated

    Oh yep I forgot set long 10000

    select dbms_metadata.get_ddl('TABLE','EMP','HR') from dual

     

    CREATE TABLE "HR"."EMP"                                            

       ( "MSGTYPE" NUMBER(*,0),                                                    

      "FLIPPED_MSGTYPE" NUMBER(*,0),  

    ) SEGMENT CREATION IMMEDIATE                                                

      PCTFREE 10 PCTUSED 40 INITRANS 1 MAXTRANS 255                                

    NOCOMPRESS LOGGING                                                            

      STORAGE(INITIAL 524288000 NEXT 524288000 MINEXTENTS 1 MAXEXTENTS 2147483645  

      PCTINCREASE 0 FREELISTS 1 FREELIST GROUPS 1                                  

      BUFFER_POOL DEFAULT FLASH_CACHE DEFAULT CELL_FLASH_CACHE DEFAULT)            

      TABLESPACE "USERS"   

     

     

    So no dependencies right?

  • 25. Re: scn_to_timestamp(DBMS_FLASHBACK.GET_SYSTEM_CHANGE_NUMBER)
    Nicolas.Gasparotto Oracle ACE
    Currently Being Moderated

    yxes2013 wrote:

     

    Ok fine, stopping now, Im afraid....   Before you can lock the thread

    You've been warned by the admin last week, don't you ?

    https://forums.oracle.com/message/11232922#11232922

     

    Nicolas.

  • 26. Re: scn_to_timestamp(DBMS_FLASHBACK.GET_SYSTEM_CHANGE_NUMBER)
    Hemant K Chitale Oracle ACE
    Currently Being Moderated

    Yes, you do not have ROWDEPENDENCIES set.   (You cannot isssue an ALTER TABLE to enable this either, it must be set when the table is created -- it adds the SCN to the row so it adds to the row length). Therefore, you cannot track row-level commit SCNs.

     

    Hemant  K Chitale

  • 27. Re: scn_to_timestamp(DBMS_FLASHBACK.GET_SYSTEM_CHANGE_NUMBER)
    Nicolas.Gasparotto Oracle ACE
    Currently Being Moderated

    HemantKChitale wrote:

     

    Yes, you do not have ROWDEPENDENCIES set.   (You cannot isssue an ALTER TABLE to enable this either, it must be set when the table is created -- it adds the SCN to the row so it adds to the row length). Therefore, you cannot track row-level commit SCNs.

     

    Hemant  K Chitale

    Sadly, that is well explained in the link provided in his thread of last week which obviously he refused again to read.

     

    Nicolas.

  • 28. Re: scn_to_timestamp(DBMS_FLASHBACK.GET_SYSTEM_CHANGE_NUMBER)
    yxes2013 Newbie
    Currently Being Moderated

    Never heard of that row dependency thing

     

    I can not understand, WHY did not Oracle make it mandatory by default when creating a table? when is it a very important tool?

  • 29. Re: scn_to_timestamp(DBMS_FLASHBACK.GET_SYSTEM_CHANGE_NUMBER)
    yxes2013 Newbie
    Currently Being Moderated

    Hi Nic,

     

    You've been warned by the admin last week, don't you ?

    https://forums.oracle.com/message/11232922#11232922

     

    Sorry I missed the point again. I thought the ones being warned is Sb that is why he is not commenting here anymore

     

    I did not see there any warn for me or maybe did not understand what how it was said?

     

    Thanks...

Legend

  • Correct Answers - 10 points
  • Helpful Answers - 5 points