Search

Top 60 Oracle Blogs

Recent comments

June 2013

12c Funny

Here’s a quirky little thing I discovered about 5 minutes after installing 12c Beta 1 (cut-n-pasted from SQL*Plus):

create or replace view v$my_stats
as
select
        ms.sid,
        sn.statistic#,
        sn.name,
        sn.class,
        ms.value
from
        v$mystat        ms,
        v$statname      sn
where
        sn.statistic# = ms.statistic#
 14  ;
create or replace view v$my_stats
                       *
ERROR at line 1:
ORA-00999: invalid view name

You can’t create views with names that start with V$ or GV$ in the sys schema. Presumably to eliminate the risk of someone’s clever view definition from overwriting and disabling one of the distributed dynamic performance views by accident.

Expunging the Expand all

I’m sure many people love the collapsed form of the Oracle documentation, with the associated "Expand All" function…

Me….I hate it.  The first thing I do is click "Expand All" and "Hide Navigation" to make it look like a standard html page and the Oracle documentation of old..

If you’re a dinosaur like me, then you can permanently turn it off – simply go into the "dcommon/js" folder and rename "doccd.js" to something else.

Oracle Database 12c Virtual RAC Installation on Oracle Linux 6

I finally got my server problems sorted out yesterday, so I was able to do a 12c virtual RAC installation. You can see what I did here:

The setup is pretty much the same as the 11gR2 setup. So there’s no real drama at all.

With the basic installation articles out of the way I can start having a play with the functionality.

PL/SQL function in SQL - short example - Oracle 12c

This is my first post about Oracle 12c. When I first time heard about PL/SQL functions inside SQL my first thought was - can I use it in my topaas.sql script to run it in loop ? I decided to give PL/SQL functions in SQL a quick try to and here are my results. My goal was to display results from SQL line by line with small delay. This is a code a decided to use:
SQL> set arraysize 1
SQL> col slow_me noprint
SQL> with function slow_me return number is
2 begin
3 dbms_lock.sleep(1);
4 return 1;
5 end;
6 select level, slow_me() slow_me from dual connect by level < 10;
7 /

LEVEL
----------
1
2
3
4

PL/SQL function in SQL - short example - Oracle 12c

This is my first post about Oracle 12c. When I first time heard about PL/SQL functions inside SQL my first thought was - can I use it in my topaas.sql script to run it in loop ? I decided to give PL/SQL functions in SQL a quick try to and here are my results. My goal was to display results from SQL line by line with small delay. This is a code a decided to use:
SQL> set arraysize 1
SQL> col slow_me noprint
SQL> with function slow_me return number is
2 begin
3 dbms_lock.sleep(1);
4 return 1;
5 end;
6 select level, slow_me() slow_me from dual connect by level < 10;
7 /

LEVEL
----------
1
2
3
4

Illogical Tuning

The title is a bit of a joke, really. It’s mirroring a title I used a little over a year ago “Logical Tuning” and reflects my surprise that a silly little trick that I tried actually worked.

If you don’t want to read the original article, here’s a quick précis – I started with the first query, which the optimizer executed as a filter subquery, and rewrote it as the second query, which the optimizer executed as two anti-joins (reducing the execution time from 95 seconds to 27 seconds):

Oracle 12c: XMLIndex Support for Hash Partitioning

XMLIndex support for Hash Partitioning is now also supported in Oracle 12c. In Oracle 11 there was already support for RANGE and LIST partitioning, but not yet for HASH partitioning. Some examples for the later two: LIST Partitioning on XMLType -- -- Create partitioned LIST partitioned XMLType table --   DROP TABLE list_part_xml PURGE;  

Read More...

Oracle 12c: PGA_AGGREGATE_LIMIT

There is a new default in town. Mark my words, you will encounter the following during your 12c life-cycle… From the Oracle 12c Reference manual PGA_AGGREGATE_LIMIT: PGA_AGGREGATE_LIMIT PGA_AGGREGATE_LIMIT specifies a limit on the aggregate PGA memory consumed by the instance. There is no difference in behavior between PGA_AGGREGATE_LIMIT being explicitly set or being set to

Read More...

Oracle 12c: XQuery Full Text

New in Oracle 12c and one of the big new features in XMLDB is the XQuery Full Text functionality and, as mentioned in the post about XQuery Update, is the official W3C standard to handle unstructured pieces of XML content. The XQuery Full Text and XQuery Full Text Index extends the XQuery XMLDB language to

Read More...