The Curse of “Expertise”


Like everyone else, I make mistakes. While the results can sometimes be unfortunate, it’s also a truth that shouldn’t be ignored. A recurring problem though is that as a designated “expert” sometimes people don’t bother to test what I’ve given them. They just roll with it and then are surprised when their production installation goes awry.

I just ran into this situation again a few days ago. I was asked to help with a query that didn’t ever finish. I worked on it for a little while and came up with something that finished in a few seconds. Since the original didn’t finish, I didn’t have a predetermined set of results to test against. I manually walked through some sample data and my results seemed to tie out… so, it seemed like I was on the right track. I showed the client what I had and they were elated with the speed improvement.

I gave a brief description of what I had attempted to do and why it ran quickly. Then I asked them to test and contact me again if there were any questions.

The next day I got a message that they were very happy with the speed and were using it. I was glad to hear that but I also had been thinking that my query was extremely complicated, so even though it has apparently passed inspection I spent a few more minutes on it and came up with a simpler approach. This new method was almost as fast the other one but more significantly it returned more rows than my previous version. Clearly, at least one of them was incorrect.

With the simplified logic of the new version, it was much easier to verify that this second attempt was correct and the older more complicated version was wrong. I reached out to my client again and notified them of the change in query and problem I found. Then suggested they rerun more extensive tests anyway because I still could be wrong.

Fortunately, this second attempt did appear to be truly correct and the performance was still more than adequate.

Finding the name of an Oracle database


Oracle offers several methods for finding the name of a database.

More significantly, 12c introduces new functionality which may change the expected value from some of the old methods due to the multi-tenant feature.

Here are 11 methods for finding the name of a database.

SELECT ‘ora_database_name’ method, ora_database_name VALUE FROM DUAL
UNION ALL
SELECT ‘SYS_CONTEXT(userenv,db_name)’, SYS_CONTEXT(‘userenv’, ‘db_name’) FROM DUAL
UNION ALL
SELECT ‘SYS_CONTEXT(userenv,db_unique_name)’, SYS_CONTEXT(‘userenv’, ‘db_unique_name’) FROM DUAL
UNION ALL
SELECT ‘SYS_CONTEXT(userenv,con_name)’, SYS_CONTEXT(‘userenv’, ‘con_name’) FROM DUAL
UNION ALL
SELECT ‘SYS_CONTEXT(userenv,cdb_name)’, SYS_CONTEXT(‘userenv’, ‘cdb_name’) FROM DUAL
UNION ALL
SELECT ‘V$DATABASE name’, name FROM v$database
UNION ALL
SELECT ‘V$PARAMETER db_name’, VALUE
FROM v$parameter
WHERE name = ‘db_name’
UNION ALL
SELECT ‘V$PARAMETER db_unique_name’, VALUE
FROM v$parameter
WHERE name = ‘db_unique_name’
UNION ALL
SELECT ‘GLOBAL_NAME global_name’, global_name FROM global_name
UNION ALL
SELECT ‘DATABASE_PROPERTIES GLOBAL_DB_NAME’, property_value
FROM database_properties
WHERE property_name = ‘GLOBAL_DB_NAME’
UNION ALL
SELECT ‘DBMS_STANDARD.database_name’, DBMS_STANDARD.database_name FROM DUAL;

The results of these will vary by version, whether the db is a container or not,  and if its is a container, whether the query runs within a pluggable database or the container root database.
Note, the con_name and cdb_name options for the SYS_CONTEXT function do not exist in 11g or lower. So those queries in the union must be removed to execute in an 11g database. Within a pluggable database some of the methods recognize the PDB as the database, while others recognize the container as the database.

So, if you are using any of these methods in an 11g database and you upgrade to a 12c pluggable db, you may expect the PDB name to be returned, but instead you’ll get the CDB name instead.
Also note, some of the methods always return the name in capital letters, others will return the exact value used to create the database.

METHOD 12c

Non-Container

12c

CDB$Root

12c

PDB

11g
GLOBAL_NAME global_name SDS12CR1 SDSCDB1 SDSPDB1 SDS11GR2
DATABASE_PROPERTIES GLOBAL_DB_NAME SDS12CR1 SDSCDB1 SDSPDB1 SDS11GR2
DBMS_STANDARD.database_name SDS12CR1 SDSCDB1 SDSPDB1 SDS11GR2
ora_database_name SDS12CR1 SDSCDB1 SDSPDB1 SDS11GR2
V$DATABASE name SDS12CR1 SDSCDB1 SDSCDB1 SDS11GR2
SYS_CONTEXT(userenv,db_name) sds12cr1 sdscdb1 sdscdb1 sds11gr2
SYS_CONTEXT(userenv,db_unique_name) sds12cr1 sdscdb1 sdscdb1 sds11gr2
SYS_CONTEXT(userenv,con_name) sds12cr1 CDB$ROOT SDSPDB1 n/a
SYS_CONTEXT(userenv,cdb_name) sdscdb1 sdscdb1 n/a
V$PARAMETER db_name sds12cr1 sdscdb1 sdscdb1 sds11gr2
V$PARAMETER db_unique_name sds12cr1 sdscdb1 sdscdb1 sds11gr2

On a related note, only the container of a multi-tenant database has instances. So, while PDBs can declare their own name for the database level with some methods above; there is no corresponding PDB-instance name functionality.

See you in Las Vegas!


I’m flying out tomorrow for Collaborate 16.
Looking forward to another great conference.

I’m presenting again this year.
I’ll be speaking on Tuesday, at 2:15
“Why Developers Need to Think like DBAs, Why DBAs Need to Think like Developers”
Session 1355 in Jasmine C

How Oracle Stores Passwords


Several years ago I wrote a small summary of the Oracle password hashing and storage for versions up to 11g.

Today I’ve completed my update of that article up to 12.1.0.2, including code to mimic generation of passwords given the appropriate salts.
The initial publication is in PDF format, I may convert and reformat it to other forms for better distribution.

The pdf file can be downloaded from my dropbox here.

It was interesting and enjoyable digging into the details of the hashes and how they change between versions and interact with the case-sensitivity settings.

I hope you enjoy it as much as I did writing it.

Splitting a clob into rows


I’ve used this tool for a wide variety of other parsing projects. One of the interesting tuning techniques I used was to pull the clob apart into 32K varchar2 chunks.
It is possible to split the clob directly using the DBMS_LOB package or through the overloaded SQL functions; but clobs are expensive objects. Varchar2 variables on the other hand are relatively light weight making the sub-parsing within them much faster. Doing this does take a little bit of care though to make sure the chunks don’t accidentally split a line in two.

Also, I do make the assumption that no one line will be more than 32K long which is fine for this function anyway since the output is a SQL collection with a varchar2 limit of 4000 bytes.
The returned VCARRAY type is a simple table collection type.

CREATE OR REPLACE TYPE VCARRAY as table of varchar2(4000)

I wrote this originally in 9i. With 12c support for 32K varchar2 in SQL I may need to revisit it and make a new version.

CREATE OR REPLACE FUNCTION split_clob(p_clob IN CLOB, p_delimiter IN VARCHAR2 DEFAULT CHR(10))
    RETURN vcarray
    PIPELINED
IS
    --                    .///.
    --                   (0 o)
    ---------------0000--(_)--0000---------------
    --
    --  Sean D. Stuber
    --  sean.stuber@gmail.com
    --
    --             oooO      Oooo
    --------------(   )-----(   )---------------
    --             \ (       ) /
    --              \_)     (_/

    c_chunk_limit   CONSTANT INTEGER := 32767;
    v_clob_length            INTEGER := DBMS_LOB.getlength(p_clob);
    v_clob_index             INTEGER;
    v_chunk                  VARCHAR2(32767);
    v_chunk_end              INTEGER;
    v_chunk_length           INTEGER;
    v_chunk_index            INTEGER;
    v_delim_len              INTEGER := LENGTH(p_delimiter);
    v_line_end               INTEGER;
BEGIN
    v_clob_length := DBMS_LOB.getlength(p_clob);
    v_clob_index := 1;

    WHILE v_clob_index <= v_clob_length
    LOOP
        /*
            Pull one 32K chunk off the clob at a time.
            This is because it's MUCH faster to use built in functions
            on a varchar2 type than to use dbms_lob functions on a clob.
        */
        v_chunk := DBMS_LOB.SUBSTR(p_clob, c_chunk_limit, v_clob_index);

        IF v_clob_index > v_clob_length - c_chunk_limit
        THEN
            -- if we walked off the end the clob,
            -- then the chunk is whatever we picked up at the end
            -- delimited or not
            v_clob_index := v_clob_length + 1;
        ELSE
            v_chunk_end := INSTR(v_chunk, p_delimiter, -1);

            IF v_chunk_end = 0
            THEN
                DBMS_OUTPUT.put_line('No delimiters found!');
                RETURN;
            END IF;

            v_chunk := SUBSTR(v_chunk, 1, v_chunk_end);
            v_clob_index := v_clob_index + v_chunk_end + v_delim_len - 1;
        END IF;

        /*
            Given a varchar2 chunk split it into lines
        */

        v_chunk_index := 1;
        v_chunk_length := NVL(LENGTH(v_chunk), 0);

        WHILE v_chunk_index <= v_chunk_length
        LOOP
            v_line_end := INSTR(v_chunk, p_delimiter, v_chunk_index);

            IF v_line_end = 0 OR (v_line_end - v_chunk_index) > 4000
            THEN
                PIPE ROW (SUBSTR(v_chunk, v_chunk_index, 4000));
                v_chunk_index := v_chunk_index + 4000;
            ELSE
                PIPE ROW (SUBSTR(v_chunk, v_chunk_index, v_line_end - v_chunk_index));
                v_chunk_index := v_line_end + v_delim_len;
            END IF;
        END LOOP;
    END LOOP;

    RETURN;
EXCEPTION
    WHEN no_data_needed
    THEN
        NULL;
END split_clob;
/

Thank you, thank you, thank you!


A little while ago Oracle announced the winners of the Oracle Database Developer Choice Awards and I was a winner in both of categories I was nominated,

SQL and PL/SQL

I was surprised and overjoyed when I was notified that I had not only been nominated; but named a finalist.
I’m truly humbled by the supportive votes I received.

I’m also inspired to try to give back even more and I’m got a few ideas brewing for my next few articles.

Thank you again!

Only a few hours left in the Oracle Database Developer Choice Awards


If you haven’t voted yet, please do so soon! Voting ends 3pm US/Pacific time.

I’ve been nominated in the SQL and PL/SQL categories

https://community.oracle.com/community/database/awards/sql-voting
https://community.oracle.com/community/database/awards/plsql-voting

%d bloggers like this: