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