HCM9.0: Resizing the Database

The database of my PeopleSoft HRMS 9.0 VMWare Image was larger than I expected. I had to get the database down to a manageable size so that we can easily make backup copies of the image. Here is what I did:

• Starting Size: 63.6 Gb
• Finishing Size: 39.8 Gb

Query to Determine Where to Free Up Space

SELECT a.tablespace_name,
total_free_space,
ROUND(total_free_space / 1042, 2) free_k,
ROUND(total_free_space / 1042 / 1042, 2) free_m,
ROUND(total_free_space / 1042 / 1042 / 1042, 2) free_g,
largest_free_extent,
total_size,
ROUND(total_free_space / total_size *100, 2) percent_free
FROM
(SELECT tablespace_name,
SUM(bytes) total_free_space,
MAX(bytes) largest_free_extent
FROM dba_free_space
GROUP BY tablespace_name)
a,
(SELECT tablespace_name,
SUM(bytes) total_size
FROM dba_data_files
GROUP BY tablespace_name)
b
WHERE a.tablespace_name = b.tablespace_name
ORDER BY 2 DESC;

Query to Determine Files for the Tablespace

SELECT file_name,
file_id,
tablespace_name,
bytes,
ROUND(bytes / 1042 / 1042, 2) mb,
maxbytes,
ROUND(maxbytes / 1042 / 1042, 2) maxmb,
(SELECT ROUND(SUM(bytes) / 1024 / 1024, 2) mb
FROM dba_segments
WHERE tablespace_name = a.tablespace_name
AND header_file = a.file_id)
used_mb,
(SELECT ROUND(((header_block + blocks) *8192) / 1024 / 1024, 2) end_mb
FROM dba_segments
WHERE tablespace_name = a.tablespace_name
AND header_block =
(SELECT MAX(header_block)
FROM dba_segments
WHERE tablespace_name = a.tablespace_name)
)
lastused_position
FROM dba_data_files a
WHERE tablespace_name = ‘PSDEFAULT’;

How I Resized My Tablespaces

ALTER DATABASE DATAFILE ‘E:\ORADATA\HCM90\PSINDEX2.DBF’
RESIZE 6266M;

ALTER DATABASE DATAFILE ‘E:\ORADATA\HCM90\SYSTEM01.DBF’
RESIZE 600M;

ALTER DATABASE DATAFILE ‘E:\ORADATA\HCM90\PSIMAGE.DBF’
RESIZE 1300M;

ALTER DATABASE DATAFILE ‘E:\ORADATA\HCM90\TLWORK.DBF’
RESIZE 477M;

ALTER DATABASE DATAFILE ‘E:\ORADATA\HCM90\TLAPP.DBF’
RESIZE 80M;

ALTER DATABASE DATAFILE ‘E:\ORADATA\HCM90\GPAPP.DBF’
RESIZE 890M;

ALTER DATABASE DATAFILE ‘E:\ORADATA\HCM90\PSUNDOTS01.DBF’
RESIZE 150M;

ALTER DATABASE DATAFILE ‘E:\ORADATA\HCM90\HRLARGE.DBF’
RESIZE 650M;

ALTER DATABASE DATAFILE ‘E:\ORADATA\HCM90\PTTLRG.DBF’
RESIZE 300M;

ALTER DATABASE DATAFILE ‘E:\ORADATA\HCM90\HRAPP.DBF’
RESIZE 375M;

ALTER DATABASE DATAFILE ‘E:\ORADATA\HCM90\SAAPP.DBF’
RESIZE 325M;

ALTER DATABASE DATAFILE ‘E:\ORADATA\HCM90\CCAPP.DBF’
RESIZE 290M;

ALTER DATABASE DATAFILE ‘E:\ORADATA\HCM90\PY0LRG.DBF’
RESIZE 280M;

ALTER DATABASE DATAFILE ‘E:\ORADATA\HCM90\PTTBL.DBF’
RESIZE 250M;

ALTER DATABASE DATAFILE ‘E:\ORADATA\HCM90\FAAPP.DBF’
RESIZE 225M;

ALTER DATABASE DATAFILE ‘E:\ORADATA\HCM90\TLLARGE.DBF’
RESIZE 210M;

ALTER DATABASE DATAFILE ‘E:\ORADATA\HCM90\AAAPP.DBF’
RESIZE 170M;

ALTER DATABASE DATAFILE ‘E:\ORADATA\HCM90\PSDEFAULT.DBF’
RESIZE 5M;

Resources

Tablespace Information

Alter Tablespace Syntax

Oracle Data & Temp Files