Neat Tricks

27 01 2010

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

Actions

Information

8 responses

27 01 2010
Anand

As said “that a COMMIT or ROLLBACK should be used at some point after performing a query on a remote database”

Why is it required?

Regards,
Anand

27 01 2010
Charles Hooper

Good question. I tried to put together a quick test case to demonstrate the potential problem, but I have not yet been able to assemble a reliable test case.

From:

http://www.jlcomp.demon.co.uk/faq/dblink_commit.html

“Why does it seem that a SELECT over a db_link requires a commit after execution ?” “Because it does!” – note that the versions listed in the article are 7.0 – 9.2, so there is a chance that the behavior might have changed for more recent releases.

From:

http://download.oracle.com/docs/cd/B19306_01/server.102/b14231/ds_txns.htm

“Note that if a distributed transaction is set to read-only, then it does not use undo segments. If many users connect to the database and their transactions are not set to READ ONLY, then they allocate undo space even if they are only performing queries.”

Possibly related:

http://coskan.wordpress.com/2008/01/16/if-you-access-a-database-link-in-a-session-then-the-link-remains-open-until-you-close-the-session-ora-02020/

Maybe someone else reading this blog article will offer advice. Based on prior experience, it is still necessary in Oracle 10g R2 to issue a COMMIT or ROLLBACK after selecting data through a database link (the COMMIT or ROLLBACK does not need to happen immediately).

2 02 2010
David Mann

I think I would only worry about a Commit or Rollback if I saw evidence on the target side that a distributed transaction had been started. This can be checked with DBA_2PC_PENDING and DBA_2PC_NEIGHBORS views. (More info in Metalink 100664.1 “How to Troubleshoot Distributed Transactions”)…

Hopefully newer versions of Oracle are smart enough to only enter into a distributed transaction if an INSERT, UPDATE, or DELETE are issued against the target database.

2 02 2010
Anand

Hi Charles,

In reference to “creates a database link to another database”, can you have a look at the below blog and comment.I am little confused as my results are not matching with the oracle documentation.

http://aprakash.wordpress.com/2010/01/30/db-link-and-global-name/

Regards,
Anand

3 02 2010
Charles Hooper

I wonder if it is a mistake in the copy of the documentation that you have.

This is what you quoted:
“The name that you give to a link on the local database depends on whether the remote database that you want to access enforces global naming. If the remote database enforces global naming, then you must use the remote database global database name as the name of the link.”

The above is different from what I quoted:

http://download.oracle.com/docs/cd/B12037_01/server.101/b10759/statements_5005.htm

”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.”

Also found in the documentation for the latest release:

http://download.oracle.com/docs/cd/E11882_01/server.112/e10592/statements_5005.htm

I believe that the quotes that I supplied from the documentation agree with your test – it is apparently the value of GLOBAL_NAMES in the local database, not the remote database that determines whether you can change the DB link name.

3 02 2010
3 02 2010
Charles Hooper

Interesting – it seems that the authors of the documentation cannot decide what is correct, even in the latest release.

http://download.oracle.com/docs/cd/E11882_01/server.112/e10595/ds_admin001.htm

“If the remote database enforces global naming, then you must use the remote database global database name as the name of the link.”

http://download.oracle.com/docs/cd/E11882_01/server.112/e10595/ds_concepts002.htm

“When you set the initialization parameter GLOBAL_NAMES to TRUE, the database ensures that the name of the database link is the same as the global database name of the remote database. For example, if the global database name for hq is hq.acme.com, and GLOBAL_NAMES is TRUE, then the link name must be called hq.acme.com. Note that the database checks the domain part of the global database name as stored in the data dictionary, not the DB_DOMAIN setting in the initialization parameter file… If you set the initialization parameter GLOBAL_NAMES to FALSE, then you are not required to use global naming. You can then name the database link whatever you want. For example, you can name a database link to hq.acme.com as foo.”

I think that this is another example where a test case, like you created, is valuable.

20 02 2010
Blogroll Report 22/01/2009 – 29/01/2010 « Coskan’s Approach to Oracle

[...] 25-Tips and tricks about some Oracle topics Charles Hooper- Neat Tricks [...]

Leave a Reply

Fill in your details below or click an icon to log in:

WordPress.com Logo

You are commenting using your WordPress.com account. Log Out / Change )

Twitter picture

You are commenting using your Twitter account. Log Out / Change )

Facebook photo

You are commenting using your Facebook account. Log Out / Change )

Google+ photo

You are commenting using your Google+ account. Log Out / Change )

Connecting to %s




Follow

Get every new post delivered to your Inbox.

Join 147 other followers

%d bloggers like this: