Neat Tricks

January 27, 2010 (Modified December 13, 2011)

Over the years I have seen a couple of interesting approaches to doing certain tasks with Oracle databases – something that makes me think – wow, that’s neat.  Below are a couple of the items that I have found to be interesting, but I probably could not make interesting enough for a dedicated article.

First, an example that retrieves the DDL needed to recreate three tables and the indexes for those tables, output to a file.  The definitions are complete with foreign keys, column constraints, and storage parameters.  Note that in some cases it might be necessary to fix line wrapping problems in the resulting text file (specifically if a line is longer than 200 characters).

SET PAGESIZE 0
SET LONG 90000
SET LINESIZE 200
COLUMN OBJECT_DEF FORMAT A200
SPOOL 'GETMETA.SQL'

SELECT
  DBMS_METADATA.GET_DDL('TABLE',TABLE_NAME,OWNER) OBJECT_DEF
FROM
  DBA_TABLES
WHERE
  TABLE_NAME IN ('T1','T2','T3')
UNION ALL
SELECT
  DBMS_METADATA.GET_DDL('INDEX',INDEX_NAME,OWNER) OBJECT_DEF
FROM
  DBA_INDEXES
WHERE
  TABLE_NAME IN ('T1','T2','T3');

SPOOL OFF

——

Next, an example that creates a database link to another database.  Note that the first example fails with some configurations – this is intentional in this example.

A Failed Attempt at a DB Link:

CREATE PUBLIC DATABASE LINK TEST_LINK CONNECT TO MY_USERNAME IDENTIFIED BY MY_PASS_HERE USING 'TEST';

Now, trying to use the connection:

SQL> SELECT COUNT(*) FROM T1@TEST_LINK; SELECT COUNT(*) FROM T1@TEST_LINK                                  *
ERROR at line 1:
ORA-02085: database link TEST_LINK.WORLD connects to TEST.WORLD

“.WORLD”? – I don’t recall specifying that.  I guess that we should consult the documentation.

 “If the value of the GLOBAL_NAMES initialization parameter is TRUE, then the database link must have the same name as the database to which it connects. If the value of GLOBAL_NAMES is FALSE, and if you have changed the global name of the database, then you can specify the global name.”

Now, creating the database link correctly (note that a COMMIT or ROLLBACK should be used at some point after performing a query on a remote database):

CREATE PUBLIC DATABASE LINK TEST CONNECT TO MY_USERNAME IDENTIFIED BY MY_PASS_HERE USING 'TEST.WORLD';

SELECT COUNT(*) FROM T1@TEST; COMMIT;

——

Next, recovering a table from the Oracle 10g (and above) recyclebin:

FLASHBACK TABLE T1 TO BEFORE DROP;

——

Output the columns in a table’s rows as semicolon delimited values (I think that I first saw this example on asktom.oracle.com):

SELECT
  REGEXP_REPLACE(COLUMN_VALUE,' *<[^>]*>[^>]*>',';')
FROM
  TABLE(XMLSEQUENCE(CURSOR(
    SELECT
      *
    FROM
      MY_TABLE)));

——

Output the columns in a table’s rows as XML:

SELECT
  *
FROM
  TABLE(XMLSEQUENCE(CURSOR(
    SELECT
      *
    FROM
      MY_TABLE)));

——

Output the text contained in a BLOB column as a VARCHAR2:

SELECT
  UTL_RAW.CAST_TO_VARCHAR2(DBMS_LOB.SUBSTR(BITS,32000,1))
FROM
  T1
WHERE
  C1='123';

——

View the internal representation of data – how is it stored in the database:

SELECT
  DUMP(DESCRIPTION)
FROM
  MY_TABLE
WHERE
  PART_ID='123';

——

Disable AWR collection on Oracle 10g if a license for the Diagnostic Pack was not purchased (there is an easy way to do this in 11g):

SQL> @DBMSNOAWR.PLB

SQL> BEGIN DBMS_AWR.DISABLE_AWR(); END;

SQL> /

The DBMSNOAWR package may be downloaded from Metalink (MOS). (Note: link updated December 13, 2011)

——

View jobs scheduled in the 10g Enterprise Manager Database Control:

SELECT
  MJ.JOB_OWNER,
  MJ.JOB_NAME,
  JS.FREQUENCY_CODE,
  TO_CHAR(JS.START_TIME,'MM/DD/YYYY HH24:MI') START_TIME,
  TO_CHAR(JS.END_TIME,'MM/DD/YYYY HH24:MI') END_TIME,
  JS.EXECUTION_HOURS,
  JS.EXECUTION_MINUTES,
  JS.INTERVAL,
  JS.MONTHS,
  JS.DAYS
FROM
  SYSMAN.MGMT_JOB MJ,
  SYSMAN.MGMT_JOB_SCHEDULE JS
WHERE
  MJ.EXPIRED=0
  AND MJ.SCHEDULE_ID=JS.SCHEDULE_ID;

——

Manually set the value of MBRC in SYS.AUX_STATS$:

EXEC DBMS_STATS.SET_SYSTEM_STATS('MBRC',16) 

——

Unsetting the DB_FILE_MULTIBLOCK_READ_COUNT parameter from the spfile to allow Oracle to auto-tune the parameter value (in 10.2.0.1 and above, requires DB bounce):

ALTER SYSTEM RESET DB_FILE_MULTIBLOCK_READ_COUNT SCOPE=SPFILE SID='*';

——

Select a random number between 1 and 50 (after seeding the random number generator):

EXEC DBMS_RANDOM.SEED(0)

SELECT
  ROUND(DBMS_RANDOM.VALUE(1,50)) MY_NUMBER
FROM
  DUAL;

 MY_NUMBER
----------
        42

——

Select a random 10 character string:

SELECT
  DBMS_RANDOM.STRING('A',10) MY_STRING
FROM
  DUAL;

MY_STRING
----------

SRnFjRGbiw

——

Count to 10:

SELECT
  ROWNUM MY_NUMBER
FROM
  DUAL
CONNECT BY
  LEVEL<=10;

 MY_NUMBER
----------
         1
         2
         3
         4
         5
         6
         7
         8
         9
        10

——

Randomly order a row source (in this case the numbers from 1 to 10):

SELECT
  MY_NUMBER
FROM
  (SELECT
    ROWNUM MY_NUMBER
  FROM
    DUAL
  CONNECT BY
    LEVEL<=10)
ORDER BY
  DBMS_RANDOM.VALUE;

 MY_NUMBER
----------
        10
         5
         4
         9
         1
         3
         7
         6
         8
         2