Hi,
Just a precision, there is no lock, not even short at the end.
The highest TM lock is mode=2 and that's since 11g:
SQL> create table DEMO as select * from dual;
Table DEMO created.
SQL> column object_id new_value object_id
SQL> select object_name,to_char(object_id,'FM0XXXXXXX') object_id from
user_objects where object_name='DEMO' and object_type='TABLE';
OBJECT_NAME OBJECT_ID
----------- ---------
DEMO 00011E13
SQL> alter session set events='trace[ksq] disk medium'
tracefile_identifier='10704';
Session altered.
SQL> create index DEMO on DEMO(dummy) online;
Index DEMO created.
SQL> alter session set events='trace[ksq] off';
Session altered.
SQL> host mv $ORACLE_BASE/diag/rdbms/cdb1a/CDB1/trace/*10704*trc last.trc
SQL> host grep &object_id last.trc
2017-10-16 20:33:15.227*:ksq.c@9033:ksqgtlctx(): ***
TM-00011E13-00000000-8A782FDE-00000000 mode=2 flags=0x401 why=167
timeout=21474836 ***
2017-10-16 20:33:15.232*:ksq.c@9033:ksqgtlctx(): ***
DL-00011E13-00000000-8A782FDE-00000000 mode=3 flags=0x10001 why=211
timeout=0 ***
2017-10-16 20:33:15.232*:ksq.c@9033:ksqgtlctx(): ***
DL-00011E13-00000000-8A782FDE-00000000 mode=3 flags=0x10001 why=211
timeout=0 ***
2017-10-16 20:33:15.234*:ksq.c@9033:ksqgtlctx(): ***
OD-00011E13-00000000-8A782FDE-00000000 mode=4 flags=0x10401 why=269
timeout=0 ***
Regards,
Franck.
On Mon, Oct 16, 2017 at 5:10 PM Eriovaldo Andrietta <ecandrietta@xxxxxxxxx>
wrote:
Tks Stefan.
2017-10-16 12:54 GMT-02:00 Stefan Knecht <knecht.stefan@xxxxxxxxx>:
Have a look at the ONLINE option of the CREATE INDEX statement.
It does need a (usually brief) lock, but only at the very end, with its
length depending on how much activity is on the underlying table.
https://docs.oracle.com/database/121/SQLRF/statements_5013.htm
On Mon, Oct 16, 2017 at 9:36 PM, Eriovaldo Andrietta <
ecandrietta@xxxxxxxxx> wrote:
Hi,
I am using :
Oracle Database 12c Enterprise Edition Release 12.1.0.2.0 - 64bit
Production 0
PL/SQL Release 12.1.0.2.0 -
Production 0
CORE 12.1.0.2.0
Production 0
TNS for IBM/AIX RISC System/6000: Version 12.1.0.2.0 -
Production 0
NLSRTL Version 12.1.0.2.0 -
Production 0
Executed in 1,249 seconds
I need to create a index in the production environmnent , but I cannot
stop the application and also cannot lock the index while it is being
created.
Is there a way to do it ?
Something like, create and rebuild it after creation.
Regards
Eriovaldo
--
//
zztat - The Next-Gen Oracle Performance Monitoring and Reaction Framework!
Visit us at zztat.net | Support our Indiegogo campaign at igg.me/at/zztat
| @zztat_oracle
