Today’s video gives a quick run through of flashback version query.
If you prefer to read articles, rather than watch videos, you might be interested in these.
- Flashback Query
- Flashback New Features and Enhancements in Oracle Database 10g
- Flashback and LogMiner Enhancements in Oracle Database 11g Release 1
- Flashback Data Archive (FDA) Enhancements in Oracle Database 12c Release 1 (12.1)
Transferring data between technologies and application tiers is done using various formats – binary, native on the one hand and open, text based such as CSV, XML and JSON on the other. Use of JSON is rapidly growing as a growing number of platforms and technologies provides support for JSON.
I recently was working on a Node.js application that exposed a REST API to HTTP consumers. The consumers could send POST requests with a (more...)
For most of our database set-ups we use a different TEMP space for application users than for end-user/support/developer/reporting usage.
The intention is to minimise the potential impact of a rogue ad-hoc query on the main applicaiton.
However, turns out this is ineffective IF you use:
ALTER SESSION SET CURRENT_SCHEMA = main_app_schema;
This is documented but I wasn’t previously aware.
No surprise that this learning opportunity was presented as a direct result of a rogue query (more...)
There are several new features in Oracle 12c that are implemented under the hood by changing the SQL statement that we write to a different statement (e.g., by adding some hidden predicates).
In OUG Ireland 2016 I talked about two such features – In Database Archiving and Temporal Validity – as part of my “Write Less (Code) with More (Oracle12c New Features)” presentation. I usually talk about another such feature in this presentation (more...)
l_schema_name varchar2(30) := 'MY_SCHEMA';
l_table_name varchar2(30) := 'A_TABLE';
cursor c_cols is
from all_tab_columns col
where col.table_name = l_table_name
and col.owner = l_schema_name
Recently I got involved in a question on Temporal Validity Periods together with Chris Saxon, one of the askTom-answer team.
The question was along the lines of: "What if I have a single start date but two possible end dates. One of the end dates is filled automatically by a background proces (could be a job) while the other one is to signal that the end date is set manually by the user. Could you (more...)
I had a discrete set of values in local variables that I wanted to use within a merge, so I selected them from dual. Here is a literal representations
SELECT 'SCOTT' login_id
,'X' alpha, 'Y' beta
Recently I found that WINDOW NOSORT STOPKEY with RANK()OVER() works very inefficiently: http://www.freelists.org/post/oracle-l/RANKWINDOW-NOSORT-STOPKEY-stopkey-doesnt-work
The root cause of this behaviour is that Oracle optimizes WINDOW NOSORT STOPKEY with RANK the same way as with DENSE_RANK:
create table test(n not null) as with gen as (select level n from dual connect by level<=100) select g2.n as n from gen g1, gen g2 where g1.n<=10 / create index ix_test on test(n) / exec (more...)
Virtual columns were added in Oracle 11g Release 1. We can implement the same concept using views, but one of the advantages that I see in virtual columns is that we can define foreign key constraints on them. Well, we can define foreign key constraints on views as well, but only in DISABLE NOVALIDATE mode, which makes them a decoration rather than a data integrity protector. So to be more precise, we can define enabled (more...)
Oracle Enterprise Manager is a complex piece of software that many organizations are running now. Some organizations set out with a formalized naming standard; some do not. Those who do not ofter end up identifying a naming standard later down the road and then making requests to change the names of the targets being monitored. In order to do this, there are two ways:
1. Delete and rediscover the target and rename at time of (more...)