Oracle Tips

The mistery of VARCHAR2 As I understand it, industry standard VARCHAR types can store 'empty' strings, but Oracle currently does not, although it reserves the right to change the functionality of VARCHAR types to have this feature. Oracle invented the VARCHAR2 type which is the non-standard varchar (which changes empty strings to nulls), and *currently* the VARCHAR is just a synonym for VARCHAR2. Oracle advises everyone to use VARCHAR2 and not VARCHAR if they wish to maintain backward compatiblity.

The mistery of Oracle NULL's If a column in a row has no value, then column is said to be null, or to contain a null. Nulls can appear in columns of any datatype that are not restricted by NOT NULL or PRIMARY KEY integrity constraints. Use a null when the actual value is not known or when a value would not be meaningful. Do not use null to represent a value of zero, because they are not equivalent. (Oracle currently treats a character value with a length of zero as null. However, this may not continue to be true in future releases, and Oracle recommends that you do not treat empty strings the same as NULLs.) Any arithmetic expression containing a null always evaluates to null. For example, null added to 10 is null. In fact, all operators (except concatenation) return null when given a null operand. Oracle is usually careful to maintain backward compatibility, so I'd be slightly surprised to see this change even in Oracle 9 and very surprised to see it change in any version of Oracle 8.

The mistery of ROWNUM and ORDER BY From the Oracle7 Server SQL Reference Manual: ROWNUM Oracle7 assigns a ROWNUM value to each row as it is retrieved, before rows are sorted for an ORDER BY clause, so an ORDER BY clause normally does not affect the ROWNUM of each row. However, if an ORDER BY clause causes Oracle7 to use an index to access the data, Oracle7 may retrieve the rows in a different order than without the index, so the ROWNUMs may differ than without the ORDER BY clause. You can use ROWNUM to limit the number of rows returned by a query, as in this example: SELECT * FROM emp WHERE ROWNUM < 10; You can also use ROWNUM to assign unique values to each row of a table, as in this example: UPDATE tabx SET col1 = ROWNUM; Note that conditions testing for ROWNUM values greater than a positive integer are always false. For example, this query returns no rows: SELECT * FROM emp WHERE ROWNUM > 1;

The first row fetched is assigned a ROWNUM of 1 and makes the condition false. The second row to be fetched is now the first row and is also assigned a ROWNUM of 1 and makes the condition false. All rows subsequently fail to satisfy the condition, so no rows are returned.

Can I replicate only certain partitions of a partitioned table ? If so, how ? ..... Solution Summary: YOU HAVE TO REPLICATE THE ENTIRE TABLE Solution Description: Oracle8 Server Replication: Appendix A: New Features: "Oracle8 supports the replication of partitioned tables and indexes. ...." Chapter: Administering A Replicated Environment Partitioned Tables and Indexes: "With masters, you must still replicate the entire table; you cannot replicate a partition. If you want to replicate selected paritions only rather than the entire table use updatable snapshots." Workaround: Create a Snapshot withe a WHERE Clause, which SELECT's only from one Partition.

How to create a read-only table ? Oracle-7 und 8 ermöglicht es, Tablespaces als «Read-Only» zu definieren. Manchmal möchte man jedoch nur eine einzelne Tabelle mit diesem Attribut versehen, dies natürlich für alle Applikationen. CREATE TRIGGER tab_readonly BEFORE DELETE OR INSERT OR UPDATE ON emp FOR EACH ROW BEGIN RAISE_APPLICATION_ERROR(-20201, 'Table Status: READ ONLY.'); END;

How to switch to another Orcale User as DBA ? Many batch jobs must change a user password to a "dummy" password to login as this user temporarilly. The problem is always the encrypted password which cannot be de-crypted so that the password can be reset to the original. There is a way to do this, using the VALUES keyword, this is exactly what IMPORT does when it must create users. We show you how to perform this task using the famous account of SCOTT/TIGER. If a DBA doesn't know the password "TIGER", he can temporarilly change this password to another password, logon as user SCOTT, and set the password back after the jobs as user SCOTT is done. Follow the instructions below: o

Connect as a DBA User and get the encypted passord for User SCOTT

sqlplus system/manager select password from dba_users where username = 'SCOTT';

PASSWORD -----------------------------F894844C34402B67 o

Change the password for User SCOTT temporarilly

alter user scott identified by hello; o

Connect as User SCOTT with the temporary password 'hello' and do the necessary jobs as SCOTT

sqlplus scott/hello o

Reset the original password for SCOTT as a DBA

alter user SCOTT identified by VALUES 'F894844C34402B67';

Show object sizes in the database It's often very interesting to know the size of each object in the database. For example if you need to pin a package, trigger in the memory. Note, that NOT the size of a table including the rows will be shown, but the size of the table itself. Object Code Owner Name Type Size -------- --------------------- --------------------PPB CDR_TOOL PACKAGE 388 107 PPB CDR_TOOL PACKAGE BODY 1736 PPB CLEANUP_PARTIAL_CDR PROCEDURE 4790 PPB CDR TABLE 2091 0 PPB TRG_CLT_MSG TRIGGER 0 453

Source

Parsed

Size

Size

---------

---------

788 2078

0

3382

6296

0 0

-####################################################################### ###################### --- %Purpose: Show Size of each Object itself (without content) in the Database --Show size of each object in the database. Note -that NOT the size including the rows will be -shown for tables, but the size for the table itself. --

-####################################################################### ###################### -set feed off; set pagesize 10000; set wrap off; set linesize 200; set heading on; set tab on; set scan on; set verify off; spool show_obyect_size.lst ttitle off; column column column column column column column

owner format a8 heading 'Object|Owner' name format a30 heading 'Name' type format a13 heading 'Type' source_size format 99999999 heading 'Source|Size' parsed_size format 99999999 heading 'Parsed|Size' code_size format 99999999 heading 'Code|Size' error_size format 99999999 heading 'Error|Size'

SELECT owner, name, type, source_size, parsed_size, code_size, error_size FROM my_object_size WHERE owner NOT IN ('SYS','SYSTEM','PUBLIC') ORDER BY owner,type / spool off;

Viewing The SQL Statements of the CPU-Intensive Oracle Processes First get the top 10 CPU-intensive Oracle processes on the operating system with the first column giving the %CPU used, the second column unix PID, the third column USER , the fourth column TERMINAL, and the last column Unix PROCESS (works only for UNIX). ps -eaf -o pcpu,pid,user,tty,comm | grep ora | grep -v \/sh | grep -v ora_ | sort -r | head -20 Now you can specify the found PID in the following SQL-Statement: column username format a9 column sql_text format a70 SELECT a.username, b.sql_text FROM v$session a, v$sqlarea b, v$process c WHERE (c.spid = '&PID' OR a.process = '&PID') AND a.paddr = c.addr AND a.sql_address = b.address /

How to create an empty copy of an existing table ? CREATE TABLE new_table AS SELECT * FROM old_table WHERE 1 = 2; Table created.

How to remove duplicate rows from a table ? SQL> select * from address; NAME -------zahn zahn hodler wyss

VORNAME -------martin martin kurt bruno

BIRTH ---1954 1954 1962 1965

Get the Duplicates with: SELECT name,vorname FROM address GROUP BY name,vorname HAVING COUNT(*) > 1; NAME VORNAME -------- -------zahn martin Delete the Duplicates with DELETE from address A WHERE (A.name, A.vorname, A.birth) IN (SELECT B.name, B.vorname, B.birth FROM address B WHERE A.name = B.name AND A.vorname = B.vorname AND A.birth = B.birth AND A.rowid > B.rowid); 1 row deleted.

How to create a new Control file, how to document the Database ? Connect as SYSTEM or SYS and do: ALTER DATABASE BACKUP CONTROLFILE TO TRACE; SELECT * FROM v$dbfile; SELECT * FROM v$logfile; The TRACE File for the Controlfile can be found in the directory defined by the init.ora Parameter: core_dump_dest.

How many valid / invalid objects exists owned by this oracle user ?

Often we should know, how many valid and invalid objects an oracle user ows. Specially if we compare a schema on two different databases. SELECT DISTINCT (object_type) object, status, COUNT(*) FROM user_objects GROUP BY object_type, status; OBJECT ------------INDEX PACKAGE PACKAGE BODY PROCEDURE PROCEDURE SEQUENCE TABLE TRIGGER TRIGGER

STATUS COUNT(*) ------- --------VALID 95 INVALID 1 INVALID 1 INVALID 1 VALID 2 VALID 20 VALID 63 INVALID 3 VALID 1

Top-N SQL queries with Inline Views Top-N queries Suppose you want to retrieve and sort information on the 4 most recently hired employees in a very efficient way. This can be achieved using an inline view combined with ORDER BY and ROWNUM. Inline Views An in-line view, which is a feature of a Top-N SQL query, is a subquery. This type of subquery differs from a regular subquery by containing an ORDER BY clause which is not allowed in a regular subquery. The ROWNUM condition, which is an enhanced sorting mechanism, would be used in the outer query to complete the Top-N SQL query. Example We need to retrieve and sort information on the 4 most recently hired employees from the following list (marked in blue color). SELECT empno,ename,TO_CHAR(hiredate,'DD.MM.YYYY') "hiredate" FROM emp ORDER BY hiredate DESC; EMPNO ---------7876 7788 7934 7900 7902 7839 7654

ENAME ---------ADAMS SCOTT MILLER JAMES FORD KING MARTIN

hiredate ---------12.01.1983 09.12.1982 23.01.1982 03.12.1981 03.12.1981 17.11.1981 28.09.1981

7844 7782 7698 7566 7521 7499 7369

TURNER CLARK BLAKE JONES WARD ALLEN SMITH

08.09.1981 09.06.1981 01.05.1981 02.04.1981 22.02.1981 20.02.1981 17.12.1980

The first approach is to used the following query, which does not select the Top-4 rows ! SELECT empno,ename,hiredate FROM emp WHERE ROWNUM < 5 ORDER BY hiredate DESC; EMPNO ---------7566 7521 7499 7369

ENAME ---------JONES WARD ALLEN SMITH

HIREDATE ---------02.04.1981 22.02.1981 20.02.1981 17.12.1980

The solution is to use an inline view with an ORDER BY and a ROWNUM condition, in the outer query to complete the Top-N SQL query. SELECT * FROM (SELECT empno,ename,hiredate FROM emp ORDER BY hiredate DESC) WHERE ROWNUM < 5; EMPNO ---------7876 7788 7934 7900

ENAME ---------ADAMS SCOTT MILLER JAMES

HIREDATE ---------12.01.1983 09.12.1982 23.01.1982 03.12.1981

The purpose of this query is to retrieve and sort information on the 4 most recently hired employees. This is a Top-N SQL query which is more efficient than a regular query because Oracle stores a maximum of only 5 rows as the data is retrieved from the table avoiding sorting all of the rows in the table at once. The WHERE clause contains 'ROWNUM < 5' which prevents sorting on more than 5 rows at one time -cool isn't it ?

Tracking the progress of a long running statement Sometimes you run an INSERT or DELETE statement that takes a long time to complete. You have wondered how many rows have already been inserted or deleted so that you can decide whether or not to abort the statement. Is there a way to display how many rows have been deleted while the statement is occurring ?

You can query the V$SESSION_LONGOPS table to track the progress of the statement. Example: Starting the following long running INSERT INSERT INTO bigemp SELECT * FROM bigemp; Check the progress: SELECT sid,sofar,totalwork,time_remaining FROM v$session_longops WHERE sid = 10 and time_remaining > 0; SID SOFAR TOTALWORK TIME_REMAINING ---------- ---------- ---------- -------------10 8448 11057 20 10 8832 11057 17 10 9024 11057 16 10 9184 11057 14 10 9536 11057 12 10 9646 11057 11 10 9920 11057 9 10 10421 11057 5 10 10529 11057 4 10 10814 11057 2 Read filename from a given PATH CREATE OR REPLACE FUNCTION get_fname(p_path IN VARCHAR2) RETURN VARCHAR2 IS l_posi NUMBER; l_fname VARCHAR2(100); BEGIN l_posi := length(p_path); LOOP --- NT or Unix -IF SUBSTR(p_path,l_posi,1) IN ('/','\') THEN l_fname := SUBSTR(p_path,l_posi + 1); EXIT; ELSE l_posi := l_posi - 1; IF (l_posi < 0) THEN EXIT; END IF; END IF; END LOOP; RETURN(l_fname); END; / Test the procedure from SQL*Plus

SQL> SET SERVEROUTPUT ON; SQL> EXEC delete_archive_logs Deleting: D:\ORADATA\ARK1\ARC\ARK1_83.ARC Deleting: D:\ORADATA\ARK1\ARC\ARK1_84.ARC Deleting: D:\ORADATA\ARK1\ARC\ARK1_85.ARC PL/SQL procedure successfully completed. The procedure can easily enhanced to read the ARCHIVEDIR from the data dictionary and to enter the days back to delete (instead of the fixed 30 days) as an argument for the procedure DELETE_ARCHIVE_LOGS.

Restrict the Number of Records While developping database application we often have to restrict the number of records an end user can insert into a table. A simple solution is the following code fragment in the BEFORE-INSERT-FOR-EACH-ROW Trigger: select count(*) into l_cnt from detail; if l_cnt >= 5 then raise_application_error(-20101, 'Maximum number of records exceeded'); end if; To reconstruct or understand the following descriptions please download the example code ex_restr_nbrof_records.sql or consult the source code, we assume, that the maximum number of detailrecords must be limited to five. We try to insert a 6Th record: insert into detail values (6,'M1'); * ERROR at line 1: ORA-20101: Maximum number of recors reached ORA-06512: at "SCOTT.BI_DETAIL", line 6 ORA-04088: error during execution of trigger 'SCOTT.BI_DETAIL' So far so good. We’ll now demonstrate what happens, when we insert records from two concurrent transactions. Delete one record in order to be able to insert one record hereafter: delete from detail where id=5; 1 row deleted. commit; Invoke the SQL-Plus tool and insert one record insert into detail values (5,'M1'); 1 row created.

DO NOT COMMIT ! Invoke a second session by starting a SQL-Plus again and run the statement insert into detail values (6,'M1'); 1 row created. commit; Commit complete. Change to the other session and issue a commit commit; Commit complete. select count(*) from detail; COUNT(*) ---------6 We now have 6 records. The maximum number allowed was 5 ! How to avoid this situation ? Oracle does not support the "dirty read“ isolation level which is defined with: "A transaction reads data written by concurrent uncommitted transaction“ A solution to solve this problem is to lock the corresponding master record: Change the ON INSERT Trigger: create or replace trigger bi_detail before insert on detail for each row declare l_cnt number; l_dummy master.id%type; begin -- lock the master record to avoit too many record -- by using concurrent sessions. select id into l_dummy from master where id = :new.m_id for update nowait; select count(*) into l_cnt from detail; if l_cnt >= 5 then raise_application_error( -20101,'Maximum number of recors reached'); end if; end; Delete the 2 records to test the solution: delete from detail where id=5; delete from detail where id=6; commit;

There should be 4 records now: select count(*) from detail; COUNT(*) ---------4 Now we try again with the two concurrent sessions: insert into detail values (5,'M1'); 1 row created. Change to the second session and issue: insert into detail values (6,'M1'); ERROR at line 1: ORA-00054: resource busy and acquire with NOWAIT specified ORA-06512: at "SCOTT.BI_DETAIL", line 6 ORA-04088: error during execution of trigger 'SCOTT.BI_DETAIL' Go back to the first session and run: commit; Commit complete. Remarks If an exact maximum number of records is specified for a table, using a lock is the only solution to avoid the above situation demonstrated. Often we want to limit the number of deteilrecords in a way, that an end user can’t claim a huge amount of disk space by inserting unbound number of records. In this case, the simple solution shown at the begin will do it. If the table doesn’t use a foreign key constraint, you can implement an auxilliary table, insert one row and lock this row instead of the masterrecord.

Interacting with Oracle via the UNIX Korn shell UNIX remains a fabulous choice for hosting Oracle databases. Both technologies have proven to be more flexible and more stable for more time than most of us care to remember. The remarkable 'Korn' shell, when coupled with the strength of the standard UNIX tool set, seems to be able to extract the best from any programmer. While many Oracle customers shy away from actively adopting and supporting UNIX development, most are perfectly happy to accept 'shell script', and 'shell script' is all we need for many back-end applications. This article demonstrates how SQL*Plus can be used in the same manner as any UNIX tool. And you will find that interacting with an Oracle database via a 'shell script' program is simple, flexible and dynamic-the UNIX way.

Background / Overview

SQL*Plus will happily integrate into the standard UNIX IO streams. The simplest way to demonstrate this point is that the following UNIX command. Note, that you first must start the Korn Shell (usually /bin/ksh), '$' is the Shell Prompt. /bin/ksh $ print "select * from dual;" | sqlplus -s scott/tiger Produces the following output: D X [ Note: the '-s' flag suppresses the standard Oracle banner. ] Once you grasp this, the possibilities for using SQL*Plus from UNIX can become quite exciting. You can construct and run Dynamic SQL or PL/SQL statements without difficulty. There is also a mechanism available to a shell script programmer that avoids commit or rollback processing between SQL*Plus transactions. By using pipes to deliver SQL or PL/SQL statements to SQL*Plus, you can avoid the use of temporary files and construct SQL or PL/SQL statements 'on-the-fly'. You can also use pipes to collect the output generated from SQL*Plus (thereby avoiding temporary files, once again), and interpret the output of pipes using the UNIX tool set. This article uses a simple UNIX file to Oracle table interface to demonstrate UNIX/Oracle communications techniques.

How to escape special characters in Oracle ? If you want to retrieve TABLE_NAMES from ALL_TABLES where the table name is like 'ADD_' using the following query, you may notice that the query is returning ADDRESS and ADD_CODES: create table address (p1 number); create table add_codes (p1 number); select distinct table_name from all_tables where table_name like 'ADD_%'; TABLE_NAME ---------ADDRESS ADD_CODES If you try to escape the '_' character with the following query, you will still get the same result.

select distinct table_name from all_tables where table_name like 'ADD\_%' Therefore the question is: How do you use LIKE to find data that contains an underscore or percent sign ? The answer is to escape the underscore and/or percent sign in the LIKE template. You have to designate what character you would like to use via the ESCAPE keyword. A slash is commonly used, but any character would actually work: select distinct table_name from all_tables where table_name like 'ADD\_%' ESCAPE '\';

Use of the NVL2 Function NVL2: check for the existence of NOT NULL Most of us are familiar with the NVL function which checks for the existence of NULL values. Oracle provides a new function called NVL2 which checks for the existence of NOT NULL. The syntax for this function is as follows. NVL2(expr1,expr2,expr3); If expr1 is not null then the function will return expr2. Otherwise, the function will return expr3. The expr1 can have any datatype and arguments expr2 and expr3 can be of any datatype other than LONG. The datatype of the return value is that of expr2. Example: SQL> select empno,ename,sal,comm,nvl2(comm,1,0) commpre from emp order by empno; EMPNO ---------7369 7499 7521 7566 7654 7698 7782 7788 7839 7844 7876 7900 7902 7934

ENAME SAL COMM COMMPRE ---------- ---------- ---------- ---------SMITH 800 0 ALLEN 1600 300 1 WARD 1250 500 1 JONES 2975 0 MARTIN 1250 1400 1 BLAKE 2850 0 CLARK 2450 0 SCOTT 3000 0 KING 5000 0 TURNER 1500 0 1 ADAMS 1100 0 JAMES 950 0 FORD 3000 0 MILLER 1300 0

The above query checks for the presence of NOT NULL in the COMM field, and returns 1 where COMM is NOT NULL and 0 where it is NULL.

Here are the differences between the NVL and NVL2 functions: o

NVL takes 2 arguments while NVL2 takes 3.

o

NVL returns the first argument if the first argument is not null, whereas NVL2 returns the second argument if the first argument is not null and returns the third argument if the first argument is null.

o

In NVL, the datatype of the return is that of the first argument, in NVL2 the datatype of the return is that of the second argument.

COUNT TABLE COLUMNS select COUNT(*) from dba_tab_columns where table_name = YourTableNameHere;