Find Size of PeopleSoft Tables and indexes [SQL]

What is the biggest pain for PeopleSoft Admins/DBA or any PeopleSoft consultant supporting the application? That's when performance of PeopleSoft application is really sluggish. There are hundreds of things that could go wrong and one of those is directly related to size of your PeopleSoft database. 

PeopleSoft database consists of PeopleSoft Tables, indexes and few other objects but primarily the size is determined by tables and indexes. 

For Oracle database, DBA could utilize OEM (Oracle Enterprise Manager) to keep a check on your database size and you could setup triggers or scripts to alert when it reaches a threshold. 

PeopleSoft Tables are the PeopleSoft record definitions that are built and have database definition. PeopleSoft Indexes are created for these PeopleSoft tables. 

Below are couple of SQLs that can be used together to estimate the size of PeopleSoft database (size of PeopleSoft tables & size of PeopleSoft indexes)

Output of below SQLs will show the size in MB and GB (approx).

-- Find PeopleSoft Table size
SELECT DS.TABLESPACE_NAME,
SEGMENT_NAME,
ROUND (SUM (DS.BYTES) / (1024 * 1024)) AS MB,
ROUND (SUM (DS.BYTES) / (1024 * 1024 * 1024)) AS APPROX_IN_GB
FROM DBA_SEGMENTS DS
WHERE SEGMENT_NAME IN (SELECT TABLE_NAME
FROM DBA_TABLES
WHERE OWNER = 'SYSADM'
AND TABLE_NAME LIKE 'PS_%'
AND NUM_ROWS > 0)
AND SEGMENT_TYPE = 'TABLE'
AND OWNER = 'SYSADM'
GROUP BY DS.TABLESPACE_NAME, SEGMENT_NAME
-- Find PeopleSoft Index size
SELECT DS.TABLESPACE_NAME,
SEGMENT_NAME,
ROUND (SUM (DS.BYTES) / (1024 * 1024)) AS MB,
ROUND (SUM (DS.BYTES) / (1024 * 1024 * 1024)) AS APPROX_IN_GB
FROM DBA_SEGMENTS DS
WHERE SEGMENT_NAME IN (SELECT TABLE_NAME
FROM DBA_TABLES
WHERE OWNER = 'SYSADM'
AND TABLE_NAME LIKE 'PS_%'
AND NUM_ROWS > 0)
AND SEGMENT_TYPE = 'INDEX'
AND OWNER = 'SYSADM'
GROUP BY DS.TABLESPACE_NAME, SEGMENT_NAME
ORDER BY ROUND (SUM (DS.BYTES) / (1024 * 1024 * 1024)) DESC;

If you like this SQL, please let us know in the comment section. Don't forget to share it others too. 

Related Posts you might like

SQL Tip: Find PS Query set on recurrence
When you schedule a PeopleSoft query, you can see the scheduled process is of type PSQUERY but process scheduler doesn't[...]
SQL Tip : Find users logged into PeopleSoft PIA with this SQL
You can find the users logged into your PeopleSoft application in multiple ways as this activity is recorded at various[...]
Find Size of PeopleSoft Tables and indexes [SQL]
What is the biggest pain for PeopleSoft Admins/DBA or any PeopleSoft consultant supporting the application? That's when performance of PeopleSoft application[...]
SQL to find when PeopleSoft table was built and analyzed in Oracle
Ever wondered when a PeopleSoft table was last built / altered or when its database stats were gathered ? Many[...]
SQL to find PeopleSoft portal objects hidden from navigation
You can hide PeopleSoft portal objects (e.g. content reference) from PeopleSoft Navigation. This can be done by navigating to 'Structure[...]
How to monitor connections to PeopleSoft Database
Ever wondered how many users are connected to PeopleSoft database? This is different than users who login into PeopleSoft application[...]
Prashant
 

Click Here to Leave a Comment Below 0 comments