PeopleSoft - Install as a Service

In PeopleTools 8.49 we can now install the Windows Application Servers and Process Schedulers as a Service.

This can easily be setup using new a menu option on psadmin.exe 4) Service Setup.

In my case, I only wanted to configure the Process Scheduler as a Service. This was easily achieved by Editing the Service Configuration File (psntsrv.cfg) and commenting out the line - Application Server Domains=.

Once the service has been configured, use the 2) Install a Service menu option and you're ready to rock!

Oracle/Unix - Pipe to Gzip

As an example, I wish to spool/pipe out an oracle export file straight to gzip on unix.

Create a Pipe

mkfifo ppipe

In the background send the pipe contents to gzip

cat ppipe | gzip -c > filename.dmp.gz &

Run the export to the pipe

exp username/password file=ppipe log=filename.log

Data Pump - Dynamic List of Tables to Export

In PeopleSoft, I needed to export out all configuration/system-owned tables as a backup each week. To achieve this, my first step was to create a view listing all these tables, by filtering out those which are not prefixed with "PS_"

CREATE OR REPLACE VIEW EXPORT_WEEKLY (TABLE_NAME) AS
SELECT table_name
  FROM all_tables
 WHERE owner = 'SYSADM' 
   AND NOT REGEXP_LIKE (table_name, '^PS(_)');

I then used Oracle datapump to export the tables listed in this view ..

expdp username/password parfile=parfilename

parfile contents

DIRECTORY=dpump_dir1
DUMPFILE=dpump_dir1:dumpfile.dmp
LOGFILE=dpump_dir1:logfile.log
SCHEMAS=SCHEMANAME
INCLUDE=TABLE:"IN (SELECT TABLE_NAME FROM EXPORT_WEEKLY)"
CONTENT=DATA_ONLY

PeopleSoft List Of System Tables

To obtain a list of all "system owned" tables in PeopleSoft, i.e. those which are not prefixed by a PS_, run the following SQL:

SELECT table_name
  FROM all_tables
 WHERE owner = 'SCHEMA_OWNER'
   AND NOT REGEXP_LIKE (table_name, '^PS(_)');

Greeting Cards by Rahila

Temp Tablespace Usage

A useful script to check on users/processes using up temp tablespace.

SELECT distinct s.username
     , s.sid
     , s.serial#
     , s.osuser
     , u.tablespace
     , u.contents
     , u.segtype
     , u.extents
     , u.blocks
  FROM v$session s
     , v$sort_usage u
 WHERE s.saddr=u.session_addr
order by s.username, s.osuser;

Create User Scripts

There are many ways of cloning a user account or two from one database to another. Here are a few simple SQLs to dynamically generate scripts from the source database, which can then be executed on the target database.

I have in this example assumed that the tablespaces etc in the source and target database are identical in name and size. I have also chosen to set all the user roles (by default) as Default.

select 'CREATE USER ' || username || ' IDENTIFIED BY VALUES ''' || password || ''' DEFAULT TABLESPACE ' || default_tablespace || ' TEMPORARY TABLESPACE ' || temporary_tablespace || ' PROFILE ' || profile || ' ACCOUNT UNLOCK;' from dba_users where username in (&USERS);

select 'GRANT ' || granted_role || ' TO ' || grantee || decode(admin_option,'YES',' WITH ADMIN OPTION;',';') from dba_role_privs where grantee IN (&USERS);

select 'GRANT ' || privilege || ' ON ' || owner || '.' || table_name || ' TO ' || grantee || decode(grantable,'YES',' WITH GRANT OPTION;',';') from dba_tab_privs where grantee IN (&USERS);

select 'ALTER USER ' || username || ' QUOTA ' || decode(max_bytes,-1,'UNLIMITED ON ' || TABLESPACE_NAME || ';', max_bytes/1024 || 'K ON ' || TABLESPACE_NAME || ';') from dba_ts_quotas where username in (&USERS);