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.
- SP_EMPTY_INDEX.DBF = 1.57G
- PSINDEX.DBF = 1.46G
- PSIMAGE.DBF = 1.26G
- 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.
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;
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;
- Alter Index Reference
- For Loop
- Dynamic SQL
- Info on dropping tablespaces
- Renaming tablespaces
- Renaming data files
- Moving Tables