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
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 ;