There is a fix out to reduce the database size:
- Financials: 704935
- HRMS: 704332
- Portal: 704763
The only difference between the changes in the fix and what I had previously done, is the blocksize setting. As far as I could tell, it really didn’t make a sizeable impact. What did make a difference was changing the properties on the other tablespaces besides the index tablespace.
Below I have listed some SQL and such that I used to shrink the database and move segments from the tablespaces.
Starting Sizes:
- SP_EMPTY_INDEX.DBF = 1.57G
- PSINDEX.DBF = 1.46G
- PSIMAGE.DBF = 1.26G
New Sizes:
- PSINDEXNEW.DBF = 1.50G (with all indexes from empty index tablespace)
- PSINDEXNEW.DBF = 2.97G (including indexes from PSINDEX)
- PSIMAGE.DBF = 750M
Original SQL to create the tablespace:
create tablespace sp_empty_index datafile ‘e:\oradata\HCM90\SP_EMPTY_INDEX.DBF’ size 10m blocksize 2k extent management local autoallocate segment space management auto; ALTER DATABASE DATAFILE ‘e:\oradata\HCM90\SP_EMPTY_INDEX.DBF’ AUTOEXTEND ON NEXT 5M MAXSIZE UNLIMITED;
New Tablespace Creation:The only difference here was the blocksize SQL.
create tablespace PSINDEXNEW datafile 'e:\oradata\HCM90\PSINDEXNEW.DBF' size 10m extent management local autoallocate segment space management auto; ALTER DATABASE DATAFILE 'e:\oradata\HCM90\PSINDEXNEW.DBF' AUTOEXTEND ON NEXT 5M MAXSIZE UNLIMITED;
What Indexes are in the tablespace?
select * from dba_segments where tablespace_name = 'SP_EMPTY_INDEX';
Moving the Indexes
declare
begin
for index_rec in (select owner, segment_name
from dba_segments
where segment_type = 'INDEX'
and tablespace_name = 'SP_EMPTY_INDEX')
loop
execute immediate 'alter index ' || index_rec.owner || '.' ||
index_rec.segment_name || ' rebuild tablespace PSINDEXNEW';
end loop;
end;
Dropping the Tablespace
drop tablespace SP_EMPTY_INDEX including contents;
Then, I had to stop the database and restart it to get it to release the lock on the datafile. Once I did this, I could delete the datafile from the system.PSINDEX tablespace
I did the same thing to the PSINDEX. Moved all of the indexes out of it into the new PSINDEXNEW tablespace. At this point, I could remove that tablespace as well.
Renaming Tablespaces:
Next, I wanted to keep the same tablespace name, and so, I renamed the tablespace back to the original. From what I understand, this requires Oracle 10g.
alter tablespace PSINDEXNEW rename to PSINDEX;
To rename the datafile as well, you must do this:1. Take the tablespace offline
alter tablespace PSINDEX offline;
2. Rename the data file in the operating system3. Rename the data file in the database
alter tablespace PSINDEX rename datafile 'E:\ORADATA\HCM90\PSINDEXNEW.DBF' to 'E:\ORADATA\HCM90\PSINDEX.DBF';
4 bring the tablespace back online
alter tablespace PSINDEX online;
Moving Tables
declare
begin
for index_rec in (select owner, segment_name
from dba_segments
where segment_type = 'TABLE'
and tablespace_name = 'PSIMAGE')
loop
execute immediate 'alter table ' || index_rec.owner || '.' ||
index_rec.segment_name || ' move tablespace PSIMAGENEW';
end loop;
end;
Moving LOBsThe PSIMAGE tablespace has two segment types: LOBINDEX and LOBSEGMENT that can be taken care of with this SQL:
declare
begin
for index_rec in (select owner, table_name, column_name
from dba_lobs
where tablespace_name = 'PSIMAGE')
loop
execute immediate 'alter table ' || index_rec.owner || '.' ||
index_rec.table_name || ' move lob(' || index_rec.column_name ||
') store as (tablespace PSIMAGENEW)';
end loop;
end;
Resources:
I do something similar to reclaim space in our development environments, but just with indexes inplace and online. We have about 14GB worth of free space (select sum(bytes)/1024/1024 from dba_free_space), and I am able to reclaim half of it after a refresh. I’ll have try rebuilding the tables and LOBs and see if it’s worth the downtime.