Data Archive Candidates

You can use the following SQL in Oracle to identify the top 100 tables that may be good candidates for archiving. Replace <OWNER> with the owner of the PeopleSoft schema (e.g. SYSADM). The field NUM_ROWS tells you the number of rows in the table (from when it was last analysed). The query also provides the approximate size of the table in MB.

Note this query can take quite a while to run - give it at least 1-2 minutes. For a quicker version, remove the sub-query that returns the approximate size of each table.

select

 OWNER,

 TABLE_NAME,

 NUM_ROWS,

 TABLESPACE_NAME,

 STATUS,

 LOGGING,

 LAST_ANALYZED,

 (

 select ltrim(to_char((sum(BYTES)/(1024*1024)), '9G999D99'), ' ') || ' MB'

 from USER_EXTENTS

 where SEGMENT_TYPE = 'TABLE'

 and SEGMENT_NAME = ARCHIVE_CANDIDATES.TABLE_NAME

 ) APPROX_SIZE

from (

 select

 OWNER,

 TABLE_NAME,

 NUM_ROWS,

 TABLESPACE_NAME,

 STATUS,

 LOGGING, 

 LAST_ANALYZED

 from 

 ALL_TABLES A

 where 

 OWNER = '<OWNER>' 

 and TABLE_NAME like 'PS%'

 and NUM_ROWS is not null 

 order by NUM_ROWS desc

) ARCHIVE_CANDIDATES 

where

 ROWNUM <= 100

;