MERGE is used to select rows from one or more sources, and update or insert into a table or view. You cannot update the same row of the target table multiple times in the same MERGE statement.
Syntax:
MERGE INTO <table/view>
USING <table/view/subquery> ON (condition)
--update
WHEN MATCHED THEN
UPDATE SET column = (value/expr),..
WHERE (condition) | DELETE WHERE (condition)
--insert
WHEN NOT MATCHED THEN
INSERT (column,..) VALUES (value/expr,..)
WHERE (condition)
--error log
LOG ERRORS INTO <table>
If the ON clause condition is true
then UPDATE section gets executed.
else
INSERT section gets executed.
Restrictions on the merge update:
1. You cannot update a column that is referenced in the ON condition clause.
2. Cannot specify DEFAULT when updating a view.
See the example code below.
Create a target table called TEST1 and inset some test data
CREATE TABLE test1 (
co1 VARCHAR2(3),
co2 VARCHAR2(3),
co3 VARCHAR2(3),
co4 NUMBER );
ALTER TABLE test1 ADD CONSTRAINT test1_pk PRIMARY KEY(co1);
INSERT INTO test1 VALUES('1', 'val', 'sd1', 100);
INSERT INTO test1 VALUES('2', 'va2', 'sd2', 100);
INSERT INTO test1 VALUES('3', 'va3', 'sd3', 100);
INSERT INTO test1 VALUES('4', 'va4', 'sd4', NULL);
INSERT INTO test1 VALUES('5', 'va5', 'sd5', 100);
commit;
Create a target table called TEST2 and inset some test data
CREATE TABLE test2 (
a1 VARCHAR2(3),
a2 NUMBER );
ALTER TABLE test2 ADD CONSTRAINT test2_pk PRIMARY KEY(a1);
INSERT INTO test2 VALUES('1', 10);
INSERT INTO test2 VALUES('3', 20);
INSERT INTO test2 VALUES('6', 30);
INSERT INTO test2 VALUES('10', 40);
INSERT INTO test2 VALUES('15', 50);
INSERT INTO test2 VALUES('2', -10);
commit;
Now by using the MERGE statement we are going to update/delete or either insert records to the target table(test1) by using the source (test2).
1. MERGE INTO test1 a
2. USING (select a1, a2
3. from test2 ) b
4. ON (a.co1 = b.a1)
5. WHEN MATCHED THEN
6. update set a.co4 = a.co4 * b.a2,
7. a.co2 = b.a2
8. where co3 = \'sd3\'
9. delete where co3 = \'sd3\'
10. WHEN NOT MATCHED THEN
11. insert (a.co1, a.co2, a.co3, a.co4) values
12. (b.a1, null, null, b.a2);
line 2. uses a subquery to select the source records.
line 4. ON clause specify the condition. This condition controls the MERGE to perform update or insert. If true then update else insert.
line 6., 7. updates the columns by using the source column data.
line 9. deletes the target row after updations when the delete condition satisfied. This delete clause evaluates the updated value not the original values.
This statement executes only when ON clause condition (line 4) returns true.
line 10.When ON clause is false then this insert statement is executed.
Wednesday, August 6, 2008
How tnsnames.ora and sqlnet.ora file work together?
I would like to bring an article which explains how useful sqlnet and tnsnames files are and how they work together.
The default domain in the Sqlnet file is used for a SCENARIO purpose only.
SCENARIO 1:
in tnsnames.ora
dba.domain = (....)
in sqlnet.ora file
names.default_domain = domain
So, in sqlplus log screen, the following can be typed in: hr/hr@dba
Note the fully qualified dbadomain would not be typed in, because it will be automatically appended to the end of the connect string specified.
SCENARIO 2:
in tnsnames.ora
dba.domain = (....)
SCENARIO.NW.NOS.ORALCE.COM = (....)
in sqlnet.ora file
names.default_domain = domain
What would happen in the SCENARIO where multiple domain have been defined in the tnsnames.ora but with only one sqlnet.ora? Just remember, the key is what is defined in the TNSNAMES.ORA file, sqlnet.ora is for convenience.
The entire connect string can be typed in.
In sqlplus log screen, just type in :
hr/hr@test <--- should again work
hr/hr@test.domain <--- should again work
hr/hr@SCENARIO <--- should *NOT* work, because in fact it will be SCENARIO.domain
hr/hr@SCENARIO.NW.NOS.ORALCE.COM <--- should work
What is being said is that if the domain is added after the name when loginning as defined in the tnsnames.ora file, then don\'t use the default domain that is defined in the sqlnet.ora file.
SCENARIO 3:
In fact a sqlnet.ora file is not really needed for the purposes of login
E.g., in tnsnames.ora
test = (....) <--- note here, no domain
SCENARIO.NW.NOS.BOEING.COM = (....)
no sqlnet.ora file exists anywhere.
In sqlplus log screen, just type in :
hr/hr@test <--- should again work
hr/hr@SCENARIO.NW.NOS.BOEING.COM <--- should work
hr/hr@SCENARIO <--- should *NOT* work
hr/hr@test.domain <--- should *NOT* work
The default domain in the Sqlnet file is used for a SCENARIO purpose only.
SCENARIO 1:
in tnsnames.ora
dba.domain = (....)
in sqlnet.ora file
names.default_domain = domain
So, in sqlplus log screen, the following can be typed in: hr/hr@dba
Note the fully qualified dbadomain would not be typed in, because it will be automatically appended to the end of the connect string specified.
SCENARIO 2:
in tnsnames.ora
dba.domain = (....)
SCENARIO.NW.NOS.ORALCE.COM = (....)
in sqlnet.ora file
names.default_domain = domain
What would happen in the SCENARIO where multiple domain have been defined in the tnsnames.ora but with only one sqlnet.ora? Just remember, the key is what is defined in the TNSNAMES.ORA file, sqlnet.ora is for convenience.
The entire connect string can be typed in.
In sqlplus log screen, just type in :
hr/hr@test <--- should again work
hr/hr@test.domain <--- should again work
hr/hr@SCENARIO <--- should *NOT* work, because in fact it will be SCENARIO.domain
hr/hr@SCENARIO.NW.NOS.ORALCE.COM <--- should work
What is being said is that if the domain is added after the name when loginning as defined in the tnsnames.ora file, then don\'t use the default domain that is defined in the sqlnet.ora file.
SCENARIO 3:
In fact a sqlnet.ora file is not really needed for the purposes of login
E.g., in tnsnames.ora
test = (....) <--- note here, no domain
SCENARIO.NW.NOS.BOEING.COM = (....)
no sqlnet.ora file exists anywhere.
In sqlplus log screen, just type in :
hr/hr@test <--- should again work
hr/hr@SCENARIO.NW.NOS.BOEING.COM <--- should work
hr/hr@SCENARIO <--- should *NOT* work
hr/hr@test.domain <--- should *NOT* work
RDA
Remote Diagnostic Agent (RDA)
On Monday morning I received a mail from my client regarding installation of oracle 10g on RHEL ES 4 platform. I started the installation and ended up with many errors. While struggling to get required RPMS and going through metalink documents I found usage of RDA.
I used to hear about RDA even when working with Oracle Corporation but never thought how useful RDA is.
Remote Diagnostic Agent (RDA) is a set of command line diagnostic scripts. RDA is used to gather detailed information about an Oracle environment.
Oracle Support encourages the use of RDA because it provides a comprehensive picture of the customer's environment.
Some times in the platform like RHEL to find required RPMs and compatibility RDA looks very great. Eben you might run custom xml scrips with this RDA.
All installation instructions are given in metalink note 314422.1.
The Tools can be run in SHELL or PEARL environment.
Example
Ater installation of RDA /home/oracle/rda
To start data collection
[oracle@rda]$ ./rda.sh
-------------------------------------------------------------------------------
RDA Data Collection Started 06-Feb-2008 11:50:16 AM
-------------------------------------------------------------------------------
Processing Initialization module ...
Processing CFG module ...
Processing Sampling module ...
Processing OCM module ...
Processing OS module ...
Processing PROF module ...
Processing PERF module ...
Processing RDSP module ...
Processing LOAD module ...
This will generate many files including a zip file usually required by Oracle Support.
RDA includes health Check Validation Engine
To check HCVE
Run
Processing HCVE tests ...
Available Pre-Installation Rule Sets:
1. Oracle Database 10g R1 (10.1.0) PreInstall (Linux-x86)
2. Oracle Database 10g R1 (10.1.0) PreInstall (Linux AMD64)
3. Oracle Database 10g R1 (10.1.0) PreInstall (IA-64 Linux)
4. Oracle Database 10g R2 (10.2.0) PreInstall (Linux AMD64)
5. Oracle Database 10g R2 (10.2.0) PreInstall (IA-64 Linux)
6. Oracle Database 10g R2 (10.2.0) PreInstall (Linux-x86)
7. Oracle Application Server 10g (9.0.4) PreInstall (Linux)
8. Oracle Application Server 10g R2 (10.1.2) PreInstall (Linux)
9. Oracle Application Server 10g R3 (10.1.3) PreInstall (Linux AMD64)
10. Oracle Application Server 10g R3 (10.1.3) PreInstall (IA-64 Linux)
11. Oracle Application Server 10g R3 (10.1.3) PreInstall (Linux-x86)
12. Oracle Portal PreInstall (Generic)
Available Post-Installation Rule Sets:
13. Oracle Portal PostInstall (generic)
14. RAC 10G DB and OS Best Practices (Linux)
15. Data Guard PostInstall (Generic)
Enter the HCVE rule set number
Hit 'Return' to accept the default (1)
>
Select your choice and press enter
Test Results
~~~~~~~~~~~~
ID NAME RESULT VALUE
===== ==================== ====== ========================================
10 OS Certified? PASSED Adequate
20 User in /etc/passwd? PASSED userOK
30 Group in /etc/group? PASSED GroupOK
40 Input ORACLE_HOME RECORD $ORACLE_HOME
50 ORACLE_HOME Valid? PASSED OHexists
60 O_H Permissions OK? PASSED CorrectPerms
70 Umask Set to 022? PASSED UmaskOK
80 LDLIBRARYPATH Unset? FAILED IsSet
100 Other O_Hs in PATH? PASSED NoneFound
110 oraInventory Permiss PASSED oraInventoryOK
120 /tmp Adequate? PASSED TempSpaceOK
130 Swap (in MB) RECORD 4605
140 RAM (in MB) PASSED 2007
150 Swap OK? PASSED SwapOK
160 Disk Space OK? PASSED DiskSpaceOK
170 Kernel Parameters OK FAILED SHMMAXTooSmall SEMOPMTooSmall
180 Got ld,nm,ar,make? PASSED ld_nm_ar_make_found
190 ulimits OK? FAILED StackTooSmall MaxLockMemTooSmall
200 EL4 RPMs OK? PASSED NotEnterprise
203 RHEL21 RPMs OK? PASSED NotRHEL21
204 RHEL3 RPMs OK? PASSED NotRHEL3
205 RHEL4 RPMs OK? PASSED RHEL4rpmsOK
208 SUSE SLES8 RPMs OK? PASSED NotSuSE
209 SUSE SLES9 RPMs OK? PASSED NotSuSE
212 Patch 3006854 Instal PASSED NotRequired
213 LD_ASSUME_KERNEL Set PASSED NotRHEL3
214 ip_local_port_range FAILED LowTooHigh HighTooLow
220 Tainted Kernel? PASSED NotVerifiable
230 Other OUI Up? PASSED NoOtherOUI
This will give you a fair idea of environment before installation.
And always RDA will have a result file, some time Oracle Support may ask this file. Usually this will be generated in output folder.
So try to learn RDA as quickly as possible. This tool helps in many ways.
Purpose of this document was to just highlight what RDA is and how best to use it. Refer Metalink for downloadable, getting started and installation instructions.
upgrade
Upgrade database from 9i to 10g
First you have to upgrade testing database from 9i to 10g. After that you will perform upgradtion activity in production database.
Example.
1.you create sample database in oracle 9i.
2.create os user for oracle10g
su - root
groupadd dba # group of users to be granted SYSDBA system privilege
groupadd oinstall # group owner of Oracle files
useradd -c oracle10g -g oinstall -G dba oracle
passwd oracle
3.Download oracle 10g software from oracle web site then install oracle 10 in that server.
4.Then set environment variables for oracle10g.
For example 9i database name test
the database to be upgraded is called test.
then copy the script from u01/app/oracle/product/10.2.0/db_1/rdbms/admin/utlu102i.sql to /tmp directory
Login to 9i database,
$sqlplus "/as sysdba"
Connected to:
Oracle9i Enterprise Edition Release 9.2.0.6.0 - Production
With the Partitioning, OLAP and Oracle Data Mining options
JServer Release 9.2.0.6.0 - Production
sql>spool /tmp/upgrade.txt
sql>@/tmp/utlu102i
Oracle Database 10.2 Upgrade Information Utility 06-20-2006 16:39:48
.
**********************************************************************
Database:
**********************************************************************
--> name: test
--> version: 9.2.0.6.0
--> compatible: 9.2.0.0.0
--> blocksize: 8192
.
**********************************************************************
Logfiles: [make adjustments in the current environment]
**********************************************************************
--> The existing log files are adequate. No changes are required.
.
**********************************************************************
Tablespaces: [make adjustments in the current environment]
**********************************************************************
--> SYSTEM tablespace is adequate for the upgrade.
.... minimum required size: 439 MB
.... AUTOEXTEND additional space required: 109 MB
--> TEMP tablespace is adequate for the upgrade.
.... minimum required size: 58 MB
.... AUTOEXTEND additional space required: 18 MB
--> DRSYS tablespace is adequate for the upgrade.
.... minimum required size: 5 MB
--> XDB tablespace is adequate for the upgrade.
.... minimum required size: 49 MB
.... AUTOEXTEND additional space required: 4 MB
.
**********************************************************************
Update Parameters: [Update Oracle Database 10.2 init.ora or spfile]
**********************************************************************
WARNING: --> "shared_pool_size" needs to be increased to at least 177861837
WARNING: --> "java_pool_size" needs to be increased to at least 67108864
WARNING: --> "streams_pool_size" is not currently defined and needs a value of at least 50331648
WARNING: --> "session_max_open_files" needs to be increased to at least 20
.
**********************************************************************
Renamed Parameters: [Update Oracle Database 10.2 init.ora or spfile]
**********************************************************************
-- No renamed parameters found. No changes are required.
.
**********************************************************************
Obsolete/Deprecated Parameters: [Update Oracle Database 10.2 init.ora
or spfile]
**********************************************************************
--> "hash_join_enabled"
.
**********************************************************************
Components: [The following database components will be upgraded
or installed]
**********************************************************************
--> Oracle Catalog Views [upgrade] VALID
--> Oracle Packages and Types [upgrade] VALID
--> JServer JAVA Virtual Machine [upgrade] VALID
...The 'JServer JAVA Virtual Machine' JAccelerator (NCOMP)
...is required to be installed from the 10g Companion CD.
--> Oracle XDK for Java [upgrade] VALID
--> Oracle Java Packages [upgrade] VALID
--> Oracle Text [upgrade] VALID
--> Oracle XML Database [upgrade] VALID
--> Oracle Workspace Manager [upgrade] VALID
--> Oracle interMedia [upgrade] VALID
...The 'Oracle interMedia Image Accelerator' is
...required to be installed from the 10g Companion CD.
--> Spatial [upgrade] VALID
.
**********************************************************************
Miscellaneous Warnings
**********************************************************************
WARNING: --> Database contains stale optimizer statistics.
.... Refer to the 10g Upgrade Guide for instructions to update
.... statistics prior to upgrading the database.
.... Component Schemas with stale statistics:
.... SYS
.... CTXSYS
.... XDB
.... WMSYS
.... ORDSYS
.... MDSYS
.
**********************************************************************
SYSAUX Tablespace:
[Create tablespace in the Oracle Database 10.2 environment]
**********************************************************************
--> New "SYSAUX" tablespace
.... minimum required size for database upgrade: 500 MB
.
PL/SQL procedure successfully completed.
SQL> spool off
SQL> exit
Disconnected from Oracle9i Enterprise Edition Release 9.2.0.6.0
With the Partitioning, OLAP and Oracle Data Mining options
JServer Release 9.2.0.6.0 - Production
Under most circumstances, at this point, the database would be backed up. This demonstration will skip over that part of the upgrade
Next, we create a PFILE from our SPFILE that we can make changes to:
SQL> create pfile from spfile;
File created.
SQL> exit
$ vi /u01/app/oracle/product/9.2.0/dbs/inittest.ora
Edit the pfile using this the diagnostic output as a guide. Here is my file:
*.background_dump_dest='/u01/app/oracle/admin/test/bdump'
*.compatible='9.2.0.0.0'
*.control_files='/u02/oradata/test/control01.ctl',
'/u02/oradata/test/control02.ctl',
'/u02/oradata/test/control03.ctl'
*.core_dump_dest='/u01/app/oracle/admin/test/cdump'
*.db_block_size=8192
*.db_cache_size=117440512
*.db_domain=''
*.db_file_multiblock_read_count=16
*.db_name='test'
*.dispatchers='(PROTOCOL=TCP) (SERVICE=testXDB)'
*.fast_start_mttr_target=300
#*.hash_join_enabled=TRUE
*.instance_name='test'
#*.java_pool_size=33554432
*.java_pool_size=67108864
*.large_pool_size=28311552
*.open_cursors=300
*.pga_aggregate_target=104857600
*.processes=150
*.query_rewrite_enabled='FALSE'
*.remote_login_passwordfile='EXCLUSIVE'
*.session_max_open_files=20
#*.shared_pool_size=38797312
*.shared_pool_size=177861837
*.sort_area_size=524288
*.star_transformation_enabled='FALSE'
*.timed_statistics=TRUE
*.undo_management='AUTO'
*.undo_retention=10800
*.undo_tablespace='UNDOTBS1'
*.user_dump_dest='/u01/app/oracle/admin/test/udump'
set pagesize 50000
tti "Database growth per month for last year"
select to_char(creation_time, 'RRRR Month') "Month",
sum(bytes)/1024/1024 "Growth in Meg"
from sys.v_$datafile
where creation_time > SYSDATE-365
group by to_char(creation_time, 'RRRR Month')
;
Monday, August 4, 2008
Upgrade database from 9i to 10g
Upgrade database from 9i to 10g
First you have to upgrade testing database from 9i to 10g. After that you will perform upgradtion activity in production database.
Example.
1.you create sample database in oracle 9i.2.create os user for oracle10g
su - root
groupadd dba # group of users to be granted SYSDBA system privilege
groupadd oinstall # group owner of Oracle files
useradd -c oracle10g -g oinstall -G dba oraclepasswd oracle
3.Download oracle 10g software from oracle web site then install oracle 10 in that server.
4.Then set environment variables for oracle10g.
For example 9i database name test.The database to be upgraded is called test.then copy the script from u01/app/oracle/product/10.2.0/db_1/rdbms/admin/utlu102i.sql to /tmp directory
Login to 9i database,
$sqlplus "/as sysdba"
Connected to:Oracle9i Enterprise Edition Release 9.2.0.6.0 - ProductionWith the Partitioning, OLAP and Oracle Data Mining optionsJServer Release 9.2.0.6.0 - Production
Run utlu102i.sql
This utility script checks that the database is ready to be upgraded to 10g. It also identifies any actions that need to be taken. The script is located in the 10g oracle home, so you will need to specify the full path to it.
@/u01/app/oracle/product/10.2.0/db_1/rdbms/admin/utlu102i.sql
Review the output and make any necessary alterations. Make a note of how many invalid objects there are.
Shut the database down with either normal or immediate
shutdown immediate
Copy the spfile (or pfile) and the password file from the existing home to the 10g one.
cp ${ORACLE_HOME}/dbs/*${ORACLE_SID}*/dbs/
Upgrade the database
sqlplus "/ as sysdba"
SQL> startup upgrade
ORACLE instance started.
Total System Global Area 398458880 bytes
Fixed Size 1261068 bytes
Variable Size 276824564 bytes
Database Buffers 117440512 bytes
Redo Buffers 2932736 bytes
Database mounted.
Database opened.
sql>
CREATE TABLESPACE sysaux
DATAFILE '/u01/ora9R2/oradata/sysaux01.dbf'
SIZE 1024M REUSE
EXTENT MANAGEMENT LOCAL
SEGMENT SPACE MANAGEMENT AUTO
ONLINE;
This next bit is the upgrade itself. It takes roughly half an hour to complete.
Spool the output to a file so that you can review it afterward.
@?/rdbms/admin/catupgrd.sql
Create a sysaux tablespace
Having a sysaux tablespace is a requirement in 10g. So, if you haven't already got one, create one now.
create tablespace sysaux
datafile '' size 512M
extent management local
segment space management auto
/
Recompile any invalid objects
SQL> select count(*) from dba_objects where status='INVALID';
@?/rdbms/admin/utlrp.sql
Then check the status of the upgrade
@?/rdbms/admin/utlu102s.sql
First you have to upgrade testing database from 9i to 10g. After that you will perform upgradtion activity in production database.
Example.
1.you create sample database in oracle 9i.2.create os user for oracle10g
su - root
groupadd dba # group of users to be granted SYSDBA system privilege
groupadd oinstall # group owner of Oracle files
useradd -c oracle10g -g oinstall -G dba oraclepasswd oracle
3.Download oracle 10g software from oracle web site then install oracle 10 in that server.
4.Then set environment variables for oracle10g.
For example 9i database name test.The database to be upgraded is called test.then copy the script from u01/app/oracle/product/10.2.0/db_1/rdbms/admin/utlu102i.sql to /tmp directory
Login to 9i database,
$sqlplus "/as sysdba"
Connected to:Oracle9i Enterprise Edition Release 9.2.0.6.0 - ProductionWith the Partitioning, OLAP and Oracle Data Mining optionsJServer Release 9.2.0.6.0 - Production
Run utlu102i.sql
This utility script checks that the database is ready to be upgraded to 10g. It also identifies any actions that need to be taken. The script is located in the 10g oracle home, so you will need to specify the full path to it.
@/u01/app/oracle/product/10.2.0/db_1/rdbms/admin/utlu102i.sql
Review the output and make any necessary alterations. Make a note of how many invalid objects there are.
Shut the database down with either normal or immediate
shutdown immediate
Copy the spfile (or pfile) and the password file from the existing home to the 10g one.
cp ${ORACLE_HOME}/dbs/*${ORACLE_SID}*
Upgrade the database
sqlplus "/ as sysdba"
SQL> startup upgrade
ORACLE instance started.
Total System Global Area 398458880 bytes
Fixed Size 1261068 bytes
Variable Size 276824564 bytes
Database Buffers 117440512 bytes
Redo Buffers 2932736 bytes
Database mounted.
Database opened.
sql>
CREATE TABLESPACE sysaux
DATAFILE '/u01/ora9R2/oradata/sysaux01.dbf'
SIZE 1024M REUSE
EXTENT MANAGEMENT LOCAL
SEGMENT SPACE MANAGEMENT AUTO
ONLINE;
This next bit is the upgrade itself. It takes roughly half an hour to complete.
Spool the output to a file so that you can review it afterward.
@?/rdbms/admin/catupgrd.sql
Create a sysaux tablespace
Having a sysaux tablespace is a requirement in 10g. So, if you haven't already got one, create one now.
create tablespace sysaux
datafile '
extent management local
segment space management auto
/
Recompile any invalid objects
SQL> select count(*) from dba_objects where status='INVALID';
@?/rdbms/admin/utlrp.sql
Then check the status of the upgrade
@?/rdbms/admin/utlu102s.sql
SGA
SGA:
The SGA is Oracle's structural memory area that facilitates the transfer of data and information between clients and the Oracle database.
sql>SHOW SGA
Total System Global Area 168783792 bytes
Fixed Size 737200 bytes
Variable Size 134217728 bytes
Database Buffers 33554432 bytes
Redo Buffers 274432 bytes
Total System Global Area : - Total in bytes of all the sub-divisions that makes up the SGA.
Fixed Size: Fixed size contains general information about the state of the database and the instance, which the background processes need to access. This does not store user data. Usually this area is less than 100k in size.
Variable Size: This part is influenced by the following init.ora parameters. shared_pool_size large_pool_size java_pool_size Database Buffers:
This holds data blocks copies that are read from datafiles and can be calculated by using following formula. size = db_block_buffers * block size Redo Buffers: This is another buffer in the SGA that holds information about changes made to the database.
Approximating size calculation of the SGA
Oracle 9i: To approximate size of the SGA (Shared Global Area), use following formula: db_cache_size + db_keep_cache_size + db_recycle_cache_size + db_nk_cache_size + shared_pool_size + large_pool_size + java_pool_size + log_buffers + 1mb
The SGA is Oracle's structural memory area that facilitates the transfer of data and information between clients and the Oracle database.
sql>SHOW SGA
Total System Global Area 168783792 bytes
Fixed Size 737200 bytes
Variable Size 134217728 bytes
Database Buffers 33554432 bytes
Redo Buffers 274432 bytes
Total System Global Area : - Total in bytes of all the sub-divisions that makes up the SGA.
Fixed Size: Fixed size contains general information about the state of the database and the instance, which the background processes need to access. This does not store user data. Usually this area is less than 100k in size.
Variable Size: This part is influenced by the following init.ora parameters. shared_pool_size large_pool_size java_pool_size Database Buffers:
This holds data blocks copies that are read from datafiles and can be calculated by using following formula. size = db_block_buffers * block size Redo Buffers: This is another buffer in the SGA that holds information about changes made to the database.
Approximating size calculation of the SGA
Oracle 9i: To approximate size of the SGA (Shared Global Area), use following formula: db_cache_size + db_keep_cache_size + db_recycle_cache_size + db_nk_cache_size + shared_pool_size + large_pool_size + java_pool_size + log_buffers + 1mb
How Deadlock occurs?
Dealdlock
Deadlock is a situation that occurs when two or more users are waiting for data locked by each other. Oracle automatically detects a deadlock and resolves them by rolling back one of the statements involved in the deadlock.
Example:
create table sample(id number,Country varchar2(10) );
insert into sample values ( 1, 'China' );
insert into sample values ( 2, 'France' );
commit;
SQL> Select * from sample ;
id country
1 China
2 France
Issue the following command in session1:
SQL> update sample set country='Italy' where id=1;
Issue the following command in Session 2:
SQL> update sample set country='Canada' where id=2;
SQL> update sample set country='Canada' where id=1;
Session2 is now waiting for the lock held by Session1
Issue the following command in session1:
SQL> update sample set country='Italy' where id=2;
This update would cause Session1 to wait on the lock held by Session2, but Session2 is already waiting on this session. This causes a deadlock
Deadlock is a situation that occurs when two or more users are waiting for data locked by each other. Oracle automatically detects a deadlock and resolves them by rolling back one of the statements involved in the deadlock.
Example:
create table sample(id number,Country varchar2(10) );
insert into sample values ( 1, 'China' );
insert into sample values ( 2, 'France' );
commit;
SQL> Select * from sample ;
id country
1 China
2 France
Issue the following command in session1:
SQL> update sample set country='Italy' where id=1;
Issue the following command in Session 2:
SQL> update sample set country='Canada' where id=2;
SQL> update sample set country='Canada' where id=1;
Session2 is now waiting for the lock held by Session1
Issue the following command in session1:
SQL> update sample set country='Italy' where id=2;
This update would cause Session1 to wait on the lock held by Session2, but Session2 is already waiting on this session. This causes a deadlock
Subscribe to:
Posts (Atom)