Learning is not a spectator sport

Complete listing of posts

  • A little bit of PLSQL can be a huge win

    A little bit of PLSQL can be a huge win

    I rarely blog about PLSQL for a very simple reason. It quickly becomes like Halloween night because all the ghouls and goblins come out to play . No matter what I post, I immediately get inundated with a swag of comments and tweets about “stored procedures are evil”, “PLSQL is vendor lock-in”, “Server side code… Read more

  • DBMS_METADATA for INDEXES

    DBMS_METADATA for INDEXES

    Just a quick tip on using DBMS_METADATA that caught me out the other day. If you have a table and you wish to get the DDL that is “related” to the table, for example object grants or audit settings etc, then there is a GET_DEPENDENT_DDL API to do this. For example, to get the object… Read more

  • Enforcing DEFAULT values

    Enforcing DEFAULT values

    Handling default values on columns has been an age old problem that we solved in 12c with extensions to the default clause. Originally the issue with a DEFAULT setting was that is was only ever considered if the column was not specified at all in the INSERT statement. For example, here’s a subset of the… Read more

  • Block recovery a lot easier than I thought

    Block recovery a lot easier than I thought

    I tweeted this snippet the other day because to be honest I was just overjoyed about being able to get the travel laptop out of its case and fire it up again. In every virtual Office Hours session and every virtual event I’ve done in the past two years, I’ve urged people to take whatever… Read more

  • When re-ordering columns is warranted

    When re-ordering columns is warranted

    Nearly 10 years ago(!!!) I blogged about how you can toggle columns to invisible and back again as a means of re-ordering the columns in the table. Of course, this is purely a logical re-ordering of the columns, and the post was mainly meant as a bit of a laugh, but I see references to… Read more

  • Don’t run tight on PGA

    Don’t run tight on PGA

    The database on my laptop seemed slow this morning. Normally I would put this down to the fact that I’ve got 30 million browser tabs open and never seem to be able to close them down , but today things seemed especially slow, even just navigating around with SQL Developer etc. So I threw a… Read more

  • After you clone your pluggable database

    After you clone your pluggable database

    Recently I cloned one my (pluggable) databases to a throwaway copy so that I could do some testing of an upcoming application deployment against comparable data volumes to make sure that the deployment would go smoothly. The clone process worked fine; that’s one of the things I enjoy about pluggable databases, just single CREATE command… Read more

  • Cleaner DDL than DBMS_METADATA

    Cleaner DDL than DBMS_METADATA

    Most of us with any kind of longevity in the Oracle world will remember the days when we wrote our own scripts to generate the DDL for a database object. We’d go hunting around in DBA_TABLES, DBA_TAB_COLUMNS, DBA_CONSTRAINTS etc to build a script that could be checked into source control or similar. Of course, as… Read more

  • INITRANS grabs a little more space

    INITRANS grabs a little more space

    INITRANS defines how many concurrent transactions you can have within a single datablock for a segment (table, materialized view, etc). A quick perusal of the documentation for INITRANS and you’ll see that it defaults to 1. We can easily verify this by creating a table and checking the data dictionary. SQL> create table t (… Read more

  • V$SESSION_LONGOPS and parallel query

    V$SESSION_LONGOPS and parallel query

    For pretty much as long as I can remember, the view V$SESSION_LONGOPS has existed. It has a suite of columns within it, but almost every usage I’ve ever seen has been about one thing – namely, predicting how long an operation will take before it finishes. Before the advent of SQL Monitoring which allows digging… Read more

  • A new dictionary view for privileges

    A new dictionary view for privileges

    So I tweeted this out on Friday and this reflects one of my joys with working with Oracle technology. The field is so broad and so deep, there is never a point where you would ever say “Yup…I’ve finished…I’ve learnt it all”. Many people think that as we get more experience with the product, that… Read more

  • Awesome DATE / TIMESTAMP enhancement in Autonomous Database

    Awesome DATE / TIMESTAMP enhancement in Autonomous Database

    Date management is a commonly encountered issue when customers want to move their database to a cloud environment. If you are using a dedicated VM, then of course you can set the timezone to whatever is appropriate to your region. However, a dedicated VM in a cloud environment is hardly getting the benefit of “cloud”,… Read more