Bu Blogda Ara

13 Aralık 2012 Perşembe

Avoid getting ORA-1652: unable to extend temp segment with lob data


When session process with lob_data and get area of temp tablespace as temp lob segments. Session will not release temp lob segment area in temp tablespace until session logoff.

For solution:

  • In Oracle Metalink, How to Release the Temp LOB Space and Avoid Hitting ORA-1652 [Metalink ID 802897.1]) ,  When session logoff, temp lob segment area release in temp tablespace.
  • In Oracle Metalink, (How to Release Temporary LOB Segments without Closing the JDBC Connection [Metalink ID 1384829.1])  , After temp lob segment process, If you use DBMS_LOB.FREETEMPORARY, temp area wil release.
  • Also there are some action in java code. You can reuse same temp lob segment area.


12 Aralık 2012 Çarşamba

Disable "select for update" and "lock table" for read Only users in Oracle




Readonly users could dml_lock until Oracle 11gR2 version. This problem has not solved yet.
  • Created users and granted only select on tables, readonly users could dml_locks on tables with "select for update" and "lock table". Let's test:


SQL> create table table1 (col1 varchar2(10));
Tablo yaratıldı.

SQL> insert into table1 values ('muratkar');
1 satır yaratıldı.
SQL> commit;
Kaydetme tamamlandı.

SQL> grant create session to readuser identified by readuser;
Erişim Yetkisi verme başarılı.

SQL> grant select on table1 to readuser;
Erişim Yetkisi verme başarılı.

SQL> connect readuser/readuser@dbatest
Bağlandı.

SQL> select * from mkar.table1 for update;
COL1
----------
muratkar

SQL> lock table mkar.table1 in exclusive mode;
Tablo(lar) Kilitli.

SQL> rollback;
Geri alma tamamlandı.


  • This is potential risk about security. Malicious employees use readonly accounts and wreak havoc in 24*7 OLTP system. In this case, DBAs takes action and kill sessions which have unnecessary dml_lock.
  • Solution of lock table is simple.Disable table lock. But Before doing truncate process, lock table enable.


SQL> alter table table1 disable table lock;
Tablo değiştirildi.
SQL> connect readuser/readuser@dbatest
Bağlandı.
SQL> lock table mkar.table1 in exclusive mode;
lock table mkar.table1 in exclusive mode
*
1 satırında HATA:
ORA-00069: kilitleme yapılamıyor -- tablo kilitleri TABLE1 için yok edilmiş



  • There is no exact solution for "select for update". You can create view for table and grant select just view, not table. Another way, logon trigger can set for transaction read only.