Blocked Patch files cause OPatch to fail with error code = 170

I had to apply a CPU patch to 9.2.0.7 Oracle Home on Windows 2003 Server box.

Opatch utility was failing with the following error:


The patch directory area must be a number.

ERROR: OPatch failed because of problems in patch area.
OPatch returns with error code = 170


Well, there are quite few reasons why you may get this error when you try to apply a patch using Opatch.
Many of them are published in some of the following Metalink notes:

Note:369555.1
Note:294350.1
Note:418479.1


But, I couldn't find anything about the one that I came across recently.

I tried everything described in the notes above. Nothing worked out, until I realized that all of the patch files have been somehow blocked.

Although the patch files had all the necessary permissions, especially read, the opatch utility couldn't read them because of the lock set by the OS.

If you check the file properties you can see the message:
"This file came from another computer and might be blocked to help protect this computer".



The reason why these files were blocked was because I copy them from a different machine.

A blocked file can be unlocked by pressing Unlock button.
The odd thing was that only one file can be unlocked at a time.



So I just deleted the patch directory and unlocked the patch zip before I extract it again.

I haven't had similar issue before. I believe this is a new "security" feature of Windows 2003 Server.

Query to determine the clustering factor before you create the index

I found the following query useful to determine the clustering factor for the indexes that has not been created yet.

select count(1) clustering_factor
from
(
select dbms_rowid.rowid_block_number(rowid) block_no,
list of the indexed columns,
count(1) num_rows,
LAG(dbms_rowid.rowid_block_number(rowid))
over (order by list of the indexed columns) prev_block
from table_name
group by dbms_rowid.rowid_block_number(rowid),
list of the indexed columns
order by list of the indexed columns
)
where block_no<>prev_block or prev_block is null

Let's take a look at the following example:

SQL> create table test
2 tablespace example
3 as select
4 mod(rownum,8) col1
5 lpad('x',1000) col2
6 from all_objects
7 where rownum<=1000;

Using the following query, we can see that there are about 7 rows per block (block size 8192) that makes the records with same COL1 to be scattered across different blocks:

SQL> select num_rows_in_block, count(1) blocks
2 from
3 (
4 select block_no, count(1) num_rows_in_block
5 from
6 (
7 select dbms_rowid.rowid_block_number(rowid) block_no
8 from test
9 )
10 group by block_no
11 )
12* group by num_rows_in_block
SQL> /

NUM_ROWS_IN_BLOCK BLOCKS
----------------- ----------
6 1
7 142

Let see how big would be the clustering factor of an index built on TEST(COL1).

SQL> select count(1) clustering_factor
2 from
3 (
4 select dbms_rowid.rowid_block_number(rowid) block_no,
5 col1,
6 count(1) num_rows,
7 LAG(dbms_rowid.rowid_block_number(rowid))
8 over (order by col1) prev_block
9 from test
10 group by dbms_rowid.rowid_block_number(rowid), col1
11 order by col1
12 )
13* where block_no<>prev_block or prev_block is null
SQL> /

CLUSTERING_FACTOR
-----------------
1000

The result was pretty obvious.
Now let's add another column COL3 in the table TEST and set its value to mod(col1, 4).
This will make the blocks to contain at most two records with the same value of COL3.

SQL> alter table test
2 add col3 number;

Table altered.

SQL> update test set col3=mod(col1,4);

1000 rows updated.

SQL> commit;

Commit complete.

SQL>

Now let see what would be the value of the clustering factor of the index created on COL3 column:

SQL> select count(1) clustering_factor
2 from
3 (
4 select dbms_rowid.rowid_block_number(rowid) block_no,
5 col3,
6 count(1) num_rows,
7 LAG(dbms_rowid.rowid_block_number(rowid))
8 over (order by col3) prev_block
9 from test
10 group by dbms_rowid.rowid_block_number(rowid), col3
11 order by col3
12 )
13* where block_no<>prev_block or prev_block is null
SQL> /

CLUSTERING_FACTOR
-----------------
572

Now let's create the actual indexes and see what their clustering factor would be:

SQL> create index test_col1_idx on test(col1);

Index created.

SQL> create index test_col3_idx on test(col3);

Index created.

SQL> select index_name, clustering_factor
2 from user_indexes
3* where table_name='TEST'
SQL> /

INDEX_NAME CLUSTERING_FACTOR
------------------------------ -----------------
TEST_COL1_IDX 1000
TEST_COL3_IDX 572

Perfect insights about the importance of the clustering factor for calculating the cost I found in Jonathan Lewis's book Cost-Based Oracle Fundamentals.

Let's go back to the very first query in this post. Why I think this query is useful?
Because sometime, even though I think some columns are perfect combination to create index on, the index might not be used by the optimizer because the clustering factor is big.
It can give you an initial clue how effective some index could be.