My good old DEPT table always had 4 rows with 4\u00a0different departments. However, I have noticed that a new row was inserted into the DEPT table and the row 50 popped up, and I would like to know when it happened?<\/p>\n
Before:<\/p>\n
SQL> select DEPTNO, DNAME, LOC from DEPT;\n\n DEPTNO DNAME LOC\n---------- -------------- -------------\n 10 ACCOUNTING NEW YORK\n 20 RESEARCH DALLAS\n 30 SALES CHICAGO\n 40 OPERATIONS BOSTON<\/pre>\nAfter:<\/p>\n
SQL> select DEPTNO, DNAME, LOC from DEPT;\n\n DEPTNO DNAME LOC\n---------- -------------- -------------\n 10 ACCOUNTING NEW YORK\n 20 RESEARCH DALLAS\n 30 SALES CHICAGO\n 40 OPERATIONS BOSTON\n 50 IT L.A.<\/pre>\nUnfortunately, I have no auditing in place, and supplemental logging is also not activated. So, auditing is not an option and LogMiner also not. I’m kind of running out of options to get the timestamp when the department 50 was inserted.<\/p>\n
My last resort is the ORA_ROWSCN pseudocolumn. Due to the Oracle documentation, for each row, ORA_ROWSCN returns the conservative upper bound system change number (SCN) of the most recent change to the row. This pseudocolumn becomes useful, in case we want to determine approximately when a row was last updated.<\/p>\n
ORA_ROWSCN is a pseudocolumn of any table that is not fixed or external, and be careful, it is not 100% precise, because Oracle tracks SCNs by transaction committed for the block in which the row resides. However, it is better then nothing for my use case.<\/p>\n
SQL> select ora_rowscn, DEPTNO, DNAME, LOC from DEPT;\n\nORA_ROWSCN DEPTNO DNAME LOC\n---------- ---------- -------------- -------------\n 9708226 10 ACCOUNTING NEW YORK\n 9708226 20 RESEARCH DALLAS\n 9708226 30 SALES CHICAGO\n 9708226 40 OPERATIONS BOSTON\n 9708226 50 IT L.A.<\/pre>\nTo convert now the SCN to a Timestamp, we can use the SCN_TO_TIMESTAMP function. It converts the SCN to a Timestamp with a precision of +\/- 3 seconds.<\/p>\n
SQL> select scn_to_timestamp(9708226) as timestamp from dual;\n\nTIMESTAMP\n---------------------------------------------------------------------------\n27-OCT-16 11.40.07.000000000 AM<\/pre>\nDue to the ORA_ROWSCN pseudocolumn, now I know at least, that the block where the department 50 was inserted was changed at the “27-OCT-16 11.40.07”.<\/p>\n
If you want it more accurate, you need a feature called Row Level Dependency Tracking. This feature is activated by the keyword ROWDEPENDENCIES during the CREATE TABLE.<\/p>\n
CREATE TABLE \"SCOTT\".\"DEPT_RLDT\" \n (\t\"DEPTNO\" NUMBER(2,0), \n\t\"DNAME\" VARCHAR2(14 BYTE), \n\t\"LOC\" VARCHAR2(13 BYTE), \n\t CONSTRAINT \"PK_DEPT_RLDT\" PRIMARY KEY (\"DEPTNO\")\n USING INDEX PCTFREE 10 INITRANS 2 MAXTRANS 255 \n STORAGE(INITIAL 65536 NEXT 1048576 MINEXTENTS 1 MAXEXTENTS 2147483645\n PCTINCREASE 0 FREELISTS 1 FREELIST GROUPS 1\n BUFFER_POOL DEFAULT FLASH_CACHE DEFAULT CELL_FLASH_CACHE DEFAULT)\n TABLESPACE \"USERS\" ENABLE\n ) SEGMENT CREATION IMMEDIATE \n PCTFREE 10 PCTUSED 40 INITRANS 1 MAXTRANS 255 \n NOCOMPRESS LOGGING\n STORAGE(INITIAL 65536 NEXT 1048576 MINEXTENTS 1 MAXEXTENTS 2147483645\n PCTINCREASE 0 FREELISTS 1 FREELIST GROUPS 1\n BUFFER_POOL DEFAULT FLASH_CACHE DEFAULT CELL_FLASH_CACHE DEFAULT)\n TABLESPACE \"USERS\" ROWDEPENDENCIES ;<\/pre>\nWith this feature, each row in the table has a system change number (SCN) that represents a time greater than or equal to the commit time of the last transaction that modified the row.<\/p>\n
You cannot change this setting after the table is created. Meaning, there is no “alter table … ROWDEPENDENCIES”.<\/p>\n
This setting is useful primarily to allow for parallel propagation in replication environments, however, it can also be used to find out quickly the time, when a row was change.<\/p>\n
Like always in life, nothing comes for free. The drawback is, that it increases the size of each row by 6 bytes. That’s why the default is NOROWDEPENDENCIES.<\/p>\n
But once this feature is enable, like in the following example for my DEPT_RLDT table, you will get
\na different SCN number for the new commited row (department 50).<\/p>\nSQL> select ora_rowscn, DEPTNO, DNAME, LOC from DEPT_RLDT;\n\nORA_ROWSCN DEPTNO DNAME LOC\n---------- ---------- -------------- -------------\n 9701315 10 ACCOUNTING NEW YORK\n 9701315 20 RESEARCH DALLAS\n 9701315 30 SALES CHICAGO\n 9701315 40 OPERATIONS BOSTON\n 9708244 50 IT L.A.\n\nSQL><\/pre>\nSCN 9701315 for deptno 10,20,30,40 and 9708244 for deptno 50. That’s cool.<\/p>\n
SQL> select scn_to_timestamp(9708244) as timestamp from dual;\n\nTIMESTAMP\n---------------------------------------------------------------------------\n27-OCT-16 11.40.24.000000000 AM<\/pre>\nDue to the SCN to a Timestamp precision of +\/- 3 seconds, it is still not 100%, but very close to it. Another important point is\u00a0that the ORA_ROWSCN represents\u00a0an upper bound SCN. We just know it has not changed after this SCN but may have been changed earlier.<\/p>\n
So … does the\u00a0ORA_ROWSCN replace auditing? No, not at all. But it can help to answer questions like the following: Please tell me when ROW xyz has been modified the last time and recover the table (using Flashback techniques) to that point.
\nCheers,
\nWilliam<\/p>\n","protected":false},"excerpt":{"rendered":"By William Sescu My good old DEPT table always had 4 rows with 4\u00a0different departments. However, I have noticed that a new row was inserted into the DEPT table and the row 50 popped up, and I would like to know when it happened? Before: SQL> select DEPTNO, DNAME, LOC from DEPT; DEPTNO DNAME LOC […]<\/p>\n","protected":false},"author":27,"featured_media":0,"comment_status":"open","ping_status":"open","sticky":false,"template":"","format":"standard","meta":{"_acf_changed":false,"footnotes":""},"categories":[229],"tags":[209],"type_dbi":[],"class_list":["post-9184","post","type-post","status-publish","format-standard","hentry","category-database-administration-monitoring","tag-oracle-12c"],"acf":[],"yoast_head":"\n
Oracle 12c - When did my row change? - dbi Blog<\/title>\n<meta name=\"robots\" content=\"index, follow, max-snippet:-1, max-image-preview:large, max-video-preview:-1\" \/>\n<link rel=\"canonical\" href=\"https:\/\/www.dbi-services.com\/blog\/oracle-12c-when-did-my-row-change\/\" \/>\n<meta property=\"og:locale\" content=\"en_US\" \/>\n<meta property=\"og:type\" content=\"article\" \/>\n<meta property=\"og:title\" content=\"Oracle 12c - When did my row change?\" \/>\n<meta property=\"og:description\" content=\"By William Sescu My good old DEPT table always had 4 rows with 4\u00a0different departments. However, I have noticed that a new row was inserted into the DEPT table and the row 50 popped up, and I would like to know when it happened? Before: SQL> select DEPTNO, DNAME, LOC from DEPT; DEPTNO DNAME LOC […]\" \/>\n<meta property=\"og:url\" content=\"https:\/\/www.dbi-services.com\/blog\/oracle-12c-when-did-my-row-change\/\" \/>\n<meta property=\"og:site_name\" content=\"dbi Blog\" \/>\n<meta property=\"article:published_time\" content=\"2016-10-28T04:51:31+00:00\" \/>\n<meta name=\"author\" content=\"Oracle Team\" \/>\n<meta name=\"twitter:card\" content=\"summary_large_image\" \/>\n<meta name=\"twitter:label1\" content=\"Written by\" \/>\n\t<meta name=\"twitter:data1\" content=\"Oracle Team\" \/>\n\t<meta name=\"twitter:label2\" content=\"Est. reading time\" \/>\n\t<meta name=\"twitter:data2\" content=\"4 minutes\" \/>\n<script type=\"application\/ld+json\" class=\"yoast-schema-graph\">{\"@context\":\"https:\/\/schema.org\",\"@graph\":[{\"@type\":\"Article\",\"@id\":\"https:\/\/www.dbi-services.com\/blog\/oracle-12c-when-did-my-row-change\/#article\",\"isPartOf\":{\"@id\":\"https:\/\/www.dbi-services.com\/blog\/oracle-12c-when-did-my-row-change\/\"},\"author\":{\"name\":\"Oracle Team\",\"@id\":\"https:\/\/www.dbi-services.com\/blog\/#\/schema\/person\/66ab87129f2d357f09971bc7936a77ee\"},\"headline\":\"Oracle 12c – When did my row change?\",\"datePublished\":\"2016-10-28T04:51:31+00:00\",\"mainEntityOfPage\":{\"@id\":\"https:\/\/www.dbi-services.com\/blog\/oracle-12c-when-did-my-row-change\/\"},\"wordCount\":512,\"commentCount\":0,\"keywords\":[\"Oracle 12c\"],\"articleSection\":[\"Database Administration & Monitoring\"],\"inLanguage\":\"en-US\",\"potentialAction\":[{\"@type\":\"CommentAction\",\"name\":\"Comment\",\"target\":[\"https:\/\/www.dbi-services.com\/blog\/oracle-12c-when-did-my-row-change\/#respond\"]}]},{\"@type\":\"WebPage\",\"@id\":\"https:\/\/www.dbi-services.com\/blog\/oracle-12c-when-did-my-row-change\/\",\"url\":\"https:\/\/www.dbi-services.com\/blog\/oracle-12c-when-did-my-row-change\/\",\"name\":\"Oracle 12c - When did my row change? - dbi Blog\",\"isPartOf\":{\"@id\":\"https:\/\/www.dbi-services.com\/blog\/#website\"},\"datePublished\":\"2016-10-28T04:51:31+00:00\",\"author\":{\"@id\":\"https:\/\/www.dbi-services.com\/blog\/#\/schema\/person\/66ab87129f2d357f09971bc7936a77ee\"},\"breadcrumb\":{\"@id\":\"https:\/\/www.dbi-services.com\/blog\/oracle-12c-when-did-my-row-change\/#breadcrumb\"},\"inLanguage\":\"en-US\",\"potentialAction\":[{\"@type\":\"ReadAction\",\"target\":[\"https:\/\/www.dbi-services.com\/blog\/oracle-12c-when-did-my-row-change\/\"]}]},{\"@type\":\"BreadcrumbList\",\"@id\":\"https:\/\/www.dbi-services.com\/blog\/oracle-12c-when-did-my-row-change\/#breadcrumb\",\"itemListElement\":[{\"@type\":\"ListItem\",\"position\":1,\"name\":\"Accueil\",\"item\":\"https:\/\/www.dbi-services.com\/blog\/\"},{\"@type\":\"ListItem\",\"position\":2,\"name\":\"Oracle 12c – When did my row change?\"}]},{\"@type\":\"WebSite\",\"@id\":\"https:\/\/www.dbi-services.com\/blog\/#website\",\"url\":\"https:\/\/www.dbi-services.com\/blog\/\",\"name\":\"dbi Blog\",\"description\":\"\",\"potentialAction\":[{\"@type\":\"SearchAction\",\"target\":{\"@type\":\"EntryPoint\",\"urlTemplate\":\"https:\/\/www.dbi-services.com\/blog\/?s={search_term_string}\"},\"query-input\":{\"@type\":\"PropertyValueSpecification\",\"valueRequired\":true,\"valueName\":\"search_term_string\"}}],\"inLanguage\":\"en-US\"},{\"@type\":\"Person\",\"@id\":\"https:\/\/www.dbi-services.com\/blog\/#\/schema\/person\/66ab87129f2d357f09971bc7936a77ee\",\"name\":\"Oracle Team\",\"image\":{\"@type\":\"ImageObject\",\"inLanguage\":\"en-US\",\"@id\":\"https:\/\/secure.gravatar.com\/avatar\/f711f7cd2c9b09bf2627133755b569fb5be0694810cfd33033bdd095fedba86d?s=96&d=mm&r=g\",\"url\":\"https:\/\/secure.gravatar.com\/avatar\/f711f7cd2c9b09bf2627133755b569fb5be0694810cfd33033bdd095fedba86d?s=96&d=mm&r=g\",\"contentUrl\":\"https:\/\/secure.gravatar.com\/avatar\/f711f7cd2c9b09bf2627133755b569fb5be0694810cfd33033bdd095fedba86d?s=96&d=mm&r=g\",\"caption\":\"Oracle Team\"},\"url\":\"https:\/\/www.dbi-services.com\/blog\/author\/oracle-team\/\"}]}<\/script>\n<!-- \/ Yoast SEO Premium plugin. -->","yoast_head_json":{"title":"Oracle 12c - When did my row change? - dbi Blog","robots":{"index":"index","follow":"follow","max-snippet":"max-snippet:-1","max-image-preview":"max-image-preview:large","max-video-preview":"max-video-preview:-1"},"canonical":"https:\/\/www.dbi-services.com\/blog\/oracle-12c-when-did-my-row-change\/","og_locale":"en_US","og_type":"article","og_title":"Oracle 12c - When did my row change?","og_description":"By William Sescu My good old DEPT table always had 4 rows with 4\u00a0different departments. However, I have noticed that a new row was inserted into the DEPT table and the row 50 popped up, and I would like to know when it happened? Before: SQL> select DEPTNO, DNAME, LOC from DEPT; DEPTNO DNAME LOC […]","og_url":"https:\/\/www.dbi-services.com\/blog\/oracle-12c-when-did-my-row-change\/","og_site_name":"dbi Blog","article_published_time":"2016-10-28T04:51:31+00:00","author":"Oracle Team","twitter_card":"summary_large_image","twitter_misc":{"Written by":"Oracle Team","Est. reading time":"4 minutes"},"schema":{"@context":"https:\/\/schema.org","@graph":[{"@type":"Article","@id":"https:\/\/www.dbi-services.com\/blog\/oracle-12c-when-did-my-row-change\/#article","isPartOf":{"@id":"https:\/\/www.dbi-services.com\/blog\/oracle-12c-when-did-my-row-change\/"},"author":{"name":"Oracle Team","@id":"https:\/\/www.dbi-services.com\/blog\/#\/schema\/person\/66ab87129f2d357f09971bc7936a77ee"},"headline":"Oracle 12c – When did my row change?","datePublished":"2016-10-28T04:51:31+00:00","mainEntityOfPage":{"@id":"https:\/\/www.dbi-services.com\/blog\/oracle-12c-when-did-my-row-change\/"},"wordCount":512,"commentCount":0,"keywords":["Oracle 12c"],"articleSection":["Database Administration & Monitoring"],"inLanguage":"en-US","potentialAction":[{"@type":"CommentAction","name":"Comment","target":["https:\/\/www.dbi-services.com\/blog\/oracle-12c-when-did-my-row-change\/#respond"]}]},{"@type":"WebPage","@id":"https:\/\/www.dbi-services.com\/blog\/oracle-12c-when-did-my-row-change\/","url":"https:\/\/www.dbi-services.com\/blog\/oracle-12c-when-did-my-row-change\/","name":"Oracle 12c - When did my row change? - dbi Blog","isPartOf":{"@id":"https:\/\/www.dbi-services.com\/blog\/#website"},"datePublished":"2016-10-28T04:51:31+00:00","author":{"@id":"https:\/\/www.dbi-services.com\/blog\/#\/schema\/person\/66ab87129f2d357f09971bc7936a77ee"},"breadcrumb":{"@id":"https:\/\/www.dbi-services.com\/blog\/oracle-12c-when-did-my-row-change\/#breadcrumb"},"inLanguage":"en-US","potentialAction":[{"@type":"ReadAction","target":["https:\/\/www.dbi-services.com\/blog\/oracle-12c-when-did-my-row-change\/"]}]},{"@type":"BreadcrumbList","@id":"https:\/\/www.dbi-services.com\/blog\/oracle-12c-when-did-my-row-change\/#breadcrumb","itemListElement":[{"@type":"ListItem","position":1,"name":"Accueil","item":"https:\/\/www.dbi-services.com\/blog\/"},{"@type":"ListItem","position":2,"name":"Oracle 12c – When did my row change?"}]},{"@type":"WebSite","@id":"https:\/\/www.dbi-services.com\/blog\/#website","url":"https:\/\/www.dbi-services.com\/blog\/","name":"dbi Blog","description":"","potentialAction":[{"@type":"SearchAction","target":{"@type":"EntryPoint","urlTemplate":"https:\/\/www.dbi-services.com\/blog\/?s={search_term_string}"},"query-input":{"@type":"PropertyValueSpecification","valueRequired":true,"valueName":"search_term_string"}}],"inLanguage":"en-US"},{"@type":"Person","@id":"https:\/\/www.dbi-services.com\/blog\/#\/schema\/person\/66ab87129f2d357f09971bc7936a77ee","name":"Oracle Team","image":{"@type":"ImageObject","inLanguage":"en-US","@id":"https:\/\/secure.gravatar.com\/avatar\/f711f7cd2c9b09bf2627133755b569fb5be0694810cfd33033bdd095fedba86d?s=96&d=mm&r=g","url":"https:\/\/secure.gravatar.com\/avatar\/f711f7cd2c9b09bf2627133755b569fb5be0694810cfd33033bdd095fedba86d?s=96&d=mm&r=g","contentUrl":"https:\/\/secure.gravatar.com\/avatar\/f711f7cd2c9b09bf2627133755b569fb5be0694810cfd33033bdd095fedba86d?s=96&d=mm&r=g","caption":"Oracle Team"},"url":"https:\/\/www.dbi-services.com\/blog\/author\/oracle-team\/"}]}},"_links":{"self":[{"href":"https:\/\/www.dbi-services.com\/blog\/wp-json\/wp\/v2\/posts\/9184","targetHints":{"allow":["GET"]}}],"collection":[{"href":"https:\/\/www.dbi-services.com\/blog\/wp-json\/wp\/v2\/posts"}],"about":[{"href":"https:\/\/www.dbi-services.com\/blog\/wp-json\/wp\/v2\/types\/post"}],"author":[{"embeddable":true,"href":"https:\/\/www.dbi-services.com\/blog\/wp-json\/wp\/v2\/users\/27"}],"replies":[{"embeddable":true,"href":"https:\/\/www.dbi-services.com\/blog\/wp-json\/wp\/v2\/comments?post=9184"}],"version-history":[{"count":0,"href":"https:\/\/www.dbi-services.com\/blog\/wp-json\/wp\/v2\/posts\/9184\/revisions"}],"wp:attachment":[{"href":"https:\/\/www.dbi-services.com\/blog\/wp-json\/wp\/v2\/media?parent=9184"}],"wp:term":[{"taxonomy":"category","embeddable":true,"href":"https:\/\/www.dbi-services.com\/blog\/wp-json\/wp\/v2\/categories?post=9184"},{"taxonomy":"post_tag","embeddable":true,"href":"https:\/\/www.dbi-services.com\/blog\/wp-json\/wp\/v2\/tags?post=9184"},{"taxonomy":"type","embeddable":true,"href":"https:\/\/www.dbi-services.com\/blog\/wp-json\/wp\/v2\/type_dbi?post=9184"}],"curies":[{"name":"wp","href":"https:\/\/api.w.org\/{rel}","templated":true}]}}