Oracle TableSpace Maintenance

Normally, I don’t get to worried about the size of TableSpaces, as they grow and shrink all the time. However, recently while setting up some automation scripts I was having problems with multiple databases being on the same server due to space issues.  So I decided it was time to take a review of the table spaces and see if there was some that I could easily do some quick maintenance to so that I could free up some space.

As always I found some guide examples out there using good old google.  The main item that I found that I could easily and quickly adjust was the undo tablespace.  Since  these scripts were for refreshing the environment and these environments are used by a very small subset of people, have a 32gb undo tablespace seemed a wee-bit excessive. So after the database clone is done I execute the following commands:

CREATE UNDO TABLESPACE undotbs2 DATAFILE ‘/u01/oracle/mynewdatabase/datafiles/undotbs02.dbf’ SIZE 2G AUTOEXTEND ON NEXT 250M;
ALTER SYSTEM SET UNDO_TABLESPACE=undotbs2;
DROP TABLESPACE undotbs1 INCLUDING CONTENTS AND DATAFILES;

After a stop and restart of the database 30 gigabytes of space are instantly reclaimed. So for 3 databases that is almost 100gb of dead space that is now free!

I also found a excellent bit of SQL for Oracle to show usage of the tablespace from Oracle-Base:

SELECT tablespace_name, size_mb, free_mb, max_size_mb, max_free_mb, TRUNC((max_free_mb/max_size_mb) * 100) AS free_pct,
RPAD(' '|| RPAD('X',ROUND((max_size_mb-max_free_mb)/max_size_mb*10,0), 'X'),11,'-') AS used_pct
FROM (SELECT a.tablespace_name, b.size_mb, a.free_mb, b.max_size_mb,a.free_mb + (b.max_size_mb - b.size_mb) AS max_free_mb
FROM (SELECT tablespace_name, TRUNC(SUM(bytes)/1024/1024) AS free_mb
FROM dba_free_space GROUP BY tablespace_name) a, (SELECT tablespace_name,
TRUNC(SUM(bytes)/1024/1024) AS size_mb,
TRUNC(SUM(GREATEST(bytes,maxbytes))/1024/1024) AS max_size_mb
FROM dba_data_files GROUP BY tablespace_name) b WHERE a.tablespace_name = b.tablespace_name ) ORDER BY tablespace_name;


For Temporary Tablespaces:

Maintenance on Temporary Tablespaces is a little easier than working with regular table spaces, you can use the following to easily shrink a temporary space:

ALTER TABLESPACE temp SHRINK SPACE KEEP 40M;

or:

ALTER TABLESPACE temp SHRINK TEMPFILE ‘/u01/oracle/mynewdatabase/datafiles/temp01.dbf’ KEEP 40M;

If you don’t specify a KEEP option it while shrink to the smallest possible size it can.

For Regular Tablespaces

I am simply going to refer to guide I was using from Oracle-Base.  There are many ways to do maintenance on these tablespaces, and since I was looking for quick and simple ideas, the temporary and undo tablespace shrink was by far the easiest to do, but it is always a good strategy to do tablespace maintenance.

Integration Broker – SFTP Target Connector

By default PeopleSoft delivers a series of connectors in the Integration Gateway to handle files in various ways. One of which is Secure File Transfer Protocol (SFTP).  While working on a new environment I ran into a testing error where the Integration Broker went to an error status after trying to invoke an SFTP Target.

It turns out that there is a Service Operation called:  INVOKE_SFTP.  This service operation is called to handle requests through the SFTP Target.  The error I was receiving was related to the OnRequestHandler, and sure enough the handle on the INVOKE_SFTP Service Operation was inactive.  After changing that to Active everything started working perfectly. It should also be noted that there needs to valid active routing on the Service Operation in order for it to process correctly.

Entity Relationship Diagrams

PeopleSoft Enterprise uses entity-relationship models in the design of the various applications. Entity relations diagrams (ERD’s) capture the relationships between, details of, and constraints imposed on the data defined in these models. ERD’s provide a visual representation of the database scheme to aid customers in the understanding and customization to the PeopleSoft Enterprise application.  These diagrams use to fairly difficult to get but now you can get them relatively easily through MOS Document Id: 1051533.1.

The new ERD’s actually are integrated with the application and after applying them to the environment and doing a couple security items you will find a new menu item: ‘Data Models’.  The older models have a structure that allows you to select various areas and then it will open up a PDF document that shows you the relationship.

It should be noted that not every relationship is available via the delivered ERDs, but for the most part it is an excellent starting point for people new to PeopleSoft to get orientated to the PeopleSoft Application Data Structure.

A quick shot out to my friend Duncan at PeopleSoft Tipster for his post years ago on the same topic: PeopleSoft Entity Relationship Diagrams (ERDs)

ERDs are available for:

HCM:   9.2 Note: 1559851.1, 9.1 Note: 968850.1, 9.0 Note: 979328.1, 8.9 Note: 981781.1, 8.8 Note: 989720.1

ELM:    9.2 Note: 1566244.1, 9.1 Note: 989317.1, 9.0 Note: 989318.1, 8.8 Note: 1050817.1

CS:       9.0/8.9 Note: 1053084.1

CRM:   9.2 Note: 2072285.1, 9.1 Note: 986733.1, 9.0 Note: 978853.1, 8.9 Note: 981780.1

FMS:    9.2 Note: 1547382.1, 9.1 Note: 1074856.1, 9.0 Note: 961655.1, 8.9 Note: 979359.1, 8.8 Note: 989673.1

SCM/SRM:   9.2 Note: 1547384.1, 9.1  Note: 1074949.1, 9.0 Note: 975752.1, 8.9 Note: 981711.1, 8.8 Note: 989690.1

EPM:   9.1/9.0/8.9 Note: 985535.1

Portal:  9.1 Note:1061022.1, 8.9 Note: 988728.1

PeopleSoft Object Owner: (OBJECTOWNERID)

Many years ago PeopleSoft introduced the object owner id which helps identify pretty much every PeopleSoft object (records, pages, content references, etc….) into where that object belongs.

This past week while working on a security matrix, the matrix needed to be broken down by module.  While with a little SQL magic and a little help from Excel and its filters the task was made significantly easier. In order to get a listing of the object owner ids within the system you can use look them up in the PSXLATITEM table.

SELECT * FROM PSXLATITEM WHERE FIELDNAME = ‘OBJECTOWNERID’ ORDER BY FIELDVALUE

If you want a specific application, Finance Objects have objectownerid starting with “F”, Human Resources “H”, Supply Chain “D”, Campus Solutions “S”, PeopleTools = “PPT”.  I also found some modules had multiple objectownerid values.

This handy bit of SQL courtesy of my friend Issam came in really handy, I honestly had never worked with the “WITH” function.  This code is specific to SQL Server 2014, but with a tweak or two it will work in Oracle 12 without an issue.  I am sure it will run in earlier versions, but as I have limited experience with the “WITH” function, it was only tested with MSSQL 2014 and Oracle 12c.

WITH PR (PORTAL_NAME, PORTAL_PRODUCT, PORTAL_SEQ_NUM, OBJECTOWNERID, PORTAL_OBJNAME, PORTAL_LABEL, PORTAL_REFTYPE, PORTAL_URI_SEG1, PORTAL_URI_SEG2, MYPATH) AS (
SELECT P.PORTAL_NAME, P.PORTAL_PRODUCT, P.PORTAL_SEQ_NUM, P.OBJECTOWNERID, P.PORTAL_OBJNAME, P.PORTAL_LABEL, P.PORTAL_REFTYPE, PORTAL_URI_SEG1, PORTAL_URI_SEG2, cast(P.PORTAL_LABEL as varchar(4000)) AS MYPATH FROM PSPRSMDEFN P
WHERE P.PORTAL_LABEL = ‘Root’
AND P.PORTAL_NAME = ‘EMPLOYEE’
UNION ALL
SELECT P_ONE.PORTAL_NAME, P_ONE.PORTAL_PRODUCT, P_ONE.PORTAL_SEQ_NUM, P_ONE.OBJECTOWNERID, P_ONE.PORTAL_OBJNAME, P_ONE.PORTAL_LABEL, P_ONE.PORTAL_REFTYPE, P_ONE.PORTAL_URI_SEG1, P_ONE.PORTAL_URI_SEG2, cast( (MYPATH + ‘ –> ‘ + P_ONE.PORTAL_LABEL) as varchar(4000)) AS MYPATH FROM PR P INNER JOIN PSPRSMDEFN P_ONE ON P.PORTAL_NAME = P_ONE.PORTAL_NAME
AND P.PORTAL_REFTYPE = ‘F’
AND P.PORTAL_OBJNAME = P_ONE.PORTAL_PRNTOBJNAME WHERE P_ONE.PORTAL_LABEL <> ‘Root’ AND P_ONE.PORTAL_NAME = ‘EMPLOYEE’ )

SELECT PORTAL_NAME, PORTAL_PRODUCT, PORTAL_SEQ_NUM, OBJECTOWNERID, PORTAL_OBJNAME, PORTAL_LABEL, PORTAL_REFTYPE, PORTAL_URI_SEG1, PORTAL_URI_SEG2, MYPATH FROM PR;

GO

Okay, I have to say, a few things in this world drive me crazy, okay maybe a little more than a few.  Now I know this won’t apply to most people as they probably only have to ever VPN into one place, but for the folks in this world that VPN into multiple places, I often find that my Cisco Anywhere client tool often gets updated.  The problem I often find is that my default URL gets changed and the drop down never works, so I end up memorizing bizarre VPN urls for my various clients.

I just want to be able to have my most common client be first in the list and ideally have my other VPN urls in the drop down.  So I a fit of frustration I did a Google search yesterday and found how to change the default URL.  Look for the file preferences.xml which should be in your c:\users\{youruserid}\appdata\local\cisco\Cisco AnyConnect VPN Client\ folder, I had some issues finding this file as it might be in the roaming folder instead of local, and it might just be hidden.  In the file you can change the entry for:  DefaultHostName.

In order to get the drop down list to work properly, I found in my c:\programdata\cisco\Cisco AnyConnect Secure Mobility Client\Profile that there was an xml file that had my default profile in it.  In my trusty UltraEdit, I found the section for the ServerList and made a new HostEntry block for each of my VPN Servers.

<ServerList>
<HostEntry>
<HostName>{MyVPN-name-vpn1}</HostName>
<HostAddress>{MyVPN-address-vpn1}</HostAddress>
</HostEntry>
<HostEntry>
<HostName>{MyVPN-name-vpn2}</HostName>
<HostAddress>{MyVPN-address-vpn2}</HostAddress>
</HostEntry>
</ServerList>

Now my drop down box works.  Now I am a little bit happier. 🙂