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

A day late but still fun


Yesterday Kim Berg Hansen posted a couple entries to his blog about scraping and parsing the Oracle Developer Choice Awards voting pages and building summaries of the results.

I’ve been doing something similar since the voting began but took a slightly different approach. I do like the idea of storing them in a table to be able to derive a history but I never did that, I instead simply looked at a snapshot in time and didn’t bother to keep trends.

Kim took two approaches, one using an apex call which he has said didn’t work very reliably and another where he simply pastes the html himself.

My method is similar but I use UTL_HTTP calls to extract the html.
Also, my function returns the raw html and then I do cleanup in my sql statement, he cleans his up within his scraping function.
Since he’s storing the results that makes sense, why incur the cost of cleanup each time he read his table?

Here is my version of the scraping function

CREATE OR REPLACE FUNCTION read_oracle_dev_choice(p_category IN VARCHAR2)
    RETURN CLOB
IS
    v_http_request    UTL_HTTP.req;
    v_http_response   UTL_HTTP.resp;
    v_buffer          VARCHAR2(32767);
    v_clob            CLOB;
    v_req_ctx         UTL_HTTP.request_context_key;
BEGIN
    v_req_ctx := UTL_HTTP.create_request_context('file:/home/oracle/devchoice', 'pa55w0rd', FALSE);

    v_http_request :=
        UTL_HTTP.begin_request('https://community.oracle.com/community/database/awards/' || p_category || '-voting/',
                               'GET',
                               'HTTP/1.1',
                               v_req_ctx);

    v_http_response := UTL_HTTP.get_response(v_http_request);
    --DBMS_OUTPUT.put_line('Response status code: ' || v_http_response.status_code);
    --DBMS_OUTPUT.put_line('Response reason phrase: ' || v_http_response.reason_phrase);
    --DBMS_OUTPUT.put_line('Response HTTP version: ' || v_http_response.http_version);

    DBMS_LOB.createtemporary(v_clob, TRUE);

    BEGIN
        LOOP
            UTL_HTTP.read_text(v_http_response, v_buffer, 32767);
            DBMS_LOB.writeappend(v_clob, LENGTH(v_buffer), v_buffer);
        END LOOP;
    EXCEPTION
        WHEN UTL_HTTP.end_of_body
        THEN
            UTL_HTTP.end_response(v_http_response);
    END;

    UTL_HTTP.destroy_request_context(v_req_ctx);

    RETURN v_clob;
END;

Once this scraping function has been created and you have your corresponding ACL and wallet entries, you can extract the results with a single SQL statement.

  SELECT *
    FROM (SELECT x.*, RANK() OVER(PARTITION BY category ORDER BY (score_rank + upvote_rank + uppct_rank)) avg_rank
            FROM (SELECT x.*,
                         RANK() OVER(PARTITION BY category ORDER BY score DESC) score_rank,
                         RANK() OVER(PARTITION BY category ORDER BY upvotes DESC) upvote_rank,
                         RANK() OVER(PARTITION BY category ORDER BY uppct DESC) uppct_rank
                    FROM (SELECT x.*, ROUND(100 * upvotes / totalvotes, 2) uppct
                            FROM (SELECT category,
                                         finalist,
                                         score,
                                         (score + 10 * votes) / 20 upvotes,
                                         (10 * votes - score) / 20 downvotes,
                                         votes totalvotes
                                    FROM (   SELECT cat category,
                                                    TO_NUMBER(REGEXP_SUBSTR(score, '[0-9]+')) score,
                                                    TO_NUMBER(REGEXP_SUBSTR(votes, '[0-9]+')) votes,
                                                    TRIM(finalist) finalist
                                               FROM (SELECT 'sql' cat, read_oracle_dev_choice('sql') html FROM DUAL
                                                     UNION ALL
                                                     SELECT 'plsql', read_oracle_dev_choice('plsql') FROM DUAL
                                                     UNION ALL
                                                     SELECT 'apex', read_oracle_dev_choice('apex') FROM DUAL
                                                     UNION ALL
                                                     SELECT 'db-design', read_oracle_dev_choice('db-design') FROM DUAL
                                                     UNION ALL
                                                     SELECT 'ords', read_oracle_dev_choice('ords') FROM DUAL),
                                                    XMLTABLE(
                                                        '//div[@class="jive-content-ideas-list-item clearfix"]'
                                                        PASSING xmltype(
                                                                    REGEXP_REPLACE(
                                                                        REGEXP_REPLACE(
                                                                            REGEXP_REPLACE(
                                                                                html,
                                                                                '^.+?
', '', 1, 0, 'n'), '.+$', '', 1, 0, 'n'), CHR(38) || '[^;]+?;')) COLUMNS score VARCHAR2(20) PATH './div/div/strong', votes VARCHAR2(20) PATH './div/span/span[@class="idea-vote-count"]', finalist VARCHAR2(20) PATH './div[@class="jive-content"]/div/div[@class="jive-content-title"]') x)) x) x) x) ORDER BY category DESC, score DESC;

And this produces results like this:


CATEGORY  FINALIST                  SCORE    UPVOTES  DOWNVOTES TOTALVOTES      UPPCT SCORE_RANK UPVOTE_RANK UPPCT_RANK   AVG_RANK
--------- -------------------- ---------- ---------- ---------- ---------- ---------- ---------- ----------- ---------- ----------
sql       Emrah Mete                 3010        335         34        369      90.79          1           1          1          1
sql       Sayan Malakshinov          1780        210         32        242      86.78          2           2          2          2
sql       Sean Stuber                 790         98         19        117      83.76          3           3          3          3
sql       Kim Berg Hansen             680         86         18        104      82.69          4           4          4          4
sql       Erik Van Roon               400         55         15         70      78.57          5           5          6          5
sql       Justin Cave                 300         44         14         58      75.86          6           6          8          7
sql       Matthias Rogel              290         38          9         47      80.85          7           7          5          6
sql       Stew Ashton                 260         38         12         50         76          8           7          7          8
plsql     Adrian Billington          1080        112          4        116      96.55          1           2          1          1
plsql     Roger Troller              1030        117         14        131      89.31          2           1          4          2
plsql     Sean Stuber                 910         97          6        103      94.17          3           3          2          3
plsql     Patrick Barel               690         79         10         89      88.76          4           4          5          4
plsql     Morten Braten               620         68          6         74      91.89          5           5          3          4
plsql     Kim Berg Hansen             440         51          7         58      87.93          6           6          7          6
plsql     Bill Coulam                 400         46          6         52      88.46          7           7          6          7
ords      Dietmar Aust               1240        128          4        132      96.97          1           1          1          1
ords      Dimitri Gielis              800         86          6         92      93.48          2           2          2          2
ords      Morten Braten               430         50          7         57      87.72          3           3          4          3
ords      Kiran Pawar                 350         39          4         43       90.7          4           4          3          4
ords      Anton Nielsen               240         28          4         32       87.5          5           5          5          5
ords      Tim St. Hilaire             130         16          3         19      84.21          6           6          6          6
db-design Heli Helskyaho             1030        119         16        135      88.15          1           1          2          1
db-design Michelle Kolbe              630         75         12         87      86.21          2           2          3          2
db-design Rob Lockard                 520         57          5         62      91.94          3           3          1          2
db-design Mark Hoxey                  160         23          7         30      76.67          4           4          4          4
apex      Jari Laine                  720         78          6         84      92.86          1           1          4          1
apex      Morten Braten               680         73          5         78      93.59          2           2          3          2
apex      Juergen Schuster            560         58          2         60      96.67          3           3          1          2
apex      Kiran Pawar                 430         48          5         53      90.57          4           4          5          5
apex      Karen Cannell               280         30          2         32      93.75          5           5          2          4
apex      Paul MacMillan              130         21          8         29      72.41          6           6          7          6
apex      Trent Schafer               120         17          5         22      77.27          7           7          6          7

I hope you find this helpful and a fun exercise to pursue.

If you haven’t voted, I encourage you to do so here…

I’m a finalist in the SQL and PL/SQL Categories and would, of course, appreciate a vote, be sure to check the other categories though too and vote up the finalists there that have helped you in some way.
Also note, you can vote up more than one person in a category, so by all means vote up as many as you want, including the other finalists in my categories if you want.

p.s. – an interesting “feature” of the web scraping is the URL works better when it includes a trailing slash “/”. I have no idea why this should help, but without it the returned CLOB will sometimes (often) contain “Unexpected Error Occurred” rather than the expected results. Occasionally the reverse happens though. If anyone can explain why this is, I would appreciate it.

Follow

Get every new post delivered to your Inbox.

%d bloggers like this: