When you are at that client site that insists that you must use your own account to interact with PeopleSoft but they don’t set your schema, just change your default schema using the command:

alter session set current_schema=sysadm;

Where sysadm is the default PeopleSoft schema in Oracle, however, PeopleSoft does not have any issues with the schema owner being different then sysadm.  A couple of ways to find the schema, is to look at the table:  psdbowner (ownerid) or psstatus (ownerid). By default the psdbowner table is stored in the PS schema.

When doing SQL in Oracle where you want to search for something containing a string, you can use the like command.

 Select * from table1 where field1 like ‘somevalue’;

This will return rows with field1 = somevalue, not exactly useful.  But you can use the wildcard ‘%’ so

 Select * from table1 where field1 like ‘%somevalue%’;

This will return rows with field1 containing the string ‘somevalue’ in any form.  There is another wildcard ‘_’ which will match any character in that position, so

Select * from tabe1 where field1 like ‘_omevalue’;

This will return rows with field1 = aomevalue, bomevalue, comevalue, domevalue, …., zomevalue

If you want to use the the literal of % and _ then you will have to put an escape character in front of it, and tell Oracle what the escape character is:

Select * from table1 where field1 like ‘lookfor\%’ escape ‘\’

This will return rows where field1 = ‘lookfor%’

The file oratab is created when you install the Oracle Database server product.  This file is typically found in the /etc directory and contains entries in the format:

 sid:oracle_home:Y|N

 So the sid represents the System Id of an Oracle Database on the current server, oracle_home represents the home of where the database instance resides, and the Y|N indicates weather or not the database instance starts at bootup. Y=Yes, N=No. 

 This file is used during the dbstart, dbshut, and oraenv commands, and can also be used in custom scripting to cycle through the databases instances housed on the server.

ORA-01126: Database Must Be Mounted

When trying to change an oracle database to no archive log mode you might receive an error: ORA-01126: database must be mounted  EXCLUSIVE and not open for this operation.

Turn Off Archive Logging:

The easiest way is to shutdown the database and then startup unmounted, then do the alters, you will want to do this as the main oracle administrator database user:

NOTE: When in NOARCHIVELOG mode the database runs normally, but there is no capacity to perform any type of point in time recovery operations or online backups. Thus, you have to shutdown the database to back it up, and when you recover the database you can only recover it to the point of the last backup. While this might be fine for a development environment, the management folks tend to frown when a weeks worth of current production data is lost forever.

Turn On Archive Logging:

To turn on archive logging, you can use the command:

alter database archivelog;

Note: When in ARCHIVELOG mode, the database will make copies of all online redo logs after they are filled. These copies are called archived redo logs. The archived redo logs are created via Oracle’s Archive Process (ARCH). This process copies the archived redo log files to one or more archive log destination directories. In order for this process to be used additional setup is required primarily the redo log destination directories. In non-production environments there can be some major down sides to running in ARCHIVELOG mode.  The most common one Smart Panda Runs into is:  ORA-00257

Check Archive Logging Status:

To see the current status issue the following command:

archive log list;

When turning on archiving, if you need to use RMAN to do a backup, you need to actually have a log archive file, if you have just turned it on, there is no archive logs, so you will get an error.  You can force Oracle to generate a log archive using the following command from sql*plus:

alter system switch logfile;

Performance testing in Oracle can be difficult when trying to compare results from two different environments.  One way to get a more accruate apple to apple comparison is to flush the data buffer cache. 

Note that this is for testing purposes only and should not be considered a recommendation in a live production system.

Oracle 9i and up:

alter session set events ‘immediate trace name flush_cache’;

Oracle 10g and up:

alter system flush buffer_cache;

You can also flush the shared pool of the SGA by using the command:

alter system flush shared_pool;