1 2 3 Previous Next 34 Replies Latest reply: Oct 21, 2013 5:52 AM by yxes2013 Go to original post RSS
      • 16. Re: scn_to_timestamp(DBMS_FLASHBACK.GET_SYSTEM_CHANGE_NUMBER)
        JustinCave

        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

          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

            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

              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

                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

                  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

                    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

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

                      • 24. Re: scn_to_timestamp(DBMS_FLASHBACK.GET_SYSTEM_CHANGE_NUMBER)
                        yxes2013

                        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

                          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

                            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

                              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

                                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

                                  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...