Learning is not a spectator sport

Complete listing of posts

Avoiding the COMMIT bomb!

I’m an Oracle dinosaur, so I like using SQL Plus. Its simple, fast, comes with every version and installation and platform, and I’m very familiar with it. (And who knows, it might still be at the forefront of the Oracle development teams! http://www.slideshare.net/hillbillyToad/sqlcl-overview-a-new-command-line-interface-for-oracle-database ) But there is one important thing I always take care of Read more

April 10, 2015
(OT) an idea for Easter (and well…every day)

I don’t travel as frequently as some people do, but with OpenWorld and various Oracle conferences each year, I definitely see my fair share of the inside of a plane and a hotel room. To pass the time on flights, I try to read, but when you’re reading on a work-based trip, it’s a case Read more

April 5, 2015
Edition based redefinition – an apology

In April 2008 (wow, does time fly!) I used the following picture in my “11g features for Developers” presentation at the Australian Oracle User Group conference. I think the picture is from the movie “Indiana Jones and the Last Crusade”… where they sought the Holy Grail. I used the picture because I said that Edition Read more

April 3, 2015
In-memory – can you REALLY drop those indexes ?

Its not a hard sell to convince anyone that a whopping huge great chunk of compressed column-based memory is going to assist with those analytic-style queries. The In-memory option seems a natural fit, especially for those people where the bottleneck in their infrastructure is the capacity to consume data from storage. What perhaps a more Read more

March 28, 2015
WHEN OTHERS … sometimes just not enough

We currently are working on a bug with Oracle Support with dbms_stats in 12.1.0.2. That may be the subject of a latter post, but in a nutshell, here’s the issue SQL> exec dbms_stats.gather_table_stats(‘MY_SCHEMA’, ‘MY_SCHEMA’); BEGIN dbms_stats.gather_table_stats(‘MY_SCHEMA’, ‘MY_SCHEMA’); END; * ERROR at line 1: ORA-21700: object does not exist or is marked for delete Now obviously Read more

March 20, 2015
v$object_usage catches me out every time 🙂

Every …. single …. time…. Why does index monitoring make me scratch my head and charge off to google so many times…Well, I’m over it, so time to put it on my blog (even though its already on many other places) so I do not get caught out anymore 🙂 It always starts like this: Read more

March 14, 2015
RETURNING BULK COLLECT and database links

Looks like the nice PL/SQL facility for returning a set of updated rows is restricted when it comes to database links (This tested on 12.1.0.1) SQL> declare 2 type int_list is table of number(12) index by pls_integer; 3 l_results int_list; 4 5 begin 6 update MY_TABLE b 7 set b.my_col = ( select max(last_ddl_time) from Read more

February 24, 2015
GROUP BY – wrong results in 12.1.0.2

I encountered this during some testing – the optimizer can be a little overzealous when it encounters nested group-by’s SQL> create table T ( ts number not null, c char(10)); Table created. SQL> insert into T 2 select 100+dbms_random.value(1,50),’x’ 3 from ( select 1 from dual connect by level < 1000 ), 4 ( select Read more

February 12, 2015
Inappropriate behaviour

You pick up little funny things in the day to day with Oracle. Like this one when you try to drop a tablespace with a queue table in it: drop tablespace MY_TSPACE including contents * ERROR at line 1: ORA-00604: error occurred at recursive SQL level 1 ORA-24005: Inappropriate utilities used to perform DDL on Read more

January 13, 2015
Data Pump import makes me crabby

I’m sitting here watching the import of a moderately sized database via transportable tablespaces. You know…the thing you use when a full export / import would be too slow, and this is meant to be … well…fast. And fast it is.. until it reaches the following step: Processing object type TRANSPORTABLE_EXPORT/TABLE_STATISTICS Now understandably, there’s plenty Read more

January 13, 2015
Slow external table access

We had an interesting issue on 12.1.0.1, where users were reporting very slow performance on queries to external tables. When I tried to replicate the problem, everything seemed just fine, so I initially reported back the familiar “Well, it works on my PC” 🙂 [Just kidding] Anyway, connecting by proxy to one of their accounts, Read more

January 2, 2015
Analytics Demo Scripts

Video 2: Ranking rows https://livesql.oracle.com/apex/livesql/s/ch1tefsz0mc75pk4ix2jbfja7 Video 3: More options for ranking rows https://livesql.oracle.com/apex/livesql/s/clebs8f5n4kr2b3vjvnvvgxkn Video 4: Using the Tabibitosan method for grouping sets of rows https://livesql.oracle.com/apex/livesql/s/cljrgh80cczwjtptlpk2e5081 Video 5: CUME_DIST, PERCENT_RANK and NTILE https://livesql.oracle.com/apex/livesql/s/clq6jmj1jzju4k23v3tnbzo8u Video 6: Dealing with NULLS https://livesql.oracle.com/apex/livesql/s/cokpdete3hiy3jo0yzoa4eerz Video 7: Analytics as predicates https://livesql.oracle.com/apex/livesql/s/cokv9220ffzt8iqs7udqsv5vm Video 8: The partition clause https://livesql.oracle.com/apex/livesql/file/content_ERYV6B909XK196IAKLBUQIJSN.html Video 9: Aggregation with partitions Read more

January 1, 2015