Tuesday, October 13, 2009

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 ingress.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.

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.

Sunday, March 8, 2009

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

Monday, January 5, 2009

Oracle FAQ Registration News

Hi Friends,

This is to inform you that recently we had registered our blog at Orafaq.com website which is known as Oralce FAQ.

- http://www.orafaq.com/aggregator/sources/429
-
http://www.orafaq.com/wiki/Blogs

Thanks,
IngressIT

Saturday, January 3, 2009

How to trace archive log



As mentioned in the below table Tracing the archive log file is done at system level.



Oracle Database Version
Data Type
Default Value
Alter Session
Alter System





9.0.1
Integer
0
No
Immediate
9.2.0
Integer
0
No
Immediate
10.1.0
Integer
0
No
Immediate
10.2.0
Integer
0
No
Immediate
We can generate the trace file with different level. 

Below table will provide you the description of different level.



Trace Level
Description
0
Disabled (default)
1
Track archival of redo log file
2
Track status of each archivelog destination
4
Track archival operational phase
8
Track archivelog destination activity
16
Track detailed archivelog destination activity
32
Track archivelog destination parameter changes
64
Track ARCn process state activity
128
Track FAL server related activities
256
Track RFS Logical Client
512
Tracks LGWR redo shipping network activity
1024
Tracks RFS physical client
2048
Tracks RFS/ARCn ping heartbeat
4096
Tracks real-time apply activity
8192
Tracks Redo Apply activity (media recovery or physical standby)


I would like to request all the reader's to comment on the post and give me suggestion if you find so...



Thanks,

IngressIT


Friday, January 2, 2009

New Poll is Added to the Blog

Hi Friends,

New Poll is added to my blog http://imergegroup.blogspot.com on the New Year. I would like to request you to vote on it.

Thanks,
IngressIT

Happy New Year...

Happy new year to you and your family.

Please note 1 extra second is added to year 2008. So please adjust your watches / Desktop (other gadgets) timings today. (Earth revolving speed reduced may be because of recession - that means recession started showing its IMPACT on Solar system also !!!!) This may be good indication for all of us. Nature is giving us 1 extra second to plan/push ourselves and make our dream come true in the year 2009.

Thanks,
IngressIT

Flushing Shared Pool and Buffer Cache

Flushing SGA memory areas, Shared Pool and Buffer Cache are the uncommon task that comes to a DBA. Yesterday night when I was going through Oracle Documentation I found the command for Flushing the shared pool. And on the next moment I thought for Buffer cache.

Shared Pool flush
For Oracle 9i and 10g (Need to test it on 11g)
Alter system flush shared_pool

Buffer Cache flush
For Oracle 9i
Alter session set events = 'immediate trace name flush_cache';

Here I found the statement and tried it on my test database and it worked fine.

For Oracle 10g, the syntax is is as follows:
alter system flush buffer_cache;

I would like to thanks you all for reading and posting comments to my post.

Enhancement to DBCA in Oracle 11g

1) Support to configure new database options Starting from Oracle Database 11g; we can configure the following options using Database Configuration Assistant:

a) Oracle Application Express

b) Oracle Database Vault

c) Oracle Warehouse Builder.

These options can be selected while creating database using DBCA. As these options was not available in Oracle Database 10g.



2) Automatic memory management option ( Memory_Target) In Oracle Database 11g, Oracle Database can manage the SGA memory and instance PGA memory completely automatically. You mention only the total memory size to be used by the instance and Oracle Database dynamically exchanges memory between the SGA and the instance PGA as needed to meet processing demands. To do so, you set only a target memory size initialization parameter (MEMORY_TARGET). Typical is the default and it uses MEMORY_TARGET instead of specifying individual values for SGA_TARGET and PGA_AGGREGATE_TARGET. With Custom, you can select PGA and SGA individually.



3) Oracle Data Mining option is made default In Oracle Database 11g, you no longer configure this option through the Database Features screen (Database components) of Database Configuration Assistant. The data mining schema are created when you run the catproc.sql script as the SYS user. While in Oracle 10g, while creating a Custom database using DBCA we need to select the Oracle Data Mining option in the Database components section In Oracle 11g, we need not configure the Data mining option and the data mining schema are created when catproc.sql is executed.



4) ORACLE_BASE and Diagnostic Destination configuration The directory that you specify when you are prompted for ORACLE_BASE by Oracle Universal Installer is stored in the Oracle home inventory. Database Configuration Assistant uses this value to derive the default database locations and the DIAGNOSTIC_DEST parameter. The diagnostic destination location contains all ADR directories (diagnostic files such as Alert logs and so on). Starting with Oracle Database 11g Release 1 (11.1), the initialization parameter settings for background dump, user dump, and core dump destinations are replaced by the Diagnostic Destination. The DIAGNOSTIC_DEST initialization parameter specifies the directory where diagnostics for an instance are located. The structure of the directory specified by DIAGNOSTIC_DEST is as follows: /diag/rdbms//
This location is known as the Automatic Diagnostic Repository (ADR) Home.




5) Secure database configuration Oracle Database 11g has new defaults for audit and password profiles. Database Configuration Assistant has a new screen to enable the new security settings during the database creation and existing database configuration.

During Database creation, dbca gives the following 2 options:

- Keep the enhanced 11g default security settings(recommended)

- Revert to pre-11g default security settings Under second option( pre-11g settings), there are 2 categories,

-Revert Audit settings to pre-11g defaults

-Revert password profile settings to pre-11g defaults



You can choose any or both of these 2.



Audit settings:



Oracle recommends that you enable these default security settings. When you enable the default security settings, Oracle Database audits some of the security-relevant SQL statements and privileges. It also sets the AUDIT_TRAIL initialization parameter to DB. Oracle Database audits the AUDIT ROLE SQL statement by default. The privileges that are audited by default are as follows:



ALTER ANY PROCEDURE/TABLE

ALTER DATABASE/PROFILE/SYSTEM/USER

AUDIT ROLE BY ACCESS AUDIT SYSTEM

AUDIT SYSTEM BY ACCESS

CREATE SESSION/USER

CREATE ANY JOB/LIBRARY/PROCEDURE

CREATE ANY TABLE EXEMPT ACCESS POLICY

CREATE EXTERNAL JOB

CREATE PUBLIC DATABASE LINK

DROP PROFILE/USER

DROP ANY TABLE/PROCEDURE

GRANT ANY PRIVILEGE/ROLE

GRANT ANY OBJECT PRIVILEGE



Oracle Database also audits all privileges and statements BY ACCESS in one statement. If you are concerned that the auditing of these statements and privileges will adversely affect your applications, you can disable this auditing in the Security Settings window of Database Configuration Assistant.



6) Switching a database from Database Control to Grid Control configuration In previous releases, Database Configuration Assistant contained functionality to configure a database either with Database Control or with Grid Control. You could configure a database either while creating it or later. However, reconfiguring a database from Database Control to Grid Control required significant manual effort. With Oracle Database 11g, Database Configuration Assistant provides the Enterprise Manager Configuration plug-in, which automates the process to switch configuration of a database from Database Control to Grid Control.



7) Automatic Maintenance tasks
This option is added in 11g and comes in at the time of the database creation. Oracle Database 11g provides the ability to automatically manage maintenance tasks such as optimizer statistics collection and proactive advisor reports. These tasks are run in a predefined maintenance window and their CPU consumption is throttled to prevent them from interfering with normal user work. The default maintenance windows are 10:00 PM - 2:00 AM on weekdays. And all weekend long. These defaults can be changed using Enterprise Manager at any time.




I hope this will be helpful information to all. The above notes is prepared with the help of metalink