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.

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

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.

Unknow user password is changed to open from expire - dbms_metadata.get_ddl

If application user profile has PASSWORD_LIFE_TIME, application user password could be expired. If you don't know old password, you can reopen account like that:


SQL> set linesize 1000
SQL> create user user_pass identified by "password" ;
User created.
SQL> alter user user_pass PASSWORD  expire;
User altered.
SQL> select username,ACCOUNT_STATUS from dba_users where username='USER_PASS';
USERNAME                       ACCOUNT_STATUS
------------------------------ --------------------------------
USER_PASS                      EXPIRED
SQL> select dbms_metadata.get_ddl('USER', username) || ';' usercreate from dba_users where username='USER_PASS';
USERCREATE
--------------------------------------------------------------------------------
   CREATE USER "USER_PASS" IDENTIFIED BY VALUES 'S:1E268627E76ACBE2A0C750D4FF86C6685514BEE0656BABDA251615E2E9BD;FDFAC641632E49E9'
      DEFAULT TABLESPACE "USERS"
      TEMPORARY TABLESPACE "TEMP"
      PASSWORD EXPIRE;
SQL> ALTER USER "USER_PASS" IDENTIFIED BY VALUES 'S:1E268627E76ACBE2A0C750D4FF86C6685514BEE0656BABDA251615E2E9BD;FDFAC641632E49E9';
User altered.
SQL> select username,ACCOUNT_STATUS from dba_users where username='USER_PASS';
USERNAME                       ACCOUNT_STATUS
------------------------------ --------------------------------
USER_PASS                      OPEN

Truncate table drop all storage - New feature 11.2

Truncate process reduce to initial value from table size before 11.2 versiyon.
New feature of truncate is drop all storage. This sytax reduce to zero from size table.


#Only truncate process:

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

SQL*Plus: Release 11.2.0.2.0 Production on Mon Dec 5 16:17:19 2011

Copyright (c) 1982, 2010, Oracle.  All rights reserved.

Connected to:

Oracle Database 11g Enterprise Edition Release 11.2.0.2.0 - 64bit Production

With the Partitioning, OLAP, Data Mining and Real Application Testing options

SQL> set linesize 1000

SQL> create table muratk_dba.truncate_table as select * from scott.emp;

Table created.

SQL> select sum(bytes)/1024/1024 from dba_segments where segment_name ='TRUNCATE_TABLE';

SUM(BYTES)/1024/1024

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

               .0625

SQL> truncate table muratk_dba.truncate_table;

Table truncated.

SQL> select sum(bytes)/1024/1024 from dba_segments where segment_name ='TRUNCATE_TABLE';

SUM(BYTES)/1024/1024

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

               .0625

# Truncate process with drop all storage feature

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

SQL> drop table muratk_dba.truncate_table purge;

Table dropped.

SQL> create table muratk_dba.truncate_table as select * from scott.emp;

Table created.

SQL> select sum(bytes)/1024/1024 from dba_segments where segment_name ='TRUNCATE_TABLE';

SUM(BYTES)/1024/1024

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

               .0625

SQL> truncate table muratk_dba.truncate_table  drop all storage;

Table truncated.

SQL> select sum(bytes)/1024/1024 from dba_segments where segment_name ='TRUNCATE_TABLE';

SUM(BYTES)/1024/1024

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

SQL> drop table muratk_dba.truncate_table purge;

Table dropped.

Privileges of deployment user

If you have deployment user, after this user creates new table, you have DML procesess in new table. Create role for deployment user, grant privileges to this role because deployment user may not grant/revoke  to/from yourself.

For Example:
oracle@dbatest> sqlplus / as sysdba
SQL> create user kapsul identified by kapsul;
User created.
SQL> grant connect,resource to kapsul;
Grant succeeded.
SQL> GRANT GRANT ANY OBJECT PRIVILEGE TO KAPSUL;
Grant succeeded.
SQL> grant CREATE ANY TABLE to kapsul;
Grant succeeded.
SQL> create role role_kapsul;
Role created.
SQL> grant role_kapsul to kapsul;
Grant succeeded.

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

oracle@dbatest> sqlplus kapsul/kapsul

SQL> create table scott.kapsul_table (sayi number);

Table created.

SQL> insert into scott.kapsul_table sayi values (3);

insert into scott.kapsul_table sayi values (3)

ERROR at line 1:

ORA-01031: insufficient privileges

SQL> grant insert,update,select,delete on scott.kapsul_table to kapsul;

grant insert,update,select,delete on scott.kapsul_table to kapsul

ERROR at line 1:

ORA-01749: you may not GRANT/REVOKE privileges to/from yourself

SQL> grant insert,update,select,delete on scott.kapsul_table to role_kapsul;

Grant succeeded.

SQL> insert into scott.kapsul_table sayi values (3);

1 row created.

SQL> commit;

Commit complete.

no-decrypt oracle user

If you don't want to lock user account in oracle database and nobody knows, gets, cracked password of user, you can use this command
SQL> alter user <username>identified by values 'no-decrypt';

For example:
#sqlplus / as sysdba
SQL>  create user passtest identified by passtest;
User created.
SQL> grant connect to passtest;
Grant succeeded.
#sqlplus passtest/passtest
SQL*Plus: Release 11.2.0.2.0 Production on Wed Nov 23 13:55:10 2011
Copyright (c) 1982, 2010, Oracle.  All rights reserved.


#sqlplus / as sysdba
SQL*Plus: Release 11.2.0.2.0 Production on Wed Nov 23 13:58:06 2011
Copyright (c) 1982, 2010, Oracle.  All rights reserved.
Connected to:
Oracle Database 11g Enterprise Edition Release 11.2.0.2.0 - 64bit Production
With the Partitioning, OLAP, Data Mining and Real Application Testing options
SQL> alter user passtest identified by values 'no-decrypt';
User altered.


#sqlplus passtest/passtest
SQL*Plus: Release 11.2.0.2.0 Production on Wed Nov 23 13:59:08 2011
Copyright (c) 1982, 2010, Oracle.  All rights reserved.
ERROR:
ORA-01017: geçersiz kullanıcı adı/parolası; oturum açma reddedildi

Resetting/Unsetting parameters in oracle database

You can reset  parameter to default. For example, you set parameter sid wrongly when you config db. Then you can reset it with this command.

SQL> alter system set fast_start_mttr_target=1200 scope=spfile sid='wrong_sid';
System altered.
-- You will see "wrong_sid.fast_start_mttr_target=1200" in pfile.

SQL> alter system reset fast_start_mttr_target scope=spfile sid='wrong_sid';
System altered.
-- Cleaned wrong parameter in parameter file.

Oracle - Tablonun başlangıç boyutunun değiştirilmesi (INITIAL RESIZE)

Bazen tablo truncate edilmesine rağmen boyutu hala yüksektir. Tablonun boyutu, truncate işlemi ile tablonun initial değerine dönecektir. Tablonun initial değerini değiştirmek için aşağıdaki yöntem kullanılabilir.

• Tablo Alter edilerek başka tablespace’e taşınır.

ALTER TABLE MY_TABLE MOVE TABLESPACE ANOTHER_TABLESPLACE STORAGE (INITIAL 2M NEXT 2M PCTINCREASE 0);

• INITIAL parametresi değiştirilerek tekrar orjinal tablespace’ine geri taşınır.

ALTER TABLE MY_TABLE MOVE TABLESPACE ORIGINAL_TABLESPACE STORAGE (INITIAL 256M NEXT 2M PCTINCREASE 0);

• Böylelikle drop ve recreate etmeye gerek yoktur.

Oracle - Oracle 11gR1 versiyonu SEC_CASE_SENSITIVE_LOGON özelliği

SEC_CASE_SENSITIVE_LOGON parametresi "true" olursa veritabanı kullanıcı şifreleri büyük, küçük harfe duyarlı olur.
alter system set sec_case_sensitive_logon=true scope=both sid='*';


SEC_CASE_SENSITIVE_LOGON parametresi "false" olursa veritabanı kullanıcı şifreleri büyük, küçük harf farketmez.
alter system set sec_case_sensitive_logon=false scope=both sid='*';


Veritabanı kurulurken; “Keep the enhanced 11g default secutiry settings” seçilirse, bu değer default olarak true gelecektir.

Insesitive password file yaratırken ignorecase=y seçeneği kullanılabilir.
$ orapwd file=orapwDB11Gb entries=5 ignorecase=y password=mypassword

Oracle - Session bazlı Temp tablespace kullanımı

  SELECT   b.tablespace,
           b.segfile#,
           b.segblk#,
           ROUND ( ( (b.blocks * p.VALUE) / 1024 / 1024), 2) size_mb,
           a.sid,
           a.serial#,
           a.username,
           a.osuser,
           a.program,
           a.status,
           b.sql_id
    FROM   v$session a,
           v$sort_usage b,
           v$process c,
           v$parameter p
   WHERE       p.name = 'db_block_size'
           AND a.saddr = b.session_addr
           AND a.paddr = c.addr
ORDER BY   size_mb DESC;

Oracle - Undo Tablespace'ini kullanarak objelerin ve tablonun eski halinin elde edilmesi

• Undo tablespace'in ilgili kayıtlar silinmediyse tablonun eski tarihli görünümüne bakılabilir. Bu özellik için flashback'in açık olmasına gerek yoktur.

select * from <tablo_ismi>  as of timestamp to_timestamp('2010-10-05 16:45:00','yyyy-mm-dd hh24:mi:ss')

• Aşağıdaki select ile objenin eski tarihli kaynak kodu elde edilebilir. Bu özellik için flashback'in açık olmasına gerek yoktur.

select * from dba_source as of timestamp to_timestamp('2011-10-03 08:45:00','yyyy-mm-dd hh24:mi:ss') where name='<Obje_ismi>';

Oracle - Oracle RAC veritabanının archivemod'a alınması

• Veritabanı exclusive modda çekilir.

sqlplus / as sysdba
SQL>alter system set cluster_database=false scope=spfile ;

• Veritabanı kapatılır.

$ srvctl stop database -d <veritabanı ismi>

• Node'lardan sadece bir tanesi açılarak archivelog modu devreye alınır. Sonra açılan node kapatılarak, exclusive modda çıkartılır.

$ sqlplus / as sysdba
SQL> startup mount
SQL> alter database archivelog;
SQL> alter system set cluster_database=true scope=spfile ;
SQL> shutdown immediate

• Veritabanı ve servisler tekrar başlatılır.

$ srvctl start database -d <veritabanı ismi>
$ srvctl start service -d <service ismi>

• Aşağıdaki komut ile modu kontrol edilebilir.

$ sqlplus / as sysdba
SQL> archive log list