How To Indentify The Row Which is Locked By an Other User's Session

Subject: How To Indentify The Row Which is Locked By an Other User's Session
Doc ID: Note:198150.1 Type: TROUBLESHOOTING
Last Revision Date: 18-MAY-2004 Status: PUBLISHED



goal: Identify the row which is locked by an other user's session
fact: Oracle Server - Enterprise Edition



fix:

To determine which row is locked in a certain table can only be queried when an
other user is waiting for the row involved.

To find the row, 3 queries must be executed:

1) This query will display some information about the object and user involved.
The OBJECT_ID value returned must be used in the second query. The
OBJECT_NAME is used in the last query. The queries must be executed as a DBA
user.

sql> select a.os_user_name,
2 a.oracle_username,
3 a.object_id,
4 c.object_name,
5 c.object_type
6 from v$locked_object a, dba_objects c
7 where a.object_id=c.object_id
8 /

2) This query will return the ROW_WAIT_FILE#, ROW_WAIT_BLOCK# and ROW_WAIT_ROW#
values for the OBJECT_ID involved. The ROW_WAIT_FILE#, ROW_WAIT_BLOCK# and
ROW_WAIT_ROW# will be needed in the last query. If this query returns no
rows, no user is waiting for this row.

sql> select sid,
2 row_wait_obj# objn,
3 row_wait_file# fn,
4 row_wait_block# bn,
5 row_wait_row# rn
6 from v$session
7 where row_wait_obj#=
8 /

3) The final query will return the row which is locked an the user is waiting
for. The OBJN, FN, BN and RN values from the second query and OBJECT_NAME
from the first query need to be subsituted in this query.

sql> select * from
2 where rowid = dbms_rowid.rowid_create(1, , , , )
3 /

bind variables

var mybind number;
execute :mybind:=100
print :mybind

bat, batch job

@echo off
set /p seq=sequence#:
echo spool D:\work\%date:~0,4%%date:~5,2%%date:~8,2%%seq%.txt>>D:\work\%date:~0,4%%date:~5,2%%date:~8,2%%seq%.sql
echo.>>D:\work\%date:~0,4%%date:~5,2%%date:~8,2%%seq%.sql
echo.>>D:\work\%date:~0,4%%date:~5,2%%date:~8,2%%seq%.sql
echo.>>D:\work\%date:~0,4%%date:~5,2%%date:~8,2%%seq%.sql
echo spool off;>>D:\work\%date:~0,4%%date:~5,2%%date:~8,2%%seq%.sql
start notepad D:\work\%date:~0,4%%date:~5,2%%date:~8,2%%seq%.sql
@echo on

DBMS_CRYPTO encryption 加密 AES

目前主流的加密方法大致有MD5、SHA-1、DES、Triple DES、AES，目前MD5、SHA-1、DES都已经有破解方法，Triple DES、AES还没有被破解的记载，其中AES是最新最安全的加密方法，最多可提供256位的加密。

Oracle 9i提供MD5、DES、Triple DES,而Oracle 10g和10g R2提供了上面提到的所有加密方法供选择。



鉴于以上情况，针对Oracle 10g，我的想法是使用DBMS_CRYPTO包提供的Triple DES或者AES进行加密（Oracle 9i中没有这个包）。

与其他方法相比，这样做的优点是：



1. Triple DES、AES是最安全的加密方法，没有被破解的记载

2. 有可逆和不可逆两种加密选择

3. 支持对BLOB和CLOB加密




1.脚本

-----------------------------------------------------------------------------------------

加密函数

create or replace function encrypt256(v_input varchar2) return raw

is

v_result raw(256);

c_key char(32) := '1q2wa3es4rz5tx6ycD*UF8tif9ogjen2';

begin

v_result := DBMS_CRYPTO.ENCRYPT(

UTL_I18N.STRING_TO_RAW(v_input, 'AL32UTF8'),

DBMS_CRYPTO.ENCRYPT_AES256+DBMS_CRYPTO.CHAIN_CBC+DBMS_CRYPTO.PAD_PKCS5,

UTL_I18N.STRING_TO_RAW(c_key, 'AL32UTF8'));

return v_result;

end;

/



解密函数

create or replace function decrypt256(v_input raw) return varchar2

is

v_result raw(256);

c_key char(32) := '1q2wa3es4rz5tx6ycD*UF8tif9ogjen2';

begin

v_result := DBMS_CRYPTO.DECRYPT(

v_input,

DBMS_CRYPTO.ENCRYPT_AES256+DBMS_CRYPTO.CHAIN_CBC+DBMS_CRYPTO.PAD_PKCS5,

UTL_I18N.STRING_TO_RAW(c_key, 'AL32UTF8'));

return UTL_I18N.RAW_TO_CHAR(v_result, 'AL32UTF8');

end;

/

-----------------------------------------------------------------------------------------



2.功能演示



SQL> insert into test values(1,encrypt256('一次成功')); <--用户插入或者修改数据时这样使用



已创建 1 行。



SQL> commit;



提交完成。



SQL> select * from test;



ID RAWS

---------- --------------------------------------------------

1 597B660AFD6CC3A90D064B00DF7EB80D <--数据已经被密文保存



SQL> select id,raws from test where raws=encrypt256('一次成功'); <--核对或者查找时这样使用



ID RAWS

---------- --------------------------------------------------

1 597B660AFD6CC3A90D064B00DF7EB80D



SQL> select id,decrypt256(raws) raws from test; <--解密方法和结果



ID RAWS

---------- --------------------------------------------------

1 一次成功





3. 加密对性能的影响

在我的测试中，逻辑读的量基本不变，扫描索引的耗时变为不加密时的10.8倍（66us --> 717us）。

INDEX RANGE SCAN YB_IND_TESTT_1 (cr=3 pr=0 pw=0 time=66 us)(object id 81639)

INDEX RANGE SCAN YB_IND_TESTT_2 (cr=3 pr=0 pw=0 time=717 us)(object id 81640)

这种耗时的增加与使用何种加密方式无关。



4. 对于脚本中密钥的保护

虽然密钥仍然是写死在脚本中，但是不用担心开发人员或者数据库管理人员会得到密钥，这里使用的方法是Oracle提供的wrap程序包，使用这个包可以将脚本以不可逆的方式加密。



例如：创建encrypt256()这个函数的脚本经过加密以后，会变成下面这样。

这样的脚本仍然可以创建函数，但没有人知道这个函数内容更没有人知道密钥是什么。

----------------------------------------------------------------------------

create or replace function encrypt256 wrapped

a000000

354

abcd

abcd

abcd

abcd

abcd

abcd

abcd

abcd

abcd

abcd

abcd

abcd

abcd

abcd

abcd

8

18b 15c

cSBfE65CYUS4abCCuiwqeQWAEZEwg/DIDK5qfC9Grfjqzqc2UfIyWpTjI7h2sFSyE4XQwwA7

LyfVV6mp8no/ANyNnp4A9SCHcrSaWUNnVD2mjcrSwg1WkEakxRGQY6SxBkD2b8xiO+tnKg8v

AVoHo6vqBfl2I4SJtjfHvhrte1k0+SPy1giZEp0MKyCcZA8pMwubQg9BmMUFIthkQXjQCklv

XRrIgkQkckLqk2iraDchjDcWFNJyV7qMqxd8ZSvVdWt5S0mISwurpXs4/yuTL5W1R4A/Z9Hw

0FII+JeAzKlRL1sFp5y0/wKhhl5rn5V2yjphQHv+r8dma0dyvPumJQEJ



/

----------------------------------------------------------------------------



5. 几点提示和注意事项

(1) 示例脚本中被加密的字段最大255字节因此需要开发人员在程序中判断长度；

这个长度限制根据需要可以调整到最大1999字节。

(2) BLOB和CLOB类型的数据也可以加密

(3) 由于使用了wrap方式保护密钥且这个方法不可逆，所以密钥必须妥善保存。

os authentication

In order for a database account to accept connections without a password the
database logon must be created with an 'IDENTIFIED EXTERNALLY' clause.

Eg: CREATE USER OSUSER_SCOTT IDENTIFIED EXTERNALLY;
This will allow SCOTT to 'CONNECT /'.
This will NOT allow SCOTT to connect as OSUSER_SCOTT in any other manner

CREATE USER OSUSER_SCOTT IDENTIFIED BY XYZ;
The user must supply the username AND password to connect as the
account has actually been created as requiring database authentication.
Ie: 'CONNECT /' will *NOT* work.
'CONNECT OSUSER_SCOTT/XYZ' will connect.

If the OS_AUTHENT_PREFIX is set to OPS$ a user can connect in either
manner if created with a password. This is more of a security risk
as there are 2 ways to access the database account.

Eg: CREATE USER OPS$SCOTT IDENTIFIED BY XYZ;
Allows the user to 'CONNECT /' or 'CONNECT OPS$SCOTT/XYZ'.

NOTE: on unix platforms, you need use sqlplus "OPS\$SCOTT/XYZ" to connect to oracle server.

The Priority of NLS Parameters Explained

Subject: The Priority of NLS Parameters Explained
Doc ID: Note:241047.1 Type: BULLETIN
Last Revision Date: 21-AUG-2007 Status: PUBLISHED


This note explains the order in which NLS parameters are taken into account
in the database client/server model using a standard client connection.
(This does NOT cover JDBC connections, please see Note 115001.1
NLS_LANG Client Settings and JDBC Drivers for more info on jdbc and NLS.)

There are 3 levels at which you can set NLS parameters: Database, Instance and
Session. If a parameter is defined at more than one level then the rules on
which one takes precedence are quite straighforward:
1. NLS database settings are overwritten by NLS instance settings
2. NLS database & NLS instance settings are overwritten by NLS session settings

In the remainder of this note we shall explain all the different settings in
detail. The categories A to C shown below indicate the order of precedence with
A being the highest and C the lowest.
For example, if you set NLS_NUMERIC_CHARACTERS in the init.ora (point B) and
in the environment (point A 6), then for a session the value defined in the
environment will take priority because point A 6 comes before point B.


A) The Session Parameters.
--------------------------

select * from NLS_SESSION_PARAMETERS;

These are the settings used for the current sql session.

These reflect (in this order):

1) The values of NLS parameters set by "alter session .... "

alter session set NLS_DATE_FORMAT = 'DD/MM/YYYY';

* this can also been done with an after logon trigger(!).
select OWNER, TRIGGER_NAME, TRIGGER_BODY from DBA_TRIGGERS where
trim(TRIGGERING_EVENT) = 'LOGON';
-> Note 251044.1 How to set a NLS session parameter at database
level for all sessions ?

2) If there are no explicit "alter sessions ..." statements done then it
reflects the setting of the corresponding NLS parameter on the client derived
from the NLS_LANG variable.

NLS_LANG consist of: NLS_LANG=_.
for example:
NLS_LANG=DUTCH_BELGIUM.WE8MSWIN1252

For information on how to find the NLS_LANG your sqlplus session is using
see point "4.2 How can I Check the Client's NLS_LANG Setting?" in Note 158577.1

3) If NLS_LANG is specified with only the part
then AMERICAN is used as default .

So if you set NLS_LANG=_BELGIUM.WE8PC850 then you get this:

PARAMETER VALUE
------------------------------ --------------
NLS_LANGUAGE AMERICAN
NLS_TERRITORY BELGIUM
NLS_CURRENCY
NLS_ISO_CURRENCY BELGIUM
....

Note the difference between NLS_LANG=_BELGIUM.WE8PC850 (correct) and
NLS_LANG=BELGIUM.WE8PC850 (incorrect), you need to set the "_" as separator.

4) If NLS_LANG is specified with only the part then the
defaults to a setting based on .

So if you set NLS_LANG=ITALIAN_.WE8PC850 then you get this:

PARAMETER VALUE
------------------------------ --------------
NLS_LANGUAGE ITALIAN
NLS_TERRITORY ITALY
NLS_CURRENCY
NLS_ISO_CURRENCY ITALY
.....

Note the difference between NLS_LANG=ITALIAN_.WE8PC850 (correct) and
NLS_LANG=ITALIAN.WE8PC850 (incorrect), you need to set the "_" as separator.

5) If NLS_LANG is specified without the _ part
then the _ part defaults to AMERICAN_AMERICA.

So if you set NLS_LANG=.WE8PC850 then you get this:

PARAMETER VALUE
------------------------------ ----------
NLS_LANGUAGE AMERICAN
NLS_TERRITORY AMERICA
NLS_CURRENCY $
NLS_ISO_CURRENCY AMERICA
....

Note the difference between NLS_LANG=.WE8PC850 (correct) and
NLS_LANG=WE8PC850 (incorrect), you need to set the "." as separator.

6) If the NLS_LANG is set (either like in point 3,4 or 5) then parameters like
NLS_SORT, NLS_DATE_FORMAT,... can be set as a "standalone" setting and will
overrule the defaults derived from NLS_LANG _ part.

So if you set NLS_LANG=AMERICAN_AMERICA.WE8PC850 and NLS_ISO_CURRENCY=FRANCE
then you get this:

PARAMETER VALUE
------------------------------ -----------
NLS_LANGUAGE AMERICAN
NLS_TERRITORY AMERICA
NLS_CURRENCY $
NLS_ISO_CURRENCY FRANCE
...

* Make sure that you set "NLS_ISO_CURRENCY=FRANCE", NLS_ISO_CURRENCY= FRANCE
(note the space) will not give an error but the parameter is just ignored
and the default based on NLS_TERRITORY will be used.


Defaults:
---------
* If NLS_DATE_LANGUAGE or NLS_SORT are not set then they are derived from
NLS_LANGUAGE.

* If NLS_CURRENCY, NLS_DUAL_CURRENCY, NLS_ISO_CURRENCY, NLS_DATE_FORMAT,
NLS_TIMESTAMP_FORMAT, NLS_TIMESTAMP_TZ_FORMAT, NLS_NUMERIC_CHARACTERS are
not set then they are derived from NLS_TERRITORY


7) If the NLS_LANG is not set at all, then it defaults to
_.US7ASCII and the values for the
_ part used are the ones found in
NLS_INSTANCE_PARAMETERS. Parameters like NLS_SORT defined as "standalone" on
the client side are ignored.

* Oracle does NOT recommend to have the NLS_LANG UNSET, please always define
at least the proper part for the NLS_LANG like shown
in point 5)
See Note 158577.1 NLS_LANG Explained (How does Client-Server Character Conversion Work?)
and Note 179133.1 The correct NLS_LANG in a Windows Environment

Note that:

* If set, client parameters (NLS_SESSION_PARAMETERS) take always precedence
above NLS_INSTANCE_PARAMETERS and NLS_DATABASE_PARAMETERS.

* This behavior can not be disabled on/from the server, so a parameter set
on the client always has precedence above an instance or database parameter.

* NLS_LANG cannot be changed by alter session, NLS_LANGUAGE and NLS_TERRITORY
can. However NLS_LANGUAGE and /or NLS_TERRITORY cannot be set as a "standalone" parameters
in the enviroment or registry on the client.

* NLS_SESSION_PARAMETERS is NOT visible for other sessions. If you need to trace
this then you have to use a logon trigger to create your own logging table
(based on session_parameters)

* The part of NLS_LANG is *NOT* shown in any system table
or view. (see section 4.2 How can I Check the Client's NLS_LANG Setting? in
Note 158577.1)

* On Windows you have two possible options, normally the NLS_LANG is set in
the registry, but it can also be set in the environment, however this is not
often done and genrally not recommended to do so.
The value in the environment takes precedence over the value in
the registry and is used for ALL Oracle_Homes on the server(!) if defined as
a system environment variable.
See Note 179133.1 The correct NLS_LANG in a Windows Environment

* NLS_COMP *cannot* be set as enviroment variable (unlike documented in the
manual). All Oracle8i and Oracle9 versions use NLS_COMP from INIT.ORA or
from explicit ALTER SESSION.
Bug 2155062 NLS_COMP cannot be set in the client ENVIRONMENT

* NLS_LENGTH_SEMANTICS *cannot* be set as enviroment variable in 9i,
from 10g onwards it can be, please note that it needs to be set as UPPERCASE.
It is however possible to do a ALTER SESSION.
If not set explicit in a session it will use the NLS_INSTANCE_PARAMETER setting.

* NLS_NCHAR_CONV_EXCP *cannot* be set as enviroment variable.
It is however possible to do a ALTER SESSION.

* NLS_LANGUAGE in the session parameters also declares the language for the
client error messages, see also Note 132090.1.

* you cannot "set" a NLS parameter in an SQL script, you need to use alter
session.

B) The Instance Parameters.
---------------------------

select * from NLS_INSTANCE_PARAMETERS;

These are the settings in the init.ora of the database at the moment that
the database was started or set trough ALTER SYSTEM.

If the parameter is not explicitly set in the init.ora / defined by
ALTER SYSTEM then it's value is NOT derived from a "higher" parameter

(=we are talking about parameters like ex. NLS_SORT who
derive a default from NLS_LANGUAGE in NLS_SESSION_PARAMETERS,
this is NOT the case for NLS_INSTANCE_PARAMETERS )

Note that:

* NLS_LANG is not a init.ora parameter, NLS_LANGUAGE and NLS_TERRITORY are.
so you need to set NLS_LANGUAGE and NLS_TERRITORY separated.

* you cannot define the or NLS_LANG in the init.ora
the clients characterset is defined by the NLS_LANG on client side (see above).

* you cannot define the database characterset in the init.ora.
The database characterset is defined by the "Create Database" command (see point c)).

* These settings take precedence above the NLS_DATABASE_PARAMETERS.

* These values are used for the NLS_SESSION_PARAMETERS if on the client the
NLS_LANG is NOT set (we strongly advice to set the NLS_LANG).

* Oracle *strongly* recommends that you set the NLS_LANG on the client at least to
NLS_LANG=.

* ALTER SYSTEM SET NLS_LENGTH_SEMANTICS does not change the SESSION parameters
(who take precedence) due to bug number 1488174 until the database is restarted.
however it can be set in the init.ora or Spfile -> see Note 144808.1

* ALTER SYSTEM SET NLS_NCHAR_CONV_EXCP does not change the SESSION parameters
(who take precedence).. workaround: use a init.ora parameter.

C) The Database Parameters.
---------------------------

select * from NLS_DATABASE_PARAMETERS;

These are always defaulting to american america if there were no parameters
explicitly set in the init.ora during database creation time (!).
If there were parameters set in the init.ora during database creation you see
them here. There is no way to change these after the database creation.
Do NOT update systemtables!
These settings are used to give the database a default if the INSTANCE and
SESSION parameters are not set.

Note that:

* NLS_LANG is not a init.ora parameter, NLS_LANGUAGE and NLS_TERRITORY are.
So you need to set NLS_LANGUAGE and NLS_TERRITORY separatly.

* These parameters are overruled by NLS_INSTANCE_PARAMETERS and
NLS_SESSION_PARAMETERS.

* you cannot define the or NLS_LANG in the init.ora
the clients characterset is defined by the NLS_LANG on client side (see above).

* you cannot define the database characterset in the init.ora.
The database (national) characterset (NLS_(NCHAR)_CHARACTERSET)
is defined by the "Create Database ..." command.

* The NLS_CHARACTERSET and NLS_NCHAR_CHARACTERSET parameters cannot be
overruled by instance or session parameters.

They are defined by the value specified in "create database ..." and are not
intended to be changed afterwards dynamically.
Do NOT update systemtables to change the characterset.
This will corrupt your database and potentialy it will by impossible to open
the database again.
See Note 225912.1 Changing the Database Character Set - an Overview
If you want to change the database characaterset. Don't mess with this if you
don't know what you are doing. Log a tar if any doubt.

* Setting the NLS_LANG during the creation of the database dous not influence
the NLS_DATABASE_PARAMETERS.

* The NLS_LANG set during the database creation has NO impact on the database
(national) Characterset.

* These settings are used in evaluation of CHECK constraints if TO_CHAR/TO_DATE
without a date format is used in the CHECK condition. Writing CHECK
constraints without explicit date formats is a bad habit, you should use
explicit formats and this setting becomes irrelevant.

Additional selects:
-------------------

A) select name,value$ from sys.props$ where name like '%NLS%';

This gives the same info as NLS_DATABASE_PARAMETERS.
You should use NLS_DATABASE_PARAMETERS instead of props$.
Note the UPPERCASE '%NLS%'

B) select * from v$nls_parameters;

A view that shows the current session parameters and
the *DATABASE* characterset as seen in the NLS_DATABASE_PARAMETERS view.

C) select name,value from v$parameter where name like '%nls%';

This gives the same info as NLS_INSTANCE_PARAMETERS.
Note the LOWERCASE '%nls%'

D) select userenv ('language') from dual;
and
select sys_context('userenv','language') from dual;

Both these select statements give the session's _
and the *DATABASE* character set. The database character set is not the
same as the character set of the NLS_LANG that you started this connection
with! So don't be fooled, although the output of this query looks like the
value of a NLS_LANG variable - it is NOT.

For more info on SYS_CONTEXT please see Note 120797.1

E) select userenv ('lang') from dual;

This select gives the short code that Oracle uses for the Language defined
by NLS_LANGUAGE setting for this session.
If NLS_LANGUAGE is set to French then this will return "F",
if NLS_LANGUAGE is set to English then this will return "GB"
If NLS_LANGUAGE is set to American then this will return "US", and so on...

F) show parameter NLS%

This will give the same as the NLS_INSTANCE_PARAMETERS

Remark:
-------

The part of NLS_LANG is *NOT* shown in any systemtable
or view (it is not known in the database). If you require to know the current
setting of a clients NLS_LANG then please see section 4.2 (How can I Check the
Client's NLS_LANG Setting?) of Note 158577.1



Related Documents:
------------------

Note 179133.1 The correct NLS_LANG in a Windows Environment
Note 158577.1 NLS_LANG Explained (How does Client-Server Character Conversion Work?)
Note 227331.1 Setting NLS Parameters - Frequently Asked Questions

Note 13978.1 NLS Sort Characteristics
Note 13882.1 Linguistic Sorting of Data in Oracle7 and Oracle8
Note 30557.1 NLS_DATE_FORMAT and a Default Century

Note 225912.1 Changing the Database Character Set - an Overview

Note 132090.1 How to get messages in your own language on MS Windows platform?
Note 120797.1 How to Determine Client IP-address,Language & Territory and Username for Current Session

Note 251044.1 How to set a NLS session parameter at database level for all sessions ?
Note 144808.1 Examples and limits of BYTE and CHAR semantics usage

Bug 2155062 NLS_COMP cannot be set in the client ENVIRONMENT

For further NLS / Globalization information you may start here:
Note 267942.1 Globalization Technology (NLS) Knowledge Browser
Note 60134.1 NLS Frequently Asked Questions

AIX / redhat linux: Percentage of memory currently used by the file cache

Percentage of memory currently used by the file cache
AIX
vmstat -vs |grep "numperm percentage"

redhat
vmstat

procs -----------memory---------- ---swap-- -----io---- --system-- ----cpu----
r b swpd free buff cache si so bi bo in cs us sy id wa
0 0 29772 37820 45952 3729772 0 0 9 127 3 51 1 0 95 5

cache: the amount of memory used as cache.

linux环境sqlplus中使用backspace键出现乱码的解决方法

linux环境sqlplus中使用backspace键出现乱码的解决方法

1. 要使用回删键（backspace）时，同时按住ctrl键

2. 设定环境变量

在bash下：$ stty erase ^H
或者把 stty erase ^H 添加到.bash_profile中。