Bhavik Fuletra :ORACLE CERTIFIED EXPERT

Hi Friends,

I really apologies for not posting any post for a long time as I was bit busy with Office Work and also was preparing for Oracle 10g R2 RAC Administration Exam.

I would like to share my happiness with all of you as I have completed Oracle RAC Exam and had received the certificate of Oracle Certified Expert.

Thanks,

Bhavik Fuletra

Adding Column to Composite Primary Key

Below is the solution of Adding a column to a composite primary key once it had lacs of records.

1. Create a Table "TEST" with Composite Primary Key on Col1, Col2 and Col3.

2. Add some thousands of records to the table.

3. Alter the table and add the new column to the table as NOT NULL with some default value.

4. Alter the table and drop the composite Primary Key constraint.

5. Alter the table and add a composite primary key constraint with (Col1, Col2, Col3 and NEW_Colxxx)

Hope it will help to you. Please drop an email to me bhavik.fuletra@gmail.com if you have any other queries with regards to Composite Primary Key issue.

Thanks and pls dont forget to provide your comments.

Script: Free Space Report for all Datafiles and if AUTOEXTENT is ON

-- ################################################################

--

-- %Purpose: Free Space Report for all Datafiles and if AUTOEXTENT is ON

--

--################################################################

--

clear columns -

      breaks -

      computes

set pagesize 100

column file_name format a32

column tablespace_name format a15

column status format a3 trunc

column t format 999,999.000 heading "Total MB"

column a format a4 heading "Aext"

column p format 990.00 heading "% Free"

SELECT  df.file_name,

        df.tablespace_name,

        df. status,

        (df.bytes/1024000) t,

        (fs.s/df.bytes*100) p,

        decode (ae.y,1,'YES','NO') a

  FROM  dba_data_files df,

        (SELECT file_id,SUM(bytes) s

           FROM dba_free_space

           GROUP BY file_id) fs,

        (SELECT file#, 1 y

           FROM sys.filext$

           GROUP BY file#) ae

  WHERE df.file_id = fs.file_id

    AND ae.file#(+) = df.file_id

ORDER BY df.tablespace_name, df.file_id;

column file_name clear

column tablespace_name clear

column status clear

column t clear

column a clear

column p clear

ttitle off

Script: DB Access Monitoring Report

-- ################################################################

--

-- %Purpose: DB Access Monitoring Report 

--           (Full Table and Index Scans, Chained Rows)

--

-- Use:      Needs Oracle DB Access

--

-- ################################################################

--

set feed off;

set pagesize 10000;

set wrap off;

set linesize 200;

set heading on;

set tab on;

set scan on;

set verify off;

set termout on;

column  STATISTIC#  form 999        head 'Id'

column  NA          form a32        head 'Statistic'

column  RIA         form 990.90     head 'Row Access via|Index [%]'

column  RTS         form 990.90     head 'Row Access via|Table Scan [%]'

column  RA          form 9999999990 head 'Rows accessed'

column  PCR         form 990.90     head 'Chained|Rows [%]'

colum   CL          form 990.90     head 'Cluster|Length'

ttitle  left  'DB Access Monitoring Report'  skip 2

spool   db_access.log

select  rpad (NAME, 32, '.') as NA,

        VALUE

  from  V$SYSSTAT

 where  NAME like '%table scan%'

    or  NAME like '%table fetch%'

    or  NAME like '%cluster%';

ttitle  off

select  A.VALUE + B.VALUE                     as RA,

        A.VALUE / (A.VALUE + B.VALUE) * 100.0 as RIA,

        B.VALUE / (A.VALUE + B.VALUE) * 100.0 as RTS,

        C.VALUE / (A.VALUE + B.VALUE) * 100.0 as PCR,

        E.VALUE / D.VALUE                     as CL

  from  V$SYSSTAT A,

        V$SYSSTAT B,

        V$SYSSTAT C,

        V$SYSSTAT D,

        V$SYSSTAT E

 where  A.NAME = 'table fetch by rowid'

   and  B.NAME = 'table scan rows gotten'

   and  C.NAME = 'table fetch continued row'

   and  D.NAME = 'cluster key scans'

   and  E.NAME = 'cluster key scan block gets'

/

Script: ANALYZE TABLE with Estimate or Compute Statistic, depending on table size

###############################################################

--

-- %Purpose: This script will help us in ANALYZE table

--                 with estimate or compute statistic, 

--                 depending on table size, see SIGN(n)

--

-- Use:    Any table less than 10 MB in total size has statistics computed

--           while tables larger than 10 MB have statistics estimated.

--           SIGN(n) ==> if n <>

--                       if n = 0 the functions returns 0

--                       if n > 0 the functions returns 1

--

-- #############################################################

--

set feed off;

set pagesize 10000;

set wrap off;

set linesize 200;

set heading on;

set tab on;

set scan on;

set verify off;

spool compute_or_estimate_stat.sql

--

SELECT 'ANALYZE TABLE '||owner||'.'||table_name||' '||

       DECODE(SIGN(10485760 - initial_extent),1,'COMPUTE STATISTICS;',

                                    'ESTIMATE STATISTICS;')

FROM   sys.dba_tables

WHERE  owner NOT IN ('SYS','SYSTEM');

/

--

spool off;

set feed on;

@compute_or_estimate_stat.sql