MUST_BE_SAME_TIMEZONE_FILE_VERSION

When you upgrade database to 11g r2

when running catupgrd.sql

SQL> SELECT TO_NUMBER('MUST_BE_SAME_TIMEZONE_FILE_VERSION')
  2     FROM sys.props$
  3     WHERE
  4       (
  5        ((SELECT TO_NUMBER(value$) from sys.props$
  6           WHERE name = 'DST_PRIMARY_TT_VERSION') !=
  7         (SELECT tz_version from registry$database))
  8        AND
  9        (((SELECT substr(version,1,4) FROM registry$ where cid = 'CATPROC') =
10           '9.2.') OR
11         ((SELECT substr(version,1,4) FROM registry$ where cid = 'CATPROC') =
12           '10.1') OR
13         ((SELECT substr(version,1,4) FROM registry$ where cid = 'CATPROC') =
14           '10.2') OR
15         ((SELECT substr(version,1,4) FROM registry$ where cid = 'CATPROC') =
16           '11.1'))
17       );
SELECT TO_NUMBER('MUST_BE_SAME_TIMEZONE_FILE_VERSION')
                 *
ERROR at line 1:
ORA-01722: invalid number


control following steps


SQL> SELECT * FROM v$timezone_file;

FILENAME                VERSION
-------------------- ----------
timezlrg_14.dat              14


SQL> select TZ_VERSION from registry$database;

TZ_VERSION
----------
        4


If version of timezone_file is different than TZ_VERSION
run following update.

SQL> update registry$database set TZ_VERSION = (select version FROM v$timezone_file);
SQL> commit;


try to run catupgrd.sql

ORA-14265: data type or length of a table subpartitioning column may not be changed

When you want to modify partition table' s key columns.

sample

1 - create partitioned table with subpartition.


SQL> CREATE TABLE vty_log
    (tarih        DATE,
    program       VARCHAR2(6),
    pcismi        VARCHAR2(12),
    mesaj         VARCHAR2(2000),
    boyut         NUMBER(10,0))
  PARTITION BY RANGE (TARIH)
  SUBPARTITION BY HASH (program)
  (
  PARTITION vty_log_201201 VALUES LESS THAN (TO_DATE(' 2012-02-01 00:00:00', 'SYYYY-MM-DD HH24:MI:SS', 'NLS_CALENDAR=GREGORIAN'))
  LOGGING
  (
  SUBPARTITION vty_log_201201_P1,
  SUBPARTITION vty_log_201201_P2,
  SUBPARTITION vty_log_201201_P3,
  SUBPARTITION vty_log_201201_P4
  )
  );

2- try to modify subpartition column

SQL> alter table vty_log modify program varchar2(10);

*

ERROR at line 1:

ORA-14265: data type or length of a table subpartitioning column may not be changed 

you have to drop/create table to achive this problem.

You can create non-partition table for each partition which has same name with partition

and exchange partition 

or

If have enoguh downtime you can export/import with orjinal table. 

or

you can create dummy table desired structure and you can insert/select/rename.

Oracle 11g Cross platform Active Standby

You can use different supported platform for standby database with using 11g active standby features.

please click here

Oracle JDBC Connection Test

Save source  file: Conn.java

import java.sql.*;
class Conn {
  public static void main (String[] args) throws Exception
  {
   Class.forName ("oracle.jdbc.OracleDriver");

   Connection conn = DriverManager.getConnection
     ("jdbc:oracle:thin:@localhost:1521:ORCL","scott","tiger");
   try {
     Statement stmt = conn.createStatement();
     try {
       ResultSet rset = stmt.executeQuery("select BANNER from SYS.V_$VERSION");
       try {
         while (rset.next())
           System.out.println (rset.getString(1));   // Print col 1
       } 
       finally {
          try { rset.close(); } catch (Exception ignore) {}
       }
     } 
     finally {
       try { stmt.close(); } catch (Exception ignore) {}
     }
   } 
   finally {
     try { conn.close(); } catch (Exception ignore) {}
   }
  }
}

ps: Change host, port, SID, user and password to your value in connection url

("jdbc:oracle:thin:@localhost:1521:ORCL","scott","tiger");

Compile java

javac -cp .:$ORACLE_HOME/jdbc/lib/ojdbc5.jar:$ORACLE_HOME/jlib/orai18n.jar Conn.java

Run program

java -cp .:$ORACLE_HOME/jdbc/lib/ojdbc5.jar:$ORACLE_HOME/jlib/orai18n.jar Conn



PS : for compile and run in windows os, change columns(:) to semicolons(;)  like
.;$ORACLE_HOME/jdbc/lib/ojdbc5.jar;$ORACLE_HOME/jlib/orai18n.jar

also change jar names in their directories the correct ones.

simple java tricks

http://www.oracle.com/technetwork/java/compile-136656.html

Oracle SNAPSHOT STANDBY Database

For test purpose or others you can open read/write mode your standby database.



--to make read/write

alter system set db_recovery_file_dest_size=100G;
alter system set db_flashback_retention_target=7200; ---5 days
alter system set db_recovery_file_dest='/u01/flashback';

shutdown immediate

startup mount

ALTER DATABASE CONVERT TO SNAPSHOT STANDBY;

shutdown
startup mount
alter database open;

select open_mode,database_role from v$database;



--to return physical standby

shutdown immediate
startup mount
ALTER DATABASE CONVERT TO PHYSICAL STANDBY;

shutdown
startup mount
select open_mode,database_role from v$database;

--start recovery.
alter database recover managed standby database disconnect from session;



http://docs.oracle.com/cd/B28359_01/server.111/b28294/manage_ps.htm#SBYDB00708

http://docs.oracle.com/cd/B28359_01/server.111/b28320/initparams056.htm#REFRN10233

ORA-02303: cannot drop or replace a type with type or table dependents

You can't replace type sources if it has dependent object(s).
for example another type is produced from this type.

If you want to replace new source you must drop dependent objects before.
and create again that after replaced main type source.

ORA-02303: cannot drop or replace a type with type or table dependents

for example

drop TYPE appuser.table_logtable;

CREATE OR REPLACE 
TYPE bankdb.type_hpl_heslog AS OBJECT
        (col1                                           DATE,
         col2                                           VARCHAR2(14)         
)
/

CREATE OR REPLACE 
TYPE appuser.table_logtable AS TABLE OF type_logtable;

Also you can find dependent object with below sql

select * from dba_dependencies
where name = 'TYPE_LOGTABLE' and owner='APPUSER';

Temporary Tablespace Usage

v$sort_usage dynamic performance views deprecated in 9.2

 http://docs.oracle.com/cd/B19306_01/server.102/b14238/changes.htm#i639263

can use V$TEMPSEG_USAGE to obtain temp segment usage by session.

col machine format a20
col osuser format a20

select s.sid,s.username, s.osuser, s.machine, sum(u.extents) from V$TEMPSEG_USAGE u, v$session s
where s.saddr=u.session_addr
group by sid,s.username, s.osuser, s.machine
order by 5 desc;


       SID USERNAME                       OSUSER               MACHINE              SUM(U.EXTENTS)
---------- ------------------------------ -------------------- -------------------- --------------
      1718 APPUSER                        orauser2             node1                          7561
      2170 RAMAZAN                        rozturk              DOMAIN\PCRAMAZAN                120
      3358 OTHERUSER                      SYSTEM               node3                            11

ORA-30036: unable to extend segment by 8 in undo tablespace 'UNDOTBS'

You have enough undo tablespace but you can't use free area althoug it is free.
or
there is no active transaction in your database but undo tablespace is full?


--firstly select free space by this select

SQL> select sum(bytes) from dba_free_space where tablespace_name='UNDOTBS';

SUM(BYTES)
----------
1080098816

--select extent total size by grouping status

SQL> SELECT DISTINCT STATUS, SUM (BYTES), COUNT (*)
  FROM DBA_UNDO_EXTENTS
GROUP BY STATUS;

STATUS    SUM(BYTES)         COUNT(*)                               
--------- ------------------ ---------------- 
ACTIVE              18481152               17 
EXPIRED             14417920              220 
UNEXPIRED         2064187392             8695 

SQL> show parameter undo

NAME                                 TYPE                             VALUE
------------------------------------ -------------------------------- -----------
undo_management                      string                           AUTO
undo_retention                       integer                          3600
undo_tablespace                      string                           UNDOTBS

you can set "_undo_autotune" and undo_retention parameters  to trigger clearing unexpired extents which no one in use.

ALTER SYSTEM SET "_undo_autotune"=FALSE;
ALTER SYSTEM SET undo_retention=3600;

Kill inactive sessions

If you want to kill all inactive session in database which has been wait 10 minutes.
You can use below statements.

SQL> select count(*) from v$session;

  COUNT(*)
----------
      503

SQL> select 'alter system kill session '''||sid||','||serial#||''' immediate;' cmd
from v$session where last_call_et>600 and status='INACTIVE';

CMD
----------------------------------------------------------------------
alter system kill session '3803,3' immediate;
alter system kill session '3806,5' immediate;
alter system kill session '3811,3' immediate;
alter system kill session '3813,10' immediate;
alter system kill session '3814,20' immediate;
alter system kill session '3816,14' immediate;
alter system kill session '3823,135' immediate;
alter system kill session '3824,25' immediate;
.....

ORA-04091: table appuser.holidays is mutating, trigger/function may not see it

When you insert a row to table which have trigger to insert another table in remote db.
You have to care about insert statement in trigger.

for example

if you use this statement for insert to remote table

INSERT INTO appuser.holidays@otherdb SELECT * FROM holidays WHERE HOL_NAME = :New.HOL_NAME;

statement raises an error like below

ORA-04091: table appuser.holidays is mutating, trigger/function may not see it
ORA-02063: preceding line from PRODDB
ORA-02063: preceding 2 lines from OTHERDB
ORA-06512: at "APPUSER.HOLIDAYS_IUD", line 6
ORA-04088: error during execution of trigger 'APPUSER.HOLIDAYS_IUD'

to aviod tihs problem you can use table columns for inserting. like below

INSERT INTO appuser.holidays@otherdb values (:New.HOL_NAME,:New.HOL_START,:New.HOL_END,:New.HOL_TYPE);

--source of trigger

CREATE OR REPLACE TRIGGER APPUSER.HOLIDAYS_IUD
 AFTER
  INSERT OR DELETE OR UPDATE
 ON appuser.holidays
REFERENCING NEW AS NEW OLD AS OLD
 FOR EACH ROW
DECLARE
BEGIN
 IF DELETING then
     DELETE FROM appuser.holidays@otherdb WHERE HOL_NAME =:Old.HOL_NAME;
 ELSIF INSERTING then
     INSERT INTO appuser.holidays@otherdb values (:New.HOL_NAME,:New.HOL_START,:New.HOL_END,:New.HOL_TYPE);
 ELSIF UPDATING then
     DELETE FROM appuser.holidays@otherdb WHERE HOL_NAME = :Old.HOL_NAME;
     --raise error like below

     --INSERT INTO appuser.holidays@otherdb SELECT * FROM holidays WHERE HOL_NAME = :New.HOL_NAME;
     INSERT INTO appuser.holidays@otherdb values (:New.HOL_NAME,:New.HOL_START,:New.HOL_END,:New.HOL_TYPE);
 END IF;
END;
/

ORA-00054: kaynak meşgul ve NOWAIT ile elde etme belirlendi veya zaman aşımı süresi doldu

ALTER TABLE TABLO1 ADD ( adi VARCHAR2 (20));

line 1: ORA-00054: kaynak meşgul ve NOWAIT ile elde etme belirlendi veya zaman aşımı süresi doldu

Sorunu aşmanın farklı yolları var. Tabloyu kullanan sessionı bulup kill edebilirsiniz.

col object format a30
col username format a20
col sidserial format a12
set linesize 200

SELECT a.SID||','||s.serial# SIDserial, s.last_call_et, s.status,s.sql_hash_value, s.username, s.sql_hash_value, a.owner || '.' || a.OBJECT OBJECT, s.lockwait,s.osuser
  FROM gv$session s, gv$access a
 WHERE s.SID = a.SID
   and s.inst_id = a.inst_id
   AND a.owner != 'SYS'
   --and s.status ='ACTIVE'
   AND UPPER (SUBSTR (a.OBJECT, 1, 2)) != 'V$'
   AND a.OBJECT = upper(trim('&object_name'));

Çok yoğun kullanılan ama sürekli küçük transactionlar olan bir tablo ise 

belirli bir zaman bekleyip yeniden deneyen bir prosedür yazabilirsiniz.(0.1 sn gibi)

11g ile gelen bir parametre ile hata almadan önce bekleyebileceğimiz süreyi session bazında set edebiliyoruz.

alter session set DDL_LOCK_TIMEOUT=60;

böylece hata almadan önce 60sn beklemiş olacaksınız.


oracle reference linki : http://docs.oracle.com/cd/B28359_01/server.111/b28320/initparams068.htm

Unixden windowsa çıktı gönderme

Bu işlemi yapabilmek için

1- Windowsda printer paylaşılmş durumda olmalı.
2- Windowsda print spooler servisi çalışıyor durumda olmalı. Firewall varsa tanımlarınızı yapmalısınız. spooler servisi varsayılan olarak 515 portunu dinliyor.

Unixden aşağıdaki şekilde çıktı gönderebiliriz.

/usr/ucb/lpr -P HOST_ADI:PRINTER_ADI -l PRINT_DOSYASI

örnek:

/usr/ucb/lpr -P pcramazan:printer33 -l abc.txt

Oracle ODBC driver manual install (11g)

like below you can install Oracle ODBC driver manually.

--Oracle ODBC driver manual install (11g)
1-

Copy ODBC directory to $ORACLE_HOME
Copy sqora32.dll, sqoras32.dll, sqresus.dll, sqresja.dll to $ORACLE_HOME\bin

2-

cd C:\oracle\11.1\bin
C:\oracle\11.1\bin>regsvr32 /s sqora32.dll
C:\oracle\11.1\bin>regsvr32 /s sqoras32.dll
C:\oracle\11.1\bin>regsvr32 /s sqresus.dll
C:\oracle\11.1\bin>regsvr32 /s sqresja.dll
C:\oracle\11.1\ODBC>regsvr32 /s deckan32.dll

3- regedit, go to HKEY_LOCAL_MACHINE/SOFTWARE/ODBC


go to Oracle in OraClient11g_home1

key Driver, value = C:\oracle\11.1\bin\sqora32.dll
key Setup, value = C:\oracle\11.1\bin\sqoras32.dll


you can run below entries with reg file.


[HKEY_LOCAL_MACHINE\SOFTWARE\ODBC\ODBCINST.INI\Oracle in OraClient11g_home1]
"APILevel"="1"
"CPTimeout"="60"
"ConnectFunctions"="YYY"
"Driver"="c:\\oracle\\11.1\\BIN\\SQORA32.DLL"
"DriverODBCVer"="03.51"
"FileUsage"="0"
"Setup"="c:\\oracle\\11.1\\BIN\\SQORAS32.DLL"
"SQLLevel"="1"

[HKEY_LOCAL_MACHINE\SOFTWARE\ODBC\ODBCINST.INI\ODBC Drivers]
"Oracle in OraClient11g_home1"="Installed"

DBMS_JOB.next_date for other user

DBMS_JOB ile yetkisi olan her kullanıcı kendi şeması altında job tanımlayabilir. Başka bir şema altında job tanımlamak (legal olarak) mümkün değil. Paketin kullanımı ile ilgili buradan detaylı bilgiyi bulabilirsiniz. Başka kullanıcı altındaki joblara müdahele etmek gibi bir gereklilikte kullanabileceğimiz bir yöntem var. Joblarını değiştirmek istediğimiz kullanıcı altına prosedür oluşturup işlerimi yapabiliriz. Prosedürü farklı yöntemlerle oluşturmak mümkün.

Örneğin sadece bizim gönderdiğimiz bir herhangi komutu execute edecek şekilde prosedür yazmak bunlardan biri.Bu durumda sadece job için değil diğer başka limititasyonları da aşmış olabiliriz. (dmbs_job, database link gibi). Bu linkten bu amaçla yazmış olduğum prosedürü inceleyebilirsiniz.


Aşağıda job için yazlmış bir prosedür var. Bu prosedür ile diğer şema altındaki jobların sonraki çalışma zamanlarını, işin çalışma zaman aralığı kadar sonraya atamış olacağız.

yani  next_date = hesaplanan interval olacak.

create or replace PROCEDURE app_user.job_ilerlet (tarih date)
IS
cmd varchar2(500) := '';
BEGIN
   FOR c IN (SELECT job, last_date, interval
               FROM user_jobs
              WHERE TRUNC (last_date) = TRUNC (tarih))
   LOOP 
      cmd := 'begin dbms_job.next_date('||c.job||', '||c.interval||'); commit; end;';
      dbms_output.put_line(cmd);
      execute immediate cmd;
   END LOOP;
END;

/

Drop Lob Object

If you find lob segments in dictionary but any tables.

OBJECT_NAME                    OBJECT_TYPE
-------------------------      -----------
SYS_LOB0000117461C00044$$      LOB        
SYS_LOB0000117446C00044$$      LOB        
SYS_LOB0000117431C00044$$      LOB        
SYS_LOB0000117547C00044$$      LOB        
SYS_LOB0000117525C00044$$      LOB        
SYS_LOB0000117504C00044$$      LOB        
SYS_LOB0000117483C00044$$      LOB        

You have to purge your user recyclebin.

purge recyclebin;

DBMS_DEBUG error

When debug a procedure if raise an error like below :


ORA-01031: insufficient privileges
ORA-06512: at "SYS.PBSDE", line 78
ORA-06512: at "SYS.DBMS_DEBUG", line 226
ORA-06512: at line 1


User does not have sufficient privileges for debugging PL/SQL
You must grant DEBUG CONNECT SESSION to your user.

--for example

GRANT DEBUG CONNECT SESSION to app_usr;

Autonomous transaction ve transaction durumu

Autonomous transaction ve transaction durumu: Oracle' da bir transaction insert,update,delete komutlarıyla başlar ve bir DDL komutu, commit veya rollback ile biter. Commit veya rollback bir procedure/function/package içinden bile çağrılsa transaction devam ettiği için o transactionı sonlandıracaktır.

Bu durumda transactionın sonlanmadan başka bir tabloya yeni bir transaction başlatıp ana transactionının durumunu değiştirmeden sonlandırmamız gerekirse bunun için autonomous transaction kullanılır.

 --aşağıdaki örnek scriptler ile deneyebilirsiniz.

create table emp (id number, ad varchar2(20), soyad varchar2(20));

insert into emp values (1,'RAMAZAN','ÖZTÜRK');
insert into emp values (2,'AHMET','ÖZTÜRK');
insert into emp values (3,'DEDE','ÖZTÜRK');
insert into emp values (4,'BABA','ÖZTÜRK');

alter table emp add sal number;

create table emp_aud (id number,old_sal number,new_sal number,cdate date);

alter table emp_aud add empid number;

create sequence idver ;

create or replace trigger trg_emp 
before delete or update on emp 
for each row
declare 
pragma autonomous_transaction;
begin
  insert into emp_aud values (idver.nextval,:old.sal,:new.sal,sysdate,:old.id);
  commit;
end;

update emp set sal=400 where id=1;


create or replace procedure emp_tmp as
begin
  insert into emp values (idver.nextval,'KEVIN','COSTNER',100);
  rollback;
end;
/


insert into emp values (idver.nextval,'KEVIN'||idver.currval,'COSTNER',200);

insert into emp values (idver.nextval,'KEVIN'||idver.currval,'COSTNER',300);

exec emp_tmp();

insert into emp values (idver.nextval,'KEVIN'||idver.currval,'COSTNER',400);

rollback;

Buradan inceleyebilirsiniz.(Concepts)

http://docs.oracle.com/cd/B28359_01/server.111/b28318/transact.htm#i7733

Increase sequence value

--add 1000 to sequence current value

DECLARE
   i   NUMBER;
   a   NUMBER;
BEGIN
   FOR i IN 1 .. 1000
   LOOP
      SELECT appuser.sq_fisno.NEXTVAL
        INTO a
        FROM DUAL;
   END LOOP;
END;

Other user Procedure

Prosedürler sahibi olan kullanıcının hakları ile çalışır.
Bazı limitasyonlar gereği veya başka bir amaçla genel olarak aşağıdaki bir sp kullanılabilir.

CREATE OR REPLACE PROCEDURE app_user.vty_run_command (cmd varchar2)
IS
BEGIN
   EXECUTE IMMEDIATE cmd;
END;
/

Bu durumda bu spyi çağıran herhangi bir user app_user hakları ile istediği işi yaptırabilir.

Örnek olarak şöyle :

--Çalıştıracağımız Prosedür

CREATE OR REPLACE PROCEDURE app_user.vty_ramazan
IS
a number :=0 ;
BEGIN
   for c in (select job from user_jobs)
   loop
   a := c.job;
   dbms_output.put_line(a);     
   end loop;
END;
/

--Prosedürü çağırmak için
set serveroutput on

Begin
  app_user.vty_run_command('begin vty_ramazan; end;');
End;
/

--Sonuç
Normalde select * from user_jobs ile kullanıcı kendi joblarını görebilir.
Bu prosedüre ile o kullanıcı altında tanımlı jobları da görebiliriz.

Bu bize ne kazandırır: DBA veya select any table veya select_catalog_role hakkı olmayan biri de başka bir kullanıcı altındaki nesne tanımlarına erişebilir.

Print from unix to windows

TODO

1- Check your printer and share printer on Windows.
2- Check print spooler service runnnig on windows. If you use Firewall check your rules. The spooler service listens 515 port by default.

You can print from unix like below command:

/usr/ucb/lpr -P HOST_NAME:PRINTER_NAME -l PRINT_FILE

HOST_NAME    : name or ip address of pc/server which connected to printer.

PRINTER_NAME : sharing name of windows printer

sample:

/usr/ucb/lpr -P pcramazan:printer33 -l abc.txt