HCM90: More Tablespace Sizing



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:


Share

1 Comment

  1. Joe says:

    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.

1 Trackbacks

Leave a comment