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.

Query to determine type of lock

Create below function

create or replace function enqueue_decode( l_p1 in number ) return varchar2
as
l_str varchar2(25);
begin
select chr(bitand(l_p1,-16777216)/16777215)||
chr(bitand(l_p1, 16711680)/65535) || ' ' ||
decode( bitand(l_p1, 65535),
          0, 'No lock',
          1, 'No lock',
          2, 'Row-Share',
          3, 'Row-Exclusive',
          4, 'Share',
          5, 'Share Row-Excl',
          6, 'Exclusive' )
 into l_str
 from dual;
 return l_str;
end;

Then pass P1 value for a wait event to determine type of lock

select  enqueue_decode(P1) from dual;

Table Sizing Information

1) Get an estimate of the number of records for the table, both now, and
   projected to 6 months from now
2) Find the average length of each record. If you cannot do this
   perfectly, then try to make a good guess.
3) Determine how often the table is updated/inserted/deleted. If it is
   changed often (and the record sizes vary), then the PCTUSED would be
   low, something like 40-60. If the table is static, then I'd make the
   table PCTUSED 85 or 90.
4) Determine my database's block size:
   select value from v$parameter where name = 'db_block_size';

Now I have all of the information needed to size my table:

Records per block = (block size - 110 bytes for overhead) * (PCTUSED/100)/
                    Average Record Size

Total blocks = (records in table) / (records per block)
Total table size = blocks * block size
   
Give a little fudge factor and make this your INITIAL extent size.
Your NEXT will be determined by your 6 month growth projection.

Oracle Query to produce UNIX cal command like calendar

break on month skip 1
set linesize 20
column month 20
select lpad( Month, 20-(20-length(month))/2 ) month,
       "Su", "Mo", "Tu", "We", "Th", "Fr", "Sa"
  from (
select to_char(dt,'fmMonthfm YYYY') month,
       to_char(dt+1,'iw') week,
       max(decode(to_char(dt,'d'),'1',lpad(to_char(dt,'fmdd'),2))) "Su",
       max(decode(to_char(dt,'d'),'2',lpad(to_char(dt,'fmdd'),2))) "Mo",
       max(decode(to_char(dt,'d'),'3',lpad(to_char(dt,'fmdd'),2))) "Tu",
       max(decode(to_char(dt,'d'),'4',lpad(to_char(dt,'fmdd'),2))) "We",
       max(decode(to_char(dt,'d'),'5',lpad(to_char(dt,'fmdd'),2))) "Th",
       max(decode(to_char(dt,'d'),'6',lpad(to_char(dt,'fmdd'),2))) "Fr",
       max(decode(to_char(dt,'d'),'7',lpad(to_char(dt,'fmdd'),2))) "Sa"
  from ( select trunc(sysdate,'y')-1+rownum dt
           from all_objects
          where rownum <= add_months(trunc(sysdate,'y'),12) - trunc(sysdate,'y') )
 group by to_char(dt,'fmMonthfm YYYY'), to_char( dt+1, 'iw' )
       )
 order by to_date( month, 'Month YYYY' ), to_number(week)