23 December 2006

On Delete, Truncate and High Water Mark In Oracle

Oracle, stores data in segments. Segments has free and used space. The mark(a logical mark) between free and used space can be called as high water mark(HWM). When new data filled, the mark moves. On deleting records, oracle does not move back HWM. That is, you have unused data but, HWM sees as if it were filled data. When you shrink table such as alter table move command, this HWM refreshes itself. When you delete from a table, a DML operation, oracle undos the changing. That is you can move back, with some additional works. But when you truncate a table, a DDL operation, oracle moves back the HWM. So this is faster than normal delete clause. With delete, you will fire existing triggers whereas truncate does not.
Truncate is especially is used instead of dropping and recreating tables. with truncate, no objects will become invalid and grants are remains the same.
Below demonstration shows analyses:

Connected to Oracle Database 10g Express Edition Release 10.2.0.1.0
Connected as hr


SQL> set serveroutput on
SQL> set timing on
SQL>
SQL> --grant execute on dbms_space to hr;

Creating a function to calculate HWM:
SQL> create or replace function get_hwm(extend_name  in varchar2
  2                                    ,extend_owner in varchar2
  3                                    ,extend_type  in varchar2) return number is
  4    total_block          number;
  5    unused_block         number;
  6    total_byte           number;
  7    unused_byte          number;
  8    last_extent_file_id  number;
  9    last_extent_block_id number;
 10    last_block           number;
 11 
 12    hwm number;
 13  begin
 14    dbms_space.unused_space(extend_owner,
 15                            extend_name,
 16                            extend_type,
 17                            total_block,
 18                            total_byte,
 19                            unused_block,
 20                            unused_byte,
 21                            last_extent_file_id,
 22                            last_extent_block_id,
 23                            last_block);
 24    hwm := total_block - (unused_block + 1);
 25    dbms_output.put_line('hwm for ' || extend_owner || '.' || extend_name || ' ' ||
 26                         extend_type || ' is ' || hwm);
 27    return hwm;
 28  end;
 29  /

Function created
Executed in 0,17 seconds
SQL> drop table t;
Table dropped
Executed in 0,02 seconds
SQL> create table t as select rownum as i from all_objects;
Table created
Executed in 3,935 seconds

SQL> create or replace trigger trg_del_t
  2    before insert or update or delete
  3   on t
  4  begin
  5    if inserting then
  6      dbms_output.put_line('Inserting t');
  7    elsif updating then
  8      dbms_output.put_line('Updating t');
  9    elsif deleting then
 10      dbms_output.put_line('Deleting t');
 11    end if;
 12 
 13  end;
 14  /

Trigger created
Executed in 0,02 seconds
SQL> select get_hwm('T','HR','TABLE') HWM from dual;
       HWM
----------
        19

hwm for HR.T TABLE is 19
Executed in 0,02 seconds

On delete, trigger fired
SQL> delete from t;
Deleting t
10209 rows deleted
Executed in 0,291 seconds

On delete, HWM remains the same
SQL> select get_hwm('T','HR','TABLE') HWM from dual;
       HWM
----------
        19

hwm for HR.T TABLE is 19
Executed in 0,01 seconds
SQL> alter table t move;
Table altered
Executed in 0,04 seconds

With ater table move HWM moves back:
SQL> select get_hwm('T','HR','TABLE') HWM from dual;
       HWM
----------
         2

hwm for HR.T TABLE is 2
Executed in 0,02 seconds
SQL> drop table t;
Table dropped
Executed in 0,15 seconds
SQL> create table t as select rownum as i from all_objects;
Table created
Executed in 2,714 seconds
SQL> create or replace trigger trg_del_t
  2    before insert or update or delete
  3   on t
  4  begin
  5    if inserting then
  6      dbms_output.put_line('Inserting t');
  7    elsif updating then
  8      dbms_output.put_line('Updating t');
  9    elsif deleting then
 10      dbms_output.put_line('Deleting t');
 11    end if;
 12 
 13  end;
 14  /

Trigger created
Executed in 0,02 seconds
SQL> select get_hwm('T','HR','TABLE') HWM from dual;
       HWM
----------
        19

hwm for HR.T TABLE is 19
Executed in 0,01 seconds

On truncate HWM DOES NOT remain the same
Truncate it is faster than normal delete
SQL> truncate table t;
Table truncated
Executed in 0,02 seconds
SQL> select get_hwm('T','HR','TABLE') HWM from dual;
       HWM
----------
         2

hwm for HR.T TABLE is 2
Executed in 0,01 seconds
SQL>

21 December 2006

Test-Driven Development With Oracle's PL/SQL

Test Güdümlü Programlama(Test-Driven Development), çevik(agile) yöntemlerden biri olan XP(Extreme Programming)'in bir parçasıdır. Son zamanlarda sıklıkla yaşanan, yazılımın istendiği şekilde çalışmaması olarak tanımlanan bug'ların tehlikeli ve maliyetli sonuçları ile kendinden daha bir söz ettiren TDD, kaliteli yazılım ürününün oluşması için çalışır. 2002 yılında ComputerWorld'un yaptığı araştırmaya göre bug'ların analiz ve çözümünün ABD ekonomisine yaklaşık 60 milyar $ maliyeti de düşünülürse, TDD'nin ne derece önemli bir konu olduğu daha iyi farkedilecektir.
Genel olarak test yöntemleri başta ve sonda olmak üzere 2 türlüdür. Testin sonda yapılması klasik yöntemdir. Kod yazılır ve sonunda testler(unit) yapılır. Testi başa almak ise TDD'nin temelini oluşturur. Kod yazılmadan testleri yazılır ve daha sonra kodlamaya girilir. En sonunda ise gerekli durumlarda refactoring olarak iyileştirmeler yapılır.
TDD'yi oluşturan test-first development aşağıdaki döngü ile belirtilebilir:

Kodlama Yapılmaz
Test Yazılır
Test Çalıştırılır
Test Hata Alır
Testi Geçebilecek Kadar Kod Yazılır
Tekrar Çalıştırılır
Hata Alınırsa İşlemler Tekrarlanır
Test Hatasız Geçilir
Test Yazılır
…...

TDD'nin sağladığı avantajlar ise şu şekilde belirtilebilir:

Yüksek Kalitede Kod(Yazılım Ürünü) Oluşur.
Yazılımın Doğru Çalıştığının Kanıtıdır.
Büyük Problemleri Küçük Parçalara Böler.
Her Test Döngü Sonucu Geribildirimlere İmkan Tanır.
Evrimsel Yazılım Geliştirme Yöntemlerini Destekler.

Java için JUnit, .NET için NUnit ve PL/SQL için ise OUnit TDD ile kullanılabilecek araçlara örnek verilebilir.

PL/SQL ile bu yöntemi kullanarak yazılan örnek bir uygulama aşağıda belirtilmiştir. Bu örnek, kendisine parametre olarak gelen bir tarihi, istenen formata dönüştürme işlemini gerçekleştirmektedir.

Format için gerekli tablo oluşturulur:
SQL> create table date_formats( id number, format varchar2(16));
Table created
SQL> insert into date_formats values(1, 'MMDDYY');
1 row inserted
SQL> insert into date_formats values(2, 'MM.DD.YYYY');
1 row inserted
SQL> select * from date_formats;
        ID FORMAT
---------- ----------------
         1 MMDDYY
         2 MM.DD.YYYY

SQL>

Test Yazılır:
SQL> create or replace package date_format_tests as
  2    procedure test_format1;
  3  end;
  4  /

Package created
SQL> show err;
No errors for PACKAGE HR.DATE_FORMAT_TESTS

SQL> create or replace package body date_format_tests as
  2 
  3    procedure test_format1 is
  4      vn_FormatId number;
  5      vs_FormattedDate varchar2(32);
  6      vs_Expected varchar2(32);
  7      vd_Date date;
  8    begin
  9      vn_FormatId := 1;
 10      vd_Date := to_date('01.12.2006', 'MM.DD.YYYY');
 11      vs_Expected := '011206';
 12      vs_FormattedDate := date_format.get_formatted_date(vn_FormatId, vd_Date);
 13      if vs_Expected = vs_FormattedDate then
 14        dbms_output.put_line('Test is Succesful....');
 15      else
 16        dbms_output.put_line('Test Failed!!!');
 17      end if;
 18    end;
 19 
 20  end;
 21  /


Test Hata Alır:
Warning: Package body created with compilation errors
SQL> show err;
Errors for PACKAGE BODY HR.DATE_FORMAT_TESTS:

LINE/COL ERROR
-------- -----------------------------------------------------------------------
12/25    PLS-00201: identifier 'DATE_FORMAT.GET_FORMATTED_DATE' must be declared
12/5     PL/SQL: Statement ignored

SQL> exec date_format_tests.test_format1;
begin date_format_tests.test_format1; end;
ORA-04063: package body "HR.DATE_FORMAT_TESTS" has errors
ORA-06508: PL/SQL: could not find program unit being called: "HR.DATE_FORMAT_TESTS"
ORA-06512: at line 1


Kodlama Yapılır:
SQL> create or replace package date_format as
  2    function get_formatted_date(pin_FormatId in number,pid_Date in date) return varchar2;
  3  end;
  4  /

Package created
SQL> show err;
No errors for PACKAGE HR.DATE_FORMAT

SQL> create or replace package body date_format as
  2 
  3    function get_formatted_date(pin_FormatId in number,pid_Date in date) return varchar2 is
  4    vs_Result varchar2(32);
  5    begin
  6      vs_Result := 'N/A';
  7      return vs_Result;
  8    end;
  9 
 10  end;
 11  /

Package body created
SQL> show err;
No errors for PACKAGE BODY HR.DATE_FORMAT


Test Yanlış Sonuç Üretir:SQL> exec date_format_tests.test_format1;
Test Failed!!!
PL/SQL procedure successfully completed

Tekrar Kodlama Yapılır:
SQL> create or replace package body date_format as
  2 
  3    function get_formatted_date(pin_FormatId in number,pid_Date in date) return varchar2 is
  4    vs_Result varchar2(32);
  5    vs_DateFormat varchar2(32);
  6    begin
  7      select df.format into vs_DateFormat from date_formats df where df.id = pin_FormatId;
  8      vs_Result := to_char(pid_Date,vs_DateFormat );
  9      return vs_Result;
 10    end;
 11 
 12  end;
 13  /

Package body created
SQL> show err;
No errors for PACKAGE BODY HR.DATE_FORMAT



Test Geçer:SQL> exec date_format_tests.test_format1;
Test is Succesful....
PL/SQL procedure successfully completed

İyileştirmeler Yapılır:
SQL> create or replace package body date_format as
  2 
  3    function get_single_query_result(pis_TableName in varchar2,pis_ColumnName  in varchar2, pis_WhereCondition  in varchar2) return varchar2 is
  4      vs_Result varchar2(32);
  5      vs_SqlStatement varchar2(1024);
  6    begin
  7      vs_SqlStatement := 'select ' || pis_ColumnName || ' from ' || pis_TableName ||  '  where ' || nvl(pis_WhereCondition, '1=1');
  8      execute immediate vs_SqlStatement into vs_Result;
  9      return vs_Result;
 10    end;
 11 
 12    function get_formatted_date(pin_FormatId in number,pid_Date in date) return varchar2 is
 13      vs_Result varchar2(32);
 14      vs_DateFormat varchar2(32);
 15    begin
 16      vs_DateFormat := get_single_query_result('date_formats', 'format', 'id = ' || pin_FormatId);
 17      vs_Result := to_char(pid_Date,vs_DateFormat );
 18      return vs_Result;
 19    end;
 20 
 21  end;
 22  /

Package body created
SQL> show err;
No errors for PACKAGE BODY HR.DATE_FORMAT

SQL> exec date_format_tests.test_format1;
Test is Succesful....
PL/SQL procedure successfully completed

20 December 2006

DROP DATABASE Command In Oracle

Yesterday I have written about how to remove an oracle database. Thanks to Tonguc, he made me to investigate DROP DATABASE command with his comment. I drop a database via this command and check database configuration files. I see that when you drop the database with command, Oracle deletes datafiles from operating system. If you want to remove all items that related database you must do some work more. I have written what that work is yesterday.

oracle@aurora:/usr/users/oracle> sqlplus system/system as sysdba;
SQL*Plus: Release 10.2.0.2.0 - Production on Tue Dec 19 22:35:37 2006

Copyright (c) 1982, 2005, Oracle.  All Rights Reserved.
 

Connected to an idle instance.
SQL> startup exclusive restrict mount;
ORACLE instance started.

Total System Global Area  167772160 bytes
Fixed Size                  2047344 bytes
Variable Size              79692432 bytes
Database Buffers           79691776 bytes
Redo Buffers                6340608 bytes
Database mounted.

SQL> drop database;
Database dropped.

Disconnected from Oracle Database 10g Enterprise Edition Release 10.2.0.2.0 - Production
With the Partitioning, OLAP and Data Mining options
SQL> exit
oracle@aurora:/usr/users/oracle> ls /data06/app/oracle/oradata/
ashp01  pre
pre oracle@aurora:/usr/users/oracle> ls /data06/app/oracle/oradata/pre
pre oracle@aurora:/usr/users/oracle> ls /data06/app/oracle/admin/pre 
adump    bdump    cdump    dpdump   pfile    scripts  udump
oracle@aurora:/usr/users/oracle> more /etc/oratab
pre:/data06/app/oracle/product/10.2:N
oracle@aurora:/usr/users/oracle> more /data06/app/oracle/product/10.2/network/admin/listener.ora
# Generated by Oracle configuration tools.
LISTENER =
  (DESCRIPTION =
    (ADDRESS = (PROTOCOL = TCP)(HOST = aurora)(PORT = 1522))
  )

SID_LIST_LISTENER =
  (SID_LIST =
    (SID_DESC =
      (GLOBAL_DBNAME = pre)
      (ORACLE_HOME = /data06/app/oracle/product/10.2)
      (SID_NAME = pre)
    )
   )

oracle@aurora:/usr/users/oracle> more /data06/app/oracle/product/10.2/network/admin/tnsnames.ora
# tnsnames.ora Network Configuration File: /data06/app/oracle/product/10.2/network/admin/tnsnames.ora
 

PRE.ATOS =
  (DESCRIPTION =
    (ADDRESS = (PROTOCOL = TCP)(HOST = aurora)(PORT = 1521))
    (CONNECT_DATA =
      (SERVER = DEDICATED)
      (SERVICE_NAME = pre)
    )
  )

oracle@aurora:/usr/users/oracle>
 

19 December 2006

How To Remove an Oracle Database

You can remove an oracle database via Graphical User Interfaced programs. I can suggest an alternative method to remove an Oracle database manually. I define the steps below:
1. Log On as sysdba.
sqlplus / as sysdba
2. Check oracle related files. You can create a sh file that removes all files as i mentioned below.Please be careful when executing commands. Some of them may be directory instead of file. You can use rmdir command instead.

SQL> DECLARE
  2    TYPE string_arr IS TABLE OF VARCHAR2(1024);
  3    file_list string_arr;
  4  BEGIN
  5    SELECT t.file_path BULK COLLECT
  6      INTO file_list
  7      FROM (SELECT NAME file_path
  8               FROM V$DATAFILE
  9             UNION
 10             SELECT MEMBER file_path
 11               FROM V$LOGFILE
 12             UNION
 13             SELECT NAME file_path
 14               FROM v$controlfile
 15             UNION
 16             SELECT VALUE file_path
 17               FROM v$parameter
 18              WHERE NAME LIKE '%dest'
 19             UNION
 20             SELECT VALUE file_path
 21               FROM v$parameter2
 22              WHERE NAME = 'utl_file_dir'
 23             UNION
 24             SELECT '$ORACLE_BASE/admin/$ORACLE_SID' file_path FROM dual) t;
 25 
 26    FOR i IN file_list.FIRST .. file_list.LAST LOOP
 27      DBMS_OUTPUT.PUT_LINE('rm -f ' || file_list(i));
 28    END LOOP;
 29  END;
 30  /

rm -f $ORACLE_BASE/admin/$ORACLE_SID
rm -f /data06/app/oracle/admin/cc/bdump
rm -f /data06/app/oracle/admin/cc/cdump
rm -f /data06/app/oracle/admin/cc/udump
rm -f /data06/app/oracle/product/10.1/rdbms/audit
rm -f /data09/oradata/cc/control01.ctl
rm -f /data09/oradata/cc/control02.ctl
rm -f /data09/oradata/cc/control03.ctl
rm -f /data09/oradata/cc/data01.dbf
rm -f /data09/oradata/cc/data02.dbf
rm -f /data09/oradata/cc/redo01.log
rm -f /data09/oradata/cc/redo02.log
rm -f /data09/oradata/cc/redo03.log
rm -f /data09/oradata/cc/sysaux01.dbf
rm -f /data09/oradata/cc/system01.dbf
rm -f /data09/oradata/cc/undotbs01.dbf
rm -f /data09/oradata/cc/users01.dbf
rm -f ?/dbs/arch

PL/SQL procedure successfully completed
3. Shutdown database
shutdown immediate
4. Remove all data files as outputted above.
5. Remove entry in tnsnames.ora
vi $ORACLE_HOME/network/admin/tnsnames.ora
6. Remove entry in listener.ora
vi $ORACLE_HOME/network/admin/listener.ora
7. Remove entry in oratab
vi $ORACLE_HOME/network/admin/listener.ora

''IN' is not a valid integer value'" Bug In TOAD With Oracle 10.2

TOAD has a bug with its versions earlier than 8.6. You can check details of bug in TOAD's support. The resolution is to upgrade TOAD with new releases.

18 December 2006

Power Of Object Oriented Mechanism With Object Views In Oracle

In Oracle database, it is possible to create virtual object tables from relational tables. With this fashion you can select as if you are selecting an object table. So, you need not to convert relational data to object oriented data if you tend to use it. Follow the example:

Connected to Oracle Database 10g Express Edition Release 10.2.0.1.0
Connected as hr


SQL> create type t_telephone_number is object( no varchar2(12));
  2  /

Type created
SQL> create type t_telephone_number_list is varray(5) of t_telephone_number;
  2  /

Type created
SQL> create type worker_typ is object( id number, name varchar2(16), telephone_list t_telephone_number_list );
  2  /

Type created
SQL> alter type worker_typ add attribute x date;
Type altered
SQL> describe worker_typ;
Element        Type                   
-------------- -----------------------
ID             NUMBER                 
NAME           VARCHAR2(16)           
TELEPHONE_LIST T_TELEPHONE_NUMBER_LIST
X              DATE                   

SQL> alter type worker_typ drop attribute x ;
Type altered
SQL> create table workers(i number, n varchar2(16) );
Table created
SQL> create table tels(i number, nm varchar2(12) );
Table created
SQL> create view vw_workers of worker_typ with object identifier(id) as
  2    select w.i, w.n,cast(
  3       multiset(select nm from tels t where t.i=w.i) as t_telephone_number_list)
  4     from workers w;

View created
SQL> insert into workers values(1,'Mennan');
1 row inserted
SQL> insert into tels values(1, '2122122122');
1 row inserted
SQL> insert into tels values(1, '2122221133');
1 row inserted
SQL> select v.id,v.name, v.telephone_list from vw_workers v where v.id = 1;
        ID NAME             TELEPHONE_LIST
---------- ---------------- --------------
         1 Mennan           [object]

Solution Of "ORA-12910: cannot specify temporary tablespace as default tablespace" Error

A few days ago i have done a full database export-import. During import I got


IMP-00017: following statement failed with ORACLE error 12910:
"CREATE USER "FOO" IDENTIFIED BY VALUES 'FOO' DEFAULT TABLESPACE "DATA" TEMPORARY TABLESPACE "TEMP""
IMP-00003: ORACLE error 12910 encountered
ORA-12910: cannot specify temporary tablespace as default tablespace


error. When i look at tablespaces and database properties, I realized that default temporary tablespace has been set "DATA" and default tablespace has been set "USERS". So during import, I was getting specified errors.


The solution was to check tablespaces.


SELECT * FROM DATABASE_PROPERTIES WHERE PROPERTY_NAME LIKE 'DEFAULT_%';
SELECT * FROM dba_data_files;
SELECT * FROM v$tablespace;



Then I dropped tablespace and recreate it.


DROP TABLESPACE DATA;



CREATE TABLESPACE "DATA" DATAFILE
'/data09/oradata/db02/data01.dbf' REUSE
AUTOEXTEND ON NEXT 655360 MAXSIZE 32767M
LOGGING ONLINE PERMANENT BLOCKSIZE 8192
EXTENT MANAGEMENT LOCAL AUTOALLOCATE SEGMENT SPACE MANAGEMENT MANUAL;



Lastly I altered the database


ALTER DATABASE DEFAULT TEMPORARY TABLESPACE TEMP;
ALTER DATABASE DEFAULT TABLESPACE DATA;



That is all.



17 December 2006

Solution Of "ORA-27140: attach to post/wait facility failed" and "ORA-12518: TNS:listener could not hand off client connection" Error

A few days ago, i have setup an Oracle 10.2 on HP Tru 64. I have installed Oracle with oracle user. Then for some reasons privilleges of $ORACLE_HOME directory has changed. When clients wants to connect database via listener, they got ORA-27140: attach to post/wait facility failed and ORA-12518: TNS:listener could not hand off client connection errors. I solved the problem with changing rights of $ORACLE_HOME/bin directory as chmod 6751 oracle.

16 December 2006

On Inserts(Insert All-First) In Oracle

In standart SQL there is a simple insert statement that does insertion in database. Oracle has extra features of insert statements to gain performance. For instance you can make a conditional inserts or make multiple inserts with insert statements:

Connected to Oracle Database 10g Express Edition Release 10.2.0.1.0
Connected as SYS


SQL> create table t_all( i number, z number );
Table created
SQL> insert into t_all values(1,1);
1 row inserted
SQL> insert into t_all values(2,2);
1 row inserted
SQL> insert into t_all values(3,3);
1 row inserted
SQL> insert into t_all values(4,4);
1 row inserted
SQL> insert into t_all values(5,5);
1 row inserted
SQL> create table t_1(i number, z number );
Table created
SQL> create table t_2(i number, z number );
Table created
SQL> create table t_3(i number, z number );
Table created
SQL> insert all
  2    into t_1 values(i,z)
  3    into t_2 values(i,z)
  4    into t_3 values(i,z)
  5  select * from t_all;

15 rows inserted
SQL> select * from t_1;
         I          Z
---------- ----------
         1          1
         2          2
         3          3
         4          4
         5          5

SQL> select * from t_2;
         I          Z
---------- ----------
         1          1
         2          2
         3          3
         4          4
         5          5

SQL> select * from t_3;
         I          Z
---------- ----------
         1          1
         2          2
         3          3
         4          4
         5          5

SQL> rollback;
Rollback complete
SQL> insert all
  2    when mod(z,4) = 1 then
  3      into t_1 values(i,z)
  4    when mod(z,3) = 1 then
  5      into t_2 values(i,z)
  6    else
  7      into t_3 values(i,z)
  8  select * from t_all;

6 rows inserted
SQL> select * from t_1;
         I          Z
---------- ----------
         1          1
         5          5

SQL> select * from t_2;
         I          Z
---------- ----------
         1          1
         4          4

SQL> select * from t_3;
         I          Z
---------- ----------
         2          2
         3          3

SQL> rollback;
Rollback complete
SQL> insert first
  2    when mod(z,4) = 1 then
  3      into t_1 values(i,z)
  4    when mod(z,3) = 1 then
  5      into t_2 values(i,z)
  6    else
  7      into t_3 values(i,z)
  8  select * from t_all;

5 rows inserted
SQL> select * from t_1;
         I          Z
---------- ----------
         1          1
         5          5

SQL> select * from t_2;
         I          Z
---------- ----------
         4          4

SQL> select * from t_3;
         I          Z
---------- ----------
         2          2
         3          3

SQL> rollback;
Rollback complete
SQL> drop table t_all;
Table dropped
SQL> create table t_all(i number, j number, k number, l number);
Table created
SQL> insert into t_all values(1,2,3,4);
1 row inserted
SQL> insert all
  2      into t_1 values(i,j)
  3      into t_2 values(i,k)
  4      into t_3 values(i,l)
  5  select * from t_all;

3 rows inserted
SQL> select * from t_1;
         I          Z
---------- ----------
         1          2

SQL> select * from t_2;
         I          Z
---------- ----------
         1          3

SQL> select * from t_3;
         I          Z
---------- ----------
         1          4

SQL> rollback;
Rollback complete
SQL>

External Table Example In Oracle

External table is useful when working data outside of database. This means you can select formatted data without loading them into database. You can also load the data with SQL*Loader utility if you wish.
A small example shows how to query data outside of database:

Connected to Oracle Database 10g Express Edition Release 10.2.0.1.0
Connected as SYS


SQL> --select * from all_directories;
SQL> create or replace directory ext as 'c:\ext';
Directory created
SQL> grant read,write on directory ext to hr;
Grant succeeded
SQL> drop table t;
Table dropped
SQL> /*
  2  t.txt :
  3  --------------
  4  1|mennan
  5  2|ali
  6  3|mehmet
  7  4|
  8 
  9  -------------
 10  */
 11 
SQL>

SQL> create table t(i number, a varchar2(12))
  2  organization external(
  3    type oracle_loader
  4    default directory ext
  5    access parameters(
  6      records delimited by newline
  7      badfile ext:'rejected.txt'
  8      logfile ext:'log.txt'
  9      fields terminated by '|'
 10      missing field values are null
 11      (i,a)
 12    )
 13    location ('t.txt')
 14  )
 15  parallel 2
 16  reject limit unlimited;

Table created
SQL> select * from t;
         I A
---------- ------------
         1 mennan
         2 ali
         3 mehmet
         4


SQL> create table tt(i ,a)
  2  organization external(
  3    type oracle_datapump
  4    default directory ext
  5    access parameters(
  6      logfile ext:'log_tt.txt'
  7    )
  8    location ('tt.txt')
  9  )
 10  as
 11  select * from t;

Table created

Go directory and check files that are created....

Some Useful Dictionary Views For Database Objects In Oracle



select
* from user_objects;
select * from user_tables;
select * from user_tab_cols;
select * from user_constraints;
select * from user_cons_columns;
select * from user_views;
select * from user_sequences;
select * from user_synonyms;
select * from user_tab_comments;
select * from user_col_comments;

On Function Based Indexes In Oracle

Indexes are used to access data more efficiently to gain performance in tems of execution time. Oracle has many types of indexes. I mentioned indexes here that are based on functions. Below example shows this usage:


SQL> conn hr/hr
Connected.

SQL> create table t(a number primary key using index(create index idx_a on t(a)), z number, m varchar(32));
Table created.
SQL> insert into t values(1,2,'MENNAN');
1 row created.
SQL> insert into t values(2,22,'ALI');
1 row created.
SQL> insert into t values(3,12,'sss');
1 row created.
SQL> create index idx_z on t(z);
Index created.
SQL> create index idx_upper_m on t( upper(m));
Index created.
SQL> set autotrace on;
SQL> select * from t where upper(m) = 'ALI';

         A          Z M
---------- ---------- ---------------
         2         22 ALI

Execution Plan
----------------------------------------------------------
Plan hash value: 745746091

-------------------------------------------------------------------------------------------
| Id  | Operation                   | Name        | Rows  | Bytes | Cost (%CPU)| Time     |
-------------------------------------------------------------------------------------------
|   0 | SELECT STATEMENT            |             |     1 |    44 |     2   (0)| 00:00:01 |
|   1 |  TABLE ACCESS BY INDEX ROWID| T           |     1 |    44 |     2   (0)| 00:00:01 |
|*  2 |   INDEX RANGE SCAN          | IDX_UPPER_M |     1 |       |     1   (0)| 00:00:01 |
-------------------------------------------------------------------------------------------

Predicate Information (identified by operation id):
---------------------------------------------------
   2 - access(UPPER("M")='ALI')

Note
-----
   - dynamic sampling used for this statement

SQL> select * from t where m = 'ALI';
         A          Z M
---------- ---------- --------------------------------
         2         22 ALI


Execution Plan
----------------------------------------------------------
Plan hash value: 1601196873

--------------------------------------------------------------------------
| Id  | Operation         | Name | Rows  | Bytes | Cost (%CPU)| Time     |
--------------------------------------------------------------------------
|   0 | SELECT STATEMENT  |      |     1 |    44 |     3   (0)| 00:00:01 |
|*  1 |  TABLE ACCESS FULL| T    |     1 |    44 |     3   (0)| 00:00:01 |
--------------------------------------------------------------------------

Predicate Information (identified by operation id):
---------------------------------------------------

   1 - filter("M"='ALI')
Note
-----
   - dynamic sampling used for this statement

On Alter Table Statements(Add-Drop-Modify Columns, Constraints) In Oracle

While requirments are changing, data structures are changing too. Some times you need to change your structure of table. Oracle supplies a bunch of alter table statements. I wrote these statements that can be used in Oracle:

Connected to Oracle Database 10g Express Edition Release 10.2.0.1.0
Connected as hr


SQL> drop table t;

Table dropped
SQL> create table t(a number, z number constraint ck_10 check(z > 10) );
Table created
SQL> alter table t add constraint pk_t primary key(z);
Table altered
SQL> alter table t add constraint unique_t unique(a);
Table altered
SQL> alter table t add constraint fk_t foreign key(z) references t(a) on delete set null;--on delete cascade
Table altered
SQL> alter table t drop primary key cascade;--deletes also fk
Table altered
SQL> alter table t drop constraint pk_t;
alter table t drop constraint pk_t
ORA-02443: Cannot drop constraint  - nonexistent constraint
SQL> alter table t add k number default 4 not null;
Table altered
SQL> alter table t rename column k to kk;
Table altered
SQL> alter table t modify kk varchar2(9) default 'test';
Table altered
SQL> INSERT INTO t(a,kk) values(1,'asdfghjkl');
1 row inserted
SQL> commit;
Commit complete
SQL> SELECT t.kk, length(t.kk) len FROM t;
KK               LEN
--------- ----------
asdfghjkl          9

SQL> alter table t modify kk varchar2(4);
alter table t modify kk varchar2(4)
ORA-01441: cannot decrease column length because some value is too big
SQL> alter table t modify kk varchar2(16);
Table altered
SQL> alter table t set unused(kk);
Table altered
SQL> SELECT * FROM t;
         A          Z
---------- ----------
         1

SQL> SELECT * FROM user_unused_col_tabs;
TABLE_NAME                          COUNT
------------------------------ ----------
T                                       1
Z                                       1

SQL> alter table t drop unused columns;
Table altered
SQL> alter table t drop  column z;
Table altered

On Constraints In Oracle : deferrable Initially deferred enable disable

You can create constraints with some additional properties in Oracle. For instance you can specify constraint deferrable initially deferred to check constraint when commit done. And also you can disable constraints to make data loading easily-especially ın datawarehouses-.
Demonstration below describes mentioned properties:

Connected to Oracle Database 10g Express Edition Release 10.2.0.1.0
Connected as hr

SQL> drop table t;
Table dropped
SQL> create table t(a number, z number);
Table created
SQL> alter table t add constraint unique_t unique(a) deferrable initially deferred;
Table altered
SQL> insert into t values(1,1);
1 row inserted
SQL> insert into t values(1,1);
1 row inserted
SQL> SELECT * FROM t;
         A          Z
---------- ----------
         1          1
         1          1

SQL> commit;
commit
ORA-02091: transaction rolled back
ORA-00001: unique constraint (HR.UNIQUE_T) violated

SQL> SELECT * FROM t;
         A          Z
---------- ----------

SQL> alter session set constraints = deferred;
Session altered
SQL> alter table t disable constraint unique_t cascade;
Table altered
SQL> insert into t values(1,1);
1 row inserted
SQL> insert into t values(1,1);
1 row inserted
SQL> commit;
Commit complete
SQL> SELECT * FROM t;
         A          Z
---------- ----------
         1          1
         1          1

SQL> alter table t enable constraint unique_t;
alter table t enable constraint unique_t
ORA-02299: cannot validate (HR.UNIQUE_T) - duplicate keys found
SQL> --other
SQL> --set constraints unique_t deferred;--session
SQL> --alter table t drop constraint unique_t;
SQL> --SELECT * FROM user_constraints;
SQL> --SELECT * FROM user_cons_columns;

Creating(NOFORCE, WITH CHECK OPTION parameters) And Inserting Views In Oracle

Views are some kind of storing queries in database. If you have a big query and want use it elsewhere, you can create a view. Normally views do not permit to inserts via themselves. In Oracle it can be possible. Additional features of views in Oracle described below:

Connected to Oracle Database 10g Express Edition Release 10.2.0.1.0
Connected as hr


SQL> drop table t;

Table dropped
SQL> create table t(a number, z number);
Table created
SQL> create or replace view t_v as select a as aa,z as zz from t;
View created
SQL> select * from t_v;
        AA         ZZ
---------- ----------

SQL> insert into t_v values(1,2);
1 row inserted
SQL> insert into t_v values(2,3);
1 row inserted
SQL> commit;
Commit complete
SQL> select * from t;
         A          Z
---------- ----------
         1          2
         2          3

SQL> select * from t_v;
        AA         ZZ
---------- ----------
         1          2
         2          3


SQL> create or replace  noforce view  no_existed_table_v  as select a as aa,z as zz from no_existed_table;

create or replace  noforce view  no_existed_table_v  as select a as aa,z as zz from no_existed_table
ORA-00942: table or view does not exist
SQL> create or replace  force view  no_existed_table_v  as select a as aa,z as zz from no_existed_table;
Warning: View created with compilation errors
SQL> select * from no_existed_table_v;
select * from no_existed_table_v
ORA-04063: view "HR.NO_EXISTED_TABLE_V" has errors

SQL> drop view t_v;

View dropped
SQL> create view t_v as select a as aa,z as zz from t with read only;
View created
SQL> insert into t_v values(1,2);
insert into t_v values(1,2)
ORA-01733: virtual column not allowed here

SQL> create or replace view t_v as select a as aa,z as zz from t where a = 1;

View created
SQL> SELECT * FROM t_v;
        AA         ZZ
---------- ----------
         1          2

SQL> insert into t_v values(3,4);
1 row inserted
SQL> create or replace view t_v as select a as aa,z as zz from t where a = 1 with check option constraint ck_v;
View created
SQL> insert into t_v values(3,4);
insert into t_v values(3,4)
ORA-01402: view WITH CHECK OPTION where-clause violation

03 December 2006

On Associative Arrays, Nested Tables and Varrays In Oracle

Oracle has three different types of collections. I mentioned them in one of my previus posts. You can take more information there.
Now, i want to give examples how to use them:


SQL> DECLARE
  2    TYPE occupation_table IS TABLE OF VARCHAR2(64) INDEX BY VARCHAR2(16);
  3    occupations occupation_table;
  4    k           VARCHAR2(16);
  5  BEGIN
  6    occupations('One') := 'Architecture';
  7    occupations('Two') := 'Engineer';
  8    k := occupations.FIRST;
  9    WHILE k IS NOT NULL LOOP
 10      dbms_output.put_line('occupations(''' || k || ''') is ' || occupations(k));
 11      k := occupations.NEXT(k);
 12    END LOOP;
 13  END;
 14  /

occupations('One') is Architecture
occupations('Two') is Engineer

PL/SQL procedure successfully completed
SQL>

2. Varrays
SQL> create type t_telephone_numbers is varray(2) of varchar2(10);
Type created
SQL> create table workers( name varchar2(16), telephone_list t_telephone_numbers );
Table created
SQL> INSERT INTO workers VALUES('Mennan',t_telephone_numbers('2122122122', '2122221133') );
1 row inserted
SQL> SELECT * FROM workers;
NAME             TELEPHONE_LIST
---------------- --------------
Mennan          
[object]
SQL> SELECT * FROM table(SELECT telephone_list FROM workers WHERE name = 'Mennan'  );
COLUMN_VALUE
------------
2122122122
2122221133

SQL>

3. Nested Tables
SQL> create type t_hobbies is table of varchar2(16);
  2  /

Type created
SQL> create table workers( name varchar2(16), hobbies t_hobbies )
  2    nested table hobbies store as workers_hobbies;

Table created
SQL> SELECT table_name, nested FROM user_tables WHERE table_name like 'WORKERS%';
TABLE_NAME                     NESTED
------------------------------ ------
WORKERS                        NO
WORKERS_HOBBIES                YES

SQL> INSERT INTO workers VALUES('Mennan',t_hobbies('Reading Book', 'Listening') );
1 row inserted
SQL> INSERT INTO workers VALUES('Ali',t_hobbies('Swimming', 'Coding', 'Football') );
1 row inserted
SQL> commit;
Commit complete
SQL> SELECT * FROM workers;
NAME             HOBBIES
---------------- -------
Mennan           [object]
Ali             
[object]
SQL> SELECT * FROM workers_hobbies;
SELECT * FROM workers_hobbies
ORA-22812: cannot reference nested table column's storage table
SQL> SELECT * FROM table(SELECT hobbies FROM workers WHERE name = 'Mennan'  );
COLUMN_VALUE
----------------
Reading Book
Listening

SQL> INSERT INTO workers VALUES('Ayse',t_hobbies('Reading Horror Books') );
INSERT INTO workers VALUES('Ayse',t_hobbies('Reading Horror Books') )
ORA-12899: value too large for column "HR"."WORKERS_HOBBIES"."COLUMN_VALUE" (actual: 20, maximum: 16)
SQL> alter type t_hobbies modify element type varchar2(8) cascade;
alter type t_hobbies modify element type varchar2(8) cascade
ORA-22324: altered type has compilation errors
ORA-22328: object "HR"."T_HOBBIES" has errors.
PLS-00729: only widening of the collection element type is allowed
ORA-06550: line 0, column 0:
PL/SQL: Compilation unit analysis terminated

SQL> alter type t_hobbies modify element type varchar2(64) cascade;
Type altered
SQL> INSERT INTO workers VALUES('Ayse',t_hobbies('Reading Horror Books') );
1 row inserted
SQL> commit;
Commit complete
SQL> SELECT * FROM table(SELECT hobbies FROM workers WHERE name = 'Ayse'  );
COLUMN_VALUE
----------------------------------------------------------------
Reading Horror Books

SQL>

02 December 2006

Handling Exceptions With Bulk Operations In Oracle

When working large amount of data, you must consider exceptions. Suppose that a bulk operation that takes 100.000 of records and inserts it to a table. There may have some exceptions during insertion such as foreign key or not null constraints. If you did not consider exceptions, you can lose your time and do more work. One other assumption that can be, you can get an error on the last record. So all of your correct data can get waste.
Oracle solves this problems with some techniques. One of them is save bulk_exceptions in forall statement. Other is dbms_errlog package. I have described dbms_errlog package one of my older posts. (For performance analysis click here)
Demonstration below simple shows the cases above:

Connected to Oracle Database 10g Express Edition Release 10.2.0.1.0
Connected as SYS

SQL>
SQL> drop table t;

Table dropped

Create a table and add some constraints.
SQL> create table t(i number not null);
Table created
SQL> alter table t add constraint ck_i check( i < 2 );
Table altered

Make insertion with normal way. You will get an exception and all work will be rolled back.
SQL> DECLARE
  2    TYPE t_t IS TABLE OF NUMBER;
  3    n_t t_t;
  4  BEGIN
  5    SELECT decode(rownum, 2, NULL, rownum) BULK COLLECT
  6      INTO n_t
  7      FROM user_tables
  8     WHERE rownum < 4;
  9    FORALL i IN n_t.FIRST .. n_t.LAST
 10      INSERT INTO t VALUES( n_t (i) );
 11  END;
 12  /

DECLARE
  TYPE t_t IS TABLE OF NUMBER;
  n_t t_t;
BEGIN
  SELECT decode(rownum, 2, NULL, rownum) BULK COLLECT
    INTO n_t
    FROM user_tables
   WHERE rownum < 4;
  FORALL i IN n_t.FIRST .. n_t.LAST
    INSERT INTO t VALUES( n_t (i) );
END;

ORA-01400: cannot insert NULL into ("SYS"."T"."I")
ORA-06512: at line 9

SQL> SELECT * FROM t;
         I
----------

SQL> rollback;
Rollback complete

First technique is handling in forall statement. You will get correct records in table.
SQL> DECLARE
  2    TYPE t_t IS TABLE OF NUMBER;
  3    n_t t_t;
  4  BEGIN
  5    SELECT decode(rownum, 2, NULL, rownum) BULK COLLECT
  6      INTO n_t
  7      FROM user_tables
  8     WHERE rownum < 4;
  9    FORALL i IN n_t.FIRST .. n_t.LAST SAVE EXCEPTIONS
 10      INSERT INTO t VALUES( n_t (i) );
 11 
 12  EXCEPTION
 13    WHEN OTHERS THEN
 14      dbms_output.put_line( sqlerrm );
 15      FOR i IN 1 .. SQL%BULK_EXCEPTIONS.COUNT LOOP
 16        dbms_output.put_line(SQL%BULK_EXCEPTIONS(i)
 17                             .ERROR_INDEX || ' : ' ||
 18                             SQLERRM(-1 * SQL%BULK_EXCEPTIONS(i).ERROR_CODE));
 19      END LOOP;
 20 
 21  END;
 22  /

ORA-24381: error(s) in array DML
2 : ORA-01400: cannot insert NULL into ()
3 : ORA-02290: check constraint (.) violated

PL/SQL procedure successfully completed
SQL> SELECT * FROM t;
         I
----------
         1

SQL> rollback;
Rollback complete

Second method is using supplied package dbms_errlog.
SQL> drop table err$_t;
Table dropped
SQL> BEGIN
  2    dbms_errlog.create_error_log('t');
  3  END;
  4  /

PL/SQL procedure successfully completed
SQL> DECLARE
  2    TYPE t_t IS TABLE OF NUMBER;
  3    n_t t_t;
  4  BEGIN
  5    SELECT decode(rownum, 2, NULL, rownum) BULK COLLECT
  6      INTO n_t
  7      FROM user_tables
  8     WHERE rownum < 4;
  9    FORALL i IN n_t.FIRST .. n_t.LAST
 10      INSERT INTO t VALUES( n_t (i) ) log errors reject LIMIT unlimited;
 11 
 12  END;
 13  /

PL/SQL procedure successfully completed
SQL> SELECT * FROM t;
         I
----------
         1


Errors are logged.
SQL> SELECT t.ora_err_number$ errno, t.ora_err_mesg$ errmess FROM err$_t t;
     ERRNO ERRMESS
---------- --------------------------------------------------------------------------------
      1400 ORA-01400: cannot insert NULL into ("SYS"."T"."I")
      2290 ORA-02290: check constraint (SYS.CK_I) violated