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なら許可する
Subscribe to:
Posts (Atom)