Hi,
here's my initialization
as sysdba
CREATE TABLESPACE test DATAFILE '/u02/app/oracle2/oradata/ORCLSE/test.db' SIZE 6M AUTOEXTEND ON;
as sysdba
CREATE user test IDENTIFIED BY test DEFAULT TABLESPACE test TEMPORARY TABLESPACE temp QUOTA UNLIMITED ON test;
as sysdba
GRANT
Create SESSION,
Alter SESSION,
Create TABLE
TO
limited_role;
GRANT limited_role TO test;
as test
--insert test data
create table test(a int primary key);
insert into test (a) VALUES (1);
insert into test (a) VALUES (2);
insert into test (a) VALUES (3);
insert into test (a) VALUES (4);
insert into test (a) VALUES (5);
insert into test (a) VALUES (6);
insert into test (a) VALUES (7);
insert into test (a) VALUES (8);
COMMIT;
here's what happen in the standard edition session 1
TEST@ORCLSE>SELECT sys_context('userenv','sid') FROM dual;
SYS_CONTEXT('USERENV','SID')
----------------------------------------------------------
13
TEST@ORCLSE>update test set a=9 where a=1;
1 row updated.
TEST@ORCLSE>
session 2 in standard edition
TEST@ORCLSE>SELECT sys_context('userenv','sid') from dual;
SYS_CONTEXT('USERENV','SID')
-------------------------------------------------------------
71
TEST@ORCLSE>update test set a=10 where a=1;
standard edition as sysdba
SYS@ORCLSE>ed
Wrote file afiedt.buf
1 SELECT distinct a.sql_id ,a.inst_id,a.blocking_session,a.blocking_session_
serial#,a.user_id,s.sql_text,a.module
2 FROM GV$ACTIVE_SESSION_HISTORY a ,gv$sql s
3 where a.sql_id=s.sql_id
4 and blocking_session is not null
5 and a.user_id <> 0
6* and a.sample_time > sysdate - 30/24/60
SYS@ORCLSE>/
no rows selected
SYS@ORCLSE>SELECT COUNT(*) FROM GV$ACTIVE_SESSION_HISTORY;
COUNT(*)
----------
0
SYS@ORCLSE>SELECT * FROM v$version;
BANNER
-----------------------------------------------------------
Oracle Database 11g Release 11.2.0.1.0 - 64bit Production
PL/SQL Release 11.2.0.1.0 - Production
CORE 11.2.0.1.0 Production
TNS for Linux: Version 11.2.0.1.0 - Production
NLSRTL Version 11.2.0.1.0 - Production
as can be observed no historical locks can be seem.
but however if I'm using Enterprise Edition, historical locks can be retrieved
enterprise edition session 1
TEST@ORCLEE>SELECT sys_context('userenv','sid') FROM dual;
SYS_CONTEXT('USERENV','SID')
----------------------------------------------------------
134
TEST@ORCLEE>update test set a=9 where a=1;
1 row updated.
TEST@ORCLEE>
enterprise edition session 2
TEST@ORCLEE>SELECT sys_context('userenv','sid') from dual;
SYS_CONTEXT('USERENV','SID')
----------------------------------------------------------
193
TEST@ORCLEE>update test set a=10 where a=1;
enterprise edition as sysdba
SYS@ORCLEE>SELECT * FROM v$version;
BANNER
--------------------------------------------------------------------------------
Oracle Database 11g Enterprise Edition Release 11.2.0.1.0 - 64bit Production
PL/SQL Release 11.2.0.1.0 - Production
CORE 11.2.0.1.0 Production
TNS for Linux: Version 11.2.0.1.0 - Production
NLSRTL Version 11.2.0.1.0 - Production
SYS@ORCLEE>ed
Wrote file afiedt.buf
1 SELECT distinct a.sql_id ,a.inst_id,a.blocking_session,a.blocking_session_
serial#,a.user_id,s.sql_text,a.module
2 FROM GV$ACTIVE_SESSION_HISTORY a ,gv$sql s
3 where a.sql_id=s.sql_id
4 and blocking_session is not null
5 and a.user_id <> 0
6* and a.sample_time > sysdate - 30/24/60
SYS@ORCLEE>/
SQL_ID INST_ID BLOCKING_SESSION BLOCKING_SESSION_SERIAL# USER_ID
------------- ---------- ---------------- ------------------------ ----------
SQL_TEXT
--------------------------------------------------------------------------------
MODULE
------------------------------------------------
bgaaky9fxur65 1 134 1058 98
update test set a=10 where a=1
SQL*Plus
SYS@ORCLEE>
i.e. there's record in GV$ACTIVE_SESSION_HISTORY.
from http://docs.oracle.com/cd/E11882_01/license.112/e47877/editions.htm#DBLIC116 and http://docs.oracle.com/cd/E11882_01/server.112/e25513/dynviews_1007.htm#REFRN30299
no where does it says that Standard edition cannot track historical data.
so is there a way to check for historical lock in Standard Edition? If yes would love to know how to retrieve historical lock in Standard Edition.
thanks a lot!