Outer joins is just one of the items that is required from time to time, but since I typically never recommend using them with PeopleSoft, I always forget the syntax.  Since my days go back to Oracle 7, I remember the messy (+) notation and so I always end up looking it up.  The good news is Oracle superseded the (+) notation by going to ISO 99 standard as of Oracle 9i, however it still supports (+) notations.

There are three types of joins you can do, left outer, right outer and full outer join. In a left outer join, all the records of the table listed on the left will be returned with the combined results of the join to the table listed on the right, however, if the right listed table does not have a matching row, NULLS will be returned for the columns of the right listed table.  The right outer join does the same thing just in reverse, whereas the full outer join basically does a UNION of the left & right outer joins.

Some database platforms do not support full outer joins so you can basically do it by doing a left outer join combined with with a right outer join using the union statement.

SELECT A.NAME, D.DEPARTMENT FROM EMPLOYEE A LEFT OUTER JOIN DEPARTMENT D ON A.DEPTID = D.DEPTID;

If you want to do the Oracle (+) syntax:

SELECT A.NAME, D.DEPARTMENT FROM EMPLOYEE A, DEPARTMENT D WHERE A.DEPTID = E.DEPTID (+);

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;