Learning is not a spectator sport

Complete listing of posts

  • Advanced Replication

    Long before Streams, long before Goldengate, if you want to keep data between sites synchronised in some fashion, or even allow sites to independently update their data, there was the Advanced Replication facility in Oracle.  An “extension” of the concept of simple materialized views (or snapshots as they were called then), you could design complete Read more

  • max_enabled_roles – nice touch

    Some people get very excited with roles, and quickly the number of roles proliferates to huge numbers…Until this happens ORA-28031: maximum of 148 enabled roles exceeded But in helping someone out on AskTom, I just found a nice touch in 11.2.0.4.  I had granted my account 200 roles (since the max_enabled_roles parameter is capped at Read more

  • Simple demo of message propagation

    If you’re using AQ, then it’s simple to setup simple enqueue and dequeue facilities on your local database to provide all sorts of asynchronous style processing in your applications.  As long as you’re applications are designed and built to handle it, the “fire and forget” model to keep user applications responsive, and all of the Read more

  • Just one more week

    I’m off to UKOUG again this year.  It’s an awesome conference, and I’ll be doing three talks there. On Monday at 3:30 it’s my first keynote talk   “12 Things You’ll Love About the Oracle Database 12.2”, followed up at 6pm by “AskTom – One Year On”. On Tuesday, at 3:30 I’ll be doing a Read more

  • Attribute clustering (part 3)

    Attribute clustering (part 3)

    So in part1 and part2, we looked at creating tables with clustered data.  If you’re ready to climb aboard the attribute clustering heading toward Fastville you might want to take an existing table and cluster it.  In part 2 we saw how we had to be extra careful with syntax.  The same rule applies with Read more

  • Attribute clustering (part 2)

    Attribute clustering (part 2)

    In the previous post, I demonstrated attribute clustering by creating a table of source data which contained data in randomised order, via SQL> create table source_data as 2 select d.* 3 from dba_objects d 4 where object_id is not null 5 order by dbms_random.random; and then it loading into a table with attribute clustering defined Read more

  • Attribute clustering (part 1)

    Attribute clustering (part 1)

    One of the nice facilities in 12c is the ability to instruct the database to co-locate data during loading in order to facilitate reduced resource consumption for subsequent queries. Here’s an example of it in use. First I’ll create table SOURCE_DATA to mimic some randomised incoming data feed SQL> exec dbms_random.seed(0) PL/SQL procedure successfully completed. Read more

  • Performing a large correlated update

    We had a question on AskTom recently asking for the best way to update a new column on a very large table.  Four in-place update options are at our disposal: Serial update SQL Parallel DML update SQL DBMS_PARALLEL_EXECUTE PL/SQL batching (as long as sensible restart-after-error logic is readily available) all of which will achieve the Read more

  • OTN tour 2016 APAC

    The OTN tour came to APAC this year, so it’s been a pleasure and privilege to be able to participate in some of the legs.  Being Perth born and bred, I know all too well that any travel to Australia from … well… anywhere except Australia, is a long haul, so I’m very grateful to Read more

  • Code enhancements without changing code

    An interesting suggestion came through on AskTom this week, which prompted the following exploration. Let us assume you are populating a table with INSERT INTO EMP SELECT * FROM SOURCE_EMPS and it falls over with ORA-12899: value too large for column To capture the data that caused that error is not trivial. Of course, we Read more

  • BULK COLLECT into nested table

    I had an observation come to me last week about PL/SQL and populating nested tables. “The BULK COLLECT into statement cannot be used repeatedly to append results into a table. Instead, it silently truncates the target table each time. “ This is true.  However, if you need to use BULK COLLECT to append results into Read more

  • Truncating a timestamp to the second

    We had an interesting AskTom question recently, about why certain TRUNC commands would generate an error when applied to a TIMESTAMP value.  At first glance, TRUNC seems fine. SQL> select trunc(localtimestamp,’MM’) to_the_month from dual; TO_THE_MO ——— 01-OCT-16 1 row selected. SQL> select trunc(localtimestamp,’YYYY’) to_the_year from dual; TO_THE_YE ——— 01-JAN-16 1 row selected. SQL> select trunc(localtimestamp,’DD’) Read more