opatch problem on Windows

There is a note in Metalink that explains that on Windows having space characters in your ORACLE_HOME variable, the patch location or JDK location causes an error when running opatch. Yesterday I saw a strange problem that is similar to the above case.

If your opatch directory contains space characters you get a strange error. Even if the above conditions were not present we got an error like this:

C:\Documents and Settings\test\Desktop\OPatch>opatch lsinventory
Exception in thread "main" java.lang.NoClassDefFoundError: and

OPatch failed with error code = 1

Metalink returns no results for this error. This error is caused by the space characters in "Documents and Settings". When you move the opatch directory to another directory which does not contain space in its name opatch runs without this problem.

Just a note to help in case someone gets the same error.

DBFS

Yesterday I attended Kevin Closson's Exadata technical deep dive webcast series part 4. It is now available to download here. In there he talks about DBFS which is a filesystem on top of the Oracle database which can store normal files like text files. DBFS is provided with Exadata and is used to store staging files for the ETL/ELT process. This looks very promising, he sites several tests he conducted and gives performance numbers too. Watch the webcast if you haven't yet.

Tablespace selection in interval partitioning

11G brought interval partitioning which is a new partitioning method to ease the maintenance burden of adding new partitions manually. The interval partition clause in the create table statement has an option to list tablespace names to be used for interval partitioning. The documentation states that the tablespaces in the list you provide are used in a round-robin manner for new partitions:

Interval partitions are created in the provided list of tablespaces in a round-robin manner.

This does not mean that any newly created partition will reside in the tablespace which is next on the list. The tablespaces may be skipped if partitions map to more than one interval. Here is a test case that shows how the list is used.

set lines 200
SQL> r
  1  create table t(col1 date,col2 varchar2(100))
  2  partition by range (col1)
  3  interval(numtoyminterval(1,'MONTH')) store in (tbs1,tbs2,tbs3)
  4* (PARTITION p0 VALUES LESS THAN (TO_DATE('1-1-2009', 'DD-MM-YYYY')) tablespace tbs1)

Table created.

SQL> r
  1  select PARTITION_NAME,HIGH_VALUE,TABLESPACE_NAME
  2* from user_Tab_partitions where table_name='T'

PARTITION_NAME                 HIGH_VALUE                                                                       TABLESPACE_NAME
------------------------------ -------------------------------------------------------------------------------- ------------------------------
P0                             TO_DATE(' 2009-01-01 00:00:00', 'SYYYY-MM-DD HH24:MI:SS', 'NLS_CALENDAR=GREGORIA TBS1

The "store in" clause lists tablespaces tbs1, tbs2 and tbs3 to be used for interval partitioning. After the above create table command I now have one partition which resides in tbs1. Let's insert a row which needs to be inserted into a new partition and see which tablespace the partition will be created in.

SQL> insert into t values(to_date('15.01.2009','dd.mm.yyyy'),'jan');

1 row created.

SQL> commit;

Commit complete.

SQL> select PARTITION_NAME,HIGH_VALUE,TABLESPACE_NAME
  2  from user_Tab_partitions where table_name='T';

PARTITION_NAME                 HIGH_VALUE                                                                       TABLESPACE_NAME
------------------------------ -------------------------------------------------------------------------------- ------------------------------
P0                             TO_DATE(' 2009-01-01 00:00:00', 'SYYYY-MM-DD HH24:MI:SS', 'NLS_CALENDAR=GREGORIA TBS1
SYS_P61                        TO_DATE(' 2009-02-01 00:00:00', 'SYYYY-MM-DD HH24:MI:SS', 'NLS_CALENDAR=GREGORIA TBS2

The row I inserted maps to one interval, which is one month, it does not have a date value which is more than one month higher than the current maximum value. So the next tablespace, tbs2, is used for the new partition.

SQL> insert into t values(to_date('15.02.2009','dd.mm.yyyy'),'feb');

1 row created.

SQL> commit;

Commit complete.

SQL> select PARTITION_NAME,HIGH_VALUE,TABLESPACE_NAME
  2  from user_Tab_partitions where table_name='T';

PARTITION_NAME                 HIGH_VALUE                                                                       TABLESPACE_NAME
------------------------------ -------------------------------------------------------------------------------- ------------------------------
SYS_P62                        TO_DATE(' 2009-03-01 00:00:00', 'SYYYY-MM-DD HH24:MI:SS', 'NLS_CALENDAR=GREGORIA TBS3
P0                             TO_DATE(' 2009-01-01 00:00:00', 'SYYYY-MM-DD HH24:MI:SS', 'NLS_CALENDAR=GREGORIA TBS1
SYS_P61                        TO_DATE(' 2009-02-01 00:00:00', 'SYYYY-MM-DD HH24:MI:SS', 'NLS_CALENDAR=GREGORIA TBS2

Again I inserted a row for the next month and the partition is created in tbs3, which is the next one on the list.

What happens if I insert a row with a date value that is more than one month after the current maximum partitioning key?

SQL> insert into t values(to_date('15.04.2009','dd.mm.yyyy'),'apr');

1 row created.

SQL> commit;

Commit complete.

SQL> select PARTITION_NAME,HIGH_VALUE,TABLESPACE_NAME
  2  from user_Tab_partitions where table_name='T';

PARTITION_NAME                 HIGH_VALUE                                                                       TABLESPACE_NAME
------------------------------ -------------------------------------------------------------------------------- ------------------------------
SYS_P62                        TO_DATE(' 2009-03-01 00:00:00', 'SYYYY-MM-DD HH24:MI:SS', 'NLS_CALENDAR=GREGORIA TBS3
P0                             TO_DATE(' 2009-01-01 00:00:00', 'SYYYY-MM-DD HH24:MI:SS', 'NLS_CALENDAR=GREGORIA TBS1
SYS_P61                        TO_DATE(' 2009-02-01 00:00:00', 'SYYYY-MM-DD HH24:MI:SS', 'NLS_CALENDAR=GREGORIA TBS2
SYS_P63                        TO_DATE(' 2009-05-01 00:00:00', 'SYYYY-MM-DD HH24:MI:SS', 'NLS_CALENDAR=GREGORIA TBS2

I skipped March and inserted a value for April. The current maximum key becomes May 1st, we do not see a partition with a maximum value of Apr 1st. The next tablespace on the list was tbs1 but we see that the new partition is on tbs2, not tbs1. Tbs1 would be used if I did not skip an interval when inserting rows.

So, the tablespaces on the list are used in a round-robin manner but each is used for only one interval. If you skip intervals the tablespaces related to that interval are skipped too.

This is something to keep in mind if you want to strictly decide which tablespace will hold which partition.

Different plans for a sql with the rule hint

I was trying to find out why a query with the RULE hint produces different plans. I got stuck so I posted the problem to the OTN database forum and Randolf Geist provided a good answer and starting point for it. A second eye on the problem can remove the mind blockage and clear your way.

Remote dependencies

When changing plsql code in a production system the dependencies between objects can cause some programs to be invalidated. Making sure all programs are valid before opening the system to users is very important for application availability. Oracle automatically compiles invalid programs on their first execution, but a successfull compilation may not be possible because the error may need code correction or the compilation may cause library cache locks when applications are running.

Dependencies between plsql programs residing in the same database are easy to handle. When you compile a program the programs dependent on that one are invalidated right away and we can see the invalid programs and compile or correct them before opening the system to the users.

If you have db links and if your plsql programs are dependent on programs residing in other databases you have a problem handling the invalidations. There is an initialization parameter named remote_dependencies_mode that handles this dependency management. If it is set to TIMESTAMP the timestamp of the local program is compared to that of the remote program. If the remote program's timestamp is more recent than the local program, the local program is invalidated in the first run. If the parameter is set to SIGNATURE the remote program's signature (number and types of parameters, subprogram names, etc...) is checked, if there has been a change the local program is invalidated in the first run.

The problem here is; if you change the remote program's signature or timestamp you cannot see that the local program is invalidated because it will wait for the first execution to be invalidated. If you open the system to the users before correcting this you may face a serious application problem leading to downtime.

Here is a simple test case to see the problem.




I start by creating a plsql package in the database TEST.

SQL> create package test_pack as
 2  procedure test_proc(a number);
 3  end;
 4  /

Package created.

SQL> create package body test_pack as
 2  procedure test_proc(a number) is
 3  begin
 4  null;
 5  end;
 6  end;
 7  /

Package body created.

On another database let's create a database link pointing to the remote database TEST, create a synonym for the remote package and create a local package calling the remote one.

SQL> create database link test.world connect to yas identified by yas using 'TEST';

Database link created.

SQL> select * from dual@yas.world;

D
-
X

SQL> create synonym test_pack for test_pack@yasbs.world;

Synonym created.

SQL> create package local_pack as
 2  procedure local_test(a number);
 3  end;
 4  /

Package created.

SQL> r
 1  create or replace package body local_pack as
 2  procedure local_test(a number) is
 3  begin
 4  test_pack.test_proc(1);
 5  end;
 6* end;

Package body created.

If we look at the status of this local package we see that it is valid.

SQL> col object_name format a30
SQL> r
 1* select status,object_name,object_type from user_objects where object_name='LOCAL_PACK'

STATUS  OBJECT_NAME                    OBJECT_TYPE
------- ------------------------------ ------------------
VALID   LOCAL_PACK                     PACKAGE
VALID   LOCAL_PACK                     PACKAGE BODY

We can execute it without any problems.

SQL> exec LOCAL_PACK.local_test(1);

PL/SQL procedure successfully completed.

Now, let's change the remote package code including the package specification.

SQL> create or replace package test_pack as
 2  procedure test_proc(b number,a number default 1);
 3  end;
 4  /

Package created.

SQL> create or replace package body test_pack as
 2  procedure test_proc(b number,a number default 1) is
 3  begin
 4  null;
 5  end;
 6  end;
 7  /

Package body created.

When we look at the local package we see its status as valid.

SQL> r
 1* select status,object_name,object_type from user_objects where object_name='LOCAL_PACK'

STATUS  OBJECT_NAME                    OBJECT_TYPE
------- ------------------------------ ------------------
VALID   LOCAL_PACK                     PACKAGE
VALID   LOCAL_PACK                     PACKAGE BODY

But when it is executed we get an error.

SQL> exec LOCAL_PACK.local_test(1);
BEGIN LOCAL_PACK.local_test(1); END;

*
ERROR at line 1:
ORA-04068: existing state of packages has been discarded
ORA-04062: signature of package "YASBS.TEST_PACK" has been changed
ORA-06512: at "YASBS.LOCAL_PACK", line 4
ORA-06512: at line 1


SQL> select status,object_name,object_type from user_objects where object_name='LOCAL_PACK';

STATUS  OBJECT_NAME                    OBJECT_TYPE
------- ------------------------------ ------------------
VALID   LOCAL_PACK                     PACKAGE
INVALID LOCAL_PACK                     PACKAGE BODY

SQL> exec LOCAL_PACK.local_test(1);

PL/SQL procedure successfully completed.

SQL> select status,object_name,object_type from user_objects where object_name='LOCAL_PACK';

STATUS  OBJECT_NAME                    OBJECT_TYPE
------- ------------------------------ ------------------
VALID   LOCAL_PACK                     PACKAGE
VALID   LOCAL_PACK                     PACKAGE BODY

The second execution compiles the package and returns the status to valid.

How can we know beforehand that the local program will be invalidated on the first execution? The only way I can think of is to check the dependencies across all databases involved. By collecting all rows from dba_dependencies from all databases we can see that when the remote program is changed the programs on other databases that use this remote program will be invalidated when they are executed. Then we can compile these programs and see if they compile without errors.

Database links may be very annoying sometimes, this case is just one of them.