2007/06/25
2007/06/14
[oracle]how to be a develop team dba
My suggestion is:
1) revoke all DBA privileges, only DBA team can have it;
2) Every developer can have his/her own schema, they can do whatever they like under their own schema;
3) Grant only select privs on other schemas for every schema owner, so they won't step onto each other;
4) For predeployment/Test/Staging databases, only grant DML privs to application users.
Note: You have to set up your team to take the responsiblity to do manitainance, replication, DDL support.
1) revoke all DBA privileges, only DBA team can have it;
2) Every developer can have his/her own schema, they can do whatever they like under their own schema;
3) Grant only select privs on other schemas for every schema owner, so they won't step onto each other;
4) For predeployment/Test/Staging databases, only grant DML privs to application users.
Note: You have to set up your team to take the responsiblity to do manitainance, replication, DDL support.
2007/06/13
[oracle][rman]recover database to a point before resetlogs(previous incarnation)
CONNECT TARGET / NOCATALOG
# step 1: start and mount a control file that knows about the incarnation to which
# you want to return. if the current control file does not know about it, then
# you must restore an older control file
STARTUP NOMOUNT;
RESTORE CONTROLFILE UNTIL TIME 'SYSDATE-250';
ALTER DATABASE MOUNT;
# step 2: obtain the primary key of old incarnation
LIST INCARNATION OF DATABASE trgt;
List of Database Incarnations
DB Key Inc Key DB Name DB ID STATUS Reset SCN Reset Time
------- ------- -------- ------------- ------- ---------- ----------
1 2 TRGT 1334358386 PARENT 154381 OCT 30 2001 16:02:12
1 116 TRGT 1334358386 CURRENT 154877 OCT 30 2001 16:37:39
# step 3: in this example, reset database to incarnation key 2
RESET DATABASE TO INCARNATION 2;
# step 4: restore and recover the database to a point before the RESETLOGS
RESTORE DATABASE UNTIL SCN 154876;
RECOVER DATABASE UNTIL SCN 154876;
# step 5: make this incarnation the current incarnation and then list incarnations:
ALTER DATABASE OPEN RESETLOGS;
LIST INCARNATION OF DATABASE trgt;
List of Database Incarnations
DB Key Inc Key DB Name DB ID STATUS Reset SCN Reset Time
------- ------- -------- ---------------- ------- ---------- ----------
1 2 TRGT 1334358386 PARENT 154381 OCT 30 2001 16:02:12
1 116 TRGT 1334358386 PARENT 154877 OCT 30 2001 16:37:39
1 311 TRGT 1334358386 CURRENT 154877 AUG 13 2002 17:17:03
% rman target / catalog rman/rman@catdb
Recovery Manager: Release 10.1.0.2.0 - Production
Copyright (c) 1995, 2003, Oracle. All rights reserved.
connected to target database: TRGT (DBID=1334531173)
connected to recovery catalog database
RMAN> RESYNC CATALOG;
RMAN-00571: ===========================================================
RMAN-00569: =============== ERROR MESSAGE STACK FOLLOWS ===============
RMAN-00571: ===========================================================
RMAN-03009: failure of resync command on default channel at 11/01/2001 12:00:43
RMAN-20003: target database incarnation not found in recovery catalog
RMAN> RESET DATABASE;
[oracle][security][role]procedure/object AUTHID
Specifying the Privileges for a Subprogram with the AUTHID Clause
To implement invoker's rights, use the AUTHID clause, which specifies whether a subprogram executes with the privileges of its owner or its current user. It also specifies whether external references (that is, references to objects outside the subprogram) are resolved in the schema of the owner or the current user.
The AUTHID clause is allowed only in the header of a standalone subprogram, a package spec, or an object type spec. In the CREATE FUNCTION, CREATE PROCEDURE, CREATE PACKAGE, or CREATE TYPE statement, you can include either AUTHID CURRENT_USER or AUTHID DEFINER immediately before the IS or AS keyword that begins the declaration section.
DEFINER is the default option. In a package or object type, the AUTHID clause applies to all subprograms.
Most supplied PL/SQL packages (such as DBMS_LOB, DBMS_PIPE, DBMS_ROWID, DBMS_SQL, and UTL_REF) are invoker's rights packages.
To implement invoker's rights, use the AUTHID clause, which specifies whether a subprogram executes with the privileges of its owner or its current user. It also specifies whether external references (that is, references to objects outside the subprogram) are resolved in the schema of the owner or the current user.
The AUTHID clause is allowed only in the header of a standalone subprogram, a package spec, or an object type spec. In the CREATE FUNCTION, CREATE PROCEDURE, CREATE PACKAGE, or CREATE TYPE statement, you can include either AUTHID CURRENT_USER or AUTHID DEFINER immediately before the IS or AS keyword that begins the declaration section.
DEFINER is the default option. In a package or object type, the AUTHID clause applies to all subprograms.
Most supplied PL/SQL packages (such as DBMS_LOB, DBMS_PIPE, DBMS_ROWID, DBMS_SQL, and UTL_REF) are invoker's rights packages.
[oracle][random]dbms_random.random
DBMS_RANDOM package
The DBMS_RANDOM package will generate random data in character, numeric or alphanumeric formats. The size and the range from which to pickup the random values can also be specified. This package is created by the script dbmsrand.sql available in the $ORACLE_HOME/rdbms/admin directory.
The following functions present in the package can be used to serve the purpose of generating random numbers and strings. RANDOM - generate random numbers.
VALUE - generate random numbers from the range provided. The range will be taken as 0-1 if none is provided.
STRING - generate strings in upper case, lower case or alphanumeric format.
The first parameter takes the string type to be generated, the following values can be provided in upper or lower case.
U - Upper case
L - Lower case
A - Alphanumeric
X - Alphanumeric with upper case alphabets.
P - Printable characters only.
Providing any other character will return the output in upper case only.
The size of the string should also be provided as the second parameter.
Oracle documentation says that it is necessary to initialize the package before using the random number generator. Oracle by default initializes the package with the seed value as the current user name, current time down to the second and the current session id.
INITIALIZE - Initialize the package to proceed with the number generation.
Provide a number (seed) as input to the routine.
SEED - Used to change the seed value. It is used in the internal algorithm to generate values. Setting this will
generate the random numbers in an order that will be similar in multiple sessions. Refer to the example below.
TERMINATE - Close the process of random number generation.
Examples:
Below are some examples of using the package.
E.g.: Generating a random number (positive or negative)
SQL> select dbms_random.random from dual;
RANDOM
_____________
1393936551
E.g.: Generating a random number between 0 and 1.
SQL> select dbms_random.value from dual;
VALUE
_____________
1
E.g.: Generating a random number from a range, between 1 to 1000.
SQL> select dbms_random.value(1,1000) num from dual;
NUM
_____________
611
E.g.: Generating a 12 digit random number.
SQL> select dbms_random.value(100000000000, 999999999999) num from dual;
NUM
_____________
175055628780
E.g.: Generating an upper case string of 20 characters
SQL> select dbms_random.string('U', 20) str from dual;
STR
_______________________
VUOQOSTLHCKIPIADIZTD
E.g.: Generating a lower case string of 20 characters
SQL> select dbms_random.string('L', 20) str from dual;
STR
____________________
xpoovuspmehvcptdtzcz
E.g.: Generating an alphanumeric string of 20 characters. There is a bug in Oracle 8i that results in special (non-alphanumeric) characters such as ']' in the string. This is resolved in Oracle 9i.
SQL> select dbms_random.string('A', 20) str from dual;
STR
__________________
sTjERojjL^OlTaIc]PLB
E.g.: Generating an upper case alphanumeric string of 20 characters
SQL> select dbms_random.string('X', 20) str from dual;
STR
________________________
SQ3E3B3NRBIP:GOGAKSC
E.g.: Generating a string of printable 20 characters. This will output a string of all characters that could possibly be printed.
SQL> select dbms_random.string('P', 20) str from dual;
STR
___________________
*Yw>IKzsj\uI8K[IQPag
E.g.: Example for calling the dbms_random package and setting the seed for generating the same set of random numbers in different sessions. Please note that the same random numbers are generated in different sessions. Though I have found this to work on most accounts, in some cases, the first number generated was different in different sessions and the remaining were same. I recommend not using this option in any of production code until it is properly document by Oracle.
jaJA>declare
2 l_num number;
3 begin
4 l_num := dbms_random.random;
5 dbms_output.put_line(l_num);
6 dbms_random.seed('amar testing 67890');
7 l_num := dbms_random.random;
8 dbms_output.put_line(l_num);
9 end;
10 /
483791552
478774329
PL/SQL procedure successfully completed.
The DBMS_RANDOM package will generate random data in character, numeric or alphanumeric formats. The size and the range from which to pickup the random values can also be specified. This package is created by the script dbmsrand.sql available in the $ORACLE_HOME/rdbms/admin directory.
The following functions present in the package can be used to serve the purpose of generating random numbers and strings. RANDOM - generate random numbers.
VALUE - generate random numbers from the range provided. The range will be taken as 0-1 if none is provided.
STRING - generate strings in upper case, lower case or alphanumeric format.
The first parameter takes the string type to be generated, the following values can be provided in upper or lower case.
U - Upper case
L - Lower case
A - Alphanumeric
X - Alphanumeric with upper case alphabets.
P - Printable characters only.
Providing any other character will return the output in upper case only.
The size of the string should also be provided as the second parameter.
Oracle documentation says that it is necessary to initialize the package before using the random number generator. Oracle by default initializes the package with the seed value as the current user name, current time down to the second and the current session id.
INITIALIZE - Initialize the package to proceed with the number generation.
Provide a number (seed) as input to the routine.
SEED - Used to change the seed value. It is used in the internal algorithm to generate values. Setting this will
generate the random numbers in an order that will be similar in multiple sessions. Refer to the example below.
TERMINATE - Close the process of random number generation.
Examples:
Below are some examples of using the package.
E.g.: Generating a random number (positive or negative)
SQL> select dbms_random.random from dual;
RANDOM
_____________
1393936551
E.g.: Generating a random number between 0 and 1.
SQL> select dbms_random.value from dual;
VALUE
_____________
1
E.g.: Generating a random number from a range, between 1 to 1000.
SQL> select dbms_random.value(1,1000) num from dual;
NUM
_____________
611
E.g.: Generating a 12 digit random number.
SQL> select dbms_random.value(100000000000, 999999999999) num from dual;
NUM
_____________
175055628780
E.g.: Generating an upper case string of 20 characters
SQL> select dbms_random.string('U', 20) str from dual;
STR
_______________________
VUOQOSTLHCKIPIADIZTD
E.g.: Generating a lower case string of 20 characters
SQL> select dbms_random.string('L', 20) str from dual;
STR
____________________
xpoovuspmehvcptdtzcz
E.g.: Generating an alphanumeric string of 20 characters. There is a bug in Oracle 8i that results in special (non-alphanumeric) characters such as ']' in the string. This is resolved in Oracle 9i.
SQL> select dbms_random.string('A', 20) str from dual;
STR
__________________
sTjERojjL^OlTaIc]PLB
E.g.: Generating an upper case alphanumeric string of 20 characters
SQL> select dbms_random.string('X', 20) str from dual;
STR
________________________
SQ3E3B3NRBIP:GOGAKSC
E.g.: Generating a string of printable 20 characters. This will output a string of all characters that could possibly be printed.
SQL> select dbms_random.string('P', 20) str from dual;
STR
___________________
*Yw>IKzsj\uI8K[IQPag
E.g.: Example for calling the dbms_random package and setting the seed for generating the same set of random numbers in different sessions. Please note that the same random numbers are generated in different sessions. Though I have found this to work on most accounts, in some cases, the first number generated was different in different sessions and the remaining were same. I recommend not using this option in any of production code until it is properly document by Oracle.
jaJA>declare
2 l_num number;
3 begin
4 l_num := dbms_random.random;
5 dbms_output.put_line(l_num);
6 dbms_random.seed('amar testing 67890');
7 l_num := dbms_random.random;
8 dbms_output.put_line(l_num);
9 end;
10 /
483791552
478774329
PL/SQL procedure successfully completed.
[oracle][random]dbms_random.random
DBMS_RANDOM package
The DBMS_RANDOM package will generate random data in character, numeric or alphanumeric formats. The size and the range from which to pickup the random values can also be specified. This package is created by the script dbmsrand.sql available in the $ORACLE_HOME/rdbms/admin directory.
The following functions present in the package can be used to serve the purpose of generating random numbers and strings. RANDOM - generate random numbers.
VALUE - generate random numbers from the range provided. The range will be taken as 0-1 if none is provided.
STRING - generate strings in upper case, lower case or alphanumeric format.
The first parameter takes the string type to be generated, the following values can be provided in upper or lower case.
U - Upper case
L - Lower case
A - Alphanumeric
X - Alphanumeric with upper case alphabets.
P - Printable characters only.
Providing any other character will return the output in upper case only.
The size of the string should also be provided as the second parameter.
Oracle documentation says that it is necessary to initialize the package before using the random number generator. Oracle by default initializes the package with the seed value as the current user name, current time down to the second and the current session id.
INITIALIZE - Initialize the package to proceed with the number generation.
Provide a number (seed) as input to the routine.
SEED - Used to change the seed value. It is used in the internal algorithm to generate values. Setting this will
generate the random numbers in an order that will be similar in multiple sessions. Refer to the example below.
TERMINATE - Close the process of random number generation.
Examples:
Below are some examples of using the package.
E.g.: Generating a random number (positive or negative)
SQL> select dbms_random.random from dual;
RANDOM
_____________
1393936551
E.g.: Generating a random number between 0 and 1.
SQL> select dbms_random.value from dual;
VALUE
_____________
1
E.g.: Generating a random number from a range, between 1 to 1000.
SQL> select dbms_random.value(1,1000) num from dual;
NUM
_____________
611
E.g.: Generating a 12 digit random number.
SQL> select dbms_random.value(100000000000, 999999999999) num from dual;
NUM
_____________
175055628780
E.g.: Generating an upper case string of 20 characters
SQL> select dbms_random.string('U', 20) str from dual;
STR
_______________________
VUOQOSTLHCKIPIADIZTD
E.g.: Generating a lower case string of 20 characters
SQL> select dbms_random.string('L', 20) str from dual;
STR
____________________
xpoovuspmehvcptdtzcz
E.g.: Generating an alphanumeric string of 20 characters. There is a bug in Oracle 8i that results in special (non-alphanumeric) characters such as ']' in the string. This is resolved in Oracle 9i.
SQL> select dbms_random.string('A', 20) str from dual;
STR
__________________
sTjERojjL^OlTaIc]PLB
E.g.: Generating an upper case alphanumeric string of 20 characters
SQL> select dbms_random.string('X', 20) str from dual;
STR
________________________
SQ3E3B3NRBIP:GOGAKSC
E.g.: Generating a string of printable 20 characters. This will output a string of all characters that could possibly be printed.
SQL> select dbms_random.string('P', 20) str from dual;
STR
___________________
*Yw>IKzsj\uI8K[IQPag
E.g.: Example for calling the dbms_random package and setting the seed for generating the same set of random numbers in different sessions. Please note that the same random numbers are generated in different sessions. Though I have found this to work on most accounts, in some cases, the first number generated was different in different sessions and the remaining were same. I recommend not using this option in any of production code until it is properly document by Oracle.
jaJA>declare
2 l_num number;
3 begin
4 l_num := dbms_random.random;
5 dbms_output.put_line(l_num);
6 dbms_random.seed('amar testing 67890');
7 l_num := dbms_random.random;
8 dbms_output.put_line(l_num);
9 end;
10 /
483791552
478774329
PL/SQL procedure successfully completed.
The DBMS_RANDOM package will generate random data in character, numeric or alphanumeric formats. The size and the range from which to pickup the random values can also be specified. This package is created by the script dbmsrand.sql available in the $ORACLE_HOME/rdbms/admin directory.
The following functions present in the package can be used to serve the purpose of generating random numbers and strings. RANDOM - generate random numbers.
VALUE - generate random numbers from the range provided. The range will be taken as 0-1 if none is provided.
STRING - generate strings in upper case, lower case or alphanumeric format.
The first parameter takes the string type to be generated, the following values can be provided in upper or lower case.
U - Upper case
L - Lower case
A - Alphanumeric
X - Alphanumeric with upper case alphabets.
P - Printable characters only.
Providing any other character will return the output in upper case only.
The size of the string should also be provided as the second parameter.
Oracle documentation says that it is necessary to initialize the package before using the random number generator. Oracle by default initializes the package with the seed value as the current user name, current time down to the second and the current session id.
INITIALIZE - Initialize the package to proceed with the number generation.
Provide a number (seed) as input to the routine.
SEED - Used to change the seed value. It is used in the internal algorithm to generate values. Setting this will
generate the random numbers in an order that will be similar in multiple sessions. Refer to the example below.
TERMINATE - Close the process of random number generation.
Examples:
Below are some examples of using the package.
E.g.: Generating a random number (positive or negative)
SQL> select dbms_random.random from dual;
RANDOM
_____________
1393936551
E.g.: Generating a random number between 0 and 1.
SQL> select dbms_random.value from dual;
VALUE
_____________
1
E.g.: Generating a random number from a range, between 1 to 1000.
SQL> select dbms_random.value(1,1000) num from dual;
NUM
_____________
611
E.g.: Generating a 12 digit random number.
SQL> select dbms_random.value(100000000000, 999999999999) num from dual;
NUM
_____________
175055628780
E.g.: Generating an upper case string of 20 characters
SQL> select dbms_random.string('U', 20) str from dual;
STR
_______________________
VUOQOSTLHCKIPIADIZTD
E.g.: Generating a lower case string of 20 characters
SQL> select dbms_random.string('L', 20) str from dual;
STR
____________________
xpoovuspmehvcptdtzcz
E.g.: Generating an alphanumeric string of 20 characters. There is a bug in Oracle 8i that results in special (non-alphanumeric) characters such as ']' in the string. This is resolved in Oracle 9i.
SQL> select dbms_random.string('A', 20) str from dual;
STR
__________________
sTjERojjL^OlTaIc]PLB
E.g.: Generating an upper case alphanumeric string of 20 characters
SQL> select dbms_random.string('X', 20) str from dual;
STR
________________________
SQ3E3B3NRBIP:GOGAKSC
E.g.: Generating a string of printable 20 characters. This will output a string of all characters that could possibly be printed.
SQL> select dbms_random.string('P', 20) str from dual;
STR
___________________
*Yw>IKzsj\uI8K[IQPag
E.g.: Example for calling the dbms_random package and setting the seed for generating the same set of random numbers in different sessions. Please note that the same random numbers are generated in different sessions. Though I have found this to work on most accounts, in some cases, the first number generated was different in different sessions and the remaining were same. I recommend not using this option in any of production code until it is properly document by Oracle.
jaJA>declare
2 l_num number;
3 begin
4 l_num := dbms_random.random;
5 dbms_output.put_line(l_num);
6 dbms_random.seed('amar testing 67890');
7 l_num := dbms_random.random;
8 dbms_output.put_line(l_num);
9 end;
10 /
483791552
478774329
PL/SQL procedure successfully completed.
[oracle][lock]mechanism
锁是一种机制，是用来管理对一个共享资源的并行访问。
解释： 只有对资源进行并行访问时才会用到锁，但单个户模式下的数据库是不需要锁的。
所以锁的目的就是在多个用户并行访问数据库时为了保证数据的一致和准确性采用的一种保护机制。
几乎所有的数据库都采用了锁的机制，但其实现机制却有很大的却别。
Oracle在锁的处理上较SYBASE和informix有着明显的优势，特别是在行级锁的处理上，基本上算是完美。
封锁问题：
一个典型的程序设计缺陷例子。
A:用户1检索到一行数据，并准备修改。
B:用户2检索到相同的数据。
C:用户2删除了这一行，并提交。
D:用户修改那一行，并提交，结果程序报错，该行不存在。
用户抱怨开发商，开发商认为是用户人为因素造成。
分析：
当用户1通过程序从数据库中提取出自己想要修改的记录时，该记录并没有锁定，此时用户2用同样的条件检索到该行，并删除，提交导致该行不复存在，此时用户1对检索到的行修改后提交时，由于该行已经被删除，导致用户操作失败。从而对软件失去信心。
解决方法：
在对该行进行修改之前，对该行锁定，防止其他人对该行进行dml操作。
select * from tab for update nowait;
此时数据库将在该行上建立一个行级排它锁，直到等到一个commit或者rollback时才释放。
悲观封锁
锁在用户修改之前就发挥作用：
Select ..for update（nowait)
Select * from tab1 for update
用户发出这条命令之后，oracle将会对返回集中的数据建立行级封锁，以防止其他用户的修改。
如果此时其他用户对上面返回结果集的数据进行dml或ddl操作都会返回一个错误信息或发生阻塞。
1：对返回结果集进行update或delete操作会发生阻塞。
2：对该表进行ddl操作将会报：
Ora-00054:resource busy and acquire with nowait specified.
原因分析
此时Oracle已经对返回的结果集上加了排它的行级锁，所有其他对这些数据进行的修改或删除操作都必须等待这个锁的释放，产生的外在现象就是其他的操作将发生阻塞，这个这个操作commit或rollback.
同样这个查询的事务将会对该表加表级锁，不允许对该表的任何ddl操作，否则将会报出ora-00054错误：
:resource busy and acquire with nowait specified.
乐观封锁
乐观的认为数据在select出来到update进取并提交的这段时间数据不会被更改。
这里面有一种潜在的危险就是由于被选出的结果集并没有被锁定，是存在一种可能被其他用户更改的可能。
因此Oracle仍然建议是用悲观封锁，因为这样会更安全。
阻塞
定义：
当一个会话保持另一个会话正在请求的资源上的锁定时，就会发生阻塞。
被阻塞的会话将一直挂起，直到持有锁的会话放弃锁定的资源为止。
4个常见的dml语句会产生阻塞
INSERT
UPDATE
DELETE
SELECT…FOR UPDATE
INSERT
Insert发生阻塞的唯一情况就是用户拥有一个建有主键约束的表。
当2个的会话同时试图向表中插入相同的数据时，其中的一个会话将被阻塞，直到另外一个会话提交或会滚。
一个会话提交时，另一个会话将收到主键重复的错误。回滚时，被阻塞的会话将继续执行。
UPDATE 和DELETE
当执行Update和delete操作的数据行已经被另外的会话锁定时，将会发生阻塞，直到另一个会话提交或会滚。
Select …for update
当一个用户发出select..for update的错作准备对返回的结果集进行修改时，如果结果集已经被另一个会话锁定，就是发生阻塞。需要等另一个会话结束之后才可继续执行。
可以通过发出select… for update nowait的语句来避免发生阻塞，如果资源已经被另一个会话锁定，则会返回以下错误：
Ora-00054:resource busy and acquire with nowait specified.
死锁-deadlock
定义:当两个用户希望持有对方的资源时就会发生死锁.
即两个用户互相等待对方释放资源时,oracle认定为产生了死锁,在这种情况下,将以牺牲一个用户作为代价,另一个用户继续执行,牺牲的用户的事务将回滚.
例子：
1：用户1对A表进行Update，没有提交。
2：用户2对B表进行Update，没有提交。
此时双反不存在资源共享的问题。
3：如果用户2此时对A表作update,则会发生阻塞，需要等到用户一的事物结束。
4：如果此时用户1又对B表作update，则产生死锁。此时Oracle会选择其中一个用户进行会滚，使另一个用户继续执行操作。
起因
Oracle的死锁问题实际上很少见，如果发生，基本上都是不正确的程序设计造成的，经过调整后，基本上都会避免死锁的发生。
锁的分类
按封锁的对象可以分成：
DML lock
DDL lock
DML LOCK
DML锁是用来保护数据在被并行访问时数据的安全和一致性，按照限制级别的高低依次可以分成以下几种：
1：ROWLOCK(TX)
DML：insert，update，delete ,select …for update.
这些操作会在所操作的行上建立排它锁，直到所在的事物提交或会滚才释放。
2：table-lock(TM )
当一个事物在进行DML操作时，所在的事务会对所操作的表加表级锁，以保证在这个事务过程中表不被改变。
表级锁按照限制等级可以分成以下几类：
3：RS
Row share table lock
以下的操作会产生RS lock
Select * from tab for update.
LOCK TABLE table IN ROW SHARE MODE;
RS不允许的操作
LOCK TABLE table IN EXCLUSIVE MODE;
4：RX
Row Exclusive Table Locks
以下的操作会产生RS lock
1:INSERT,UPDATE ,DELETE
2:LOCK TABLE table IN ROW EXCLUSIVE MODE;
RX不允许的操作
LOCK TABLE table IN SHARE MODE;
LOCK TABLE table IN SHARE EXCLUSIVE MODE;
LOCK TABLE table IN EXCLUSIVE MODE;
5：S
Share Table Locks
以下操作产生S lock.
LOCK TABLE table IN SHARE MODE;
S不允许的操作
LOCK TABLE table IN SHARE ROW EXCLUSIVE MODE;
LOCK TABLE table IN EXCLUSIVE MODE;
LOCK TABLE table IN ROW EXCLUSIVE MODE;
SRX
Share Row Exclusive Table Locks
以下操作产生SRX LOCK
LOCK TABLE table IN SHARE ROW EXCLUSIVE MODE;
SRX不允许的操作.
LOCK TABLE table IN SHARE MODE;
LOCK TABLE table IN SHARE ROW EXCLUSIVE MODE;
LOCK TABLE table IN ROW EXCLUSIVE MODE;
LOCK TABLE table IN EXCLUSIVE MODE;
X
Exclusive Table Locks
一下操作产生X LOCK
LOCK TABLE table IN EXCLUSIVE MODE;
X不允许的操作
不允许所有的操作
分类: ( oracle ) :: 评论 (7) :: 静态链接网址 :: 引用 (0)
关于锁的例子 [回复]
在上面举的需要锁定一条记录来防止别人删除的例子并不恰当. 在这个例子中, 如果某人开始更新一条记录,这时利用oracle的select ... for update 来锁定这条记录,如果发生这种情况,后果会更糟:
某人开始更新一条记录, 这条记录被锁定; 然后这个人发现不急着修改, 就在没提交修改的情况下离开机器回家休假一周. 然后其它人发现这条记录需要立即进行一次修改, 却发现修改失败, 因为这条记录处于锁定状态.
正确的程序执行顺序应该是: 某人A想要修改这条记录, 应用程序提示这条记录被B锁定, 修改失败. 然后提示用户, 是否要强行修改.
这个逻辑应该做在应用程序中, 不是数据库中
Mike老狼 | 24/09/2006, 11:19
好文章 [回复]
海是无边无际的,朋友是QQ116362628
hsetfxn | 27/10/2006, 18:34
吸尘器qtr [回复]
你们家里的地毯很脏吗?那么用吸尘器来帮助你.保证使用过吸尘器后,你们家一点灰尘都没有.现在大家可以放心把垃圾扔在地毯上拉,因为有吸尘器这样的工具,再也不怕了,所以,大家放心用吸尘器吧,不会对人体带来任何伤害.QQ:yfi
owmcfqf | 17/11/2006, 04:04
吸尘器xkm [回复]
你们家里的地毯很脏吗?那么用吸尘器来帮助你.保证使用过吸尘器后,你们家一点灰尘都没有.现在大家可以放心把垃圾扔在地毯上拉,因为有吸尘器这样的工具,再也不怕了,所以,大家放心用吸尘器吧,不会对人体带来任何伤害.QQ:cig
owmcsne | 17/11/2006, 04:12
eixahjn uwroa [回复]
zpjbd nhkbfe yqur nrxejs ptowjrl dajrske dekr
utpzwvid okam | 05/03/2007, 17:09
eixahjn uwroa [回复]
zpjbd nhkbfe yqur nrxejs ptowjrl dajrske dekr
utpzwvid okam | 05/03/2007, 17:11
楼主对锁的级别分类有问题 [回复]
RS,RX,S,SRX,X 之类是对TM级别的分类，不能把它们
和TX,TM 混在一块。估计楼主对此理解还不够，
不知道ORACLE为什么要设计这样的锁类型。
解释： 只有对资源进行并行访问时才会用到锁，但单个户模式下的数据库是不需要锁的。
所以锁的目的就是在多个用户并行访问数据库时为了保证数据的一致和准确性采用的一种保护机制。
几乎所有的数据库都采用了锁的机制，但其实现机制却有很大的却别。
Oracle在锁的处理上较SYBASE和informix有着明显的优势，特别是在行级锁的处理上，基本上算是完美。
封锁问题：
一个典型的程序设计缺陷例子。
A:用户1检索到一行数据，并准备修改。
B:用户2检索到相同的数据。
C:用户2删除了这一行，并提交。
D:用户修改那一行，并提交，结果程序报错，该行不存在。
用户抱怨开发商，开发商认为是用户人为因素造成。
分析：
当用户1通过程序从数据库中提取出自己想要修改的记录时，该记录并没有锁定，此时用户2用同样的条件检索到该行，并删除，提交导致该行不复存在，此时用户1对检索到的行修改后提交时，由于该行已经被删除，导致用户操作失败。从而对软件失去信心。
解决方法：
在对该行进行修改之前，对该行锁定，防止其他人对该行进行dml操作。
select * from tab for update nowait;
此时数据库将在该行上建立一个行级排它锁，直到等到一个commit或者rollback时才释放。
悲观封锁
锁在用户修改之前就发挥作用：
Select ..for update（nowait)
Select * from tab1 for update
用户发出这条命令之后，oracle将会对返回集中的数据建立行级封锁，以防止其他用户的修改。
如果此时其他用户对上面返回结果集的数据进行dml或ddl操作都会返回一个错误信息或发生阻塞。
1：对返回结果集进行update或delete操作会发生阻塞。
2：对该表进行ddl操作将会报：
Ora-00054:resource busy and acquire with nowait specified.
原因分析
此时Oracle已经对返回的结果集上加了排它的行级锁，所有其他对这些数据进行的修改或删除操作都必须等待这个锁的释放，产生的外在现象就是其他的操作将发生阻塞，这个这个操作commit或rollback.
同样这个查询的事务将会对该表加表级锁，不允许对该表的任何ddl操作，否则将会报出ora-00054错误：
:resource busy and acquire with nowait specified.
乐观封锁
乐观的认为数据在select出来到update进取并提交的这段时间数据不会被更改。
这里面有一种潜在的危险就是由于被选出的结果集并没有被锁定，是存在一种可能被其他用户更改的可能。
因此Oracle仍然建议是用悲观封锁，因为这样会更安全。
阻塞
定义：
当一个会话保持另一个会话正在请求的资源上的锁定时，就会发生阻塞。
被阻塞的会话将一直挂起，直到持有锁的会话放弃锁定的资源为止。
4个常见的dml语句会产生阻塞
INSERT
UPDATE
DELETE
SELECT…FOR UPDATE
INSERT
Insert发生阻塞的唯一情况就是用户拥有一个建有主键约束的表。
当2个的会话同时试图向表中插入相同的数据时，其中的一个会话将被阻塞，直到另外一个会话提交或会滚。
一个会话提交时，另一个会话将收到主键重复的错误。回滚时，被阻塞的会话将继续执行。
UPDATE 和DELETE
当执行Update和delete操作的数据行已经被另外的会话锁定时，将会发生阻塞，直到另一个会话提交或会滚。
Select …for update
当一个用户发出select..for update的错作准备对返回的结果集进行修改时，如果结果集已经被另一个会话锁定，就是发生阻塞。需要等另一个会话结束之后才可继续执行。
可以通过发出select… for update nowait的语句来避免发生阻塞，如果资源已经被另一个会话锁定，则会返回以下错误：
Ora-00054:resource busy and acquire with nowait specified.
死锁-deadlock
定义:当两个用户希望持有对方的资源时就会发生死锁.
即两个用户互相等待对方释放资源时,oracle认定为产生了死锁,在这种情况下,将以牺牲一个用户作为代价,另一个用户继续执行,牺牲的用户的事务将回滚.
例子：
1：用户1对A表进行Update，没有提交。
2：用户2对B表进行Update，没有提交。
此时双反不存在资源共享的问题。
3：如果用户2此时对A表作update,则会发生阻塞，需要等到用户一的事物结束。
4：如果此时用户1又对B表作update，则产生死锁。此时Oracle会选择其中一个用户进行会滚，使另一个用户继续执行操作。
起因
Oracle的死锁问题实际上很少见，如果发生，基本上都是不正确的程序设计造成的，经过调整后，基本上都会避免死锁的发生。
锁的分类
按封锁的对象可以分成：
DML lock
DDL lock
DML LOCK
DML锁是用来保护数据在被并行访问时数据的安全和一致性，按照限制级别的高低依次可以分成以下几种：
1：ROWLOCK(TX)
DML：insert，update，delete ,select …for update.
这些操作会在所操作的行上建立排它锁，直到所在的事物提交或会滚才释放。
2：table-lock(TM )
当一个事物在进行DML操作时，所在的事务会对所操作的表加表级锁，以保证在这个事务过程中表不被改变。
表级锁按照限制等级可以分成以下几类：
3：RS
Row share table lock
以下的操作会产生RS lock
Select * from tab for update.
LOCK TABLE table IN ROW SHARE MODE;
RS不允许的操作
LOCK TABLE table IN EXCLUSIVE MODE;
4：RX
Row Exclusive Table Locks
以下的操作会产生RS lock
1:INSERT,UPDATE ,DELETE
2:LOCK TABLE table IN ROW EXCLUSIVE MODE;
RX不允许的操作
LOCK TABLE table IN SHARE MODE;
LOCK TABLE table IN SHARE EXCLUSIVE MODE;
LOCK TABLE table IN EXCLUSIVE MODE;
5：S
Share Table Locks
以下操作产生S lock.
LOCK TABLE table IN SHARE MODE;
S不允许的操作
LOCK TABLE table IN SHARE ROW EXCLUSIVE MODE;
LOCK TABLE table IN EXCLUSIVE MODE;
LOCK TABLE table IN ROW EXCLUSIVE MODE;
SRX
Share Row Exclusive Table Locks
以下操作产生SRX LOCK
LOCK TABLE table IN SHARE ROW EXCLUSIVE MODE;
SRX不允许的操作.
LOCK TABLE table IN SHARE MODE;
LOCK TABLE table IN SHARE ROW EXCLUSIVE MODE;
LOCK TABLE table IN ROW EXCLUSIVE MODE;
LOCK TABLE table IN EXCLUSIVE MODE;
X
Exclusive Table Locks
一下操作产生X LOCK
LOCK TABLE table IN EXCLUSIVE MODE;
X不允许的操作
不允许所有的操作
分类: ( oracle ) :: 评论 (7) :: 静态链接网址 :: 引用 (0)
关于锁的例子 [回复]
在上面举的需要锁定一条记录来防止别人删除的例子并不恰当. 在这个例子中, 如果某人开始更新一条记录,这时利用oracle的select ... for update 来锁定这条记录,如果发生这种情况,后果会更糟:
某人开始更新一条记录, 这条记录被锁定; 然后这个人发现不急着修改, 就在没提交修改的情况下离开机器回家休假一周. 然后其它人发现这条记录需要立即进行一次修改, 却发现修改失败, 因为这条记录处于锁定状态.
正确的程序执行顺序应该是: 某人A想要修改这条记录, 应用程序提示这条记录被B锁定, 修改失败. 然后提示用户, 是否要强行修改.
这个逻辑应该做在应用程序中, 不是数据库中
Mike老狼 | 24/09/2006, 11:19
好文章 [回复]
海是无边无际的,朋友是QQ116362628
hsetfxn | 27/10/2006, 18:34
吸尘器qtr [回复]
你们家里的地毯很脏吗?那么用吸尘器来帮助你.保证使用过吸尘器后,你们家一点灰尘都没有.现在大家可以放心把垃圾扔在地毯上拉,因为有吸尘器这样的工具,再也不怕了,所以,大家放心用吸尘器吧,不会对人体带来任何伤害.QQ:yfi
owmcfqf | 17/11/2006, 04:04
吸尘器xkm [回复]
你们家里的地毯很脏吗?那么用吸尘器来帮助你.保证使用过吸尘器后,你们家一点灰尘都没有.现在大家可以放心把垃圾扔在地毯上拉,因为有吸尘器这样的工具,再也不怕了,所以,大家放心用吸尘器吧,不会对人体带来任何伤害.QQ:cig
owmcsne | 17/11/2006, 04:12
eixahjn uwroa [回复]
zpjbd nhkbfe yqur nrxejs ptowjrl dajrske dekr
utpzwvid okam | 05/03/2007, 17:09
eixahjn uwroa [回复]
zpjbd nhkbfe yqur nrxejs ptowjrl dajrske dekr
utpzwvid okam | 05/03/2007, 17:11
楼主对锁的级别分类有问题 [回复]
RS,RX,S,SRX,X 之类是对TM级别的分类，不能把它们
和TX,TM 混在一块。估计楼主对此理解还不够，
不知道ORACLE为什么要设计这样的锁类型。
enable remote desktop with regedit
［注意］
レジストリに不正な値を書き込んでしまうと、システムに重大な障害を及ぼし、最悪の場合、システムの再インストールを余儀なくされることもあります。レジストリ エディタの操作は慎重に行うとともに、あくまで御自分のリスクで設定を行ってください。何らかの障害が発生した場合でも、本Windows Server Insider編集部では責任を負いかねます。ご了承ください。
レジストリ・エディタによる操作
リモート・デスクトップ接続を許可するためのレジストリは以下のとおりである。
キー名 HKEY_LOCAL_MACHINEのSYSTEM\CurrentControlSet\Control\Terminal Server
名前 fDenyTSConnections
型 DWORD
値 1 なら許可しない（デフォルト値）／0なら許可する
レジストリに不正な値を書き込んでしまうと、システムに重大な障害を及ぼし、最悪の場合、システムの再インストールを余儀なくされることもあります。レジストリ エディタの操作は慎重に行うとともに、あくまで御自分のリスクで設定を行ってください。何らかの障害が発生した場合でも、本Windows Server Insider編集部では責任を負いかねます。ご了承ください。
レジストリ・エディタによる操作
リモート・デスクトップ接続を許可するためのレジストリは以下のとおりである。
キー名 HKEY_LOCAL_MACHINEのSYSTEM\CurrentControlSet\Control\Terminal Server
名前 fDenyTSConnections
型 DWORD
値 1 なら許可しない（デフォルト値）／0なら許可する
2007/06/10
the minimal readable expression of information
"The minimal readable expression of information" is my thought of the morden infromation soceity. There is much information and too limited time for people understand and process the information.
The main field of this major:
read
understand
convert
match
sort
archive
input
The main field of this major:
read
understand
convert
match
sort
archive
input
2007/06/08
Fw Oracle JDBC Logging using java.util.logging
Oracle JDBC Logging using java.util.logging
An Oracle White Paper
June 2006
Oracle JDBC Logging using java.util.logging
INTRODUCTION..........................................................................................3
ENABLING AND USING JDBC LOGGING.........................................3
Configure the classpath................................................................................3
Enable Logging.............................................................................................3
Configue Logging.........................................................................................4
Advanced Configuration..............................................................................5
Using Loggers................................................................................................6
A Detailed Example.....................................................................................6
CONCLUSION................................................................................................8
Oracle JDBC Logging using java.util.logging Page 2
Oracle JDBC Logging using java.util.logging
INTRODUCTION
The Oracle JDBC drivers use two different mechanisms to generate log output. Versions of the JDBC drivers for older versions of Java, 1.2 and 1.3, use a proprietary mechanism. Versions of the JDBC drivers for newer versions of Java, 1.4 and later, use the Java standard logging mechanism, java.util.logging This note describes how to use java.util.logging with the Oracle JDBC drivers.
ENABLING AND USING JDBC LOGGING
Configure the classpath
Oracle ships several jar files for each version of the drivers. The optimized jar files to do not contain any logging code. There will be no Oracle JDBC log output when using the optimized jar files. To get any log output you must use the debug jar files. These files are indicated with a _g in the file name. (This comes from the -g option to the Java compiler, which is used when compiling these jars.) At the time of writing the only jar files that contain code for java.util.logging are ojdbc14_g.jar and ojdbc14dms_g.jar.
Step 1: Make sure that a debug jar (ojdbc14_g.jar) is the only Oracle JDBC jar file in your classpath.
Enable Logging
In order to get any log output from the Oracle JDBC drivers you must enable logging. There is a global switch that turns logging on and off. When it is off, the drivers will not produce any log output. When it is on, what logging is produced is controlled by the configuration of java.util.logging. There are two ways to enable the global logging switch, programmatically or setting a Java system property. You can use the programmatic way to control what parts of your program generate log output. If you cannot or do not want to change the source, you can set the Java system property to enable logging for the entire program execution.
Step 2a: globally enable logging by setting the oracle.jdbc.Trace system property
java -Doracle.jdbc.Trace=true ...
OR
Oracle JDBC Logging using java.util.logging Page 3
Step 2b: programmatically enable logging by modifying your code to enable/disable logging
oracle.jdbc.driver.OracleLog.setTrace(true); // enable logging ... oracle.jdbc.driver.OracleLog.setTrace(false); // disable logging
If this is all you do you will get minimal logging of serious errors written to the console. Usually this is less that useful. In order to generate more and probably more useful output, you must configure java.util.logging.
Configue Logging
java.util.logging is a very rich and powerful tool. Describing all the things you can do with it is beyond the scope of this note. This note provides a basic set of tools that will let you generate useful log output. For more complex configurations look at the JavaDoc for java.util.logging.
You can configure java.util.logging either programmatically or via a configuration file. For the most part there is little need to configure it programmatically. You can turn Oracle JDBC logging on and off programmatically using OracleLog.setTrace. In most cases there is no need to change the configuration during the course of execution. This note will only cover configuration files. If you must use programmatic configuration, information in the rest of this note should help you figure out what values to use when configuring java.util.logging programmatically.
One place to look for configuration information is the OracleLog.properties file in the demo directory of your JDBC installation. This file contains basic information on how to configure java.util.logging and provides some initial settings that you can start with. In order to use a configuration file you must identify that file to the Java runtime. You tell Java about your file by setting a system property. You can use both java.util.logging.config.file and oracle.jdbc.Trace at the same time.
Step 3: java -Djava.util.logging.config.file=/jdbc/demo/OracleLog.properties -Doracle.jdbc.Trace=true ...
This will use the default OracleLog.properties file (assuming it is reachable from /jdbc/demo). That may get you the output you want, or it may not. The rest of this note will show you how to create your own config file and in the process help you understand how to modify the sample OracleLog.properties file.
Step 4: create a file, for example myConfig.properties, and insert the following.
level=SEVERE oracle.jdbc.level=ALL oracle.jdbc.handlers=java.util.logging.ConsoleHandler java.util.logging.ConsoleHandler.level=ALL
Oracle JDBC Logging using java.util.logging Page 4
java.util.logging.ConsoleHandler.formatter = java.util.logging.SimpleFormatter
Save this file and execute the Java command above replacing OracleLog.properties with myConfig.properties. This will produce a huge amount of output, so make sure your program execution is very short.
Advanced Configuration
This gives us a working setup that traces everything to the console. Let's modify the configuration file to dump everything to a file instead. Instead of using the ConsoleHandle, use the FileHandler.
.level=SEVERE oracle.jdbc.level=ALL oracle.jdbc.handlers=java.util.logging.FileHandler java.util.logging.FileHandler.level=ALL java.util.logging.FileHandler.pattern = jdbc.log java.util.logging.FileHandler.count = 1 java.util.logging.FileHandler.formatter = java.util.logging.SimpleFormatter
This will generate exactly the same log output, but instead send it to a file named jdbc.log in the current directory.
Step 5: You will want to reduce the amount of detail. You control the level of detail by changing the level settings. The defined levels from least detail to most are
OFF SEVERE WARNING INFO CONFIG FINE FINER FINEST ALL
In order to reduce the amount of detail, change java.util.logging.FileHandler.level from ALL to CONFIG
java.util.logging.FileHandler.level=CONFIG
It is not necessary to change the level of the oracle.jdbc logger although you can. Setting the FileHandler level will control what log messages end up in the log file.
Oracle JDBC Logging using java.util.logging Page 5
Using Loggers
Setting the level as above reduces all the logging output from JDBC. Sometimes we want to see a lot of output from one part of the code and very little from other parts. To do that you must understand more about loggers.
Loggers exist in a tree structure defined by their names. The root logger is named "", the empty String. If you look at the first line of the configuration file you see '.level=SEVERE'. This is setting the level of the root logger. The next line is 'oracle.jdbc.level=ALL'. This sets the level of the logger named 'oracle.jdbc'. The oracle.jdbc logger is a member of the logger tree. Its parent is named 'oracle'. The parent of the oracle logger is the root logger. Logging messages are sent to a particular logger, for example oracle.jdbc. If the message passes the level check at that level the message is passed to the handler at that level, if any, and to the parent logger. So a log message sent to oracle.log is compared against that logger's level, CONFIG if you are following along. If the level is the same or less (less detailed) then it is sent to the FileHandler and to the parent logger, 'oracle'. Again it is checked against the level. If as in this case, the level is not set then it uses the parent level, SEVERE. If the message level is the same or less it is passed to the handler, which there isn't one, and sent to the parent. In this case the parent in the root logger.
All this tree stuff didn't help you reduce the amount of output. What will help is that the JDBC drivers use several subloggers. If you restrict the log messages to one of the subloggers you will get substantially less output. The loggers used by the Oracle JDBC drivers include
oracle.jdbc almost all Oracle JDBC messages oracle.jdbc.driver the core driver code oracle.jdbc.pool DataSources and Connection pooling oracle.jdbc.rowset RowSets oracle.jdbc.xa distributed transaction support oracle.sql complex SQL data types
The drivers may use other loggers as well. That will vary from release to release.
A Detailed Example
Suppose you want to trace what is happening in the oracle.sql component, but you also want to capture some basic information about the rest of the driver. This is a more complex use of logging. Here is the config file.
# # set levels # 1 level=SEVERE 2 oracle.level=ALL 3 oracle.jdbc.driver.level=CONFIG
Oracle JDBC Logging using java.util.logging Page 6
4 oracle.jdbc.pool.level=OFF 5 oracle.jdbc.util.level=OFF 6 oracle.sql.level=FINE # # Config handlers # 7 oracle.handlers=java.util.logging.ConsoleHandler 8 java.util.logging.ConsoleHandler.level=ALL 9 java.util.logging.ConsoleHandler.formatter = \ java.util.logging.SimpleFormatter
Let's consider what each line is doing.
1 Set the root logger to SEVERE. We don't want to see any logging from other, non-Oracle components unless something fails badly, so we set the default level for all loggers to SEVERE. Each logger inherits its level from its parent unless set explicitly. By setting the root logger to SEVERE we insure that all other loggers inherit that level except for the ones we set otherwise.
2 We want output from both the oracle.sql and oracle.jdbc.driver loggers. Their common ancestor is oracle, so we set the level there to ALL. We will control the detail more explicitly at lower levels.
3 We only want to see the SQL execution from oracle.jdbc.driver so we set that to CONFIG. This is a fairly low volume level but will allow us to keep track of what our test is doing.
4 We are using a DataSource in our test and don't want to see all of that logging so we turn it OFF.
5 Similarly we don't want to see the logging from the oracle.jdbc.util package. If we were using XA or rowsets we would turn them off as well.
6 We want to see what is happening in oracle.sql so we set oracle.sql.level to FINE. This provides a lot of information about the public method calls without overwhelming detail.
7 We are going to dump everything to stderr. When we run the test we will redirect stderr to a file.
8 We want to dump everything to the console which is System.err. We are doing the filtering with the loggers rather than the Handler this time.
9 We will use a simple, more or less human readable format. Another choice is XMLFormatter. XMLFormatter is the best choice for logs that you send to Oracle Support because it permits us to more easily use automated processing of the log output.
When you run your test with this config file you will get an XML document that contains moderately detailed information from the oracle.sql package, a little bit of information from the core driver code and nothing from any other code.
Oracle JDBC Logging using java.util.logging Page 7
CONCLUSION
This paper shows you all the tools you will need for most purposes. java.util.logging is a powerful tool with lots of switches and knobs. You can send different parts of the log stream to different places and write custom filters that pick out exactly the log messages you want to see. These advanced uses are beyond the scope of this note. The basic tools described above should cover most of your needs and will give you a head start in learning the more advanced techniques. The best source for more information about java.util.logging is the JavaDoc.
One final note. The Oracle JDBC logging code varies dramatically from release to release. We are constantly striving to make it more useful and more maintainable. Older releases don't always do exactly what we or you would like them to do. This note describes a philosophy rather than exactly what will happen in any given release. The techniques described here should get you something close to what you want, even if not exactly. Experiment. This note describes how we want logging to work and each subsequent release should be closer to this ideal. I hope this helps.
An Oracle White Paper
June 2006
Oracle JDBC Logging using java.util.logging
INTRODUCTION..........................................................................................3
ENABLING AND USING JDBC LOGGING.........................................3
Configure the classpath................................................................................3
Enable Logging.............................................................................................3
Configue Logging.........................................................................................4
Advanced Configuration..............................................................................5
Using Loggers................................................................................................6
A Detailed Example.....................................................................................6
CONCLUSION................................................................................................8
Oracle JDBC Logging using java.util.logging Page 2
Oracle JDBC Logging using java.util.logging
INTRODUCTION
The Oracle JDBC drivers use two different mechanisms to generate log output. Versions of the JDBC drivers for older versions of Java, 1.2 and 1.3, use a proprietary mechanism. Versions of the JDBC drivers for newer versions of Java, 1.4 and later, use the Java standard logging mechanism, java.util.logging This note describes how to use java.util.logging with the Oracle JDBC drivers.
ENABLING AND USING JDBC LOGGING
Configure the classpath
Oracle ships several jar files for each version of the drivers. The optimized jar files to do not contain any logging code. There will be no Oracle JDBC log output when using the optimized jar files. To get any log output you must use the debug jar files. These files are indicated with a _g in the file name. (This comes from the -g option to the Java compiler, which is used when compiling these jars.) At the time of writing the only jar files that contain code for java.util.logging are ojdbc14_g.jar and ojdbc14dms_g.jar.
Step 1: Make sure that a debug jar (ojdbc14_g.jar) is the only Oracle JDBC jar file in your classpath.
Enable Logging
In order to get any log output from the Oracle JDBC drivers you must enable logging. There is a global switch that turns logging on and off. When it is off, the drivers will not produce any log output. When it is on, what logging is produced is controlled by the configuration of java.util.logging. There are two ways to enable the global logging switch, programmatically or setting a Java system property. You can use the programmatic way to control what parts of your program generate log output. If you cannot or do not want to change the source, you can set the Java system property to enable logging for the entire program execution.
Step 2a: globally enable logging by setting the oracle.jdbc.Trace system property
java -Doracle.jdbc.Trace=true ...
OR
Oracle JDBC Logging using java.util.logging Page 3
Step 2b: programmatically enable logging by modifying your code to enable/disable logging
oracle.jdbc.driver.OracleLog.setTrace(true); // enable logging ... oracle.jdbc.driver.OracleLog.setTrace(false); // disable logging
If this is all you do you will get minimal logging of serious errors written to the console. Usually this is less that useful. In order to generate more and probably more useful output, you must configure java.util.logging.
Configue Logging
java.util.logging is a very rich and powerful tool. Describing all the things you can do with it is beyond the scope of this note. This note provides a basic set of tools that will let you generate useful log output. For more complex configurations look at the JavaDoc for java.util.logging.
You can configure java.util.logging either programmatically or via a configuration file. For the most part there is little need to configure it programmatically. You can turn Oracle JDBC logging on and off programmatically using OracleLog.setTrace. In most cases there is no need to change the configuration during the course of execution. This note will only cover configuration files. If you must use programmatic configuration, information in the rest of this note should help you figure out what values to use when configuring java.util.logging programmatically.
One place to look for configuration information is the OracleLog.properties file in the demo directory of your JDBC installation. This file contains basic information on how to configure java.util.logging and provides some initial settings that you can start with. In order to use a configuration file you must identify that file to the Java runtime. You tell Java about your file by setting a system property. You can use both java.util.logging.config.file and oracle.jdbc.Trace at the same time.
Step 3: java -Djava.util.logging.config.file=/jdbc/demo/OracleLog.properties -Doracle.jdbc.Trace=true ...
This will use the default OracleLog.properties file (assuming it is reachable from /jdbc/demo). That may get you the output you want, or it may not. The rest of this note will show you how to create your own config file and in the process help you understand how to modify the sample OracleLog.properties file.
Step 4: create a file, for example myConfig.properties, and insert the following.
level=SEVERE oracle.jdbc.level=ALL oracle.jdbc.handlers=java.util.logging.ConsoleHandler java.util.logging.ConsoleHandler.level=ALL
Oracle JDBC Logging using java.util.logging Page 4
java.util.logging.ConsoleHandler.formatter = java.util.logging.SimpleFormatter
Save this file and execute the Java command above replacing OracleLog.properties with myConfig.properties. This will produce a huge amount of output, so make sure your program execution is very short.
Advanced Configuration
This gives us a working setup that traces everything to the console. Let's modify the configuration file to dump everything to a file instead. Instead of using the ConsoleHandle, use the FileHandler.
.level=SEVERE oracle.jdbc.level=ALL oracle.jdbc.handlers=java.util.logging.FileHandler java.util.logging.FileHandler.level=ALL java.util.logging.FileHandler.pattern = jdbc.log java.util.logging.FileHandler.count = 1 java.util.logging.FileHandler.formatter = java.util.logging.SimpleFormatter
This will generate exactly the same log output, but instead send it to a file named jdbc.log in the current directory.
Step 5: You will want to reduce the amount of detail. You control the level of detail by changing the level settings. The defined levels from least detail to most are
OFF SEVERE WARNING INFO CONFIG FINE FINER FINEST ALL
In order to reduce the amount of detail, change java.util.logging.FileHandler.level from ALL to CONFIG
java.util.logging.FileHandler.level=CONFIG
It is not necessary to change the level of the oracle.jdbc logger although you can. Setting the FileHandler level will control what log messages end up in the log file.
Oracle JDBC Logging using java.util.logging Page 5
Using Loggers
Setting the level as above reduces all the logging output from JDBC. Sometimes we want to see a lot of output from one part of the code and very little from other parts. To do that you must understand more about loggers.
Loggers exist in a tree structure defined by their names. The root logger is named "", the empty String. If you look at the first line of the configuration file you see '.level=SEVERE'. This is setting the level of the root logger. The next line is 'oracle.jdbc.level=ALL'. This sets the level of the logger named 'oracle.jdbc'. The oracle.jdbc logger is a member of the logger tree. Its parent is named 'oracle'. The parent of the oracle logger is the root logger. Logging messages are sent to a particular logger, for example oracle.jdbc. If the message passes the level check at that level the message is passed to the handler at that level, if any, and to the parent logger. So a log message sent to oracle.log is compared against that logger's level, CONFIG if you are following along. If the level is the same or less (less detailed) then it is sent to the FileHandler and to the parent logger, 'oracle'. Again it is checked against the level. If as in this case, the level is not set then it uses the parent level, SEVERE. If the message level is the same or less it is passed to the handler, which there isn't one, and sent to the parent. In this case the parent in the root logger.
All this tree stuff didn't help you reduce the amount of output. What will help is that the JDBC drivers use several subloggers. If you restrict the log messages to one of the subloggers you will get substantially less output. The loggers used by the Oracle JDBC drivers include
oracle.jdbc almost all Oracle JDBC messages oracle.jdbc.driver the core driver code oracle.jdbc.pool DataSources and Connection pooling oracle.jdbc.rowset RowSets oracle.jdbc.xa distributed transaction support oracle.sql complex SQL data types
The drivers may use other loggers as well. That will vary from release to release.
A Detailed Example
Suppose you want to trace what is happening in the oracle.sql component, but you also want to capture some basic information about the rest of the driver. This is a more complex use of logging. Here is the config file.
# # set levels # 1 level=SEVERE 2 oracle.level=ALL 3 oracle.jdbc.driver.level=CONFIG
Oracle JDBC Logging using java.util.logging Page 6
4 oracle.jdbc.pool.level=OFF 5 oracle.jdbc.util.level=OFF 6 oracle.sql.level=FINE # # Config handlers # 7 oracle.handlers=java.util.logging.ConsoleHandler 8 java.util.logging.ConsoleHandler.level=ALL 9 java.util.logging.ConsoleHandler.formatter = \ java.util.logging.SimpleFormatter
Let's consider what each line is doing.
1 Set the root logger to SEVERE. We don't want to see any logging from other, non-Oracle components unless something fails badly, so we set the default level for all loggers to SEVERE. Each logger inherits its level from its parent unless set explicitly. By setting the root logger to SEVERE we insure that all other loggers inherit that level except for the ones we set otherwise.
2 We want output from both the oracle.sql and oracle.jdbc.driver loggers. Their common ancestor is oracle, so we set the level there to ALL. We will control the detail more explicitly at lower levels.
3 We only want to see the SQL execution from oracle.jdbc.driver so we set that to CONFIG. This is a fairly low volume level but will allow us to keep track of what our test is doing.
4 We are using a DataSource in our test and don't want to see all of that logging so we turn it OFF.
5 Similarly we don't want to see the logging from the oracle.jdbc.util package. If we were using XA or rowsets we would turn them off as well.
6 We want to see what is happening in oracle.sql so we set oracle.sql.level to FINE. This provides a lot of information about the public method calls without overwhelming detail.
7 We are going to dump everything to stderr. When we run the test we will redirect stderr to a file.
8 We want to dump everything to the console which is System.err. We are doing the filtering with the loggers rather than the Handler this time.
9 We will use a simple, more or less human readable format. Another choice is XMLFormatter. XMLFormatter is the best choice for logs that you send to Oracle Support because it permits us to more easily use automated processing of the log output.
When you run your test with this config file you will get an XML document that contains moderately detailed information from the oracle.sql package, a little bit of information from the core driver code and nothing from any other code.
Oracle JDBC Logging using java.util.logging Page 7
CONCLUSION
This paper shows you all the tools you will need for most purposes. java.util.logging is a powerful tool with lots of switches and knobs. You can send different parts of the log stream to different places and write custom filters that pick out exactly the log messages you want to see. These advanced uses are beyond the scope of this note. The basic tools described above should cover most of your needs and will give you a head start in learning the more advanced techniques. The best source for more information about java.util.logging is the JavaDoc.
One final note. The Oracle JDBC logging code varies dramatically from release to release. We are constantly striving to make it more useful and more maintainable. Older releases don't always do exactly what we or you would like them to do. This note describes a philosophy rather than exactly what will happen in any given release. The techniques described here should get you something close to what you want, even if not exactly. Experiment. This note describes how we want logging to work and each subsequent release should be closer to this ideal. I hope this helps.
ActiveWidgets
ActiveWidgets also provides a open source version as ActiveUI project
http://sourceforge.net/projects/activeui/
http://sourceforge.net/projects/activeui/
2007/06/07
oracle rman recover under nocatalog mode and without controlfile (DBMS_BACKUP_RESTORE)
dbmsbkrs.sql - dbms backup restore
prvtbkrs.plb - prvt backup restore
deviceAllocate (type, name, ident, noio, params) return varchar2
restoreControlfileTo
restoreDataFileTo
the guide line of nocatalog (controlfile) rman backup:
+ should backup controlfile independently and increase CONTROL_FILE_RECORD_KEEP_TIME to make the entries kept as long as possible
+ should make a complete backup plan
+ should be familiar to rman facility
prvtbkrs.plb - prvt backup restore
deviceAllocate (type, name, ident, noio, params) return varchar2
restoreControlfileTo
restoreDataFileTo
# backup databases
rman target / nocatalog << __EOF__
run {
allocate channel C1 type disk;
backup full tag 'FullBackup' format 'd:KDE%d_%u_%s_%p.dbf' database include current controlfile;
sql 'alter system archive log current';
release channel C1;
}
__EOF__
# simulate a database corruption
sqlplus "/ as sysdba" << __EOF__
shutdown immediate;
__EOF__
rm $ORADATA/$ORACLE_SID/*.dbf
rm $ORADATA/$ORACLE_SID/*.ora
# restore controlfile
sqlplus "/ as sysdba" << __EOF__
startup force nomount;
DECLARE
devtype varchar2(256);
done boolean;
BEGIN
devtype:=sys.dbms_backup_restore.deviceAllocate(
type=>'', -- can also be 'sbt_tape'
ident=>'T1');
sys.dbms_backup_restore.restoreSetDatafile;
sys.dbms_backup_restore.restoreControlfileTo(cfname=>'d:\oracle\Control01.ctl');
sys.dbms_backup_restore.restoreBackupPiece(
done=>done,
handle=>'D:\KDE\DEMO_01FR79OT_1_1.DBF',
params=>null);
sys.dbms_backup_restore.deviceDeallocate;
END;
/
__EOF__
# restore datafile
sqlplus "/ as sysdba" << __EOF__
DECLARE
devtype varchar2(256);
done boolean;
BEGIN
DECLARE
devtype varchar2(256);
done boolean;
BEGIN
devtype:=sys.dbms_backup_restore.deviceAllocate (type=>'',
ident=>'t1');
sys.dbms_backup_restore.restoreSetDatafile;
sys.dbms_backup_restore.restoreDatafileTo(dfnumber=>01,
toname=>'d:oracleoradatademoSYSTEM01.DBF');
sys.dbms_backup_restore.restoreDatafileTo(dfnumber=>02,
toname=>'d:oracleoradatademoUNDOTBS01.DBF');
sys.dbms_backup_restore.restoreDatafileTo(dfnumber=>03,
toname=>'d:oracleoradatademoDRSYS01.DBF');
sys.dbms_backup_restore.restoreDatafileTo(dfnumber=>04,
toname=>'d:oracleoradatademoEXAMPLE01.DBF');
sys.dbms_backup_restore.restoreDatafileTo(dfnumber=>05,
toname=>'d:oracleoradatademoINDX01.DBF');
sys.dbms_backup_restore.restoreDatafileTo(dfnumber=>06,
toname=>'d:oracleoradatademoODM01.DBF');
sys.dbms_backup_restore.restoreDatafileTo(dfnumber=>07,
toname=>'d:oracleoradatademoTOOLS01.DBF');
sys.dbms_backup_restore.restoreDatafileTo(dfnumber=>08,
toname=>'d:oracleoradatademoUSERS01.DBF');
sys.dbms_backup_restore.restoreDatafileTo(dfnumber=>09,
toname=>'d:oracleoradatademoXDB01.DBF');
sys.dbms_backup_restore.restoreBackupPiece(done=>done,
handle=>'D:KDEDEMO_01FR79OT_1_1.DBF',
params=>null);
sys.dbms_backup_restore.deviceDeallocate;
END;
/
__EOF__
# startup and recovery
sqlplus "/ as sysdba" << __EOF__
startup force mount;
recover database using backup controlfile until cancel;
-- the following message is prompted and answer should be entered manually
-- Specify log: {=suggested | filename | AUTO | CANCEL}
-- D:KDEARC00002.001
__EOF__
# reset logs and open the database
sqlplus "/ as sysdba" << __EOF__
alter database open resetlogs;
__EOF__
the guide line of nocatalog (controlfile) rman backup:
+ should backup controlfile independently and increase CONTROL_FILE_RECORD_KEEP_TIME to make the entries kept as long as possible
+ should make a complete backup plan
+ should be familiar to rman facility
2007/06/06
ChizuMaga - Microsoft Japan Map Service Magzine
Though it supports whole country search and display on the map, but it cannot run on my favoriate opera...
2007/06/04
compare the difference of columns between 2 tables
(SELECT
TABLE_NAME,
COLUMN_NAME,
DATA_TYPE,
DATA_TYPE_MOD,
DATA_TYPE_OWNER,
DATA_LENGTH,
DATA_PRECISION,
DATA_SCALE,
NULLABLE,
COLUMN_ID,
DEFAULT_LENGTH,
NUM_DISTINCT,
LOW_VALUE,
HIGH_VALUE,
DENSITY,
NUM_NULLS,
NUM_BUCKETS,
LAST_ANALYZED,
SAMPLE_SIZE,
CHARACTER_SET_NAME,
CHAR_COL_DECL_LENGTH,
GLOBAL_STATS,
USER_STATS,
AVG_COL_LEN,
CHAR_LENGTH,
CHAR_USED,
V80_FMT_IMAGE,
DATA_UPGRADED
FROM USER_TAB_COLUMNS WHERE TABLE_NAME = 'M01'
MINUS
SELECT
TABLE_NAME,
COLUMN_NAME,
DATA_TYPE,
DATA_TYPE_MOD,
DATA_TYPE_OWNER,
DATA_LENGTH,
DATA_PRECISION,
DATA_SCALE,
NULLABLE,
COLUMN_ID,
DEFAULT_LENGTH,
NUM_DISTINCT,
LOW_VALUE,
HIGH_VALUE,
DENSITY,
NUM_NULLS,
NUM_BUCKETS,
LAST_ANALYZED,
SAMPLE_SIZE,
CHARACTER_SET_NAME,
CHAR_COL_DECL_LENGTH,
GLOBAL_STATS,
USER_STATS,
AVG_COL_LEN,
CHAR_LENGTH,
CHAR_USED,
V80_FMT_IMAGE,
DATA_UPGRADED
FROM USER_TAB_COLUMNS WHERE TABLE_NAME = 'M02')
UNION
(SELECT
TABLE_NAME,
COLUMN_NAME,
DATA_TYPE,
DATA_TYPE_MOD,
DATA_TYPE_OWNER,
DATA_LENGTH,
DATA_PRECISION,
DATA_SCALE,
NULLABLE,
COLUMN_ID,
DEFAULT_LENGTH,
NUM_DISTINCT,
LOW_VALUE,
HIGH_VALUE,
DENSITY,
NUM_NULLS,
NUM_BUCKETS,
LAST_ANALYZED,
SAMPLE_SIZE,
CHARACTER_SET_NAME,
CHAR_COL_DECL_LENGTH,
GLOBAL_STATS,
USER_STATS,
AVG_COL_LEN,
CHAR_LENGTH,
CHAR_USED,
V80_FMT_IMAGE,
DATA_UPGRADED
FROM USER_TAB_COLUMNS WHERE TABLE_NAME = 'M02'
MINUS
SELECT
TABLE_NAME,
COLUMN_NAME,
DATA_TYPE,
DATA_TYPE_MOD,
DATA_TYPE_OWNER,
DATA_LENGTH,
DATA_PRECISION,
DATA_SCALE,
NULLABLE,
COLUMN_ID,
DEFAULT_LENGTH,
NUM_DISTINCT,
LOW_VALUE,
HIGH_VALUE,
DENSITY,
NUM_NULLS,
NUM_BUCKETS,
LAST_ANALYZED,
SAMPLE_SIZE,
CHARACTER_SET_NAME,
CHAR_COL_DECL_LENGTH,
GLOBAL_STATS,
USER_STATS,
AVG_COL_LEN,
CHAR_LENGTH,
CHAR_USED,
V80_FMT_IMAGE,
DATA_UPGRADED
FROM USER_TAB_COLUMNS WHERE TABLE_NAME = 'M01')
TABLE_NAME,
COLUMN_NAME,
DATA_TYPE,
DATA_TYPE_MOD,
DATA_TYPE_OWNER,
DATA_LENGTH,
DATA_PRECISION,
DATA_SCALE,
NULLABLE,
COLUMN_ID,
DEFAULT_LENGTH,
NUM_DISTINCT,
LOW_VALUE,
HIGH_VALUE,
DENSITY,
NUM_NULLS,
NUM_BUCKETS,
LAST_ANALYZED,
SAMPLE_SIZE,
CHARACTER_SET_NAME,
CHAR_COL_DECL_LENGTH,
GLOBAL_STATS,
USER_STATS,
AVG_COL_LEN,
CHAR_LENGTH,
CHAR_USED,
V80_FMT_IMAGE,
DATA_UPGRADED
FROM USER_TAB_COLUMNS WHERE TABLE_NAME = 'M01'
MINUS
SELECT
TABLE_NAME,
COLUMN_NAME,
DATA_TYPE,
DATA_TYPE_MOD,
DATA_TYPE_OWNER,
DATA_LENGTH,
DATA_PRECISION,
DATA_SCALE,
NULLABLE,
COLUMN_ID,
DEFAULT_LENGTH,
NUM_DISTINCT,
LOW_VALUE,
HIGH_VALUE,
DENSITY,
NUM_NULLS,
NUM_BUCKETS,
LAST_ANALYZED,
SAMPLE_SIZE,
CHARACTER_SET_NAME,
CHAR_COL_DECL_LENGTH,
GLOBAL_STATS,
USER_STATS,
AVG_COL_LEN,
CHAR_LENGTH,
CHAR_USED,
V80_FMT_IMAGE,
DATA_UPGRADED
FROM USER_TAB_COLUMNS WHERE TABLE_NAME = 'M02')
UNION
(SELECT
TABLE_NAME,
COLUMN_NAME,
DATA_TYPE,
DATA_TYPE_MOD,
DATA_TYPE_OWNER,
DATA_LENGTH,
DATA_PRECISION,
DATA_SCALE,
NULLABLE,
COLUMN_ID,
DEFAULT_LENGTH,
NUM_DISTINCT,
LOW_VALUE,
HIGH_VALUE,
DENSITY,
NUM_NULLS,
NUM_BUCKETS,
LAST_ANALYZED,
SAMPLE_SIZE,
CHARACTER_SET_NAME,
CHAR_COL_DECL_LENGTH,
GLOBAL_STATS,
USER_STATS,
AVG_COL_LEN,
CHAR_LENGTH,
CHAR_USED,
V80_FMT_IMAGE,
DATA_UPGRADED
FROM USER_TAB_COLUMNS WHERE TABLE_NAME = 'M02'
MINUS
SELECT
TABLE_NAME,
COLUMN_NAME,
DATA_TYPE,
DATA_TYPE_MOD,
DATA_TYPE_OWNER,
DATA_LENGTH,
DATA_PRECISION,
DATA_SCALE,
NULLABLE,
COLUMN_ID,
DEFAULT_LENGTH,
NUM_DISTINCT,
LOW_VALUE,
HIGH_VALUE,
DENSITY,
NUM_NULLS,
NUM_BUCKETS,
LAST_ANALYZED,
SAMPLE_SIZE,
CHARACTER_SET_NAME,
CHAR_COL_DECL_LENGTH,
GLOBAL_STATS,
USER_STATS,
AVG_COL_LEN,
CHAR_LENGTH,
CHAR_USED,
V80_FMT_IMAGE,
DATA_UPGRADED
FROM USER_TAB_COLUMNS WHERE TABLE_NAME = 'M01')
free video composer and converter - Zwei-Stein Video Editor
Zwei-Stein Video Editor
http://www.zs4.net/
http://www.thugsatbay.com/tab/?q=
http://www.zs4.net/
http://www.thugsatbay.com/tab/?q=
2007/06/01
recent read articles
http://java.ccidnet.com/art/297/20070516/1082605_1.html
http://searchwebservices.techtarget.com.cn/tips/444/3347444.shtml
http://www.stackasterisk.jp/tech/systemConstruction/openVpn01_01.jsp
http://searchwebservices.techtarget.com.cn/tips/444/3347444.shtml
http://www.stackasterisk.jp/tech/systemConstruction/openVpn01_01.jsp
Subscribe to:
Posts (Atom)