Category: Oracle

Hallelujah…

OK. It’s been a long wait, but 10g Release 2 is now available for Tru64. There is still no sign of the 10.1.0.4 or 10.1.0.5 patches, but you can’t have everything. Of course, the big issue now is, how can I persuade the company to go for a migration???
This is definitely the last release for Tru64, so we’ve got to move to it, but who knows when?

Cheers

Tim…

Oracle ACE of the Year…

I got home last night to find a message on my phone trying to arrange an interview for Oracle Magazine about being “Oracle ACE of the Year”. Needless to say this was a bit of a shock, since it was the first thing I’d heard about it.

It seems Oracle Magazine had already contacted me about it, but either my spam filter had deleted the message, or I deleted it without reading it. 🙁

Anyway, I did the interview later that night and I’ve got to have a photo taken for the magazine. The profile and photo will be in the November/December issue of Oracle Magazine.

This year has been totally mad. First I get nominated as an ACE, then this. It’s all a bit scary and humbling.

This award went to Mark Rittman last year, so I expect him to pass on the sash and tiara in November. I’ll try not to do a Gwyneth Paltrow. 🙂

Cheers

Tim…

Rules Manager (tip of the iceberg)…

I’ve been having a play with the Rules Manager introduced in Oracle 10g Release 2. There is quite a bit of functionality in there, so my article is just a “tip of the iceberg” type of thing.

This would have come in extremely handy in two previous jobs, both of which required quite complex state-event engines. It certainly would have made the decision processes a little more flexible, provided the performance was acceptable.

Cheers

Tim…

Loading LOB Data Using SQL*Loader…

In a recent post I discussed the use of BLOBs and External Tables. As a result of that I thought I better take a look at how SQL*Loader copes with LOBs, so I wrote Loading LOB Data Using SQL*Loader. SQL*Loader has a number of methods for loading LOBs, but I focussed on the one that most closely matches the method used by external tables.

Cheers

Tim…

A Tale of 4 Schedulers…

A long time ago in a galaxy far, far away, there was only one Oracle scheduler. It was managed using the DBMS_JOB package and the job information was stored in a single table.

SELECT table_name
FROM   dba_tables
WHERE  owner = 'SYS'
AND    table_name LIKE 'JOB$%'
ORDER BY table_name;

TABLE_NAME
------------------------------
JOB$

1 row selected.

SQL>

Information about scheduled jobs was available from the “DBA_JOBS%” views.

SELECT view_name
FROM   dba_views
WHERE  owner = 'SYS'
AND    view_name LIKE 'DBA_JOBS%'
ORDER BY view_name;

VIEW_NAME
------------------------------
DBA_JOBS
DBA_JOBS_RUNNING

2 rows selected.

SQL>

Oracle 10g introduced a new scheduler that was managed using the DBMS_SCHEDULER package, whose job information was stored in the “SCHEDULER$%” tables.

SELECT table_name
FROM   dba_tables
WHERE  owner = 'SYS'
AND    table_name LIKE 'SCHEDULER$%'
ORDER BY table_name;

TABLE_NAME
------------------------------
SCHEDULER$_CHAIN
.
.
.
SCHEDULER$_WINGRP_MEMBER

22 rows selected.

SQL>

Information about scheduled jobs for this new scheduler was available from the “DBA_SCHEDULER%” views.

SELECT view_name
FROM   dba_views
WHERE  owner = 'SYS'
AND    view_name LIKE 'DBA_SCHEDULER%'
ORDER BY view_name;

VIEW_NAME
------------------------------
DBA_SCHEDULER_CHAINS
.
.
.
DBA_SCHEDULER_WINGROUP_MEMBERS

19 rows selected.

SQL>

In addition to the two internal schedulers, the OEM 10g DBConsole had its own scheduler, which stored job information in the “MGMT_JOB%” tables.

SELECT table_name
FROM   dba_tables
WHERE  owner = 'SYSMAN'
AND    table_name LIKE 'MGMT_JOB%'
ORDER BY table_name;

TABLE_NAME
------------------------------
MGMT_JOB
.
.
.
MGMT_JOB_VALUE_PARAMS

44 rows selected.

SQL>

To confuse matters further, the OEM 10g Grid Control repository had a similar scheduler to OEM 10g DBConsole, but with a few extra tables.

SELECT table_name
FROM   dba_tables
WHERE  owner = 'SYSMAN'
AND    table_name LIKE 'MGMT_JOB%'
ORDER BY table_name;

TABLE_NAME
------------------------------
MGMT_JOB
.
.
.
MGMT_JOB_VALUE_PARAMS

53 rows selected.

SQL>

Faced with the choice of four ways to schedule a job, most DBAs called “Tim Hall” decided to pick a single scheduler and keep things simple 🙂

Cheers

Tim…

PS. Don’t even get me started on OS schedulers and the Oracle Applications scheduler… 😉

Is read-only access to production systems safe?

There is an interesting thread on the Dizwell Forum about access to production systems using tools like TOAD. Of course, this begs the question, is there such a thing as “safe” read-only access to production data?

If you are allowing read-only access to tables on a production system, you are also allowing record and table locking. If you don’t believe me, take a look at this:

tim_hall@dev1> CONN sys/????@db10g AS SYSDBA
Connected.
sys@db10g> CREATE USER test_user1 IDENTIFIED BY test_user1 QUOTA UNLIMITED ON USERS;

User created.

sys@db10g> GRANT CONNECT, CREATE TABLE TO test_user1;

Grant succeeded.

sys@db10g> CREATE USER test_user2 IDENTIFIED BY test_user2 QUOTA UNLIMITED ON USERS;

User created.

sys@db10g> GRANT CONNECT TO test_user2;

Grant succeeded.

sys@db10g> CONN test_user1/test_user1@db10g
Connected.
test_user1@db10g> CREATE TABLE test_tab1 (
2    id  NUMBER
3  );

Table created.

test_user1@db10g> INSERT INTO test_tab1 (id) VALUES (1);

1 row created.

test_user1@db10g> GRANT SELECT ON test_tab1 TO test_user2;

Grant succeeded.

test_user1@db10g> CONN test_user2/test_user2@db10g
Connected.
test_user2@db10g> DECLARE
2    l_id  test_user1.test_tab1.id%TYPE;
3  BEGIN
4    SELECT id
5    INTO   l_id
6    FROM   test_user1.test_tab1
7    WHERE  id = 1
8    FOR UPDATE;
9
10    ROLLBACK;
11  END;
12  /

PL/SQL procedure successfully completed.

test_user2@db10g> LOCK TABLE test_user1.test_tab1 IN EXCLUSIVE MODE;

Table(s) Locked.

test_user2@db10g> ROLLBACK;

Rollback complete.

test_user2@db10g>

Looks like the SELECT privilege is not so safe after all 🙁

Of course, you don’t need direct access to the tables. You could always provide access to the data via APIs, but that’s not what your average TOAD user wants to hear!

Cheers

Tim…

Converting Documents to Highlighted HTML using Oracle Text…

I had a question on my forum about converting documents stored in BLOBs to HTML with highlighted search terms. On reading this my instant reaction was, “I don’t have a clue!”, but it’s actually quite easy using the “CTX_DOC.MARKUP” procedure. Here’s a simple example:

https://oracle-base.com/articles/10g/ctx_doc_markup.php

Cheers

Tim…