ORA-29549 Java session state cleared

The "normal" way this error can occur is to compile a java object in the database and execute it in the same session.   E.g.  (from http://forums.oracle.com/forums/thread.jspa?threadID=856644)

(Oracle 10gR2)

SQL> CREATE OR REPLACE AND RESOLVE JAVA SOURCE NAMED "testc" AS
public class testc {
  public static int testm() {
    return 0;
  }
}
/

--create plsql wrapper function

create or replace FUNCTION TEST
RETURN NUMBER
IS
LANGUAGE JAVA
NAME 'testc.testm() return int';

cat call_test.sql
declare
result number;
begin
result :=test;
end;
/

SQL> @call_test

PL/SQL procedure successfully completed.




Now alter the java object ...

SQL> alter java source "testc" compile;

Java altered.

SQL> @call_test
declare
*
ERROR at line 1:
ORA-29549: class USER1.testc has changed, Java session state cleared
ORA-06512: at "USER1.TEST", line 1
ORA-06512: at line 4

Call it again and it executes sucessfully:

SQL> @call_test

PL/SQL procedure successfully completed.


Note that to produce the error, testc must be called first.  If the java code is not called before the alter java, then the error does not occur, ie login, do some sql not involving testc, then alter java.  After the alter java, calling testc does not produce any errors.  

Looks like running the code sets some sort of session state for the java object, and this state conflicts with the results of the alter java, producing the error.  If the state is not set first, then altering the java code does not cause any errors.  

Running testc in a separate session never produces errors, whether run before or after the alter java.

SQL> alter system set events '29549 TRACE NAME ERRORSTACK LEVEL 3';
... generates trace file with the calling code (contents of call_test.sql).
Change to ERRORSTACK OFF when not required.

Invalid Objects From Dropping Function Based Indexes

Create some objects ...

SCOTT@DMBLI> create table t1(x int, y varchar2(20));

Table created.

-- put some data.

SCOTT@DMBLI> create index t1idx on t1(y);

Index created.

SCOTT@DMBLI> create procedure testprc
2 as
3 var1 varchar2(20);
4 begin
5 select y into var1 from t1 where rownum=1;
6 end;
7 /

Procedure created.

SCOTT@DMBLI> create view v1
2 as select * from t1;

View created.

SCOTT@DMBLI> select object_name, object_type, status , timestamp, last_ddl_time from user_objects
2 where created > sysdate -1;

OBJECT_NAME                    OBJECT_TYPE     STATUS  TIMESTAMP      LAST_DDL_TIME

------------------------------ --------------- ------- ------------------- --------------------------

T1                             TABLE           VALID   2010-09-07:16:45:19 07-SEP-2010 16:45:51

T1IDX                          INDEX           VALID   2010-09-07:16:45:51 07-SEP-2010 16:45:51

TESTPRC                        PROCEDURE       VALID   2010-09-07:16:46:58 07-SEP-2010 16:46:58

V1                             VIEW            VALID   2010-09-07:16:47:14 07-SEP-2010 16:47:14

=> All valid.

SCOTT@DMBLI> drop index T1IDX;

Index dropped.

SCOTT@DMBLI> select object_name, object_type, status , timestamp, last_ddl_time from user_objects
2 where created > sysdate -1;

OBJECT_NAME                    OBJECT_TYPE     STATUS  TIMESTAMP       LAST_DDL_TIME

------------------------------ --------------- ------- ------------------- --------------------------

T1                             TABLE           VALID   2010-09-07:16:45:19 07-SEP-2010 16:48:22

TESTPRC                        PROCEDURE       VALID   2010-09-07:16:46:58 07-SEP-2010 16:46:58

V1                             VIEW            VALID   2010-09-07:16:47:14 07-SEP-2010 16:47:14

=> Drop “ordinary” index does not invalidate proc and view.


Dropping Function Based index does ...

SCOTT@DMBLI> create index t1idx on t1(upper(y));

Index created.

SCOTT@DMBLI> select object_name, object_type, status , timestamp, last_ddl_time from user_objects
2 where created > sysdate -1;

OBJECT_NAME                    OBJECT_TYPE     STATUS  TIMESTAMP          LAST_DDL_TIME

------------------------------ --------------- ------- ------------------- --------------------------

T1                             TABLE           VALID   2010-09-07:16:45:19 07-SEP-2010 16:48:50

T1IDX                          INDEX           VALID   2010-09-07:16:48:50 07-SEP-2010 16:48:50

TESTPRC                        PROCEDURE       VALID   2010-09-07:16:46:58 07-SEP-2010 16:46:58

V1                             VIEW            VALID   2010-09-07:16:47:14 07-SEP-2010 16:47:14

SCOTT@DMBLI> drop index T1IDX;

Index dropped.

SCOTT@DMBLI> select object_name, object_type, status , timestamp, last_ddl_time from user_objects where created > sysdate -1;

OBJECT_NAME                    OBJECT_TYPE     STATUS  TIMESTAMP       LAST_DDL_TIME

------------------------------ --------------- ------- ------------------- --------------------------

T1                             TABLE           VALID   2010-09-07:16:45:19 07-SEP-2010 16:49:05

TESTPRC                        PROCEDURE       INVALID 2010-09-07:16:46:58 07-SEP-2010 16:46:58

V1                             VIEW            INVALID 2010-09-07:16:47:14 07-SEP-2010 16:47:14

Select/execute to auto-compile ...

SCOTT@DMBLI> select * from v1;

no rows selected

SCOTT@DMBLI> exec testprc;

PL/SQL procedure successfully completed.

SCOTT@DMBLI> select object_name, object_type, status , timestamp, last_ddl_time from user_objects where created > sysdate -1;

OBJECT_NAME                    OBJECT_TYPE     STATUS  TIMESTAMP      LAST_DDL_TIME

------------------------------ --------------- ------- ------------------- --------------------------

T1                             TABLE           VALID   2010-09-07:16:45:19 07-SEP-2010 16:49:05

TESTPRC                        PROCEDURE       VALID   2010-09-07:16:49:32 07-SEP-2010 16:49:32

V1                             VIEW            VALID   2010-09-07:16:47:14 07-SEP-2010 16:49:22

Hidden column not analyzed ...

No hidden columns yet ...

SCOTT@DMBLI> select table_name column_name, num_distinct, hidden_column, virtual_column from user_tab_cols where virtual_column ='YES';

no rows selected

SCOTT@DMBLI> create index t1idx on t1(upper(y)) compute statistics;

Index created.

SCOTT@DMBLI> select last_analyzed , sysdate from user_indexes where index_name='T1IDX';

LAST_ANALYZED              SYSDATE

-------------------------- --------------------------

07-SEP-2010 17:06:28       07-SEP-2010 17:06:46

SCOTT@DMBLI> select table_name column_name, num_distinct, hidden_column, virtual_column from user_tab_cols where virtual_column ='YES';

COLUMN_NAME NUM_DISTINCT HID VIR
------------------------------ ------------ --- ---
T1 YES YES

=> hidden column created by the FBI. But it is not analyzed even with the compute stats (it does not know there are a few rows in the table). Run dbms_stats to get stats on this table ...


SCOTT@DMBLI> exec dbms_stats.gather_table_stats(ownname=>null, tabname=> 'T1',estimate_percent=>10, cascade=>true, method_opt=> 'FOR ALL HIDDEN COLUMNS SIZE 1');

PL/SQL procedure successfully completed.

SCOTT@DMBLI> select table_name column_name, num_distinct, hidden_column, virtual_column from user_tab_cols where virtual_column ='YES';

COLUMN_NAME NUM_DISTINCT HID VIR

------------------------------ ------------ --- ---

T1 3 YES YES

... which matches what’s in there;

SCOTT@DMBLI> select * from t1;

X Y

---------- --------------------

1 a

2 b

3 c

If we update the table to look like this:

SCOTT@DMBLI> select * from t1;

X Y
---------- --------------------
1 a
2 A
3 a

then the analyze result changes, as expected.

SCOTT@DMBLI> exec dbms_stats.gather_table_stats(ownname=>null, tabname=> 'T1',estimate_percent=>10, cascade=>true, method_opt=> 'FOR ALL HIDDEN COLUMNS SIZE 1');

PL/SQL procedure successfully completed.

SCOTT@DMBLI> select table_name column_name, num_distinct, hidden_column, virtual_column from user_tab_cols where virtual_column ='YES';

COLUMN_NAME NUM_DISTINCT HID VIR
------------------------------ ------------ --- ---
T1 1 YES YES

Bitmap vs B-Tree indexes - contention

In 11g R1

SQL> conn apps/apps SQL> create table tb1 ( a number, dt timestamp, c varchar2(12));

SQL> create bitmap index tb1_bidx on tb1 (a);

SQL> select index_name, index_type from user_indexes;
INDEX_NAME INDEX_TYPE

------------------------------ ---------------------------
TB1_BIDX BITMAP


cat pop1.sql
set timing on
begin
for nn in 1 .. 10000 loop
insert into tb1 select trunc(dbms_random.value(1,11)),sysdate, 'bm' from dual;
end loop;
end;
/
-- commit;
exit


SQL> !date
Fri May 28 13:58:29 EST 2010

SQL> EXEC dbms_workload_repository.create_snapshot;

PL/SQL procedure successfully completed.

1=1
maxlp=10
while [ $i -le $maxlp ]; do sqlplus apps/apps @pop1.sql & i=`echo $(($i+1))`; done

Elapsed: 00:00:12.86
Elapsed: 00:00:36.45
Elapsed: 00:00:44.34
Elapsed: 00:00:48.84
Elapsed: 00:00:57.59
Elapsed: 00:01:06.21
Elapsed: 00:01:10.08
Elapsed: 00:01:09.17
Elapsed: 00:01:20.89
Elapsed: 00:01:26.39
So longest took 1.5 mins. Add all up:


60*5+12+36+44+48+57+6+10+9+20+26
--------------------------------
568

Fri May 28 14:02:37 2010
SQL> EXEC dbms_workload_repository.create_snapshot;


sqlplus / as sysdba @awrrpt



Top 5 Timed Foreground Events - BM

Event                         Waits Time(s) Avg wait (ms) % DB time Wait Class

enq: TX - row lock contention 35    454     12977         75.68 Application

DB CPU                        70    11.74

Data file init write         426    24      57             4.01 User I/O

library cache: mutex X         5    4       839            0.70 Concurrency

log file sync                 21    3       140            0.49 Commit

SQL> drop index tb1_bidx;
Index dropped.

SQL> create index tb1_idx on tb1(a);
Index created.

SQL> EXEC dbms_workload_repository.create_snapshot;
PL/SQL procedure successfully completed.

SQL> !date
Fri May 28 14:21:15 EST 2010

1=1
maxlp=10
while [ $i -le $maxlp ]; do sqlplus apps/apps @pop1.sql & i=`echo $(($i+1))`; done

Elapsed: 00:00:08.33
Elapsed: 00:00:09.54
Elapsed: 00:00:07.39
Elapsed: 00:00:06.00
Elapsed: 00:00:09.91
Elapsed: 00:00:08.40
Elapsed: 00:00:09.76
Elapsed: 00:00:12.15
Elapsed: 00:00:04.55
Elapsed: 00:00:13.84

SQL> select 8+9+7+6+9+8+9+12+4+13 from dual;
8+9+7+6+9+8+9+12+4+13
---------------------
85 <-- instead of 558!

Top 5 Timed Foreground Events - BT

Event              Waits Time(s) Avg wait (ms) % DB time Wait Class
buffer deadlock        7 25      3522          23.27     Other
DB CPU                21                       20.26
enq: HW - contention   4 5       1210           4.57     Configuration
buffer busy waits     47 4       79             3.49     Concurrency
Data file init write  44 3       69             2.88     User I/O

So the response time dropped from 1 min 26 secs to 14 secs, as "row-lock contention" was replaced by (much lower) instances of "buffer deadlock".

Oracle backups and consistent datafiles

I saw this whilst trawling through the web today, looking for info on Oracle database;

"It is good practice to perform an orderly shutdown (NORMAL, TRANSACTIONAL, or IMMEDIATE) before performing the cold backup. However, you will still obtain a consistent cold backup if you perform a SHUTDOWN ABORT. And I have had no issues restoring from such a backup in the past. But just to be safe, I always recommend an orderly shutdown.

I have had some instances that take a very long time for an orderly shutdown. For those cases, I perform a SHUTDOWN ABORT, STARTUP RESTRICT and then a SHUTDOWN IMMEDIATE. This way, I can ensure an orderly shutdown has been performed, and the instance terminates much more quickly than if I did not do the SHUTDOWN ABORT."

The red italics are mine and I completely disagree with that part of the author's comment.  If the database was open to users, then a shutdown abort (in general) does NOT leave the datafiles in a consistent state (that's why it's so fast to abort the instance). 

The author then "had no issues restoring from such a backup in the past."  That's because he must have copied the online redo logs as well as the datafiles into the backup.  If the datafiles and online redo logs are then restored from the backup, starting up the instance will initiate auto-recovery using the restored online redo logs.  If there are enough redo log entries in the online logs to fully recover the datafiles, then the database will open.  So the fact that the database opens doesn't mean that the datafiles in the backup were consistent, only that Oracle had silently recovered the database during the startup process.  If the online redo logs were not restored from backup, then the datafiles would remain inconsistent and "alter database open" will fail.  (Unless you are very lucky)


Complicating this is Oracle's recommendation never to copy online redo logs as part of a backup strategy.  This is because, if you are performing an online backup (where the database is open to users throughout the duration of the backup), then you're supposed to backup the archived redo log files (along with the datafiles of course).  When properly done, all the redo generated during the time required to copy the datafiles are dumped into the archived logs, so that's why you don't copy the online logs.

But if you're performing a cold backup, there's no harm in backing up the lot. Just remember that if the database is in archivelog mode, and you want to use that backup (cold or hot) later on, say to recover from media failure holding the database files, then do not accidentally restore the online redo logs from the backup on top of (overwriting) your current, live online redo logs.  If you do that, you will likely lose the only copy of the latest redo data that you need to perform a full recovery all the way up to the point of failure.  In fact, many dba's cite this as yet another good reason not to copy the online redo logs during a backup.  Furthermore, if your cold backup is consistent (because the database was shutdown immediate, normal or transactional), the online redo logs can be recreated after restoring the database and opening it with reset logs.  So again, you do not need to copy the online redologs during backup.

Like the author, I used to cold-backup dev/test databases running in noarchivelog mode as,

Script 1
shutdown abort
startup
shutdown immediate
backup

But is it really necessary to restart the database and then shut it down with immediate?  Put it another way, is it really necessary to get a consistent backup of the datafiles in order to be able to restore and recover the backup later?  No, it isn't.  Consider: If the "startup" in script 1 always suceeds, then that proves that you can always recover from the shutdown abort.  So just backup the datafiles and redo logs straight away after the abort.  Later, if the backup is restored by the dba, all files will be in the state they were in after the abort.  Will the dba's startup work?  Yes, just as it did when the script ran.  In other words, the more you believe script 1 works, the more evidence you have that it can be replaced.  All you need to do is "shutdown abort" and copy all the files (with the redo logs).   But knowing dba's, they'll always do it the "safest" way, ie with script 1.

Is there ever a chance that an aborted, noarchivelog mode database, will fail to startup after an abort?  I've never seen it happen, but of course that doesn't mean it won't.

Apps Initialize in R12

In R12, the old fnd_client_info.set_org_context  no longer seems sufficient to query data from apps views:

SQL> show user
USER is "APPS"




SQL> select fnd_profile.value('ORG_ID') from dual;

FND_PROFILE.VALUE('ORG_ID')
--------------------------------------------------------------------------------
85

SQL> select USERENV ('CLIENT_INFO') from dual;

USERENV('CLIENT_INFO')
----------------------------------------------------------------



SQL> exec fnd_client_info.set_org_context(85);

PL/SQL procedure successfully completed.

SQL> select USERENV ('CLIENT_INFO') from dual;

USERENV('CLIENT_INFO')
----------------------------------------------------------------
85

SQL>  select count(*) from ra_customer_trx_partial_v;

  COUNT(*)
----------
         0  <-- We know there are plenty of rows!


SQL> exit


One needs to run  fnd_global.apps_initialize and   mo_global.init  as follows ...

sqlplus as apps again and ...

Get parameters for apps security ...

SQL> select user_id,responsibility_id,responsibility_application_id, security_group_id
from fnd_user_resp_groups
where user_id = (

    select user_id from fnd_user where user_name = 'ROBERT')
    and responsibility_id = (select responsibility_id from         fnd_responsibility_vl where responsibility_name =
    'Purchasing Super User')

;


USER_ID RESPONSIBILITY_ID RESPONSIBILITY_APPLICATION_ID SECURITY_GROUP_ID
---------- ----------------- ----------------------------- -----------------
     11787             20707                           201                 0

SQL> exec fnd_global.apps_initialize(11787,20707,201);

PL/SQL procedure successfully completed.

SQL> exec mo_global.init ('PO');

PL/SQL procedure successfully completed.

SQL> select USERENV ('CLIENT_INFO') from dual;

USERENV('CLIENT_INFO')
----------------------------------------------------------------
85                                                    0

 
And test again ...


SQL> select count(*) from ra_customer_trx_partial_v;

  COUNT(*)
----------
     14857

Logging in as someone else with proxy users

Example of proxy users in Oracle 10g (and probably 9i), where one can login to a different database account without knowing the password to the other account:

SQL> show user
USER is "SYSTEM"
SQL> create user lm1 identified by pass1;

User created.

SQL> create user lm2 identified by pass2;

User created.

SQL> grant connect to lm1, lm2;

Grant succeeded.

SQL> alter user lm2 grant connect through lm1;

User altered.

SQL> connect lm1/pass1
Connected.
SQL> show user
USER is "LM1"
SQL> connect lm1[lm2]/pass1
Connected.
SQL> show user
USER is "LM2"  

... so lm1 was able to connect to lm2 without using the password pass2 for lm2.

This superceeds the "alter user identified by values" method in older releases (which is still valid).

To reverse,

SQL> alter user lm2 revoke connect through lm1;

User altered.

SQL> conn lm1/pass1
Connected.
SQL> show user
USER is "LM1"
SQL> connect lm1[lm2]/pass1
ERROR:
ORA-28150: proxy not authorized to connect as client
Warning: You are no longer connected to ORACLE.

Multi-column partitioning

Simple example of table with more than 1 partition column.

create table xp (a int, b int, c int, data varchar2(20))
partition by range (a,b)(
  partition part1 values less than (1,1),
  partition part2 values less than (2,2),
  partition part3 values less than (3,3),
  partition part4 values less than (MAXVALUE,MAXVALUE)

);

Check:

select * from user_part_key_columns;

NAME                           OBJEC COLUMN_NAME     COLUMN_POSITION
------------------------------ ----- --------------- ---------------
XP                             TABLE A                             1
XP                             TABLE B                             2

select partition_name, high_value, partition_position
from user_tab_partitions
where table_name='XP';


PARTITION_NAME                 HIGH_VALUE           PARTITION_POSITION
------------------------------ -------------------- ------------------
PART1                          1, 1                                  1
PART2                          2, 2                                  2
PART3                          3, 3                                  3
PART4                          MAXVALUE, MAXVALUE                    4


Now insert some values:

create or replace procedure popxp
(imax in int, jmax in int)
is
begin
for i in 0..imax loop
  for j in 0..jmax loop
    insert into xp values (i, j, null, null);
  end loop;
end loop;
end;

/

Run it:

exec popxp(3,3);

And look at the values by partition:
select * from xp partition (part1) order by a,b;

         A          B          C DATA
---------- ---------- ---------- --------------------
         0          0
         0          1
         0          2
         0          3
         1          0


select * from xp partition (part2) order by a,b;

         A          B          C DATA
---------- ---------- ---------- --------------------
         1          1
         1          2
         1          3
         2          0
         2          1

select * from xp partition (part3) order by a,b;

         A          B          C DATA
---------- ---------- ---------- --------------------
         2          2
         2          3
         3          0
         3          1
         3          2

select * from xp partition (part4) order by a,b;

         A          B          C DATA
---------- ---------- ---------- --------------------
         3          3

So in determining the partition for a row, the value of the first column over-rides that of the 2nd column.   For e.g. (0,3) lies in PART1 because a=0 satisfies the condition a<1 for PART1.  Although b=3 violates the condition b<1, this fact is irrelevant.  

The 2nd column is only considered when the value of the 1st column lies on a partition boundary.  For e.g., (2,0), (2,1), (2,2) and (2,3) all lie on partition boundaries for column "a" of PART2.  For (2,0) and (2,1) the "b" values are the deciding factor in placing these rows in PART2.

For (2,2) "b" is on the boundary for PART2, so "a" again determines the partition for the row.  For (2,3) "b" is now a boundary value for PART3, and since a=2 satisfies the condition for PART3, the row goes there (regardless of "b").


In summary,

Row		PART1	->	PART2	->	PART3	->	PART4
(2,0)		a is too big, so go to next partition.		a is boundary.  So consider b. b=0 satisfies condition for PART2, so put the row there.		-		-
(2,1)		a is too big, so go to next partition.		a is boundary.  So consider b. b=1 satisfies condition for PART2, so put the row here.		-		-
(2,2)		a is too big, so go to next partition.		Both a and b lie on boundaries.  So a dominates again.  Since a=2 is too big for PART2, go to the next partition.		a=2 satisfies condition for PART3 so put the row here.		-
(2,3)		a is too big, so go to next partition.		b is too big for this partition.  So a dominates again.  Since a=2 is too big for PART2, go to the next partition.		a=2 satisfies condition for PART3 so put the row here.		-