oracle

Flashback version query and the proper use of timestamp and scn clauses


Flashback version query essentially enables you to lookup the incarnations of a row (defined by primary key) in the past, in a consecutive manner. Version information is depicted by a couple of pseudo-columns, namely versions_xid, versions_startscn, versions_endscn, versions_starttime, versions_endtime and versions_operation. See Using Oracle Flashback Version Query in the docs for explanations.

In combination with flashback query or flashback transaction query, one may restore a row incarnation from the past into a new table or even rollback to a past row incarnation within the same table.

This article will discuss flashback version query together with flashback query to restore one to many rows, just shown for a row of a unique key here for brevity, detailing when and when not to use timestamp and scn select where clauses to prevent pitfalls. An example table / dataset will be given, representing a real world scenario where some past data needs to be identified first and is then to be made available again.

Flashback version query uses the following pattern, including the pseudo-columns introduced above, on an actual application-, but not a system-table (alike flashback transaction query). A timestamp– or scn-range must be supplied to define the lookup window (defined by the stock of the available undo-data, remember) and to actually populate the pseudo-columns, respectively:

SELECT pseudo_col1, ..., app_col1, ... FROM app_tab
VERSIONS BETWEEN
  { SCN | TIMESTAMP } { expr | MINVALUE }
  AND { expr | MAXVALUE } ]

Flashback query then provides for the historic data selection in terms of application-table columns, typically used to run a create table as select..., if you like, supplying an as of timestamp– or scn selector:

-- CTAS, maybe
SELECT app_col1, ... FROM app_tab
  AS OF { SCN | TIMESTAMP } expr

All theory so far, yet the real example upfront, make shure have grasped what this trinity in fact means:

  • Flashback version query
  • Flashback query
  • Flashback transaction query

Also be prepared to unhesitantly get down to pen and paper when it comes to history lookup time windows and row level incarnations of data. It’s quite tricky and bewildering sometimes, be concentrated, have a sketch of timelines at hand (you know for https://en.wikipedia.org/wiki/Primer_(film), for example, some visualization like https://en.wikipedia.org/wiki/File:Time_Travel_Method-2.svg may come in very handy).

(more…)

Advertisements

Grepping the actual bind values of sql through v$sql_bind_capture


Programmatically or intercepted by forced cursor sharing, sql where clause actual values shall always be bound to variable placeholders and never / no longer be literally coded. You know Tom Kyte talking that story on each and every occasion, pointing out that so many sql areas got stuck in size and performance because of literal values and the resulting multitude of variants of sql statements. On the other hand, people complain about the sporadic issue that the optimizer takes wrong decisions due to one-time probing of actual values in a given session, a nightmare in 10g, much improved by adaptive cursor sharing throughout 12c meanwhile. Well, I do not want to dive into this again, what follows is just a commented recipe sql statement to inspect what actual bind values have had been in action for a (potentially) sub-optimal sql execution. Please note, that the STATISTICS_LEVEL initialization parameter takes to be greater than BASIC to have the statement deliver any data.

The statement is neither complex nor long-running, just using two perfomance views v$sql_bind_capture and v$sqlarea, resp. While v$sql_bind_capture provides for the bind names, values, capture state and time, v$sqlarea offers different ways to approach the subject, by schema or sql-id or module etc, and not at least, the raw text of the statement in question.

(more…)

Getting a raw constant number of rows from oracle’s table sample function


You may of course know these two famous posts called To sample or not to sample… (part-2) about data sampling by Mark Hornick. Although very limited in scope, the two posts (imho) very well sketch why we may employ data sampling and how we may lift off table sampling in oracle.
In general, sampling is used to make a representative statement about a collection of data while only regarding a limited random selection, the sample. As long as you are ok to analyze just a sufficient subset of your 1o million rows table for an analysis, you will save your environment a lot of resources and time. On some other scenario, a limited random data selection may also serve verification or testing purposes where, however, not the representativeness but the randomness at a more or less constant sample size, determines the quality of the sample output. Again, as long as you are ok to not exceed this 15 minutes time window overnight, you will be allowed to run that live unit test on any table in question, on 1, 10 or 100 million rows.
In sql, selecting in regard to gain a representative statement will feed the sample function with a requested percentage of rows to sample from. This is what the oracle sample function already offers. Yet another sql to accept a requested actual number of rows to return, independent of the table size, is not available so far (although most people do expect exactly this behaviour when they spot the sql sample function for the first time, weird). The following text will outline a snippet of pl/sql to provide for a sample function to accept the expected number of rows as a parameter.

(more…)

Deprecation announcement of oracle restart along 12c withdrawn


As being spotted on Bjoern Rost’s blog, commented by Trap, today, Oracle obviously has been backing down on the deprecation announcement of oracle restart along 12c. On metalink, see:

Withdrawn: Deprecation Announcement of Oracle Restart with Oracle Database 12c (Doc ID 1584742.1)

This is good news, accepted with delight, since we do no longer need to turn back the hands of time into the nifty-oracle-bounce-handycraft-scripts-era. In fact, I wonder how many dba’s are already comfortable with systemd service registration. I suppose, a lot of dba’s would have been resorting back to the dusty sysv configurations, using the systemd-sysv-compatibility engine, which is sort of retrofitting a car-key-starter with a car that already comes with wireless keying and just some starter button. Look around, the major share of on-premise oracle database installations is still single instance compared to rac and even 12c-containers.

Have fun, Peter

Undo retention, flashback query and ora-01555 snapshot too old


Setting up a fault aware database environment is in charge of regarding possible physical and logical error scenarios. On the physical side of the medal, you got real-application-cluster or dataguard at your disposal. The logical one usually comprises backup and replication. Recently, oracle and others introduced features that enables a database to recover from logical errors without or with less remote systems and data. Namely flashback is a powerful technology to look back into (query), back out (transaction) or fully restore (database) the history of data. Flashback of course takes accompanying (history) (meta)data to do its job, namely again, undo-before-images, archived-redo-logs and flashback-logs. So, essentially, the convenience of your course into history depends on the amount of history metadata that is available to the database at the time of a logical error, say an inadvertant delete from.

You may already have learned or heard about or even hardly experienced that flashback-logs may be purged from the recovery-area in favour of archived-redo-logs, rendering your configured flashback-retention-time (DB_FLASHBACK_RETENTION_TARGET) a value of theory. So far, sizing the file-system recovery-area is a task to be performed in a clear-sighted manner. The same is true then for undo-before-images (compare to logs) und the undo-tablespace (compare to area) to accomodate enough data to meet your configured undo-retention-time (UNDO_RETENTION) in UNDO_MANAGEMENT=AUTO mode, while in addition still support long running workload queries (not to overwrite old undo). This article discusses the usability of the (enterprise-manager) undo-advisor in sizing your undo-tablespace to always foster successful flashback queries up to the configured undo-retention-time (as opposed to the nasty surprise of getting an ora-01555, see below, which is also misleading here, imho, in suggesting that the undo-tablespace is to small now, as we know it from workload queries – nope, it has been to small before now, in the past, as regarded to flashback queries – also see : ora-01555 snapshot too old when running flashback query).

(more…)