Data Archive Candidates
You can use the following SQL in Oracle to identify the top 100 tables that may be good candidates for archiving. 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 ;
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.
