About "_unnest_subquery"

Peoplesoft give some recommandation regarding the Oracle init parameters. These recommandations include the hidden parameter "_unnest_subquery" to be set to false (true by default), even for the most recent version.
It is surprising me, because this recommandation exists for several years now, and on older database as well.

Ok, let's see what if we don't follow this specific recommandation.

On 10.2.0.4 database, we have the following query running for hours :

UPDATE PS_AB_PDI_CN_TAO
 SET country_2char = COALESCE(( SELECT q.country_2char
                                FROM ( SELECT o.oprid
                                            , j.emplid
                                            , j.empl_rcd
                                            , j.effdt
                                            , j.effseq
                                            , c.country_2char
                                      FROM ps_job j
                                         , psoprdefn o
                                         , ps_opr_def_tbl_Hr h
                                         , ps_location_Tbl l
                                         , ps_country_tbl c
                                      WHERE EXISTS ( SELECT 'x'
                                                     FROM ps_job j2
                                                     WHERE j.emplid = j2.emplid
                                                     AND j2.empl_Rcd = 1)
                                      AND j.emplid = o.emplid
                                      AND o.oprclass = h.oprclass
                                      AND j.effdt = ( SELECT MAX(j3.EFFDT)
                                                      FROM PS_JOB j3
                                                      WHERE j3.EMPLID = j.EMPLID
                                                      AND j3.EMPL_RCD = j.EMPL_RCD
                                                      AND j3.EFFDT <= sysdate)
                                     AND j.effseq = ( SELECT MAX(j4.EFFSEQ)
                                                      FROM PS_JOB j4
                                                      WHERE j4.EMPLID = j.EMPLID
                                                      AND j4.EMPL_RCD = j.EMPL_RCD
                                                      AND j4.EFFDT = j.effdt)
                                     AND l.setid = j.setid_location
                                     AND l.location = j.location
                                     AND h.country = l.country
                                     AND l.effdt = ( SELECT MAX(l2.effdt)
                                                     FROM ps_location_tbl l2
                                                     WHERE l.setid = l2.setid
                                                     AND l.location = l2.location
                                                     AND l2.effdt <= j.effdt)
                                     AND c.country = l.country ) q
                                 WHERE Q.EFFDT = ( SELECT MAX(EFFDT)
                                                   FROM PS_JOB Q2
                                                   WHERE Q.EMPLID = Q2.EMPLID
                                                   AND Q2.EFFDT <= SYSDATE
                                                   AND Q.EFFDT <= SYSDATE)
                                 AND Q.EFFSEQ = ( SELECT MAX(J3.EFFSEQ)
                                                  FROM PS_JOB J3
                                                  WHERE Q.EMPLID = J3.EMPLID
                                                  AND Q.EMPL_RCD = J3.EMPL_RCD
                                                  AND Q.EFFDT = J3.EFFDT)
                                 AND Q.OPRID = PS_AB_PDI_CN_TAO.OPRID)
                               ,' ')
  WHERE COUNTRY_2CHAR = ' ';

The explain plan is the following :

----------------------------------------------------------------------------------------------------------------
| Id  | Operation                                  | Name              | Rows  | Bytes | Cost (%CPU)| Time     |
----------------------------------------------------------------------------------------------------------------
|   0 | UPDATE STATEMENT                           |                   | 41882 |  3599K|    18   (0)| 00:00:01 |
|   1 |  UPDATE                                    | PS_AB_PDI_CN_TAO  |       |       |            |          |
|*  2 |   TABLE ACCESS FULL                        | PS_AB_PDI_CN_TAO  | 41882 |  3599K|    18   (0)| 00:00:01 |
|   3 |   HASH UNIQUE                              |                   |     1 |   137 |    26  (12)| 00:00:01 |
|*  4 |    HASH JOIN                               |                   |     1 |   137 |    15  (14)| 00:00:01 |
|   5 |     NESTED LOOPS                           |                   |     1 |   117 |     8   (0)| 00:00:01 |
|   6 |      NESTED LOOPS                          |                   |     1 |   110 |     7   (0)| 00:00:01 |
|   7 |       NESTED LOOPS SEMI                    |                   |     1 |    85 |     5   (0)| 00:00:01 |
|   8 |        NESTED LOOPS                        |                   |     1 |    74 |     4   (0)| 00:00:01 |
|   9 |         NESTED LOOPS                       |                   |     1 |    39 |     2   (0)| 00:00:01 |
|  10 |          TABLE ACCESS BY INDEX ROWID       | PSOPRDEFN         |     1 |    27 |     1   (0)| 00:00:01 |
|* 11 |           INDEX UNIQUE SCAN                | PS_PSOPRDEFN      |     1 |       |     0   (0)| 00:00:01 |
|  12 |          TABLE ACCESS BY INDEX ROWID       | PS_OPR_DEF_TBL_HR |    33 |   396 |     1   (0)| 00:00:01 |
|* 13 |           INDEX UNIQUE SCAN                | PS_OPR_DEF_TBL_HR |     1 |       |     0   (0)| 00:00:01 |
|  14 |         TABLE ACCESS BY INDEX ROWID        | PS_JOB            |     1 |    35 |     2   (0)| 00:00:01 |
|* 15 |          INDEX RANGE SCAN                  | PSAJOB            |     1 |       |     1   (0)| 00:00:01 |
|  16 |           SORT AGGREGATE                   |                   |     1 |    16 |            |          |
|* 17 |            FILTER                          |                   |       |       |            |          |
|* 18 |             INDEX RANGE SCAN               | PSAJOB            |     2 |    32 |     2   (0)| 00:00:01 |
|  19 |             SORT AGGREGATE                 |                   |     1 |    22 |            |          |
|  20 |              FIRST ROW                     |                   |     1 |    22 |     2   (0)| 00:00:01 |
|* 21 |               INDEX RANGE SCAN (MIN/MAX)   | PSAJOB            |     1 |    22 |     2   (0)| 00:00:01 |
|  22 |                SORT AGGREGATE              |                   |     1 |    22 |            |          |
|  23 |                 FIRST ROW                  |                   |     1 |    22 |     2   (0)| 00:00:01 |
|* 24 |                  INDEX RANGE SCAN (MIN/MAX)| PSAJOB            |     1 |    22 |     2   (0)| 00:00:01 |
|* 25 |        INDEX RANGE SCAN                    | PSAJOB            |     8 |    88 |     1   (0)| 00:00:01 |
|* 26 |       TABLE ACCESS BY INDEX ROWID          | PS_LOCATION_TBL   |     1 |    25 |     2   (0)| 00:00:01 |
|* 27 |        INDEX RANGE SCAN                    | PS_LOCATION_TBL   |     1 |       |     1   (0)| 00:00:01 |
|  28 |         SORT AGGREGATE                     |                   |     1 |    21 |            |          |
|* 29 |          INDEX RANGE SCAN                  | PS_LOCATION_TBL   |     1 |    21 |     2   (0)| 00:00:01 |
|  30 |      TABLE ACCESS BY INDEX ROWID           | PS_COUNTRY_TBL    |     1 |     7 |     1   (0)| 00:00:01 |
|* 31 |       INDEX UNIQUE SCAN                    | PS_COUNTRY_TBL    |     1 |       |     0   (0)| 00:00:01 |
|  32 |     VIEW                                   | VW_SQ_1           |  3743 | 74860 |     6  (17)| 00:00:01 |
|  33 |      SORT GROUP BY                         |                   |  3743 |   109K|     6  (17)| 00:00:01 |
|* 34 |       INDEX FAST FULL SCAN                 | PS_JOB            |  3743 |   109K|     5   (0)| 00:00:01 |
----------------------------------------------------------------------------------------------------------------

Well, the last three lines are not as I would expect.
Let's try this hidden parameter :
"_unnest_subquery"=false (true by default)
Finally the explain plan change a lot and looks much better.

------------------------------------------------------------------------------------------------------------------
| Id  | Operation                                    | Name              | Rows  | Bytes | Cost (%CPU)| Time     |
------------------------------------------------------------------------------------------------------------------
|   0 | UPDATE STATEMENT                             |                   | 41882 |  3599K|    18   (0)| 00:00:01 |
|   1 |  UPDATE                                      | PS_AB_PDI_CN_TAO  |       |       |            |          |
|*  2 |   TABLE ACCESS FULL                          | PS_AB_PDI_CN_TAO  | 41882 |  3599K|    18   (0)| 00:00:01 |
|   3 |   HASH UNIQUE                                |                   |     1 |   117 |    21   (5)| 00:00:01 |
|   4 |    NESTED LOOPS                              |                   |     1 |   117 |     8   (0)| 00:00:01 |
|   5 |     NESTED LOOPS                             |                   |     1 |   110 |     7   (0)| 00:00:01 |
|   6 |      NESTED LOOPS SEMI                       |                   |     1 |    85 |     5   (0)| 00:00:01 |
|   7 |       NESTED LOOPS                           |                   |     1 |    74 |     4   (0)| 00:00:01 |
|   8 |        NESTED LOOPS                          |                   |     1 |    39 |     2   (0)| 00:00:01 |
|   9 |         TABLE ACCESS BY INDEX ROWID          | PSOPRDEFN         |     1 |    27 |     1   (0)| 00:00:01 |
|* 10 |          INDEX UNIQUE SCAN                   | PS_PSOPRDEFN      |     1 |       |     0   (0)| 00:00:01 |
|  11 |         TABLE ACCESS BY INDEX ROWID          | PS_OPR_DEF_TBL_HR |    33 |   396 |     1   (0)| 00:00:01 |
|* 12 |          INDEX UNIQUE SCAN                   | PS_OPR_DEF_TBL_HR |     1 |       |     0   (0)| 00:00:01 |
|  13 |        TABLE ACCESS BY INDEX ROWID           | PS_JOB            |     1 |    35 |     2   (0)| 00:00:01 |
|* 14 |         INDEX RANGE SCAN                     | PSAJOB            |     1 |       |     1   (0)| 00:00:01 |
|  15 |          SORT AGGREGATE                      |                   |     1 |    16 |            |          |
|* 16 |           FILTER                             |                   |       |       |            |          |
|* 17 |            INDEX RANGE SCAN                  | PSAJOB            |     2 |    32 |     2   (0)| 00:00:01 |
|  18 |            SORT AGGREGATE                    |                   |     1 |    22 |            |          |
|  19 |             FIRST ROW                        |                   |     1 |    22 |     2   (0)| 00:00:01 |
|* 20 |              INDEX RANGE SCAN (MIN/MAX)      | PSAJOB            |     1 |    22 |     2   (0)| 00:00:01 |
|  21 |               SORT AGGREGATE                 |                   |     1 |    19 |            |          |
|  22 |                FIRST ROW                     |                   |     1 |    19 |     2   (0)| 00:00:01 |
|* 23 |                 INDEX RANGE SCAN (MIN/MAX)   | PSAJOB            |     1 |    19 |     2   (0)| 00:00:01 |
|  24 |                  SORT AGGREGATE              |                   |     1 |    22 |            |          |
|  25 |                   FIRST ROW                  |                   |     1 |    22 |     2   (0)| 00:00:01 |
|* 26 |                    INDEX RANGE SCAN (MIN/MAX)| PSAJOB            |     1 |    22 |     2   (0)| 00:00:01 |
|* 27 |       INDEX RANGE SCAN                       | PSAJOB            |     8 |    88 |     1   (0)| 00:00:01 |
|* 28 |      TABLE ACCESS BY INDEX ROWID             | PS_LOCATION_TBL   |     1 |    25 |     2   (0)| 00:00:01 |
|* 29 |       INDEX RANGE SCAN                       | PS_LOCATION_TBL   |     1 |       |     1   (0)| 00:00:01 |
|  30 |        SORT AGGREGATE                        |                   |     1 |    21 |            |          |
|* 31 |         INDEX RANGE SCAN                     | PS_LOCATION_TBL   |     1 |    21 |     2   (0)| 00:00:01 |
|  32 |     TABLE ACCESS BY INDEX ROWID              | PS_COUNTRY_TBL    |     1 |     7 |     1   (0)| 00:00:01 |
|* 33 |      INDEX UNIQUE SCAN                       | PS_COUNTRY_TBL    |     1 |       |     0   (0)| 00:00:01 |
------------------------------------------------------------------------------------------------------------------

Finally the query is running in few seconds only.

Ok, the Peoplesoft support continue to recommand this setting "_unnest_subquery"=false, and I understand now it helps.

However, looks interesting, cost is same, so what happens ? What makes Oracle decide to run the first intead of the second ?
The only comment about this parameter on Metalink is : "This parameter controls whether the optimizer attempts to unnest correlated subqueries or not." Fair enough, let's see within 10053 trace file the differences.

The part of the query causing the issue is the subquery with J3 alias and SYSDATE.
AND J.EFFDT = ( SELECT MAX(J3.EFFDT)
FROM PS_JOB J3
WHERE J3.EMPLID = J.EMPLID
AND J3.EMPL_RCD = J.EMPL_RCD
AND J3.EFFDT <= SYSDATE)
Surprisingly, for this subquery, the 10053 trace with "_unnest_subquery"=true shows that it even doesn't consider a RANGE SCAN (Min/Max) against PSAJOB where it is the lowest cost when "_unnest_subquery"=false.

Well, maybe time to raise a SR and start to follow the advices.

Enjoy !

The PS_HOME subfolders

On PSST0101 blog, an interesting list of the folders existing under PS_HOME with some explanations for each.

Do not hesitate to contribute.

Enjoy,

CentOS 5.3 is out

I know, it is a little bit out off topic here, and this is not because this is the 1st of April, but because I'm using it as the main host of all my virtual machines, and even if Oracle database software is officially not certified on CentOS, a lot of people are using it as a database server, as a free OS fully RHEL compatible.

Several week after RHEL 5.3, and two-three weeks later OEL 5.3, CentOS is finaly released its own version of 5.3.

If you are on CentOS 5.x (mine is CentOS5.2), it is very easy to upgrade. For some reasons I had to run the yum update a couple of times before taking in account the news packages. But then, everything run fine :

[root@hercules /root]# rpm -q centos-release
centos-release-5-2.el5.centos
[root@hercules /root]# yum list updates
Loading "fastestmirror" plugin
Loading mirror speeds from cached hostfile
* base: mirror.fraunhofer.de
* updates: ftp.belnet.be
* addons: ftp.hosteurope.de
* extras: ftp.hosteurope.de
Updated Packages
NetworkManager.x86_64 1:0.7.0-3.el5 base
NetworkManager-glib.x86_64 1:0.7.0-3.el5 base
[...]the list of update pakages is around 300, I won't post here[...]
[root@hercules /root]# yum update
Loading "fastestmirror" plugin
Loading mirror speeds from cached hostfile
* base: mirror.widexs.nl
* updates: ftp.belnet.be
* addons: ftp.hosteurope.de
* extras: centos.intergenia.de
Setting up Update Process
Resolving Dependencies
--> Running transaction check
---> Package glx-utils.x86_64 0:6.5.1-7.7.el5 set to be updated
---> Package xorg-x11-drv-ati.x86_64 0:6.6.3-3.22.el5 set to be updated
[...]

Transaction Summary
=============================================================================
Install 11 Package(s)
Update 272 Package(s)
Remove 2 Package(s)

Total download size: 363 M
Is this ok [y/N]: y
Downloading Packages:
(1/283): mkinitrd-5.1.19. 100% |=========================| 449 kB 00:01
(2/283): dbus-glib-0.73-8 100% |=========================| 162 kB 00:00
[...]
Complete!
[root@hercules /root]# rpm -q centos-release
centos-release-5-3.el5.centos.1

To check your Linux distribution, you can use :
[root@hercules /root]# more /etc/redhat-release
CentOS release 5.3 (Final)

Don't forget to bounce your server :
[root@hercules /root]# shutdown -r now

Lastly, if you are using VMWare like I do, don't forget to re-configure it :
[root@hercules /]# cd appl/vmware/bin
[root@hercules /appl/vmware/bin]# ./vmware-config.pl
Making sure services for VMware Server are stopped.

Stopping VMware services:
Virtual machine monitor [ OK ]
Bridged networking on /dev/vmnet0 [ OK ]
Bridged networking on /dev/vmnet2 [ OK ]
Virtual ethernet [ OK ]

Configuring fallback GTK+ 2.4 libraries.
[...]
Starting VMware services:
Virtual machine monitor [ OK ]
Virtual ethernet [ OK ]
Bridged networking on /dev/vmnet0 [ OK ]
Bridged networking on /dev/vmnet2 [ OK ]
Starting VMware virtual machines... [ OK ]

The configuration of VMware Server 1.0.8 build-126538 for Linux for this
running kernel completed successfully.

[root@hercules /appl/vmware/bin]#

All is running fine !

Thanks to the CentOS' team, even if this version is coming very late compared to the source.
To go further : http://www.centos.org

Enjoy,

Modify NLS_LENGTH_SEMANTICS online

From Peopletools 8.48, when working on a UNICODE Oracle database, it is recommanded, do not say mandatory, to set the NLS_LENGTH_SEMANTICS to CHAR to be able to enter multi-bytes characters.

By default, NLS_LENGTH_SEMANTICS is set to BYTE.
This is a problem if you didn't modify this parameter when started the database, you cannot change it afterwards online without bouncing the database.
If you can change the value for your own session (alter session) and everything works fine and you can play with the new value, if you do it on system level (alter system), the new value is not taken in account (however, the alter system command does not return any error !):
SQL> select name,value,isdefault from v$parameter where name ='nls_length_semantics';

NAME VALUE ISDEFAULT
----------------------- -------- ---------
nls_length_semantics BYTE TRUE


SQL> alter system set nls_length_semantics=char;

System altered.

SQL> create table sysadm.nicolas(column1 varchar2(10));

Table created.

SQL> select column_name,data_type,data_length,char_length,char_used
from dba_tab_columns where table_name='NICOLAS';

COLUMN_NAME DATA_TYPE DATA_LENGTH CHAR_LENGTH C
------------ --------- ----------- ----------- -
COLUMN1 VARCHAR2 10 10 B
As you can see, the character used is BYTE, not CHAR as it is expected after parameter value change.

The only workaround to this, is to change the spfile and bounce the database :
SQL> alter system set nls_length_semantics=char scope=spfile;

System altered.

SQL> startup force
ORACLE instance started.

Total System Global Area 1073741824 bytes
Fixed Size 2074664 bytes
Variable Size 746588120 bytes
Database Buffers 318767104 bytes
Redo Buffers 6311936 bytes
Database mounted.
Database opened.
SQL> drop table sysadm.nicolas;

Table dropped.

SQL> select name,value,isdefault from v$parameter where name ='nls_length_semantics';

NAME VALUE ISDEFAULT
----------------------- -------- ---------
nls_length_semantics CHAR FALSE


SQL> create table sysadm.nicolas(column1 varchar2(10));

Table created.

SQL> select column_name,data_type,data_length,char_length,char_used from dba_tab_columns where table_name='NICOLAS';

COLUMN_NAME DATA_TYPE DATA_LENGTH CHAR_LENGTH C
------------ --------- ----------- ----------- -
COLUMN1 VARCHAR2 30 10 C

SQL> select * from v$version where rownum=1;

BANNER
----------------------------------------------------------------
Oracle Database 10g Enterprise Edition Release 10.2.0.4.0 - 64bi

Now it is becoming CHAR as expected from the beginning.

I found it disapointed.

Addendum : it is an known bug #1488174 (internal bug, non-public), opened several years ago on database version 8.x, not solved yet. Find out more in the metalink note #144808.1.

Create index with compute statistics

When you are (re)building an index on Peoplesoft with in Application designer, Peoplesoft is writing a sql script to (re)create the index on the database level.
The statement of index creation contains "compute statistics" option.
If in most of cases there is no issue in that, sometimes you could have some strange behaviour behind this simple statement.

Some times ago, on one of our 9.2 database, we had a query which was not fast as expected. Just to try, we decided to create a new index. It was still not fast enough, then drop this new index.
Strangely, this index creation makes Oracle optimizer change his behaviour comparing before the index (creation and drop) and start to use an other explain plan.
How comes ? We created and drop a new index (through Application Designer) and Oracle "decided" to change the execution plan, hmmm, strange.
After looking deeper, it appears the table statistics had been updated by the index creation containing "compute statistics".

Example :
Connected to:
Oracle9i Enterprise Edition Release 9.2.0.8.0 - Production
With the Partitioning, OLAP and Oracle Data Mining options
JServer Release 9.2.0.8.0 - Production

SQL> select num_rows, last_analyzed from user_tables where table_name = 'EMP';

NUM_ROWS LAST_ANA
---------- --------


SQL> create index idx on emp(ename) compute statistics;

Index created.

SQL> drop index idx;

Index dropped.

SQL> select num_rows, last_analyzed from user_tables where table_name = 'EMP';

NUM_ROWS LAST_ANA
---------- --------
14 20/03/09

But, in some cases, I wouldn't want the table statistics to be updated by my index creation. That's bad, that does not happen without this option "compute statistics" :
SQL> create index idx on emp(ename);

Index created.

SQL> select num_rows, last_analyzed from user_tables where table_name = 'EMP';

NUM_ROWS LAST_ANA
---------- --------

Finally, found an event to work around and avoid that table statistics update :
SQL> alter session set events '8130 trace name context forever';

Session altered.

SQL> create index idx on emp(ename) compute statistics;

Index created.

SQL> select num_rows, last_analyzed from user_tables where table_name = 'EMP';

NUM_ROWS LAST_ANA
---------- --------

Ah, now that's better, now if I drop this index, my explain plan won't change as before the index.

This case has been tested on 9.2.0.6 and 9.2.0.8. Seems not to be a problem anymore in the higher version.

Enjoy,

Peoplesoft 8.49 on OEL 64-bits

As I wrote few days ago, Peoplesoft on Linux 64-bits is now supported on OEL5.2 min (and also RHEL) from Peopletools 8.49.14 and higher.
The Peoplesoft binaries are the same for 64-bits and 32-bits OS.
So, you could either install the same Peoplesoft (application binaries and Peopletools binaries) you already have for your 32-bits Linux, or much more simple, just copy the entire PS_HOME over the network.
There is no issue about that.

The slight difference is for the Oracle libraries.
Do not forget, Peoplesoft is still working with in the 32-bits Oracle libraries, so you have to create a link to the 32-bits librairies inside the 64-bits folder.
If on a 32-bits OS you have to create the following link :
[oracle@orion2:/apps/oracle/product/11.1.0/lib]$ ln -s $ORACLE_HOME/lib/libclntsh.so.11.1 libclntsh.so.9.0

On a 64-bits Oracle binaries, it must be the following :
[oracle@orion2:/apps/oracle/product/11.1.0/lib]$ ln -s $ORACLE_HOME/lib32/libclntsh.so.11.1 libclntsh.so.9.0

Apart from that, everything is same and works fine.

This migration could be a good step before managing the coming Peopletools 8.50 upgrade, because it has been told the Peopletools 8.50 will be a real 64-bits application, moreover it seems it will be supported only on Linux 64-bits, the certification on a 32-bits Linux system will be retired.
Read pages 34 and 43 of PeopleTools Roadmap - OOW 2008

Enjoy,

A database move from 32-bits to 64-bits

Today, a move of a database from OEL5.3 32-bits to OEL5.3 64-bits. The database is running on Oracle 11.1.0.7 32-bits on source.

Firstly, be sure your target OS is a 64-bits OS :
[oracle@orion2:/apps/oracle/product/11.1.0/bin]$ uname -m
x86_64

And also an Oracle software 64-bits :
[oracle@orion2:/apps/oracle/product/11.1.0/bin]$ file oracle
oracle: setuid setgid ELF 64-bit LSB executable, AMD x86-64, version 1 (SYSV), for GNU/Linux 2.6.9, dynamically linked (uses shared libs), for GNU/Linux 2.6.9, not stripped

Shut the source database down, and copy to the new pletform all the files (including spfile or pfile).
Then on the 64-bits OS, start the database in upgrade mode and run utlirp.sql script, that'll invalidate all the objects :
[oracle@orion2:/apps/oracle/admin/DMOCRM9/pfile]$ export ORACLE_SID=DMOCRM9
[oracle@orion2:/apps/oracle/admin/DMOCRM9/pfile]$ sqlplus / as sysdba

SQL*Plus: Release 11.1.0.7.0 - Production on Sun Mar 15 11:16:14 2009

Copyright (c) 1982, 2008, Oracle. All rights reserved.

Connected to an idle instance.

SQL> startup upgrade pfile='/apps/oracle/admin/DMOCRM9/pfile/initDMOCRM9.ora'
ORACLE instance started.

Total System Global Area 764121088 bytes
Fixed Size 2163600 bytes
Variable Size 197135472 bytes
Database Buffers 557842432 bytes
Redo Buffers 6979584 bytes
Database mounted.
Database opened.
SQL> @$ORACLE_HOME/rdbms/admin/utlirp.sql
[...]

Finally, restart your database in normal mode, and run utlrp.sql script to recompile all the invalidated objects :
SQL> shutdown immediate
Database closed.
Database dismounted.
ORACLE instance shut down.
SQL> startup pfile='/apps/oracle/admin/DMOCRM9/pfile/initDMOCRM9.ora'
ORACLE instance started.

Total System Global Area 764121088 bytes
Fixed Size 2163600 bytes
Variable Size 197135472 bytes
Database Buffers 557842432 bytes
Redo Buffers 6979584 bytes
Database mounted.
Database opened.
SQL> @$ORACLE_HOME/rdbms/admin/utlrp.sql
SQL> Rem
[...]

Check the status of the objects onto the database :
SQL> select * from dba_registry
SQL> /

CATALOG
Oracle Database Catalog Views
11.1.0.7.0 VALID 15-MAR-2009 11:47:55
SERVER SYS
SYS
DBMS_REGISTRY_SYS.VALIDATE_CATALOG



CATPROC
Oracle Database Packages and Types
11.1.0.7.0 VALID 15-MAR-2009 11:47:55
SERVER SYS

SYS
DBMS_REGISTRY_SYS.VALIDATE_CATPROC

DBSNMP,OUTLN,SYSTEM,TSMSYS


SQL>
SQL> select count(*) from dba_objects where status != 'VALID';

COUNT(*)
----------
0

Lastly, you can be happy with your 64-bits database :
[oracle@orion2:/apps/oracle/admin/DMOCRM9/pfile]$ sqlplus / as sysdba

SQL*Plus: Release 11.1.0.7.0 - Production on Sun Mar 15 12:01:13 2009

Copyright (c) 1982, 2008, Oracle. All rights reserved.


Connected to:
Oracle Database 11g Enterprise Edition Release 11.1.0.7.0 - 64bit Production
With the Partitioning, OLAP, Data Mining and Real Application Testing options

SQL>
SQL> select * from v$version;

Oracle Database 11g Enterprise Edition Release 11.1.0.7.0 - 64bit Production
PL/SQL Release 11.1.0.7.0 - Production
CORE 11.1.0.7.0 Production
TNS for Linux: Version 11.1.0.7.0 - Production
NLSRTL Version 11.1.0.7.0 - Production

SQL> desc v$session
Name Null? Type
----------------------------------------- -------- ----------------------------
SADDR RAW(8)
[...]

Peoplesoft on Linux 64-bits

Good news for the Linux 64-bits customers. Peoplesoft announced the 3rd of March'09 they are now certified on Linux 64-bits (OEL/RHEL), please see the metalink3 note :
Oracle PeopleSoft extends certification of OEL and RHEL 5 x86-64

The requirements for 64-bits appliance are OEL5.2 or RHEL5.2 minimum, and Peopletools 8.49.14 min.

Now, need to be tested on my lab...

OTN forums : 28 weeks later

Following the previous post I wrote last September (28 days later), today 28 weeks later, in reference to an other horror movie, is a logical way to go right now.
So, 28 weeks later, are there any improvements in OTN Forums ?
No, not really. Even worse. As a "birthday" present, today a new issue appears (again) with the email address...

Less and less valuable people are particpating, people does not learn how to give points or "close" thread by marking thread as answered when it is not, forums slowness and often up-down effects, and of course, still these bloody markups we cannot avoid anymore, and still no getting back the red bullets to mark threads as (un)read.

It seems the upgrade of last summer was a downgrade, nothing more. And despite all the complaints of OTN members, nothing has been done to really improve the OTN forums.

Finally, why continue to use it ?

Move Peoplesoft install to a new server

Since I move from OEL4.6 to OEL5.3, same word size (32-bits), same OS, no need to reinstall everything.
The fastest and simplest way, is to copy PS_HOME, TUX_HOME and WEBLOGIC_HOME to the new server.

Recreate the user with same .bash_profile and same rights as of the source.

If you copy to a different path directory, don't worry :
1. For PS_HOME, just define it as well in the .bash_profile of the PS_HOME owner
2. For Weblogic, when you recreate the webserver domain, give the new path
3. For Tuxedo, modify the script $PS_HOME/setup/psdb.sh, set the variable TUXDIR according to the new path :
...
TUXDIR=/apps/bea/tuxedo/9.1;export TUXDIR
...

Before running any application server, you should be sure about one environment variable value (due to OEL 5.x) . Open the file $PS_HOME/psconfig.sh, and check the variable PS_HOSTTYPE, it should be :
PS_HOSTTYPE=redhat-4-ia32;export PS_HOSTTYPE
Then disconnect and reconnect as the PS_HOME owner, that'll recall the psconfig.sh.
Because of some changes on OEL5.x, this file is not automatically updated by the installer, if you do not set correctly PS_HOSTTYPE, you could hit the following error when running psconfig.sh :
-bash - Error : Unsupported Host Type unknown...
ERROR: PS_HOSTTYPE is not set to a valid value.


Finally, you'll need :
1. Recreate the web server domain :
[weblogic@orion2 /apps/psoft/hrms9/setup/PsMpPIAInstall]$ export DISPLAY=0.0
[weblogic@orion2 /apps/psoft/hrms9/setup/PsMpPIAInstall]$ ./setup.linux -console
...
2. Reconfigure the application server domain with the psadmin menu

But it is very easy, only few minutes are required.
Assuming the tnsnames.ora file is updating to the new database location, everything should start on this new server.

Nicolas.

Oracle upgrade 10.2.0.4 to 11.1.0.7

Before running the upgrade, I'll do a manual upgrade, a script must be run on the source database :
$ORACLE_HOME/rdbms/admin/utlu111i.sql
Don't forget it. Apply the recommandations returned by the script.

Then, stop the database.
Since I move from a OEL4.6 server to a OEL5.3 server, I copied all the datafiles over the network.
Here an example of one datafiles :
ssh root@192.168.1.21 "gzip -c < /oradata2/DMOHRMS9/datafiles/ccapp.dbf -"|gunzip -c > /oradata/DMOHRMS9/datafiles/ccapp.dbf

Upgrade the database itself by running the upgrade script (database started in UPGRADE mode) :
$ORACLE_HOME/rdbms/admin/catupgrd.sql
The script bring your database down.
Finally, start your db in normal mode (OPEN) :
[oracle@orion2 /home/oracle]DMOHRMS9$ sqlplus / as sysdba

SQL*Plus: Release 11.1.0.7.0 - Production on Thu Feb 26 19:07:20 2009

Copyright (c) 1982, 2008, Oracle. All rights reserved.

Connected to an idle instance.

SQL> startup
ORACLE instance started.

Total System Global Area 765833216 bytes
Fixed Size 1316120 bytes
Variable Size 188746472 bytes
Database Buffers 570425344 bytes
Redo Buffers 5345280 bytes
Database mounted.
Database opened.
SQL>

And run the additionals two scripts to finalize the upgrade :
$ORACLE_HOME/rdbms/admin/catuppst.sql
$ORACLE_HOME/rdbms/admin/utlrp.sql

We have now a 11gR1 database on the new server.

Next step is the PS_HOME, Weblogic and Tuxedo move to the new server.

Oracle 11.1.0.7 install on OEL5.3

After installing OEL5.3, time to install Oracle. Peoplesoft 8.48 and 8.49 are fully supported on the last version, 11.1.0.7.

So, let's go.

Firstly, be sure you have all the required packages installed :
binutils-2.17.50.0.6-9.el5.i386
compat-db-4.2.52-5.1.i386
compat-gcc-34-3.4.6-4.i386
compat-gcc-34-c++-3.4.6-4.i386
compat-glibc-headers-2.3.4-2.26.i386
compat-libgcc-296-2.96-138.i386
compat-libstdc++-296-2.96-138.i386
compat-libstdc++-33-3.2.3-61.i386
cpp-4.1.2-44.el5.i386
elfutils-libelf-0.137-3.el5.i386
elfutils-libelf-devel-0.137-3.el5.i386
elfutils-libelf-devel-static-0.137-3.el5.i386
gcc-4.1.2-44.el5.i386
gcc-c++-4.1.2-44.el5.i386
glibc-2.5-34.i686
glibc-common-2.5-34.i386
glibc-devel-2.5-34.i386
glibc-headers-2.5-34.i386
java-1.4.2-gcj-compat-1.4.2.0-40jpp.115.i386
ksh-20080202-2.el5.i386
libaio-0.3.106-3.2.i386
libaio-devel-0.3.106-3.2.i386
libgcc-4.1.2-44.el5.i386
libstdc++-4.1.2-44.el5.i386
libstdc++43-devel-4.3.2-7.el5.i386
libstdc++-devel-4.1.2-44.el5.i386
make-3.81-3.el5.i386
sysstat-7.0.2-3.el5.i386
unixODBC-2.2.11-7.1.i386
unixODBC-devel-2.2.11-7.1.i386

If some are missing, add it, and no more the question on which cd is it, OEL5.3 is delidered as a DVD right now.

And also the kernel settings :
1. /etc/sysctl.conf
Change the line
net.core.rmem_default = 262144
by
net.core.rmem_default = 4194304
After modification, run "sysctl -p" to take the changes in account without reboot the server.

2. /etc/pam.d/login
Add the line :
session required pam_limits.so


Then in a XWindows client, run the following command as oracle user and everything should be fine.
./runInstaller :






Finally, the patchset 1:




Enjoy,

Oracle Entreprise Linux 5.3

The installation of OEL5.3 is pretty similar of the previous version.
Two main differences however :
1. We have now a DVD iso image available for download (http://edelivery.oracle.com)


2. We have an Oracle specific package (oracle-validated-1) to check some settings and create the oracle user and dba/oinstall groups :


All the other steps are same as the previous version, I won't paste the screenshots again over here.
You can find all the screenshots of the OEL4.6 install in one of my previous posts :
http://gasparotto.blogspot.com/2007/12/on-peoplesoft-road-oel-46.html

Enjoy,

Speed up a copy over the network

I have to copy all datafiles of a database over the network between two of my VMs.
The datafiles are big, all together, it is around 150Gb. My network is slow and busy (an average of 5 or 6Mb/s).
The standard scp command line copy one 1gb datafile in 6 minutes (5'52'') :

Sun Feb 22 15:39:57 CET 2009
scp root@192.168.1.21:/oradata/DMOFSCM9/datafiles/pclarge.dbf .
Sun Feb 22 15:45:49 CET 2009
ls -lrt
-rw-r----- 1 root root 1040195584 Feb 22 15:45 ./pclarge.dbf

How to speed up the copy ?
I found a way to reduce that time to 34 seconds for this 1Gb datafile. Since the servers are mine, everything is allowed .
But only one command line :
Sun Feb 22 15:45:49 CET 2009
********
Sun Feb 22 15:46:23 CET 2009
ls -lrt
-rw-r--r-- 1 root root 1040195584 Feb 22 15:46 ./pclarge.dbf

Any idea ?

update : thank you for the suggestions, I have to test them.
Currently, I used the following command, I don't say it is the best, but at least, was not bad according to the down time :
ssh root@192.168.1.21 "gzip -c < /oradata/DMOFSCM9/datafiles/pclarge.dbf -"|gunzip -c > /oradata/DMOFSCM9/datafiles/pclarge.dbf

OEL5.3, Oracle 11.1.0.7 and PTools patches

My lab is becoming out of date, built more than one year ago.
My OS is Oracle Entreprise Linux 4.6, and last week the 5.3 has been released, time to get a newer OS.
My databases are Oracle 10.2.0.4, and now Peopletools 8.49 is supported onto 11.1.0.x for a while, time for change.
Lastly, my Peopletools are 8.49.11, the latest patch is 8.49.18, also time to go further.

It is a lot of work, I'm planning to create a new VM, install all the required binaries (Oracle, Peopletools, Application HRMS, CRM and FSCM) then move the databases to that new VM and upgrade them.

I'll post soon the steps of that migration.

Enjoy,

DEMO HRMS 9.0 : ORA-00600

If you are installing, working, with Peoplesoft HRMS9.0, Peopletools 8.49 on Oracle database 10gR2 (currently on 10.2.0.4), you could easily hit an ORA-600 error.

Go to the front end application, menu :

"Manager Self Service / Job and Personal Information / Request Job Change"

You'll receive the following pop-up error message :

And the alert.log of the database :

[ora102@orion:/apps/oracle/admin/DMOHRMS9/bdump]DMOHRMS9$ tail -f alert_DMOHRMS9.log

Starting background process QMNC

QMNC started with pid=13, OS id=6767

Sun Feb 8 17:38:24 2009

Completed: ALTER DATABASE OPEN

Sun Feb 8 17:38:32 2009

Starting background process CJQ0

CJQ0 started with pid=19, OS id=6830

Sun Feb 8 17:45:08 2009

Errors in file /apps/oracle/admin/DMOHRMS9/udump/dmohrms9_ora_6865.trc:ORA-00600: internal error code, arguments: [kkqtcpcky:ficand], [], [], [], [], [], [], []

The content of the file /apps/oracle/admin/DMOHRMS9/udump/dmohrms9_ora_6865.trc :
[ora102@orion:/apps/oracle/admin/DMOHRMS9/bdump]DMOHRMS9$ more /apps/oracle/admin/DMOHRMS9/udump/dmohrms9_ora_6865.trc
/apps/oracle/admin/DMOHRMS9/udump/dmohrms9_ora_6865.trc
Oracle Database 10g Enterprise Edition Release 10.2.0.4.0 - Production
With the Partitioning, OLAP, Data Mining and Real Application Testing options
ORACLE_HOME = /apps/oracle/10.2.0
System name: Linux
Node name: orion.phoenix-nga
Release: 2.6.9-67.0.0.0.1.ELsmp
Version: #1 SMP Sun Nov 18 00:23:42 EST 2007
Machine: i686
Instance name: DMOHRMS9
Redo thread mounted by this instance: 1
Oracle process number: 22
Unix process pid: 6865, image: oracle@orion.phoenix-nga
*** ACTION NAME:() 2009-02-08 17:45:08.957
*** MODULE NAME:(PSAPPSRV@orion.phoenix-nga (TNS V1-V3)) 2009-02-08 17:45:08.957 *** SERVICE NAME:(DMOHRMS9) 2009-02-08 17:45:08.957
*** SESSION ID:(145.39) 2009-02-08 17:45:08.957
*** 2009-02-08 17:45:08.956
ksedmp: internal or fatal error
ORA-00600: internal error code, arguments: [kkqtcpcky:ficand], [], [], [], [], [], [], []
Current SQL statement for this session:
SELECT ' ' ,C.MANAGER_ID ,COUNT(*) FROM PS_JOB A , PS_DEPT_TBL C WHERE A.EMPL_STATUS IN ('A', 'L', 'P', 'W') AND A.EFFDT= ( SELECT MAX(A2.EFFDT) FROM PS_JOB A2 WHERE A.EMPLID=A2
.EMPLID AND A.EMPL_RCD=A2.EMPL_RCD AND A2.EFFDT<=TO_DATE(:1,'YYYY-MM-DD')) AND A.EFFSEQ= ( SELECT MAX(A3.EFFSEQ) FROM PS_JOB A3 WHERE A.EMPLID=A3.EMPLID AND A.EMPL_RCD=A3.EMPL_R CD AND A.EFFDT=A3.EFFDT ) AND C.SETID = A.SETID_DEPT AND C.DEPTID = A.DEPTID AND C.EFFDT= ( SELECT MAX(C2.EFFDT) FROM PS_DEPT_TBL C2 WHERE C.SETID=C2.SETID AND C.DEPTID=C2.DEPTI D AND C2.EFFDT<=TO_DATE(:2,'YYYY-MM-DD')) AND C.MANAGER_ID IN(SELECT A.EMPLID FROM PS_JOB A , PS_DEPT_TBL C WHERE A.EMPL_STATUS IN ('A', 'L', 'P', 'W') AND A.EFFDT= ( SELECT MAX (A2.EFFDT) FROM PS_JOB A2 WHERE A.EMPLID=A2.EMPLID AND A.EMPL_RCD=A2.EMPL_RCD AND A2.EFFDT<=TO_DATE(:3,'YYYY-MM-DD')) AND A.EFFSEQ= ( SELECT MAX(A3.EFFSEQ) FROM PS_JOB A3 WHERE A.EMPLID=A3.EMPLID AND A.EMPL_RCD=A3.EMPL_RCD AND A.EFFDT=A3.EFFDT ) AND C.SETID = A.SETID_DEPT AND C.DEPTID = A.DEPTID AND C.EFFDT= ( SELECT MAX(C2.EFFDT) FROM PS_DEPT_TBL C2 W HERE C.SETID=C2.SETID AND C.DEPTID=C2.DEPTID AND C2.EFFDT<=TO_DATE(:4,'YYYY-MM-DD')) AND C.MANAGER_ID=:5) GROUP BY C.MANAGER_ID

----- Call Stack Trace -----

[...]

If we are running the same query onto the database within SQL*Plus, same error, even to get the explain plan :
SQL> explain plan for
2 SELECT ' ' ,C.MANAGER_ID ,COUNT(*)
3 FROM PS_JOB A , PS_DEPT_TBL C
4 WHERE A.EMPL_STATUS IN ('A', 'L', 'P', 'W')
5 AND A.EFFDT= ( SELECT MAX(A2.EFFDT) FROM PS_JOB A2 WHERE A.EMPLID=A2.EMPLID AND A.EMPL_RCD=A2.EMPL_RCD AND A2.EFFDT<=TO_DATE(:1,'YYYY-MM-DD')) 6 AND A.EFFSEQ= ( SELECT MAX(A3.EFFSEQ) FROM PS_JOB A3 WHERE A.EMPLID=A3.EMPLID AND A.EMPL_RCD=A3.EMPL_RCD AND A.EFFDT=A3.EFFDT ) 7 AND C.SETID = A.SETID_DEPT AND C.DEPTID = A.DEPTID AND C.EFFDT= ( SELECT MAX(C2.EFFDT) FROM PS_DEPT_TBL C2 WHERE C.SETID=C2.SETID AND C.DEPTID=C2.DEPTID AND C2.EFFDT<=TO_DATE(:2,'YYYY-MM-DD')) AND C.MANAGER_ID IN(SELECT A.EMPLID FROM PS_JOB A , PS_DEPT_TBL C WHERE A.EMPL_STATUS IN ('A', 'L', 'P', 'W') AND A.EFFDT= ( SELECT MAX(A2.EFFDT) FROM PS_JOB A2 WHERE A.EMPLID=A2.EMPLID AND A.EMPL_RCD=A2.EMPL_RCD AND A2.EFFDT<=TO_DATE(:3,'YYYY-MM-DD')) 8 9 10 11 12 AND A.EFFSEQ= ( SELECT MAX(A3.EFFSEQ) FROM PS_JOB A3 WHERE A.EMPLID=A3.EMPLID AND A.EMPL_RCD=A3.EMPL_RCD AND A.EFFDT=A3.EFFDT ) AND C.SETID = A.SETID_DEPT AND C.DEPTID = A.DEPTID AND C.EFFDT= ( SELECT MAX(C2.EFFDT) FROM PS_DEPT_TBL C2 WHERE C.SETID=C2.SETID AND C.DEPTID=C2.DEPTID AND C2.EFFDT<=TO_DATE(:4,'YYYY-MM-DD')) AND C.MANAGER_ID=:5) GROUP BY C.MANAGER_ID 13 14 15 16 17 18 ;

FROM PS_JOB A , PS_DEPT_TBL C

* ERROR at line 3: ORA-00600: internal error code, arguments: [kkqtcpcky:ficand], [], [], [], [], [], [], []

Some of the indexes of the two tables involved in this query are created in descending order.

The descending indexes have been introduced with the Peopletools 8.48 onwards for some (for me, mysterious) reasons, let's say surely to improve the performances.

But, I remember some months ago a benchmark I did on CRM9 (Peopletools 8.48 - Oracle database 10.2.0.3), the indexes in descending order cause a lot of performances issues. I had to use an hidden parameter to deactivate the descending indexes to have better performance.

/* For reminder, it is not only more than a huge work to recreate all the indexes without that DESCending clause, but that make the Peoplesoft application customized, which is not so good for the support, that's why the hidden parameter was a good workaround to keep the Peoplesoft objects closer to the standard. */

Here again, the descending indexes causes the trouble.

As you could have seen above, it is not performance issue, but transactional error and a database ORA-600.

It seems to hit an "old" Oracle bug (from 8.1.7.x to 9.2.x, bug ref #869177) for which the workaround was the setting of "_IGNORE_DESC_IN_INDEX" to TRUE (FALSE by default).

So, it seems we are still under the same effect, my database is 10.2.0.4. So, let's change the setting :

SQL> conn / as sysdba
Connected.
SQL> alter system set "_ignore_desc_in_index" = true scope=both;

System altered.

SQL>

Now, it is not enough, the indexes have to be rebuilt. Rebuild in the Peoplesoft meaning, DROP + CREATE.

So, open the Application Designer, connect within PS user.

Insert into the project the two records JOB and DEPT_TBL.

Then build the project, check only "Create Index", go to the settings, and be sure you are checking the "Recreate index if it already exists", then build the project, as show below :

Then, run the generated SQL script, that won't modify the record (the tables), just drop and recreate the indexes. Oracle will ignore the DESC option embedded in the commands lines of the indexes creation because of the setting of that hidden parameter "_IGNORE_DESC_IN_INDEX".

Connected to:
Oracle Database 10g Enterprise Edition Release 10.2.0.4.0 - Production
With the Partitioning, OLAP, Data Mining and Real Application Testing options

SQL> @rebuild_indexes

Index dropped.


Index dropped.


Index dropped.


Index dropped.


Index dropped.


Index dropped.


Index dropped.


Index created.


Index altered.
[...]

Finally, go back to the front end application, and retry the previous failing transaction, from now that'll work fine :

Problem solved.

However, here above was only an example on two records (tables), but you could have to rebuild all the indexes for all the records. A lot of time is required for that.

Conclusion : before creating, especially before loading the Peoplesoft objects into the database (before the first datamover load of the database setup), it could be a good idea to set this hidden parameter "_IGNORE_DESC_IN_INDEX" to TRUE.

Caution : from now, you have a difference between the Peoplesoft datamodel (with the DESCending order in the indexes description) and the Oracle dictionary (without the DESCending order), that means every time you'll rebuild the records, the Application Designer will try to rebuild the indexes, that is not the case when objects are same on both sides. This is one of the biggest problem of this solution.

Caution 2 : playing around hidden parameter is supposed to be validated by your Oracle support.

Going further : after raising a SR to the Oracle support, it seems this bug is solved on Oracle 11.1, and a fix will be included in the coming 10.2.0.5 patchset.

Wait and see, or manage an upgrade to 11.1 (after testing, I confirm, it is working fine on 11.1.0.7).

Enjoy,

Full PeopleBooks on OTN

As announced on the PeopleSoft Technology Blog the Peoplebooks are now available with a workable search feature.
The Peoplebooks are still available in PDF format (less usable) :
http://www.oracle.com/technology/documentation/psftent.html

But from now, this is a fully working Peoplebooks which are avalaible, a very great new :
http://www.oracle.com/pls/psft/homepage

Enjoy it !

2009

First of all, I wish an happy New Year to all the Oracle community ! Best wishes, and may the economic crisis don't impact you, or at a minimum level.

What are waiting us for the new year 2009 ?

Well, several new versions are waiting, let's make some bets :
1. Peopletools 8.50, a Web 2.0 ready, and a big difference compared to the Peopletools 8.4x. Maybe for the first quarter.
2. Peoplesoft 9.1, few Peoplesoft applications are waiting in that new version, started by FSCM. Maybe for the second quarter.
3. Oracle 10.2.0.5, with a lot of bug fixes I'm looking for, let's bet for the second quarter, or maybe the third.
4. Oracle 11.2, let's bet for the third quarter of the year - during the OOW2009 ?
5. Oracle Entreprise Linux 5.3, sometime between the first and the second quarter.

And also a wish regarding the OTN forums, make it more usable and pointless and workable...
Metalink2 could be less funny and more usable...
Metalink3 could be improved, currently many links are still pointing to the old and unavailable GSC...

Enjoy this year !

impdp with in Peoplesoft database

I was very enthusiat by the new features offered by the Oracle import dump, the new import utility in 10g.
Especially with the option NETWORK_LINK which allow us to do an import in a database through a database link directly from the database source, without having the need of the creation of any dump file.
It is very useful regarding the size of Peoplesoft database dump, several dozen of Gb.
Until today, to save disk space, it was common to use an old export utility into a dump file and compress on the fly. Same for the import, uncompress and import into database on fly.
Since it is not possible to do with expdp, the NETWORK_LINK should offer a good alternative.

Unfortunately, two mains points make it impossible to use :

1. NETWORK_LINK does not work with tables containing LONG datatype.
It is not a joke, but Peoplesoft database (before the Peoplesoft version 9.0 and Peopletools 8.49) still contains a lot of tables like that. I was very disapointed by this limitation, and I was surprised because nothing in the documentation say anything about that limitation. You could read the Metalink note 6630677.8 (IMPDP skips table exported just before table with LONG column using NETWORK_LINK - seems to be fixed in 11.1.0.6).
Ok, we could find a workaround and work through a file for these tables (with LONG datatype column in it), even if it's a pity. Or work with the very old, but much more robust SQL*Plus command COPY.

2. There is a bug in the number of imported rows reported in the log file (known bug #7146127, not solved yet). In some cases, for tables containing a lot of columns (I don't know what's the number to hit this error), it is reported 1 imported row in the log file, even if it imported several of thousands of rows. So, now how to prove how many have been imported ? Run a count against every single imported tables ? Not acceptable.

Here is the output of number of columns by tables (Peoplesoft HRMS9, Peopletools 8.49) :
Nb tables with less than 100 columns.................. : 42655
Nb tables with more than 100 and less than 200 columns : 171
Nb tables with more than 200 columns.................. : 21

What else ? Still stuck with exp/imp utility despite they are old.

Enjoy,

Addendum (04-FEB-2009) : regarding the wrong number of rows reported in the logfile, there is one off-patch for 10.2.0.4, it is fixed in the coming 11.2, please have a look into the following Metalink note :
Impdp Logfile Reports Wrong Number of Row Imported For a Table If Using NETWORK_LINK
Doc ID: 752476.1

Oracle 11.1.0.6_1013

Last month, October 2008, Oracle has released a new version of the initial patchset level of 11gR1 - 11.1.0.6 - downloadable on http://download.oracle.com
If you start a new project with in 11gR1 database, take care to have this version, the file is suffixed by _1013.
This new recut of the 11gR1 include a bug fix - see the Metalink note 738115.1 for more information.

Enjoy,

Metalink3, the end of Peoplesoft GSC, future of database support

Yesterday, Firday 7th, November 2008, the website Metalink3 has take in place of the Peoplesoft Global Support Center (http://www.peoplesoft.com).
That was expected since Oracle bought Peoplesoft. Finally, it is happening.
It is a very big change for Peoplesoft cutomers.
For who work on Peoplesoft for several years, that means a lot of habits have to be change (started with the severity scale and basics words as "case" becoming "SR").

Awaiting the effectivness of the replacement (from the coming Monday, after the outage during this week-end), the Metalink3 was available in "reduce" mode without all the features. That is more likely the Oracle classic Metalink site we know for the database support.

Moreover, it has been already announced, the Metalink3 website will become also the support website for the Oracle Database, eBusiness... the date is not defined yet.

Well, hopefully, that'll help,

Enjoy,

Nicolas.