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… 😉

Fearless…

I saw Fearless (Huo Yuan Jia), the new Jet Li film, at the weekend. It’s based on a true story and has some seriously cool fight sequences. I’ve said it before and I’ll say it again, in my opinion Jet Li is the personification of martial arts. He is such a complete practitioner. He has technique, power, speed, flexibility, precision, finesse and grace by the bucket load. This is supposed to be his last martial arts epic, but I hope he keeps producing films with quality fight scenes like those in Hero, Unleashed and this film.

Cheers

Tim…

PS. Take your reading glasses, it’s subtitled 🙂

Happy Birthday to me… (Yesterday…)

It was my 37th birthday yesterday.

On the way to visit my family I hit a pigeon, which wasn’t very nice. There wasn’t anything I could do to avoid it, other than not drive that day… 🙁

My brother and his wife hired a bouncy castle to keep the kids occupied, which of course meant that all the adults went nuts on it for the whole day, including the grandparents whose ages varied from 65 up to 78. I hope I can still jump around on a bouncy castle when I’m 78 🙂 There were several tramplings, flying head-butts and body-slam incidents, but everyone survived with only mild bruising 🙂

Cool!

Cheers

Tim…

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…

West Mid Show…

I went to the West Mid Show yesterday. It’s an agricultural show held every year, near the town where I grew up. Although we did look at most of the exhibits, the main reason for visiting was the impressive display of tractors, diggers, combine harvester, ploughs, bailers, mowers, quad bikes etc. Most of the day was spent watching my 4 year old nephew play on all this equipment, punctuated by me nervously saying phrases like, “Please don’t do that!”, and, “Don’t break it, I don’t have a spare £26,000 to buy a broken tractor”. It’s amazing how a four year old can find novel ways to break agricultural equipment. 🙂

Cheers

Tim…

The Fast and the Furious: Tokyo Drift…

I went to see The Fast and the Furious: Tokyo Drift last night. Like the previous films, it’s very low on story and characters, but heavy on cars and racing. I’m not really into cars, but it is quite nice to turn your brain off just watch kids race around like nutters. If you liked the previous films, this is more of the same. If not, go and see XMen-3… 🙂

Cheers

Tim…

Link Spam and Great Kick…

I’ve been getting loads of link spam recently. Unfortunately, I get it from many angles:

  • Blog Comments – I installed the “WP-Hashcash” plugin and so far it has prevented all link spam, but I’m a bit fed up of deleting the moderated spam. It is a great plugin though!
  • Article Comments – I don’t have any anti-spam protection on these, so I guess I need to do a bit of programming.
  • Forum Posts – I use phpBB, so I guess I need to hunt for some plugins to help me out.
  • Forum Users – I get loads of users signing up who don’t post, but have dodgy homepage URLs. I guess they are all trying to use my forum as a link farm. I have a visual confirmation check on signup, so either they are signing up manually, or they have a way round this security feature.

I usually get rid of these spam posts pretty quickly, but it does get a bit depressing having to clean up on a daily basis. Drastic measure may be taken soon 🙂

On a lighter note, I got kicked in the solar plexus last night at Karate. It was a perfectly timed heel bang on target and it doubled me over straight away. After a few seconds of not breathing I dropped to my hands and knees and waited for a few more seconds before my lungs started to work again. It was a rather odd because I remember feeling like I wanted to breath, puke and laugh all at the same time. Not the most normal combinaton. The funniest thing was, even though I couldn’t breath I managed to croak out the words, “That was a good kick!”. You gotta laugh 🙂

Cheers

Tim…

Update: I’ve used the “WP-Hashcash” plugin approach on my article comments, so now they must be posted from my page, and the comment must be posted within 15 minutes of the initial request for the page. Let’s see how that works 🙂