Search

OakieTags

Who's online

There are currently 0 users and 29 guests online.

Recent comments

Oakies Blog Aggregator

A little known RI clause

Most people are aware of this standard referential integrity control, namely you can’t delete the parent if children exist


SQL> drop table PAR cascade constraints purge;

Table dropped.

SQL> create table PAR ( p int primary key, data int);

Table created.

SQL> insert into PAR
  2  select rownum, rownum
  3  from dual connect by level <= 7;

7 rows created.

SQL>
SQL> drop table CHD purge;

Table dropped.

SQL> create table CHD ( c int primary key, p int references PAR ( p ) );

Table created.

SQL>
SQL> insert into CHD
  2  select rownum, mod(rownum,4)+1
  3  from dual connect by level <= 12;

12 rows created.

SQL>
SQL> delete from PAR
  2  where p = 2;
delete from PAR
*
ERROR at line 1:
ORA-02292: integrity constraint (MCDONAC.SYS_C0018225) violated - child record found

And most people are also aware that you can head to the other extreme, and wipe out the children when you wipe out the parent


SQL> drop table PAR cascade constraints purge;

Table dropped.

SQL> create table PAR ( p int primary key, data int);

Table created.

SQL> insert into PAR
  2  select rownum, rownum
  3  from dual connect by level <= 7;

7 rows created.

SQL>
SQL> drop table CHD purge;

Table dropped.

SQL> create table CHD ( c int primary key, p int references PAR ( p ) ON DELETE CASCADE);

Table created.

SQL>
SQL> insert into CHD
  2  select rownum, mod(rownum,4)+1
  3  from dual connect by level <= 12;

12 rows created.

SQL>
SQL> delete from PAR
  2  where p = 2;

1 row deleted.

SQL> select * from CHD;

         C          P
---------- ----------
         2          3
         3          4
         4          1
         6          3
         7          4
         8          1
        10          3
        11          4
        12          1

9 rows selected.

But don’t forget, there is also a third option that you can implement declaratively


SQL> drop table PAR cascade constraints purge;

Table dropped.

SQL> create table PAR ( p int primary key, data int);

Table created.

SQL> insert into PAR
  2  select rownum, rownum
  3  from dual connect by level <= 7;

7 rows created.

SQL>
SQL> drop table CHD purge;

Table dropped.

SQL> create table CHD ( c int primary key, p int references PAR ( p ) ON DELETE SET NULL);

Table created.

SQL>
SQL> insert into CHD
  2  select rownum, mod(rownum,4)+1
  3  from dual connect by level <= 12;

12 rows created.

SQL>
SQL> delete from PAR
  2  where p = 2;

1 row deleted.

SQL> select * from CHD;

         C          P
---------- ----------
         1
         2          3
         3          4
         4          1
         5
         6          3
         7          4
         8          1
         9
        10          3
        11          4
        12          1

12 rows selected.

SQL>
SQL>

I don’t think I’ve seen this used in a Production instance. If you’ve seen it, please add a comment – I’d be curious to see a use case.

Spot the Oracle Faces

My wife has been going through old photo’s from her mother today, trying to find a picture of Uncle Stan. In the box of photographs was also a magazine – an Oracle magazine!

Oracle Magazine award winners 2003!

Oracle Magazine award winners 2003!

As you can see from the front cover, it is the Oracle Magazine from the end of 2003, with the Oracle Magazine 2003 Award Winners on it. The tiny photograph on the bottom right is me:-). Sue’s mum, Di, seemed to be more interested in what I did at work than my own mum (but then Di was like that).

So how many of the people on the magazine do you recognize? If you’ve met them, you should be able to identify a few – even though we are all at least 13 years older than those pictures. If you click on the image, you will get a larger version which might help. It is SO long ago that I don’t think there is an electronic issue of the magazine online, not even in the Oracle Magazine archive. But then, who wants to know about enterprise grid computing in 10g now? I could not even find another copy of the front cover in my 90-second search.

I’m not really one for looking to the past but I do enjoy the odd reminisce. It’s good to see what happened in the past (be it good or bad) and where it has left you in the present. There seems to have been quite a bit of this going on around me this week. Some people on the OakTable have been sharing pictures with the group of a similar vintage (so way before my time), I was talking about how we got into presenting and the Oracle community with Brendan Tierney over the last couple of days and at home we have been looking back even further. The “Uncle Stan” I mentioned was a POW in WW2 in Singapore and he painted the Changi Murals when he was there – painted to help keep up the spirits of those in the infirmary at the time. We will visit The Changi Museum to see the replicas and read the history when we are out there in 2 weeks and, if we are lucky, we might even get to see the originals.

Getting the 2003 Oracle magazine “Beta tester of the year” award was my first real step into the Oracle community. I’d only just started presenting (I think once at UKOUG Tech conference & one SIG, a couple of Oracle Life Science conferences plus being the “friendly face of the end user” at an Open World in 2002 talk…maybe 2003. I never even got on the agenda for that one). I got the award more as the representative of the work done by people in my team, ie their work, as opposed to mine – and for a long while I felt a bit guilty about it. But as a good friend pointed out, it was a team that I had built, doing work I guided and, between myself and Shanthi Sivadasan, we had it all running well and we were doing stuff that no one else would own up to doing (and that HP offered to help us with – and we ended up helping them!).

So back to the magazine cover. Who can you spot? Some I am pretty sure are no longer anything to do with the Oracle scene, but some still are…:

Arup Nanda, DBA of the year (oTY)
Tim Sharick, CTO oTY
Ronan Miles, IT Manager oTY
Peter Charles Smith, PL/SQL developer oTY
TonyJambu, consultant oTY
Bob Magan, developer oTY
Jeroen Baltussen, web services developer oTY
Harvinder Singh Saluja, Jdeveloper oTY
Arno Van Der Klok Java developer oTY
Matt Rhoades, BI developer oTY
Arnaud Bontemps, Portal developer oTY
Tom Copeland, Open Source developer oTY
{how many “X develop of the years”? How many “DBA-types” of the year? Oh yes, 10g was supposed to be the death of the DBA – again}
Jamie Kinney & Grant McAlister, Linux Innovators oTY
Hoosh Asfar, Early Adopter oTY
Mogens Norgaard {who?}, Educator oTY
Tom Kyte {another obscure one}, Oracle Book Author oTY
Jason Hunter, Oracle Magazine Author oTY
Me, Beta Tester oTY
Andrew Clarke, OTN contributor oTY
Rick Hamilton, Architect oTY

Wrong Results Involving INDEX FULL SCAN (MIN/MAX) in 12.1.0.2

One of my customers that recently upgraded to 12c hit a bug (22913528) that I think is good to be aware of. Note that as the title of this post states, the problem only occur in 12.1.0.2. At least, I wasn’t able to reproduce it in any other version.

To reproduce it you simply need a composite partitioned table with a non-partitioned or global-partitioned index. In other words, if all your indexes are local, you shouldn’t be impacted by the bug.

The SQL statements I use to prepare the schema to reproduce it are the following:

#993333; font-weight: bold; text-transform: uppercase;">CREATE #993333; font-weight: bold; text-transform: uppercase;">TABLE t #66cc66;">(p #993333; font-weight: bold; text-transform: uppercase;">DATE#66cc66;">, s number#66cc66;">, i #993333; font-weight: bold; text-transform: uppercase;">DATE#66cc66;">)
#993333; font-weight: bold; text-transform: uppercase;">PARTITION BY RANGE #66cc66;">(p#66cc66;">) #993333; font-weight: bold; text-transform: uppercase;">INTERVAL#66cc66;">(numtoyminterval#66cc66;">(#cc66cc;">1#66cc66;">,#ff0000;">'MONTH'#66cc66;">)#66cc66;">) 
#993333; font-weight: bold; text-transform: uppercase;">SUBPARTITION BY LIST #66cc66;">(s#66cc66;">) #993333; font-weight: bold; text-transform: uppercase;">SUBPARTITION TEMPLATE #66cc66;">(
   SUBPARTITION s1 #993333; font-weight: bold; text-transform: uppercase;">VALUES #66cc66;">(#cc66cc;">1#66cc66;">)#66cc66;">,
   SUBPARTITION s2 #993333; font-weight: bold; text-transform: uppercase;">VALUES #66cc66;">(#cc66cc;">2#66cc66;">)#66cc66;">,
   SUBPARTITION s3 #993333; font-weight: bold; text-transform: uppercase;">VALUES #66cc66;">(#cc66cc;">3#66cc66;">)#66cc66;">,
   SUBPARTITION s4 #993333; font-weight: bold; text-transform: uppercase;">VALUES #66cc66;">(#cc66cc;">4#66cc66;">)#66cc66;">,
   SUBPARTITION s5 #993333; font-weight: bold; text-transform: uppercase;">VALUES #66cc66;">(#cc66cc;">5#66cc66;">)#66cc66;">,
   SUBPARTITION s6 #993333; font-weight: bold; text-transform: uppercase;">VALUES #66cc66;">(#cc66cc;">6#66cc66;">)#66cc66;">,
   SUBPARTITION s7 #993333; font-weight: bold; text-transform: uppercase;">VALUES #66cc66;">(#cc66cc;">7#66cc66;">)
#66cc66;">)#66cc66;">(
  PARTITION p_others #993333; font-weight: bold; text-transform: uppercase;">VALUES LESS THAN #66cc66;">(to_date#66cc66;">(#ff0000;">'2016-01-01 00:00:00'#66cc66;">, #ff0000;">'YYYY-MM-DD HH24:MI:SS'#66cc66;">)#66cc66;">)
#66cc66;">);
 
#993333; font-weight: bold; text-transform: uppercase;">INSERT #993333; font-weight: bold; text-transform: uppercase;">INTO t 
#993333; font-weight: bold; text-transform: uppercase;">SELECT to_date#66cc66;">(#ff0000;">'2016-01-01 00:00:00'#66cc66;">, #ff0000;">'YYYY-MM-DD HH24:MI:SS'#66cc66;">)#66cc66;">-rownum#66cc66;">, 
       mod#66cc66;">(rownum#66cc66;">,#cc66cc;">7#66cc66;">)#66cc66;">+#cc66cc;">1#66cc66;">, 
       trunc#66cc66;">(sysdate#66cc66;">+mod#66cc66;">(rownum#66cc66;">,#cc66cc;">7#66cc66;">)#66cc66;">)
#993333; font-weight: bold; text-transform: uppercase;">FROM dual 
#993333; font-weight: bold; text-transform: uppercase;">CONNECT #993333; font-weight: bold; text-transform: uppercase;">BY level #66cc66;"><= #cc66cc;">365;
 
#993333; font-weight: bold; text-transform: uppercase;">COMMIT;
 
#993333; font-weight: bold; text-transform: uppercase;">CREATE #993333; font-weight: bold; text-transform: uppercase;">INDEX i #993333; font-weight: bold; text-transform: uppercase;">ON t #66cc66;">(i#66cc66;">);
REM #993333; font-weight: bold; text-transform: uppercase;">CREATE #993333; font-weight: bold; text-transform: uppercase;">INDEX i #993333; font-weight: bold; text-transform: uppercase;">ON t #66cc66;">(i#66cc66;">) #993333; font-weight: bold; text-transform: uppercase;">GLOBAL #993333; font-weight: bold; text-transform: uppercase;">PARTITION BY HASH #66cc66;">(i#66cc66;">) #993333; font-weight: bold; text-transform: uppercase;">PARTITIONS #cc66cc;">4;
REM #993333; font-weight: bold; text-transform: uppercase;">CREATE #993333; font-weight: bold; text-transform: uppercase;">INDEX i #993333; font-weight: bold; text-transform: uppercase;">ON t #66cc66;">(i#66cc66;">) #993333; font-weight: bold; text-transform: uppercase;">LOCAL;
 
execute dbms_stats.gather_table_stats#66cc66;">(user#66cc66;">, #ff0000;">'T'#66cc66;">)

Note that the minimum value for each subpartition key is the following:

SQL#66cc66;">> #993333; font-weight: bold; text-transform: uppercase;">SELECT s#66cc66;">, min#66cc66;">(i#66cc66;">)
  #cc66cc;">2  #993333; font-weight: bold; text-transform: uppercase;">FROM t
  #cc66cc;">3  #993333; font-weight: bold; text-transform: uppercase;">GROUP #993333; font-weight: bold; text-transform: uppercase;">BY s;
 
         S #993333; font-weight: bold; text-transform: uppercase;">MIN#66cc66;">(I#66cc66;">)
#808080; font-style: italic;">---------- ---------
         #cc66cc;">1 #cc66cc;">15#66cc66;">-APR#66cc66;">-#cc66cc;">16
         #cc66cc;">2 #cc66cc;">16#66cc66;">-APR#66cc66;">-#cc66cc;">16
         #cc66cc;">3 #cc66cc;">17#66cc66;">-APR#66cc66;">-#cc66cc;">16
         #cc66cc;">4 #cc66cc;">18#66cc66;">-APR#66cc66;">-#cc66cc;">16
         #cc66cc;">5 #cc66cc;">19#66cc66;">-APR#66cc66;">-#cc66cc;">16
         #cc66cc;">6 #cc66cc;">20#66cc66;">-APR#66cc66;">-#cc66cc;">16
         #cc66cc;">7 #cc66cc;">21#66cc66;">-APR#66cc66;">-#cc66cc;">16

The query to reproduce the bug is the following (the right answer is “17-APR-16”):

SQL#66cc66;">> #993333; font-weight: bold; text-transform: uppercase;">SELECT min#66cc66;">(i#66cc66;">) #993333; font-weight: bold; text-transform: uppercase;">FROM t #993333; font-weight: bold; text-transform: uppercase;">WHERE s #66cc66;">= #cc66cc;">3;
 
#993333; font-weight: bold; text-transform: uppercase;">MIN#66cc66;">(I#66cc66;">)
#808080; font-style: italic;">---------
#cc66cc;">15#66cc66;">-APR#66cc66;">-#cc66cc;">16

The execution plan shows that the query optimizer “loses” the partition pruning information:

SQL#66cc66;">> #993333; font-weight: bold; text-transform: uppercase;">EXPLAIN #993333; font-weight: bold; text-transform: uppercase;">PLAN #993333; font-weight: bold; text-transform: uppercase;">FOR #993333; font-weight: bold; text-transform: uppercase;">SELECT min#66cc66;">(i#66cc66;">) #993333; font-weight: bold; text-transform: uppercase;">FROM t #993333; font-weight: bold; text-transform: uppercase;">WHERE s #66cc66;">= #cc66cc;">3;
 
SQL#66cc66;">> #993333; font-weight: bold; text-transform: uppercase;">SELECT #66cc66;">* #993333; font-weight: bold; text-transform: uppercase;">FROM table#66cc66;">(dbms_xplan.display#66cc66;">(format#66cc66;">=>#ff0000;">'basic +partition'#66cc66;">)#66cc66;">);
 
PLAN_TABLE_OUTPUT
#808080; font-style: italic;">-------------------------------------------------------------------------
Plan hash value: #cc66cc;">1554646154
 
#808080; font-style: italic;">-------------------------------------------
#66cc66;">| Id  #66cc66;">| Operation                  #66cc66;">| Name #66cc66;">|
#808080; font-style: italic;">-------------------------------------------
#66cc66;">|   #cc66cc;">0 #66cc66;">| #993333; font-weight: bold; text-transform: uppercase;">SELECT STATEMENT           #66cc66;">|      #66cc66;">|
#66cc66;">|   #cc66cc;">1 #66cc66;">|  SORT AGGREGATE            #66cc66;">|      #66cc66;">|
#66cc66;">|   #cc66cc;">2 #66cc66;">|   #993333; font-weight: bold; text-transform: uppercase;">INDEX FULL SCAN #66cc66;">(#993333; font-weight: bold; text-transform: uppercase;">MIN#66cc66;">/#993333; font-weight: bold; text-transform: uppercase;">MAX#66cc66;">)#66cc66;">| I    #66cc66;">|
#808080; font-style: italic;">-------------------------------------------

It goes without saying that when the index isn’t used the result is correct:

SQL#66cc66;">> #993333; font-weight: bold; text-transform: uppercase;">SELECT /*+ no_index(t) */ min#66cc66;">(i#66cc66;">) #993333; font-weight: bold; text-transform: uppercase;">FROM t #993333; font-weight: bold; text-transform: uppercase;">WHERE s #66cc66;">= #cc66cc;">3;
 
#993333; font-weight: bold; text-transform: uppercase;">MIN#66cc66;">(I#66cc66;">)
#808080; font-style: italic;">---------
#cc66cc;">17#66cc66;">-APR#66cc66;">-#cc66cc;">16
 
SQL#66cc66;">> #993333; font-weight: bold; text-transform: uppercase;">EXPLAIN #993333; font-weight: bold; text-transform: uppercase;">PLAN #993333; font-weight: bold; text-transform: uppercase;">FOR #993333; font-weight: bold; text-transform: uppercase;">SELECT /*+ no_index(t) */ min#66cc66;">(i#66cc66;">) #993333; font-weight: bold; text-transform: uppercase;">FROM t #993333; font-weight: bold; text-transform: uppercase;">WHERE s #66cc66;">= #cc66cc;">3;
 
SQL#66cc66;">> #993333; font-weight: bold; text-transform: uppercase;">SELECT #66cc66;">* #993333; font-weight: bold; text-transform: uppercase;">FROM table#66cc66;">(dbms_xplan.display#66cc66;">(format#66cc66;">=>#ff0000;">'basic +partition'#66cc66;">)#66cc66;">);
 
PLAN_TABLE_OUTPUT
#808080; font-style: italic;">--------------------------------------------------------------------------
Plan hash value: #cc66cc;">1728811753
 
#808080; font-style: italic;">--------------------------------------------------------
#66cc66;">| Id  #66cc66;">| Operation               #66cc66;">| Name #66cc66;">| Pstart#66cc66;">| Pstop #66cc66;">|
#808080; font-style: italic;">--------------------------------------------------------
#66cc66;">|   #cc66cc;">0 #66cc66;">| #993333; font-weight: bold; text-transform: uppercase;">SELECT STATEMENT        #66cc66;">|      #66cc66;">|       #66cc66;">|       #66cc66;">|
#66cc66;">|   #cc66cc;">1 #66cc66;">|  SORT AGGREGATE         #66cc66;">|      #66cc66;">|       #66cc66;">|       #66cc66;">|
#66cc66;">|   #cc66cc;">2 #66cc66;">|   PARTITION #993333; font-weight: bold; text-transform: uppercase;">RANGE #993333; font-weight: bold; text-transform: uppercase;">ALL   #66cc66;">|      #66cc66;">|     #cc66cc;">1 #66cc66;">|#cc66cc;">1048575#66cc66;">|
#66cc66;">|   #cc66cc;">3 #66cc66;">|    PARTITION LIST SINGLE#66cc66;">|      #66cc66;">|     #cc66cc;">3 #66cc66;">|     #cc66cc;">3 #66cc66;">|
#66cc66;">|   #cc66cc;">4 #66cc66;">|     #993333; font-weight: bold; text-transform: uppercase;">TABLE #993333; font-weight: bold; text-transform: uppercase;">ACCESS FULL   #66cc66;">| T    #66cc66;">|   #993333; font-weight: bold; text-transform: uppercase;">KEY #66cc66;">|   #993333; font-weight: bold; text-transform: uppercase;">KEY #66cc66;">|
#808080; font-style: italic;">--------------------------------------------------------

If you are on 12.1.0.2 (or you are thinking about upgrading to it), because of this bug I strongly advise you to check whether you have non local indexes…

Active and Inactive Sessions

Most people are aware of the STATUS column in V$SESSION. If it’s ‘ACTIVE’ then that connection is in the process of consuming database resources (running a SQL statement etc).

However, a lesser known column which is probably even more useful is the LAST_CALL_ET column. The name suggests the time since the last call, but the documentation provides a more useful description:

 

If the session STATUS is currently ACTIVE, then the value represents the elapsed time (in seconds) since the session has become active.

If the session STATUS is currently INACTIVE, then the value represents the elapsed time (in seconds) since the session has become inactive.

 

Thus this is a great column to get a view on what sessions are doing on your system, for example


select s.sid||','||s.serial# sess, 
       s.USERNAME,
       s.last_call_et, 
       s.status, 
       s.sql_address, 
       s.program
from v$session s
where ( s.status = 'ACTIVE' and s.last_call_et > 10 ) or      -- has been active for 10 seconds or more
      ( s.status != 'ACTIVE' and s.last_call_et > 1200 );     -- has been inactive for 20 mins or more

WordPress 4.5 Released

WordPress 4.5 “Coleman” has been released.

I just applied it to the five WordPress sites I manage by manually triggering the auto-update and everything went through fine.

There are some updates to the standard themes that you will need to manually trigger for update, but there was no drama there either.

I fully expect a rash of little updates to get released over the coming days as new bugs are spotted. </p />
</p></div>

    	  	<div class=

Collaborate 2016 Oaktable World Sessions

Oaktable World Las Vegas is happening at Collaborate 2016! Many thanks to Tim Gorman, Alex Gorbachev and Mark Farnham for organizing!
Free Oaktable World t-shirts available at Delphix booth 1613 on Tuesday and at the Oaktable World talks on Wednesday. Also available at the Delphix booth is free copies of Mike Swing’s “the little r12.2.5 upgrade essentials for managers and tema members”. Mike will be doing Q&A at the Delphix booth Tuesday 1:15-2:00 and book signing on Wednesday 2:00- 2:45.

Oaktable World all day Wednesday 9:15-6:15 Mandalay Bay Ballroom I

IMG_7040




Time Session Type Presenter Name Proposed Topic
09:15 – 10:15 60 mins Alex Gorbachev Back of a Napkin Guide to Oracle Database in the Cloud
10:30 – 11:30 “Re-Energize” session no OTW session
11:45 – 11:55 10 mins Alex Gorbachev Internet of Things 101
12:00 – 12:10 10 mins Tim Gorman How Oracle Data Recovery Mechanisms Complicate Data Security, and What To Do About It
12:15 – 12:25 10 mins
12:30 – 12:40 10 mins Kyle Hailey Challenges and solutions masking data
12:45 – 12:55 10 mins Dan Norris Tools used for security and compliance on Linux
13:00 – 14:00 Oracle keynote no OTW session
14:00 – 15:00 60 mins Kellyn Pot’Vin-Gorman Performance Feature Enhancements in Enterprise Manager 13c wtih DB12c
15:00 – 16:00 60 mins Dan Norris IPv6: What You Need to Know (with Linux & Exadata references)
17:15 – 18:15 60 mins Kyle Hailey Data for DevOps

All talks in Mandalay Bay Ballroom I

EM13c Corrective Actions

While at Collaborate 2016, a number of us were surprised that people still aren’t using Corrective Actions and in EM13c, there are a number of cool ones built into the system to make your life easier.  In this post, we’ll use the very valuable, Add Tablespace corrective action to ensure the DBA  is no longer woke up at night, automating the tedious task of adding logical space by extending or adding data files.

sheldon_bed

So let’s talk about how you can stop databases from making you get out of bed to add tablespace… </p />
</p></div></div>

    	  	<div class=

TABLE Operator with Locally Defined Types

postit-missed-itIn my OUG Ireland 2016 – Summary post I mentioned the Oren Nakdimon session called “Write Less (Code) with More (Oracle 12c New Features)”. One of the things he mentioned was the removal of restrictions associated with the use of the TABLE operator on local table types. If I had read about this or seen it before, it had certainly slipped my mind, so I made a note to write something about it and add a link to it from my PL/SQL new features article. So here it is.

It’s a neat little feature.

Cheers

Tim…

 


TABLE Operator with Locally Defined Types was first posted on April 12, 2016 at 11:22 am.
©2012 "The ORACLE-BASE Blog". Use of this feed is for personal non-commercial use only. If you are not reading this article in your feed reader, then the site is guilty of copyright infringement.

Mentoring

Becoming a mentor was not a direct path for me.  Even though I inspired people with my perseverance and common sense, I had no inclinations for a corner office position, (I often joke about passing up the corner offices in a fight to get to the server room) and this is often a trait that is expected for someone who is titled with “Mentor.”

cusack_gpb

In the five years in an official mentoring capacity, I’ve learned as much as I feel I’ve imparted among those I’ve mentored.  I now find others asking me for advice on how to become a mentor or how to mentor more effectively and I do have some advice on that topic I’d like to share.

Becoming a Mentor

As I mentor both men and women, I can verify that each gender commonly requires different things in a mentor.  Men will often want to know WHAT they can do to attain success.  They are looking for contacts, a network of opportunities and examples they can duplicate.  The guys rarely want anything than some quick advice and definitely don’t want to talk to other men that are being mentored by me… </p />
</p></div></div>

    	  	<div class=

Why PLSQL ?

With Collaborate 2016 under way, there seems no better time to reflect on why PL/SQL is the natural choice for anyone who loves to code, and loves their data