Query to display redundant indexes on a table

Below query will display redundant indexes on a table

select  *
from
( select index_name,
         max(decode(p, 1,     c,NULL)) ||
         max(decode(p, 2,', '||c,NULL)) ||
         max(decode(p, 3,', '||c,NULL)) ||
         max(decode(p, 4,', '||c,NULL)) ||
         max(decode(p, 5,', '||c,NULL)) ||
         max(decode(p, 6,', '||c,NULL)) ||
         max(decode(p, 7,', '||c,NULL)) ||
         max(decode(p, 8,', '||c,NULL)) ||
         max(decode(p, 9,', '||c,NULL)) ||
         max(decode(p,10,', '||c,NULL)) ||
         max(decode(p,11,', '||c,NULL)) ||
         max(decode(p,12,', '||c,NULL)) ||
         max(decode(p,13,', '||c,NULL)) ||
         max(decode(p,14,', '||c,NULL)) ||
         max(decode(p,15,', '||c,NULL)) ||
         max(decode(p,16,', '||c,NULL)) index_cols
    from (select index_name, substr(column_name,1,30) c, column_position p
               from user_ind_columns )
   group by index_name  ) A,
( select index_name,
         max(decode(p, 1,     c,NULL)) ||
         max(decode(p, 2,', '||c,NULL)) ||
         max(decode(p, 3,', '||c,NULL)) ||
         max(decode(p, 4,', '||c,NULL)) ||
         max(decode(p, 5,', '||c,NULL)) ||
         max(decode(p, 6,', '||c,NULL)) ||
         max(decode(p, 7,', '||c,NULL)) ||
         max(decode(p, 8,', '||c,NULL)) ||
         max(decode(p, 9,', '||c,NULL)) ||
         max(decode(p,10,', '||c,NULL)) ||
         max(decode(p,11,', '||c,NULL)) ||
         max(decode(p,12,', '||c,NULL)) ||
         max(decode(p,13,', '||c,NULL)) ||
         max(decode(p,14,', '||c,NULL)) ||
         max(decode(p,15,', '||c,NULL)) ||
         max(decode(p,16,', '||c,NULL)) index_cols
    from (select index_name, substr(column_name,1,30) c, column_position p
               from user_ind_columns )
   group by index_name  ) B
where a.index_name <> b.index_name
  and a.index_cols like b.index_cols || '%'

Table Sizing

Try to size the tables 1st...

number_of_rows/(((100-pctfree)*(blocksize of database-90))
/(100*(avg_row_length + 3 + number_of_columns
+ number_of_cols_over_250_bytes))) = Blocks_Required

You will have to estimate the average row size for each table
(Assumptions here ... I usually guess 75% fill for varchar columns...
Add all table sizes together and multiply by 2 or 3 times ... (allow for
growth) ... you will have to understand how the data will grow in order
to make a "rough guess" at the factor to multiply by ...

If you know what you will need for indexes ... size each index

select number_of_rows/(((100-pctfree)*(blocksize of database-161))
/(100*(avg_length_of_indexed_cols+8+number_of_index ed_cols
+ num_of_ind_cols_over_127_bytes))) = Blocks_Required
Again ... allow for growth by multipling by a factor...

Rollback segments ... you should always assume large rollbacks ...
you need to know how many users, type of database activity etc ...
I would assume 4 users / rollback seg ... with each segment 5-100mg
depending on the type of db activity ...

Temporary tablespace - I always start with 500mg tablespace here
unless I know the application will require a "LOT" of sorting and grouping.

System tablespace - I always use a MIN of 250mg.