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.

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