Special Query

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



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
/


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;


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
$