Approximate Database Size

The following SQL is for an Oracle Database and it attempts to approximate the size of a PeopleSoft database using the user extents table. Basically, it looks at size of all tables and indexes (through table spaces) in the PeopleSoft database. I emphasize that this is just an approximate size and will be less than the true size.

Note, you will need to change the <OWNER> parameters to the owner of your PeopleSoft database (e.g. SYSADM). Each query takes a while to run, give them at least a few minutes.

This query gives you the total size of all tables:

select
    SUM(BYTES)/(1024*1024) TOTAL_SIZE_OF_TABLES_IN_MB
from 
    USER_EXTENTS UE
where 
    UE.SEGMENT_TYPE = 'TABLE'
    and UE.TABLESPACE_NAME in (
        select distinct TABLESPACE_NAME 
        from ALL_TABLES
        where OWNER = 'SYSADM'
        and TABLE_NAME like 'PS%'
    )
;

While this query gives you the total size of all indexes:

select
    SUM(BYTES)/(1024*1024) TOTAL_SIZE_OF_INDEXS_IN_MB
from 
    USER_EXTENTS UE
where 
    UE.SEGMENT_TYPE = 'INDEX'
    and UE.TABLESPACE_NAME in (
        select distinct TABLESPACE_NAME
        from ALL_INDEXES
        where TABLE_OWNER = 'SYSADM'
        and TABLE_NAME like 'PS%'
    )
;

Add the two sizes together to get your approximate database size.

Unless otherwise stated, the content of this page is licensed under Creative Commons Attribution-ShareAlike 3.0 License