Oracle – PeopleSoft Tutorial

Tag Archive

Tag Archives for " Oracle "

ORA-00997: illegal use of LONG datatype

During PeopleTools upgrade or PeopleSoft application upgrade, you may get an Oracle error: ORA-00997: illegal use of LONG datatype. This post will walk you through the steps to workaround and resolve the error. I encountered this error during PTools upgrade and based on the symptoms, it looks like you may face this error in PeopleSoft application […]

ORA-60019: Creating initial extent of size 14 in tablespace of extent size 13

During PeopleTools upgrade, there is a step to convert LONG datatypes to CLOB and BLOB. When using Oracle database, you may get this error: ORA-60019: Creatin g initial extent of size 14 in tablespace of extent size 13 The numbers associated with the extend size may change according to the tablespace involved in the DDL statement […]

How to Resolve TNS-03505: Failed to resolve name

Every now and then when I created a new Oracle database in an existing Oracle base (be it 11g or 12c) – I get the error “TNS-03505: Failed to resolve name” whenever I try to do tnsping on a new database. You may receive this error even when not doing tnsping. For example, you may see […]

Get Count of All PeopleSoft Tables with one SQL

While performing upgrades or applying maintenance to your PeopleSoft applications, you might want to get row count of all your PeopleSoft tables. There are few ways of doing it which we will discuss below and finally the one which I think is the easiest and most efficient way. Option 1: Running UPGCOUNT AE UPGCOUNT is […]

ORA-20005: object statistics are locked (stattype = ALL)

While doing an application or a PeopleTools upgrade, you do update stats at a couple of steps. Also, to support your PeopleSoft instance, you do update stats at frequent interval. What update stats can cause is an error very similar to something like this: SQL Error. Error Position: 0 Return: 20005 – ORA-20005: object statistics […]

Oracle PL/SQL Cheat Sheet

PL/SQL (Procedural Language/Structured Query Language) is Oracle Corporation’s procedural language extension for SQL and the Oracle relational database. PL/SQL is available in Oracle Database (since version 7) and IBM DB2 (since version 9.7). Oracle Corporation usually extends PL/SQL functionality with each successive release of the Oracle Database. While PL/SQL is easy to learn, it can […]

(SQR 3725) Bad return fetching row from database.

The PeopleTools upgrade step “Running DBTSFIX Report” might fail with the error: Rel script conversion program PRCSDEFN match Error on line 842: (SQR 3725) Bad return fetching row from database. SQR for PeopleSoft: Program Aborting. How to Fix (SQR 3725) Bad return fetching row from database. This error is caused by invalid packages in case of […]

This Oracle 12C Setting Can Jeopardize Your PeopleSoft Installation

Oracle Database 12C is supported with PeopleTools 8.53 but there is one feature in Oracle 12C that can jeopardize your PeopleSoft Installation. Recently while installing PeopleSoft 9.2 with PeopleTools 8.53 using Oracle 12C as database, I was struck at running initial database creation scripts like dbowner.sql and psroles.sql. Scripts weren’t creating users and roles as […]

How to Manually Uninstall Oracle in Windows 8

Let’s face it – Oracle database is an excellent product but its uninstall process is still mediocre. There are a lot of unknown issues arise when you try to uninstall Oracle database and then try to install it again. We get a lot of queries every week regarding problems installing Oracle products. Most of these […]

Understanding Oracle Database and PeopleSoft Database

Some of the terms especially when it comes to database are loosely used and may cause confusion when performing a PeopleSoft installation. This post will help you revisit and understand PeopleSoft database concepts when used with Oracle database. When you perform a PeopleSoft installation, you will create these types of PeopleSoft databases: • System (also […]

ORA-01555: snapshot too old

We were doing stress testing in PeopleSoft ELM and ran the Mass Enrollment AppEngine. After running for about 90 minutes, it failed with the error ORA-01555: snapshot too old: rollback segment number 19 with name “_SYSSMU19_1270741541$” too small The ORA-01555 error is caused by Oracle read consistency mechanism and is usually associated with long running […]

Common Database Errors

Common Database Errors Following is a list of known SQL errors, and potential solutions to the problems that they represent. Known Database Error Codes Database Error Code SQL State Message Solution DB2 -803 23505 SQL0803N One or more values in the INSERT statement, UPDATE statement, or foreign key update caused by a DELETE statement are […]

How to Find Oracle Hidden Parameters

Oracle has hundreds of initialization parameters, which are hidden and undocumented. Many savvy Oracle professionals are known to commonly adjust the hidden parameters to improve the overall performance of their systems. However, because these are “undocumented” parameters, most Oracle professionals rely on publications such as “Oracle Internals” to get insights into the proper setting for the […]

Enabling Automated SQL Tuning in Oracle 11g

You need to identify if Automatic SQL Tuning job is enabled and regularly running. Use the following query to determine if any Automatic SQL Tuning jobs are enabled: SELECT client_name, status, consumer_group, window_group FROM dba_autotask_client ORDER BY client_name; If the Automatic SQL Tuning job is enabled, you will see something like this: […]

How to Identify User and SQLs in an Oracle database?

This is a very common scenario for a DBA to be in a situation where a DBA needs to find out one of the following: Which user is running which SQL? A particular SQL is being run by which user? A particular user is running which SQL? As it is evident from these questions – […]

Automated SQL Tuning in Oracle 11g

Oracle Database 11g made it easier for the less experienced to have a shot at SQL tuning, which was earlier considered to be a bastion for veteran SQL tuners. A typical Oracle SQL tuning process had the following steps: Identification of high-resource SQL statements and bottlenecks Generating and interpreting execution plans Extracting data from the […]

Performance Tuning by Identifying Poor SQLs using v$

To a user, an application is never fast enough to satisfy his/her needs. Similarly, to a database administrator, an application is never fast enough to satisfy the needs of the user community. Perception and unreasonable expectations are definitely a cause. However, , insufficient hardware, poorly tuned application code, a poorly tuned database, or an architecture that doesn’t […]

PeopleSoft 8.53: Creating PeopleSoft HCM9.2 Database

This post is in series of post for Installation of HCM9.2 on Windows 7. It’s one of the most important task in PeopleSoft Installation and require that you finish all the previous steps. PeopleSoft database creation is a long process and to make it easy to follow, below post has been divided into sections. Creation […]

1 2 Next »