Mennan On Writing...

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 your 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 droppimg and recreating tables. with truncate, no objects will become invalid and grants are remains the same.

Below demonstration shows analyses:

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.

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.

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

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.

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:

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

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.

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.

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:

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:

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:

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:

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:

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:

Today I have found a very powerful web site that contains a bunch of software management links, books, templates, tolls and so on. Thanks to author of site, John Musser, Columbia University. You can enter site here...

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:

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.

Demonstration below simple shows the cases above:

It is possible to make inserts and updates with records. This records can be rowtype of a table or a user defined data type. Let's show with an example:

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

SQL>
SQL> drop table t;

Table dropped

SQL> create table t(i number, v varchar2(16));

Table created

SQL> DECLARE
  2    r_t t%ROWTYPE;
  3    TYPE t_t IS RECORD(
  4       i PLS_INTEGER
  5      ,v VARCHAR2(32));
  6    v_t t_t;
  7  BEGIN
  8    r_t.i := 1;
  9    r_t.v := 'First';
 10 
 11    v_t.i := 2;
 12    v_t.v := 'Second';
 13 
 14    INSERT INTO t VALUES r_t;
 15    INSERT INTO t VALUES v_t;
 16 
 17    r_t.v := 'Not First';
 18    UPDATE t SET ROW = r_t WHERE i = r_t.i;
 19    COMMIT;
 20 
 21  END;
 22  /

PL/SQL procedure successfully completed

SQL> SELECT * FROM t;

         I V
---------- ----------------
         1 Not First
         2 Second

SQL>

It is possible to create anydatas and anytypes in Oracle. Oracle has also some powerful object oriented features. With this OO features, you may not need to use anytypes.

A simple example shows how to use:

Oracle supports to store and manipulate url types. Base type of uri types is UriType type. There are three sub types called HTTPURITYPE, XDBURITYPE and DBURITYPE. For more information please check the docs.

I want to show a simple example with HttpUriType below:

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

SQL>
SQL> DECLARE
  2    content CLOB;
  3    uri     httpuritype;
  4  BEGIN
  5    uri     := httpuritype.createUri('http://www.google.com');
  6    content := uri.getClob();
  7    dbms_output.put_line('URI is ' || uri.getUrl());
  8    dbms_output.put_line('Content Type is ' || uri.getContentType());
  9    dbms_output.put_line('Length is ' || length(content));
 10    dbms_output.put_line('Sample of page is ' || substr(content, 1, 100));
 11  END;
 12  /

URI is http://www.google.com
Content Type is text/html
Length is 3164
Sample of page is <html><head><meta http-equiv="content-type" content="text/html; charset=ISO-8859-9"><title>Google</t

PL/SQL procedure successfully completed

SQL>

Authentication is a very importantt consideration on writing programs in security side. In Oracle's PL/SQL, default behaviour authentication mode of program units are DEFINER that is when executing program units you get writer's authentication. You can override this by explicitly declering authentication mode.

Example below, i simple show the differences bietween two methods:

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

SQL>
SQL> drop function get_count2;

Function dropped

SQL> drop function get_count;

Function dropped

SQL> SELECT COUNT(*)  FROM user_tables;

  COUNT(*)
----------
       730

get_count is default auth mode. When another user calls this function it will use SYS's user_tables

SQL> CREATE OR REPLACE FUNCTION get_count RETURN NUMBER AUTHID DEFINER IS
  2    table_count NUMBER;
  3  BEGIN
  4    SELECT COUNT(*) INTO table_count FROM user_tables;
  5 
  6    RETURN table_count;
  7  END;
  8  /

Function created

get_count2 is CURRENT_USER auth mode. When another user calls this function it will use its user_tables

SQL> CREATE OR REPLACE FUNCTION get_count2 RETURN NUMBER AUTHID CURRENT_USER IS
  2    table_count NUMBER;
  3  BEGIN
  4    SELECT COUNT(*) INTO table_count FROM user_tables;
  5 
  6    RETURN table_count;
  7  END;
  8  /

Function created

SQL> grant execute on get_count to hr;

Grant succeeded

SQL> grant execute on get_count2 to hr;

Grant succeeded

SQL> conn hr/hr;
Connected to Oracle Database 10g Enterprise Edition Release 10.2.0.1.0
Connected as hr

The count is SYS's table count.

SQL> SELECT sys.get_count FROM dual;

 GET_COUNT
----------
       730

The count is HR's table count.

SQL> SELECT sys.get_count2 FROM dual;

GET_COUNT2
----------
        38

SQL>