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>
23 December 2006
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
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>
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
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]
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>
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....
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
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
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;
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
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:
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
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
Subscribe to:
Posts (Atom)