Analysis
Determining largest indexes by blocks
select index_name, leaf_blocks, num_rows
from dba_indexes
where owner = 'SYSADM'
and leaf_blocks is not null
order by leaf_blocks desc;
Determining indexes with the most free space
I found that the index with the most free space only had 32M free.
select a.owner, a.index_name, a.leaf_blocks, a.num_rows, b.tablespace_name,
b.blocks, b.blocks - a.leaf_blocks free_blocks,
(b.blocks - a.leaf_blocks) * to_number(p.value) / 1024 freespace_k
from dba_indexes a, dba_segments b, v$parameter p
where p.name = 'db_block_size'
and a.owner not in ('SYS', 'SYSTEM')
and a.index_type <> 'BITMAP'
and a.leaf_blocks is not null
and a.owner = b.owner
and a.index_name = b.segment_name
order by free_blocks desc;
Determining index space used by empty tables
One thing I noticed in the previous selection is that a number of indexes were on tables that had no rows, but the index had 128K allocated to them. I found that 21606 indexes matched this scenario totaling about 21G of unused space!
select count(*), sum(b.blocks), sum(b.blocks) * max(to_number(p.value)) / 1024 total_kilobytes
from dba_indexes a, dba_segments b, v$parameter p
where a.index_type <> 'BITMAP"
and a.owner = b.owner
and a.index_name = b.segment_name
and a.leaf_blocks = 0
and a.num_rows = 0
and b.blocks = 128;
Finding the PeopleSoft record name for the Oracle index name
In order to be able to put all of these changes into a PeopleSoft Project, I need to be able to tie the index to a PeopleSoft record. Here is an example join for dba_indexes and PSRECDEFN:
select o.index_name, o.table_name, r.recname
from dba_indexes o, psrecdefn r
where (r.sqltablename = o.table_name or (r.sqltablename = ' ' and o.table_anme = 'PS_' || r.recname))
and rownum < 5;
Creating a Project
I wanted to make PeopleSoft do most of the leg work, and so, my next step was to create a project in Application Designer. This way Application Designer will create all of the project header rows in the database (ex: PSPROJECTDEFN, etc.). I had to add the record for the first index just to be able to save the project. I did all this part manually in Application Designer.
I saved my project as SP_INDEX_REBUILD, but you can choose whatever name you would like. Just make sure that you change the name in the SQL below to the name that you choose.
You can use the first row in the project item table as a template for inserting the rest.
select * from psprojectitem
where projectname = 'SP_INDEX_REBUILD';
So, to insert the rest of the tables into the project, I used the statement in the database:
insert into psprojectitem
select distinct 'SP_INDEX_REBUILD', 0, 1, r.recname, 0, ' ', 0, ' ', 0, ' ', 0, 0, 0, 0, 1, 0
from dba_indexes a, dba_segments b, v$parameter p, psrecdefn r
where (r.sqltablename = a.table_name or (r.sqltablename = ' ' and a.table_name = 'PS_' || r.recname))
and p.name = 'db_block_size'
and a.index_type <> 'BITMAP'
and a.owner = b.owner
and a.index_name = b.segment_name
and a.leaf_blocks = 0
and a.num_rows = 0
and b.blocks = 128
and not exists (select 'x' from psprojectitem where projectname = 'SP_INDEX_REBUILD' and objectvalue1 = r.recname);
Warning: this statement did take some time to run. You may want to see if you can optimize it a little. It took about 20 minutes to complete.
Before I could see the new records attached to the project in Application Designer I had to clear the cache in Configuration Manager.
Inserting Rows for DDL
insert into psidxddlparm
select distinct recname, indexid, 2, 0, 'INIT', 0
from pskeydefn k, psprojectitem p
where k.recname = p.objectvalue1
and p.projectname = 'SP_INDEX_REBUILD'
and not exists (select 'x' from psidxddlparm i
where recname = i.recname
and indexid = i.indexid
and platformid = 2
and parmname = 'INIT');
Updating the rest of the rows
update psidxddlparm
set parmvalue = 0
where recname in (select objectvalue1
from psprojectitem
where objectvalue1 = recname);
Updating the Indexes
I cleared the cache to make sure that Application Designer recognized the new changes that I made in the database.
Then, just changing the indexes is not enough the make Application Designer realize that there is something that needs to be rebuilt. So, you have to change the build properties. On the Alter tab, make sure to “Alter even if no changes”. Then, build the project. I had to use the Alter tables options and not just the Create Indexes option to get it to build.
Added line to init.ora
db_2k_cache_size = 10M
If you don’t add this like, you will get the error message: “ORA-29339: tablespace block size 2048 does not match configure block sizes”.
Creating the new 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;
insert into psidxddlparm
select distinct recname, indexid, 2, 0, ‘INDEXSPC’, ‘sp_empty_index’
from pskeydefn k, psprojectitem p
where k.recname = p.objectvalue1
and p.projectname = ‘SP_INDEX_REBUILD’
and not exists (select ‘x’ from psidxddlparm i
where k.recname = i.recname
and k.indexid = i.indexid
and i.platformid = 2
and i.parmname = ‘INDEXSPC’);
update psidxddlparm
set parmvalue = ‘sp_empty_index’
where recname in (select objectvalue1
from psprojectitem
where objectvalue1 = recname)
and platformid = 2
and parmname = ‘INDEXSPC’;
select ‘alter index sysadm.’ || indexname || ‘ rebuild tablespace sp_empty_index;’
from (select distinct ‘PS’ || indexid || recname as indexname
from pskeydefn
where recname in (select objectvalue1
from psprojectitem
where objectvalue1 = recname)) a;
Reclaiming Space for the File System
I used the notes I made for resizing the database to get the free space to the file system.
PSINDEX was 47.89% free — 13.28G free.
Resources
DBAZine.com: Indexes – Part 2: Is This Index the Right Size?
recycle and keep buffers in other tablespace block sizes