Space Information In a Database

select nvl(b.tablespace_name,
             nvl(a.tablespace_name,'UNKOWN')) name,
       kbytes_alloc kbytes,
       kbytes_alloc-nvl(kbytes_free,0) used,
       nvl(kbytes_free,0) free,
       trunc(((kbytes_alloc-nvl(kbytes_free,0))/kbytes_alloc)*100,2) pct_used,
       nvl(largest,0) largest
from ( select sum(bytes)/1024 Kbytes_free,
              max(bytes)/1024 largest,
              tablespace_name
       from  sys.dba_free_space
       group by tablespace_name ) a,
     ( select sum(bytes)/1024 Kbytes_alloc,
              tablespace_name
       from sys.dba_data_files
       group by tablespace_name )b
where a.tablespace_name (+) = b.tablespace_name

Generate dummy data in Oracle table (Courtesy asktom.oracle.com)

create or replace procedure do_dummy_data( p_tname in varchar2,
p_records in number )
 authid current_user
 as
     l_insert long;
     l_rows   number default 0;
 begin

    execute immediate 'create table clone_' || p_tname ||
                      ' as select * from ' || p_tname ||
                      ' where 1=0';

     l_insert := 'insert into clone_' || p_tname ||
                 ' select ';

     for x in ( select data_type, data_length,
                 rpad( '9',data_precision,'9')/power(10,data_scale) maxval
                  from user_tab_columns
                 where table_name = 'CLONE_' || upper(p_tname)
                 order by column_id )
     loop
         if ( x.data_type in ('NUMBER', 'FLOAT' ))
         then
             l_insert := l_insert || 'dbms_random.value(1,' || x.maxval || '),';
         elsif ( x.data_type = 'DATE' )
         then
             l_insert := l_insert ||
                   'sysdate+dbms_random.value+dbms_random.value(1,1000),';
         else
             l_insert := l_insert || 'dbms_random.string(''A'',' ||x.data_length || '),';
         end if;
     end loop;
     l_insert := rtrim(l_insert,',') ||
                   ' from all_objects where rownum <= :n';

     loop
         execute immediate l_insert using p_records - l_rows;
         l_rows := l_rows + sql%rowcount;
         exit when ( l_rows >= p_records );
     end loop;
 end;
 /

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)