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.

Purge SQL from shared pool

• Find SQL_ID which one you want to purge SQL.

SELECT   a.cursors, a.sql_id, b.sql_text

  FROM   (  SELECT   COUNT ( * ) AS cursors, ssc.sql_id
              FROM   gv$sql_shared_cursor ssc
          GROUP BY   ssc.sql_id
          ORDER BY   cursors DESC) a, (SELECT   sa.sql_id, sa.sql_text
                                         FROM   gv$sqlarea sa) b
 WHERE   a.sql_id = b.sql_id AND a.cursors > 10;

• Purge related SQL from shared_pool.

DECLARE

   SQ_ADD        VARCHAR2 (100) := '';

   SQ_HASH_VAL   VARCHAR2 (100) := '';

BEGIN

   EXECUTE IMMEDIATE 'select address,hash_value from v$sqlarea where sql_id=''fg9ymndtmypa8'''

      INTO   SQ_ADD, SQ_HASH_VAL;

   DBMS_SHARED_POOL.purge (SQ_ADD || ',' || SQ_HASH_VAL, 'C');

--dbms_output.put_line(SQ_ADD||','||SQ_HASH_VAL);

END;

Lock and Unlock table statistics

--Control stats lock type of table.

SELECT stattype_locked FROM dba_tab_statistics WHERE table_name = 'WRR$_REPLAY_UC_GRAPH_EXT' and owner = 'SYS';

--lock statistics

exec dbms_stats.lock_table_stats('SYS', 'WRR$_REPLAY_UC_GRAPH_EXT');

--unlock statistics

exec dbms_stats.unlock_table_stats('SYS', 'WRR$_REPLAY_UC_GRAPH_EXT');

How to use DBMS_CRYPTO package

• You can encrypt and decrypt string with  this package. Example: Created p_encrypt package, and encrypt and decrypt string with p_encrypt package.

CREATE OR REPLACE PACKAGE MURATK_DBA.p_encrypt
AS
  FUNCTION encrypt_ssn( p_ssn IN VARCHAR2 ) RETURN RAW;
  FUNCTION decrypt_ssn( p_ssn IN RAW ) RETURN VARCHAR2;
END p_encrypt;
/

CREATE OR REPLACE PACKAGE BODY MURATK_DBA.p_encrypt
AS
--DO NOT FORGET TO WRAP THIS BEFORE LOADING INTO DATABASE
--IF IT IS NOT WRAPPED, THE KEY WILL BE EXPOSED
--THE WRAP UTILITY IS LOCATED IN THE \BIN DIRECTORY (WRAP.EXE)
  G_CHARACTER_SET VARCHAR2(10) := 'AL32UTF8';
  G_STRING VARCHAR2(32) := '12345678901234567890123456789012';
  G_KEY RAW(250) := utl_i18n.string_to_raw
                      ( data => G_STRING,
                        dst_charset => G_CHARACTER_SET );
  G_ENCRYPTION_TYPE PLS_INTEGER := dbms_crypto.encrypt_aes256 
                                    + dbms_crypto.chain_cbc 
                                    + dbms_crypto.pad_pkcs5;
  
  FUNCTION encrypt_ssn( p_ssn IN VARCHAR2 ) RETURN RAW
  IS
    l_ssn RAW(32) := UTL_I18N.STRING_TO_RAW( p_ssn, G_CHARACTER_SET );
    l_encrypted RAW(32);
  BEGIN
    l_ssn := utl_i18n.string_to_raw
              ( data => p_ssn,
                dst_charset => G_CHARACTER_SET );

    l_encrypted := dbms_crypto.encrypt
                   ( src => l_ssn,
                     typ => G_ENCRYPTION_TYPE,
                     key => G_KEY );
                     
    RETURN l_encrypted;
  END encrypt_ssn;
  
  FUNCTION decrypt_ssn( p_ssn IN RAW ) RETURN VARCHAR2
  IS
    l_decrypted RAW(32);
    l_decrypted_string VARCHAR2(32);
  BEGIN
    l_decrypted := dbms_crypto.decrypt
                    ( src => p_ssn,
                      typ => G_ENCRYPTION_TYPE,
                      key => G_KEY );

    l_decrypted_string := utl_i18n.raw_to_char
                            ( data => l_decrypted,
                              src_charset => G_CHARACTER_SET );
    RETURN l_decrypted_string;
  END decrypt_ssn;
  
END p_encrypt;
/

• Connect to database

#sqlplus / as sysdba

SQL> select muratk_dba.p_encrypt.encrypt_ssn('şifrelenecek metin') encrypt_data from dual;

Encrypt_data

--------------------------------------------------------------------------------

991D93B1E8C581931E9CDE8A86B5EACC5102BEAA54473461FF37FCB677ECA2E2

SQL> select muratk_dba.p_encrypt.decrypt_ssn('991D93B1E8C581931E9CDE8A86B5EACC5102BEAA54473461FF37FCB677ECA2E2')  DECRYPT_DATA  from dual;

DECRYPT_DATA

--------------------------------------------------------------------------------

şifrelenecek metin

JDBC Connection of SQL Server Name Instance

• You should find port number of name instance.

http://decipherinfosys.wordpress.com/2008/01/02/finding-the-port-number-for-a-particular-sql-server-instance/

Run Script on name instance.
DECLARE @tcp_port nvarchar(5)
EXEC xp_regread
@rootkey    =    'HKEY_LOCAL_MACHINE',
@key        =    'SOFTWARE\MICROSOFT\Microsoft SQL Server\<Name_Instance>\MSSQLSERVER\SUPERSOCKETNETLIB\TCP',
@value_name    =    'TcpPort',
@value        =    @tcp_port OUTPUT
select @tcp_port

• Then you can connect like that:

To use a port number, do the following:
jdbc:sqlserver://<database_server_name>:<name_instance_port>;integratedSecurity=true;<more properties as required>;
To use a JDBC URL property, do the following:
jdbc:sqlserver://localhost;instanceName=instance1;integratedSecurity=true;<more properties as required>;

Create external table of alert_log from alertlog file

/*
You will have to change:
- Create directory for directory of alertlog file as bdump_dir : CREATE OR REPLACE DIRECTORY BDUMP_DIR AS '/oracle/diag/rdbms/test/test/trace';
- Update <SID> to your sid in table script .
*/
create table alert_log (
  line_number number,
  text varchar2(4000)
)
organization external (
  type oracle_loader
  default directory bdump_dir
  access parameters (
    records delimited by newline
    nobadfile
    nodiscardfile
    nologfile
    fields missing field values are null
    (
      line_number recnum,
      text position(1:4000)
    )
  )
  location ('alert_<SID>.log')
)
reject limit unlimited
noparallel;


SQL> select * from alert_log where text like '%ORA-27037%' ;
LINE_NUMBER         TEXT
-----------                    -------------------------------------------------------

        254 ORA-27037: unable to obtain file status
        262 ORA-27037: unable to obtain file status
        268 ORA-27037: unable to obtain file status
        277 ORA-27037: unable to obtain file status
        285 ORA-27037: unable to obtain file status
        291 ORA-27037: unable to obtain file status
        298 ORA-27037: unable to obtain file status
        306 ORA-27037: unable to obtain file status
        312 ORA-27037: unable to obtain file status

9 rows selected.