Learning is not a spectator sport
Connor McDonald on SQL and the Oracle Database
Tag: 12c
Extending Flashback Data Archive in 12c
Flashback Data Archive (previously called Total Recall) has been around for a long time now. For those unfamiliar with it, (and by the way, if you are on Enterprise Edition, you should be familiar with it, because its a free feature), here is a…
A 12.2 treat for the festive season
We’re being asked to store more and more data, yet keep backup windows, query performance and the like unchanged, no matter how much we store. As a result, more and more database shops are needing to partition their data. The problem is – partitioning…
Advanced Replication
Long before Streams, long before Goldengate, if you want to keep data between sites synchronised in some fashion, or even allow sites to independently update their data, there was the Advanced Replication facility in Oracle. An “extension” of the concept of simple materialized views…
Attribute clustering (part 3)
So in part1 and part2, we looked at creating tables with clustered data. If you’re ready to climb aboard the attribute clustering heading toward Fastville you might want to take an existing table and cluster it. In part 2 we saw how we had…
Attribute clustering (part 2)
In the previous post, I demonstrated attribute clustering by creating a table of source data which contained data in randomised order, via SQL> create table source_data as 2 select d.* 3 from dba_objects d 4 where object_id is not null 5 order by dbms_random.random;…
Attribute clustering (part 1)
One of the nice facilities in 12c is the ability to instruct the database to co-locate data during loading in order to facilitate reduced resource consumption for subsequent queries. Here’s an example of it in use. First I’ll create table SOURCE_DATA to mimic some…
Code enhancements without changing code
An interesting suggestion came through on AskTom this week, which prompted the following exploration. Let us assume you are populating a table with INSERT INTO EMP SELECT * FROM SOURCE_EMPS and it falls over with ORA-12899: value too large for column To capture the…
Exadata Express – I’m in !
Jumping into 12.2 on Exadata Express is made easy with the packaging up of the client configuration files. Here’s what I had to do to get SQL Plus going into the database 1) Download the client credentials file from the Service Console 2) Unzip…
Securefile in 12c – part 2
In a previous post, I noted that the parameter db_securefile changes from PERMITTED in 11g, to PREFERRED in 12c. Jonathan Lewis raised the interesting question of what happens when you upgrade to 12c from 11g, where a partitioned table may already have some basicfile…
Little enhancements are always nice
Before LATERAL and CROSS APPLY were added (exposed to us) in 12c, a common technique to do correlated joins was using the TABLE/MULTISET technique. For example, we might have had a couple of tables: SQL> create table t as 2 select object_type, min(created) min_dte,…
Direct mode operations on IOT’s
An AskTom contributor brought to my attention, that direct mode insert on index organized tables now appears possible in 12c. We can see the difference by running a simple script in both v11 and v12 SQL> select * from v$version; BANNER ——————————————————————————– Oracle Database…
dbms_output and the scheduler
One of the nifty things in 12c is the ability to pick up DBMS_OUTPUT output from your scheduler jobs. So if you haven’t built an extensive instrumentation or logging facility, you’ll still have some details you can pick up from the scheduler dictionary views….
