Search

OakieTags

Who's online

There are currently 0 users and 20 guests online.

Recent comments

Uncategorized

Navigating the world of Oracle database documentation

If you head on over to http://docs.oracle.com/en/database/ you’ll be both amazed and dismayed by the volume of documentation you can find about the database.  If you’re a seasoned Oracle professional, then you probably dont think twice about finding PIVOT examples in Chapter 18 of the Data Warehousing guide Smile

But for the novice, whether it be DBA or Developer, it can be a bit overwhelming.  One resource that you might find a useful addition to your navigation of the Oracle universe, is the interactive quick reference site.

Joining Oracle

Last month I joined Oracle after nearly 20 years of working with their technology.  Some people congratulated me on my new role, others told me that going from working solo to working for a massive organisation would be disaster.  In the latter case, this was often associated with an impassioned “But why?”

To be honest, I found that a little discourteous – its an assumption that to work for a multinational is to become “part of the problem”.  In my career, I’ve worked for several large organisations (Fujitsu and BHP to name a couple).  I’ve always been proud of the accomplishments with those companies, never regretful.

But the best way to answer the question I figured, was to speak directly to it.

So here is why I joined Oracle …  I hope you enjoy and subscribe, because I’ll be publishing a lot more content (of a more technical nature) on my new channel.

https://youtu.be/PtGDlSKmsBw

Things that are there but you cannot use

I did a "desc" command on the STANDARD package today, the package that helps define PL/SQL, and saw the XOR function!

SQL> declare
  2    x boolean;
  3  begin
  4    x := XOR(true,true);
  5    dbms_output.put_line(case when x then 'TRUE' else 'FALSE' end);
  6
  7    x := XOR(true,false);
  8    dbms_output.put_line(case when x then 'TRUE' else 'FALSE' end);
  9
 10    x := XOR(false,false);
 11    dbms_output.put_line(case when x then 'TRUE' else 'FALSE' end);
 12
 13  end;
 14  /
FALSE
TRUE
FALSE

PL/SQL procedure successfully completed.

I cant find any reference to it in the Oracle PL/SQL documentation anywhere, so its definitely not supported so using it is probably off limits until you see officially in the documentation.

SQL Text / View Expansion

One of the common issues faced when debugging or investigating SQL performance is that tools often do not not show the underlying objects at play when a query involves views.

Oracle 12c now provides a capability to display the underlying objects; DBMS_UTILITY.EXPAND_SQL_TEXT will provide the entire syntax of the”exploded” query as shown below:

Example EXPAND_SQL_TEXT – View Definition:

create view dept_emps
 as
 select dname,loc,d.deptno,
     empno,ename,job,hiredate,mgr,sal,comm
   from emp e join dept d
     on e.deptno = d.deptno
   order by dname,ename;

Example EXPAND_SQL_TEXT Use

Here’s a query using the view, unless you know the schema you might not realize multiple database objects were in use.

select * from dept_emps;

Here’s the PL/SQL you might use to “expand” the SQL text to show the objects really involved:

Oracle 12 and latches, part 2

In my previous post, I looked at non shared latches and how the latching is done by Oracle. This post is a description on how the latching works for shared latches.

The information is quite internal, if you landed on this page it might be a good idea to start with my first post on this topic: first post.

A famous example for shared latches is the ‘cache buffers chains’ latch.

For the sake of the test I quite randomly scanned a test table, and had a little gdb script to look at the function call ksl_get_shared_latch:

Delphix Express – a free version of Delphix

 

#555555;">#2970a6;" href="http://datavirtualizer.com/wp-content/uploads/2015/03/2401421989_e05053cbf7_z.jpg">2401421989_e05053cbf7_z

 

#555555; text-align: right;">photo by #212124;" title="Go to Yannis's photostream" href="https://www.flickr.com/photos/yannisag/" data-track="attributionNameClick" data-rapid_p="48">Yannis (CC 2.0)

Continuous Delivery – Moving to SECUREFILE

You’ve been google-ing and you’ve seen articles (for example) like http://www.oracle.com/technetwork/articles/sql/11g-securefiles-084075.html and you’re pretty pumped about using SECUREFILE features.  You’ve got lots of existing CLOB data and moving to SECUREFILE is going to make your life much easier.  You’re also excited about the fact that none of your code has to change – you just change the existing CLOB columns to be stored as SECUREFILE and you’ll have set yourself up for all sorts of feature goodness !

But how do we do it in a continuous delivery (CD) model ?  Because moving CLOB’s sounds like downtime doesn’t it ?

And by default, that’s exactly what it will be.  Let’s explore that with a faux application that uses CLOB’s.

Continuous delivery…

“Continuous Delivery (CD) is a software engineering approach in which teams keep producing valuable software in short cycles and ensure that the software can be reliably released at any time”

(Source: https://en.wikipedia.org/wiki/Continuous_delivery)

Perhaps a simpler definition is “CD is the currently the cool thing to do” Smile

Sarcasm aside, there’s a lot of common sense in being able to rapidly push out software changes in a safe manner. 

Partial uniqueness

I had an interesting request recently from a developer.

“ I have a table created as per below

create table C_TEST (
  col_1 varchar2(3),
  col_2 varchar2(3),
  col_3 number
  );

The rows defined by col_1, col_2, col_3 must be unique but only when col_3 is present.  If col_3 is not present, then we allow anything.  Hence if the table is populated like this:

Oracle Developer awards…chosen by Developers

I was reading the following post today http://stevenfeuersteinonplsql.blogspot.com.au/2015/06/the-oracle-database-developer-choice.html

Oracle are planning on rewarding developers in the following areas:

  • SQL
  • PL/SQL
  • Oracle REST Data Services
  • Oracle Application Express
  • Database Design

 

At first glance I had a bit of a cynical view…it could easily be one of those things where if you come from a company that has massive investment in Oracle, then surprise surprise you float to the top of the heap. 

But this part of the post caught my eye: