Oracle Tips

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.

	> 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.

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;
/ 

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
/ 

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 

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 !

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;
/

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
/

For
example:

SQL> @script.sql parameter1 parameter2 parameter3

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

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

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.

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> 

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
$