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:

Advertisements

2 Responses to “HCM90: More Tablespace Sizing”

  1. Move a Tablespace « PSST0101 Says:

    […] 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 […]

  2. 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.


Leave a Reply

Fill in your details below or click an icon to log in:

WordPress.com Logo

You are commenting using your WordPress.com account. Log Out / Change )

Twitter picture

You are commenting using your Twitter account. Log Out / Change )

Facebook photo

You are commenting using your Facebook account. Log Out / Change )

Google+ photo

You are commenting using your Google+ account. Log Out / Change )

Connecting to %s

%d bloggers like this: