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:
PSST0101 
January 23, 2008 at 12:04 am
[…] maybe some of the SQL may help someone. It is an attempt at boiling all the steps from another post into one SQL script. The rename at the end didn’t seem to work for some reason, and I […]
January 23, 2008 at 5:35 pm
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.