oracle

Install the Oracle OE- and SH-schemata for the OrderEntry- and SalesHistory-benchmarks

# - oe takes intermedia, sh takes partitioning and bitmap-indexes, so ee is a must
# - https://oracle-base.com/articles/misc/install-sample-schemas

# [ oracle@my.host.de.de $ ~ ]
mkdir sample-schemas ; cd sample-schemas
wget https://github.com/oracle/db-sample-schemas/archive/v12.2.0.1.zip
unzip v12.2.0.1.zip
cp -R db-sample-schemas-12.2.0.1/* $ORACLE_HOME/demo/schema
cd $ORACLE_HOME/demo/schema
perl -p -i.bak -e 's#__SUB__CWD__#'$(pwd)'#g' *.sql */*.sql */*.dat

# yet install
rlsqlplus system/oracle@my_db_srv
show con_name -- MY_DB_SRV
-- system_password sys_password hr_password oe_password pm_password ix_password sh_password
--   bi_password tablespace_name temp_tablespace_name log_location ez_connect_string
@mksample oracle oracle hr oe pm ix sh bi users temp /u01/app/oracle/product/12.2.0/dbhome_1/demo/schema/log/ my_db_srv
^d

# see the oe/sh log files
less $ORACLE_HOME/demo/schema/log/oe_oc_v3.log
less $ORACLE_HOME/demo/schema/log/sh_v3.log

Very cool bind overwrite to mimic sqlplus script optional parameters

This is just a reblog of On SQL*Plus Defines directed by SQL*Plus default values for script parameters, so credits go elsewhere. However, it’s such fun to recognize this guy employing the schortcomings of a 1st/2nd generation language such as sqlplus to actually overcome another distressing deficiency which is the lack of optional, at least fixed left-to-right positional command line parameters.
So what Vlad just does is to overwrite the binding value of an unprotected intrinsic variable for command line input transfer. Namely, he knows that command line input bindings like &1 and &2 and so forth will always exist in the runtime realm of the processor and purposeful overwrites one or the other positional binding by declaring a column blabla new_value 1,2,... slots of just the same name. That’s cool stuff, really, since it provides for a default value setting on a missing input. Something, sqlplus does not deliver, according to the manuals.

Real fun, have it

Presenting “Analytic Functions in Practice” at the DOAG-Regio Hamburg/Nord Dec 4 2018


Save the date to see my presentation about “Oracle Analytic Functions in Practice Applications” along the regular meetup of the DOAG/Oracle-community in Hamburg on Dec 4 2018. Although analytic functions is nothing really new in the Oracle world, I’m again attempting to propagate the thrilling productivity of this SQL-toolset, since practice applications are still not as dispersed as expected. To this extent, I want to stress windowing and aggregation over analytics and statistics in real world scenarios and examples. The presentation will be held in German, I suppose. Event details go here and there:

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:

(more…)