Re: How to create index without lock it while is creating

From: Franck Pachot <franck_at_pachot.net>
Date: Mon, 16 Oct 2017 18:39:39 +0000
Message-ID: <CAK6ito2uqx4HkQG+penv+obgQnQOhvVbXp8TmSgk6MpYFx5jQw_at_mail.gmail.com>



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_at_9033:ksqgtlctx(): *** TM-00011E13-00000000-8A782FDE-00000000 mode=2 flags=0x401 why=167 timeout=21474836 ***
2017-10-16 20:33:15.232*:ksq.c_at_9033:ksqgtlctx(): *** DL-00011E13-00000000-8A782FDE-00000000 mode=3 flags=0x10001 why=211 timeout=0 ***
2017-10-16 20:33:15.232*:ksq.c_at_9033:ksqgtlctx(): *** DL-00011E13-00000000-8A782FDE-00000000 mode=3 flags=0x10001 why=211 timeout=0 ***
2017-10-16 20:33:15.234*:ksq.c_at_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_at_gmail.com> wrote:

> Tks Stefan.
>
>
> 2017-10-16 12:54 GMT-02:00 Stefan Knecht <knecht.stefan_at_gmail.com>:
>
>> 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_at_gmail.com> 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
>> | _at_zztat_oracle
>>
>
>

--
http://www.freelists.org/webpage/oracle-l
Received on Mon Oct 16 2017 - 20:39:39 CEST

Original text of this message