Oracle Tips

Object names and Case

Normally Oracle stores most of the information it saves in the Oracle base tables or data dictionary tables in uppercase. But if the objects names are specified within double quotes then the names are stored as it is. By default Oracle internally converts the object name in SQL statements to uppercase so when the rdbms attempts to perform DDL or DML against the imported object Oracle cannot find it.

When objects are imported from other database management tools the object names are usually stored as received. This means that lower case letters, spaces, and other characters not usually found in an Oracle object name may be stored.

You can get around this problem by enclosing the object name in double quotes.

	> select table_name, owner from dba_tables
	  2  where table_name = 'i came from access';

	TABLE_NAME                     OWNER
	------------------------------ ------------------------------
	i came from access             MPOWEL01

	> insert into "i came from access" values ('one');

	1 row created.

	> select * from "i came from access";

	FLD1
	--------------------
	one
	
	> drop table "i came from access";

	Table dropped.

Note that when querying the dictionary query view, look for exactly the object name you expect to find. When referencing the object in an SQL statement enclosed it in double quotes, Oracle understood what object to work with and found it.

It is advisable to inform the developers and customers using Micro Soft tools to use upper case in their DDL for object names. Better yet do not allow objects to be created from Access or other third party tools that suffer from this condition if you can.. Double quotes are also used to enclose Oracle reserved words that are used as column names in SQL statements.

How to implement Sleep Function in PL/SQL ?


DECLARE
  Found  BOOLEAN := FALSE;
  Count  NUMBER  := 0;
BEGIN
  WHILE (NOT Found AND NOT (Count > 10)) LOOP
    BEGIN
      -- Try to get free slot, if OK, set Found = TRUE
      -- else EXCEPTION will automatically fire.
      (Insert Code here)
      Found := TRUE;
    EXCEPTION
      WHEN OTHERS THEN
        Found := FALSE;
        DBMS_LOCK.SLEEP(10);
        Count := Count + 1;
    END;
  END LOOP;
END;
/

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
/

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 sofar,totalwork,time_remaining FROM v$session_longops WHERE sid = 55 and time_remaining > 0; 

     SOFAR  TOTALWORK TIME_REMAINING
---------- ---------- --------------
     8448      11057             20
     8832      11057             17
     9024      11057             16
     9184      11057             14
     9536      11057             12
     9646      11057             11
     9920      11057              9
    10421      11057              5
    10529      11057              4
    10814      11057              2

How to return an exit code from SQL*Plus to the Unix Shell ?


To return a code from SQL*Plus to the UNIX shell script, the WHENEVER SQLERROR can be used. We can return a 
status from 0...255 to the UNIX shell. This is usually in the $? environment variable ( for the Korn or Bash Shell ) 
immediately after execution of a command. 

#!/bin/ksh 

sqlplus -s scott/tiger <<-EOF 1>/dev/null 2>&1 

variable r1 number
whenever sqlerror exit sql.sqlcode 

begin
  -- The Variable rc is the return code (from your programm logic)
  -- which you want to send from SQL*Plus to the shell, when
  -- the scripts aborts due the WHENEVER SQLERROR
  :r1 := 1;
  if (:r1 <> 0)
  then
      raise_application_error((-20000-224) - :r1, 'Error!');
  end if;
end;
/
EOF 

Run this code as follows

$ ./test.ksh
$ echo $? 

1 


The shell will only keep an unsigned byte in the status return value (values 0...255). 
It takes our exit file and just looks at that last byte. By using -20000-224 and subtracting from your return code, 
we end up exiting with the value of your return code. :r1 has to be in the range...255 !

Finding whether a string is a number or not


The following SQL script can be used

select instr(translate(column_name, 'abcdefghijklmnopqrstuvwxyzABCDEFGHIJKLMNOPQRSTUVWXYZ','XXXXXXXXXXXXXXXXXXXXXXXXXXXXXXXXXXXXXXXXXXXXXXXXXXX'),'X') FROM ;

It returns 0 if it is a number, 1 if it is not.

This works if the field has only characters or digits. If you care about other characters, change the translate to add those characters.
 
IN PL/SQL a function can be created

create or replace function is_number(str in varchar2) return number IS
n1 number;
BEGIN
n1 := to_number(str);
return(1);
exception when VALUE_ERROR then return(0);
END;
/

Script to see redundant indexes, where all leading columns are duplicated



select a.owner, b.owner, a.column_name, a.table_name, a.index_name, a.column_position
from 
all_ind_columns a, all_ind_columns b
where 
a.index_owner = b.index_owner and
a.column_name = b.column_name and
a.table_name = b.table_name and
a.column_position = b.column_position and
a.index_name <> b.index_name
ORDER BY a.index_name,a.column_position
/

To pass parameters to a SQL-Script, simply separate them with a space


For
example:

SQL> @script.sql parameter1 parameter2 parameter3

In the SQL-Script, refer the the parameters as &1, &2, and &3.

How to create a read-only table ?


CREATE TRIGGER tab_readonly_emp BEFORE DELETE OR INSERT OR UPDATE
ON employees
BEGIN
  RAISE_APPLICATION_ERROR(-20201, 'Table Status: READ ONLY.');
END;

Information about the Installed Software


V$LICENSE view contains information about license limits.

V$OPTION view lists database options and features for 
           Standard Edition, Enterprise Edition, or Personal Oracle.
 
V$PARAMETER displays information about the initialization parameters 
                  that are currently in effect for the session

V$SPPARAMETER displays information about the contents of the 
                        server parameter file.

V$TIMER view lists the elapsed time in hundredths of seconds. 
        Time is measured since the beginning of the epoch, which is operating system specific, 
        and wraps around to 0 again whenever the value overflows four bytes (roughly 497 days).

V$TIMEZONE_NAMES displays valid time zone names.

V$VERSION displays version numbers of core library components in the Oracle Database. 
                   There is one row for each component.

V$DATABASE displays information about the database from the control file.

GLOBAL_NAME contains one row that displays the global name of the current database.

DATABASE_PROPERTIES lists Permanent database properties.

How to migrate LONG fields into VARCHAR2

The use of LONG values is subject to some restrictions
A method for transfering data from LONG to varchar2 is given


SQL> 
SQL> CREATE TABLE long_tab (
  2  long_field   LONG
  3  ); 

Table created.

SQL> 
SQL> ALTER TABLE long_tab ADD (char_field VARCHAR2(2000)); 

Table altered.

SQL> 
SQL> update long_tab set char_field=long_field;
update long_tab set char_field=long_field
                               *
ERROR at line 1:
ORA-00932: inconsistent datatypes: expected NUMBER got LONG

Use the PL/SQL code

SQL> 
SQL> DECLARE
  2  CURSOR C1 IS SELECT rowid, long_field FROM long_tab;
  3  BEGIN
  4  FOR rec IN C1 LOOP
  5  UPDATE long_tab SET char_field = rec.long_field;
  6  END LOOP;
  7  COMMIT;
  8  END;
  9  / 

PL/SQL procedure successfully completed.

SQL> 
SQL> 
SQL>

RUN SQL command from UNIX shell

The following example shows how to run SQL statement in a single UNIX command


$ echo "select count(*) from tab;" | sqlplus -s scott/tiger

Another method is to store the SQL command is a file

$ sqlplus -s scott/tiger < get_count.sql

  COUNT(*)
----------
        36
$


Your Name:
Email:
Subject:
Comments:

Oracle Tips

See Also