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.
No Comments