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:
1 2 3 4 5 6 7 8 9 10 11 12 13 14 15 | create tablespace sp_empty_indexdatafile ‘e:\oradata\HCM90\SP_EMPTY_INDEX.DBF’size 10mblocksize 2kextent management local autoallocatesegment 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.
1 2 3 4 5 6 7 8 9 10 11 12 13 | create tablespace PSINDEXNEWdatafile 'e:\oradata\HCM90\PSINDEXNEW.DBF'size 10mextent management local autoallocatesegment space management auto;ALTER DATABASE DATAFILE 'e:\oradata\HCM90\PSINDEXNEW.DBF'AUTOEXTEND ON NEXT 5M MAXSIZE UNLIMITED; |
What Indexes are in the tablespace?
1 2 3 | select * from dba_segmentswhere tablespace_name = 'SP_EMPTY_INDEX'; |
Moving the Indexes
1 2 3 4 5 6 7 8 9 10 11 12 13 14 15 16 17 18 19 20 21 | declarebegin 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
1 | 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.
1 | alter tablespace PSINDEXNEW rename to PSINDEX; |
To rename the datafile as well, you must do this:1. Take the tablespace offline
1 | alter tablespace PSINDEX offline; |
2. Rename the data file in the operating system3. Rename the data file in the database
1 | alter tablespace PSINDEX rename datafile 'E:\ORADATA\HCM90\PSINDEXNEW.DBF' to 'E:\ORADATA\HCM90\PSINDEX.DBF'; |
4 bring the tablespace back online
1 | alter tablespace PSINDEX online; |
Moving Tables
1 2 3 4 5 6 7 8 9 10 11 12 13 14 15 16 17 18 19 20 21 | declarebegin 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:
1 2 3 4 5 6 7 8 9 10 11 12 13 14 15 16 17 18 19 20 21 | declarebegin 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.