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.

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.

Avoiding ORA-01555 errors in Oracle Active Dataguard

Queries on the standby database rely on undo generated on the primary to rollback uncommitted changes. The undo_retention period set at the primary will determine how soon undo can be overwritten and thus will also determine the length of the timing window that a standby query may avoid running into an ORA-01555 error. The occurrence of ORA-01555 is rare when the active standby is operating in realtime apply mode, because the standby recovery will keep pace with the primary.

An ORA-01555 is more likely to be encountered if real-time apply is not enabled, or in situations where a network disconnect or standby outage has resulted an archive log gap. In these cases the elapsed time to complete a redo log file at the primary database can be longer than the time spent by recovery to apply it to the standby database. Lets use as an example the case where it takes the primary 10
minutes to generate 1000MB of redo and takes a standby only 30 seconds to apply (33MB/sec apply rate). If the undo_retention on the primary is set to 10 minutes, the standby has effectively reduced the retention period to 30 seconds for the standby query workload.  If a standby query runs for more than 30 seconds, it is
likely to run into the ORA-01555 error because recovery has already applied more redo that overwrites the rollback segment. If active standby queries experience ORA-01555 errors, first make sure the standby is in real-time apply mode, and if so, also increase the undo_retention period on the primary database

Output of sql is different about cursor_sharing between exact and force

DB Version: Oracle 11.2.0.2.0
OS: Linux
Problem:
If cursor_sharing parameter is FORCE and there are function based index, you would get wrong output your SQL. For example:

SQL>CREATE INDEX owner.index_name ("sdate" DESC);
SQL>@script.sql


 Process   sdate
--------- -----------
  9630571 


SQL>Drop INDEX owner.index_name;
SQL>@script.sql


 Process   sdate
--------- -----------
  9630571 11/2/2011


Solutions:
--You can set  cursor_sharing to EXACT. But you should test in test db about all problem. This parameter is so effective in database.
SQL> show parameter cursor_sharing
NAME TYPE VALUE
------------------------------------ -------------------------------- ------------------------------
cursor_sharing string FORCE


SQL> alter system set cursor_sharing=EXACT scope=both sid='*';

-- You couldn't set cursor_sharing to EXACT. download and apply patch for Bug 10259620 and test

ORA-01008: not all variables bound- RMAN-03014

RMAN tool'unda ORA-01008 hatasını alarak denk geldiğim bug'ı paylaşmak istedim.

I got  ORA-01008 error with RMAN tool. It is oracle bug. I want to share.
DB version 11.2.0.2, cursor_sharing=FORCE

DBGSQL: TARGET> select nvl(max(al.recid), '0'),nvl(max(al.recid), 0) into :txtparmvalue, :parmvalue from v$archived_log al where al.status in ('X', 'A') and al.is_recovery_dest_file = 'YES' and al.creator = 'RMAN'
DBGSQL: sqlcode = 1008
RMAN-00571: ===========================================================
RMAN-00569: =============== ERROR MESSAGE STACK FOLLOWS ===============
RMAN-00571: ===========================================================
RMAN-03002: failure of backup command at 11/02/2011 23:52:15
RMAN-03014: implicit resync of recovery catalog failed
ORA-01008: not all variables bound

For Solution:

• Flush shared pool  . It is temporary solution.         -- alter system flush shared_pool;
• Set cursor_sharing to EXACT                              -- alter system set cursor_sharing=EXACT scope=both sid='*';
• Apply patch. (Patch 9877980)