Top 60 Oracle Blogs

Recent comments

March 2015

OT: A software tool that improves my writing

I would like a little more help with my writing than the Spelling & Grammar check in Microsoft Word can provide so I am checking out a tool called Word Rake.(read more)

Public appearances 2015

This is going to be a list of my upcoming public appearances in 2015.

Upcoming Events

UKOUG Systems Event

I’ll be at the UKOUG systems event in London May 20. A well established one day event where many great speakers have presented in the past. After a short detour to Birmingham the event is now back in London and @Enkitec I am thrilled to be there. My presentation is titled “Migrating to Exadata the easy way” and incorporates a fair bit of new research.

12c Parallel Execution New Features: Concurrent UNION ALL - Part 3

In the final part of this instalment I want to focus on the possible optimisation of remote access that I outlined in the initial part, which is based on the idea of running multiple concurrent remote branches of a UNION ALL to overcome the query coordinator bottleneck of straightforward remote queries that need to transfer larger amounts of data.For that purpose I now simply change my sample query to access the serial table T2 via the DB link defined in the setup of the initial part, like that:

set echo on timing on time on

select /*+ PQ_CONCURRENT_UNION(@"SET$1") */ count(*) from (
select id, regexp_replace(t2.filler, '^\s+([[:alnum:]]+)\s+$', lpad('\1', 10), 1, 1, 'c') as result from t2@loop

WHEN OTHERS … sometimes just not enough

We currently are working on a bug with Oracle Support with dbms_stats in

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 that’s not meant to be happening, and it pertains to incremental stats on a database that’s been upgraded from to   More on that another time.

SQLTXPLAIN and the AWR Warehouse, Part I

Yeah, so I did it-  I installed SQLTXPLAIN, (SQLT) on the AWR Warehouse!

Tablespace HWM

The following question appeared the Oracle-L list-server recently:

Oracle 12c SQL – Session-Specific SEQUENCE

Session-Specific Sequence

CREATE SEQUENCE now offers a SESSION parameter allowing a sequence to be reset in each session where it is used.

create sequence session_sample_seq
start with 1 increment by 1

When using session-specific sequences; values will reset each time a new session begins. This works the same way that a Global Temporary Table is reinitialized (default is GLOBAL). Rows in Global Temporary Tables exist either for the life of the session or transaction; so resetting sequences makes sense in many cases.

While particularly useful for GTTs; session-specific sequences are NOT limited to GTTs.

It is possible to use ALTER SEQUENCE to convert a SESSION sequence to a GLOBAL (normal type of sequence) and back (be sure to test carefully if you decide to do this you might have unintended consequences!)

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:

  • Turn on index monitoring on some indexes
  • Wait…
  • Come back later, and get ready to check on my results
  • Then this happens…
SQL> select * from v$object_usage;

no rows selected

And I panic…Did I run the commands on the wrong database ?  Did they not run properly ? Do I need to flush a pool ? etc etc etc

And after plenty of wasted minutes…I end up digging up the source for the v$object_usage view

Friday Philosophy – My Introduction To Programming Way Back When

One fortunate thing about me is my age. Or rather, how old I was in the 1980’s. I was at school in the 80’s, I did my ‘O’ Levels (taken at age 16) in 1984. One of my ‘O’ levels was in Computer Studies. This was before Windows and Excel and Word and all that office software, before the internet was in existence (TCP/IP was only standardized in 1982!) and phones were all tethered to the wall with a cable. What were we taught in Computer Studies? Programming. That and a bit about hardware, but mostly it was programming.

This beast had  about 48k of memory and hi-res 320*192 pixels

This beast had about 48k of memory and hi-res 320*192 pixels