how to create a database directory on current SO directory

I needed to create a one-click import with datapump script on Windows operating system, and for this I needed to create a database directory mapped on the current directory where the dumps are.
So I wrote an sql file:
 create_directory.sql

CREATE OR REPLACE DIRECTORY DATAPUMP AS '&&1'
/
exit
/

And import_one_click.bat file looks like this:

sqlplus / as sysdba @create_directory.sql %CD%

impdp '/ as sysdba' directory=DATAPUMP  dumpfile=export_andrei.dmp logfile=import_andrei.log schemas='ANDREI'

how to fix a shared server issue?

This is more of a question for you guys.

In the alert log i have this:

Errors in file /oradb/app/oracle/admin/test/bdump/test_s003_123472.trc:
ORA-00600: internal error code, arguments: [ksudel1], [], [], [], [], [], [], []
ORA-00600: internal error code, arguments: [unable to load XDB library], [], [], [], [], [], [], []
Wed Nov  3 15:35:36 2010
found dead shared server 'S000', pid = (10, 2)
found dead shared server 'S001', pid = (24, 3)
found dead shared server 'S002', pid = (28, 1)
found dead shared server 'S003', pid = (29, 1)

And of course lots of trace files with test_s****.trc

Actually the solution I adopted is to make all connection on this database DEDICATED. So I changed everywhere SERVER=DEDICATED. And since then I had no error coming.

Do you have any ideas?

how to create pl/sql procedure to send mails

This is an example of a procedure that sends an email from an Oracle10g database:

CREATE OR REPLACE PROCEDURE  p_send_mail (
   sender          IN VARCHAR2,
   recipient       IN VARCHAR2,
   carbon_copies   IN VARCHAR2 DEFAULT NULL ,
   mail_subject    IN VARCHAR2,
   mail_body       IN VARCHAR2
)
AS
   conntype          UTL_SMTP.connection;
   mailsrvr          VARCHAR2 (50) := 'mail_server_name_or_ip';  
   svrport           VARCHAR2 (3) := 25;
   svrdate           VARCHAR2 (255) := TO_CHAR (SYSDATE, 'dd Mon yy hh24:mi:ss');
   msghdr            VARCHAR2 (2000) := NULL;
   linefeed          VARCHAR (2) := CHR (13) || CHR (10);
   msgbody           VARCHAR2 (8000);
   activation_link   VARCHAR2 (100);
BEGIN
   conntype := UTL_SMTP.open_connection (mailsrvr, svrport);
   msghdr :=
         'Date: '
      || svrdate
      || linefeed
      || 'From: <'
      || sender
      || '>'
      || linefeed
      || 'Subject: '
      || mail_subject
      || linefeed
      || 'To: '
      || recipient
      || linefeed
      || ''
      || linefeed;
   msgbody := msghdr || mail_body;
   UTL_SMTP.helo (conntype, mailsrvr);
   UTL_SMTP.mail (conntype, sender);
   UTL_SMTP.rcpt (conntype, recipient);
   UTL_SMTP.DATA (conntype, msgbody);
   UTL_SMTP.quit (conntype);
END;
/

 On this post you may find this procedure,  an example on how to use this procedure and a solution to an error that he gets.
http://kr.forums.oracle.com/forums/thread.jspa?threadID=650243
Use it well.

how to gather statistics for all the tables

Gathering statistics for schemas, tables , indexes is a very important part of database maintaining tasks. Without statistics a growing database can have really big performance issues.

Here is a procedure that gathers statistics for every table except system ones for Oracle 10g

create or replace procedure analyze_tables
is

err_msg varchar2(200);
v_table varchar2(100);
v_owner varchar2(100);
v_sql varchar2(800);

cursor c_tabela is
select TABLE_NAME , owner
from dba_tables
where owner not in ('CTXSYS','SYS','SYSTEM','WMSYS','OUTLN','XDB','OWNER','DBSNMP','DMSYS','EXFSYS','SYSMAN','TSMSYS')
;

begin
open c_tabela;
fetch c_tabela into v_table,v_owner;
while c_tabela%found loop
v_sql:= 'begin DBMS_STATS.gather_table_stats ('''||v_owner||''','''||v_table||''',ESTIMATE_PERCENT=>50,METHOD_OPT=>''for all indexed columns size auto'', degree => 4, CASCADE=>True); end;';
execute immediate v_sql;
fetch c_tabela into v_table,v_owner;
end loop;

exception
when others then
err_msg := TO_CHAR(SQLCODE) || ':' || SQLERRM;
insert into log_rebuid (data, mesg_err) values (sysdate, 'SCHEMA '||err_msg );

end analyze_tables;

It is good to see on a production system a weekly job that gathers statistics, either fort tables, or schemas. Another interesting procedure is this one:

create or replace procedure rebuild_index (v_index in varchar2)
is
err_msg varchar2(200);
begin
execute immediate 'alter index '||v_index||' rebuild';
exception
when others then
err_msg := TO_CHAR(SQLCODE) || ':' || SQLERRM;
insert into log_rebuid (data, mesg_err) values (sysdate, 'INDEX '||err_msg );
end ;

It rebuilds the index on a table. Be careful because on a 24/7 database, it puts a lock on a table until the index is rebuilt. If it's a big index then you might have some locks on the database.

how to monitor tablespaces using sql

I usually monitor tablespaces using Toad, it has a nice graphical way of showing the tablespaces in DATABASE -> ADMINISTER -> TABLESPACES menu, and SCHEMA BROWSER ->TABLESPACES  to get a better view at the datafiles.

This is a different way of monitoring tablespaces. The following sql shows a complete report of the tablespaces of your database ordered by the free space percentage:

select  sel1.*
          , sel2. autoextensible
from(
SELECT a.tablespace_name,
       ROUND (a.bytes_alloc / 1024 / 1024) megs_alloc,
       ROUND (NVL (b.bytes_free, 0) / 1024 / 1024) megs_free,
       ROUND ( (a.bytes_alloc - NVL (b.bytes_free, 0)) / 1024 / 1024)
          megs_used,
       ROUND ( (NVL (b.bytes_free, 0) / a.bytes_alloc) * 100) Pct_Free,
       100 - ROUND ( (NVL (b.bytes_free, 0) / a.bytes_alloc) * 100) Pct_used,
       ROUND (maxbytes / 1048576) MAX
 FROM (  SELECT f.tablespace_name,
                 SUM (f.bytes) bytes_alloc,
                 SUM (
                    DECODE (f.autoextensible,
                            'YES', f.maxbytes,
                            'NO', f.bytes))
                    maxbytes
            FROM dba_data_files f
        GROUP BY tablespace_name) a,
       (  SELECT f.tablespace_name, SUM (f.bytes) bytes_free
            FROM dba_free_space f
        GROUP BY tablespace_name) b
 WHERE a.tablespace_name = b.tablespace_name(+)
UNION ALL
  SELECT h.tablespace_name,
         ROUND (SUM (h.bytes_free + h.bytes_used) / 1048576) megs_alloc,
         ROUND (
            SUM ( (h.bytes_free + h.bytes_used) - NVL (p.bytes_used, 0))
            / 1048576)
            megs_free,
         ROUND (SUM (NVL (p.bytes_used, 0)) / 1048576) megs_used,
         ROUND (
            (SUM ( (h.bytes_free + h.bytes_used) - NVL (p.bytes_used, 0))
             / SUM (h.bytes_used + h.bytes_free))
            * 100)
            Pct_Free,
         100
         - ROUND (
              (SUM ( (h.bytes_free + h.bytes_used) - NVL (p.bytes_used, 0))
               / SUM (h.bytes_used + h.bytes_free))
              * 100)
            pct_used,
         ROUND (SUM (f.maxbytes) / 1048576) MAX
    FROM sys.v_$TEMP_SPACE_HEADER h,
         sys.v_$Temp_extent_pool p,
         dba_temp_files f
   WHERE     p.file_id(+) = h.file_id
         AND p.tablespace_name(+) = h.tablespace_name
         AND f.file_id = h.file_id
         AND f.tablespace_name = h.tablespace_name
GROUP BY h.tablespace_name
) sel1,
( select max(autoextensible) autoextensible, tablespace_name from dba_data_files  group by tablespace_name
          union all
           select max(autoextensible) autoextensible, tablespace_name from dba_temp_files  group by tablespace_name) sel2
where sel1.tablespace_name= sel2.tablespace_name
ORDER BY 5 asc, max desc

You can find this sql and many other useful ones on this post: http://oracle-core-dba.blogspot.com/2008/01/cool-scripts-for-daily-dba-activities.html.



If you want next to see details of a certain tablespace you can use this sql:


select df.TABLESPACE_NAME, df.FILE_NAME, df.FILE_ID, df.STATUS, df.ONLINE_STATUS ,df.AUTOEXTENSIBLE , DF.USER_BYTES MAX_BYTES, SUM (fS.bytes) BYTES_FREE
 from dba_data_files df, dba_free_space fs
 WHERE df.tablespace_name = fs.tablespace_name(+)
 and df.file_id=fs.file_id(+)
 and df.TABLESPACE_NAME='USERS'
 GROUP BY df.TABLESPACE_NAME, df.FILE_NAME, df.FILE_ID, df.STATUS, df.ONLINE_STATUS ,df.AUTOEXTENSIBLE , DF.USER_BYTES

This is a handy way to monitor your tablespaces especially if you want to build a report concerning the tablespaces and datafiles.

how to log exceptions in a pl/sql procedure

Logging errors in plsql procedures is a very important debugging factor especially if the procedure is used in night jobs. If a night job fails, in the morning it's best that you know why the procedure hang, and what has it done until it hang.

First of all create a log table:

CREATE TABLE AM_ERROR_LOG
(
  ERROR_SQL     VARCHAR2(4000 BYTE),
  DATE_RUN       DATE    DEFAULT SYSDATE,
  NAMEPROCEDURE  VARCHAR2(200 BYTE),
  ERROR VARCHAR2(1000 BYTE)
)
;

It's best that this table is on another schema (a log schema), and especially on another tablespace (a log tablespace). Because this is a log table, and there are many inserts on it, and doesn't have to be that fast, it is best not to have indexes.

Then create a procedure like this one:

CREATE OR REPLACE PROCEDURE p_am_log_err (
   sqlstr      VARCHAR2,
   datee       DATE,
   proc_name   VARCHAR2,
   err         VARCHAR2
)
IS
PRAGMA AUTONOMOUS_TRANSACTION;
BEGIN
   INSERT INTO AM_ERROR_LOG
               (ERROR_SQL, date_run, nameprocedure, ERROR
               )
        VALUES (sqlstr, datee, proc_name, err
               );
   COMMIT;
END;
/

The important thig here is that this procedure has  PRAGMA AUTONOMOUS_TRANSACTION  this means that the commit here doesn't affect other transactions.

Here is an example on how to use the above procedure:

CREATE OR REPLACE PROCEDURE P_night_job_proc()
IS

errMsg VARCHAR2(4000);

BEGIN
---------------------------- procedure body
begin
 -----------------------internal plsql block
COMMIT;

EXCEPTION
                  WHEN OTHERS THEN
                            errMsg := SQLERRM;
                           p_am_log_err('current running sql', sysdate, 'CALCUL_CHEI', errMsg);
end;
  COMMIT;
-------------------- end of procedure body
EXCEPTION
                  WHEN OTHERS THEN
                            errMsg := SQLERRM;
                           p_am_log_err('another running sql', sysdate, 'CALCUL_CHEI', errMsg);
 
END P_night_job_proc;
/


Of course WHEN OTHERS THEN is a very general and easy way to capture an exception. Still it's best practice to capture exceptions and insert them in a log table especially for key procedures.

how to stop an Oracle dbms_job

A dbms_job is created like this:

DECLARE
  X NUMBER;
BEGIN
  DBMS_JOB.SUBMIT
  ( job       => X
   ,what      => 'My_procedure;'
   ,next_date => to_date('21/10/2010 03:00:00','dd/mm/yyyy hh24:mi:ss')
   ,interval  => 'TRUNC(sysdate)+1+3/24'
   ,no_parse  => FALSE
  );
  DBMS_OUTPUT.PUT_LINE('Job Number is: ' || to_char(x));
COMMIT;
END;

This one usually runs at 3 AM, calling the procedure "My_procedure". The thing for the dbms_jobs is that you can set them to run repeatably any time you like.

But when the procedure it calls, hangs, it becomes a bit of a trick. For this example if the procedure hangs, in v$session you can see an active session running a certain sql (in most of the times it's blocking other sessions). If you try to kill the session, in a couple of minutes it appears again. This is the usual behavior of a dbms_job, if the session is killed it starts over and over again until the procedure is successfully done.

To be certain that you have any jobs running, you can run this sql:

SELECT r.sid, r.job, r.this_date, r.this_sec, what
FROM dba_jobs_running r,dba_jobs j
WHERE r.job = j.job
AND j.broken ='N';

To stop the Oracle dbms_job you must issue the following:

BEGIN
 DBMS_JOB.broken
  ( job       =>2  -- this is the job number assigned above
   ,broken  => TRUE
  );
 COMMIT;
END;

Right now you can kill the session if it's doing too much damage to the database. I recommend that you look into the procedure, or into the table of logged exceptions (maybe I'll write a post on how to log all the exceptions in a procedure).
After you are convinced that it's OK to bring the job online issue the following:

BEGIN
 DBMS_JOB.broken
  ( job       =>2
   ,broken  => FALSE
  );
 COMMIT;
END;

When you issue this command, the job starts running again. In many situations this is not a desirable thing. That's why, when I usually have a hang night job, I prefer to reschedule the job for the next day in staid of setting it to broken. This sql should do it:

BEGIN
  DBMS_JOB.CHANGE
    (
      job        => 2
     ,what       => 'My_procedure;'
     ,next_date  => to_date('22/10/2010 03:00:00','dd/mm/yyyy hh24:mi:ss')
     ,interval   => 'TRUNC(sysdate)+1+3/24'
    );
END;

Then a can kill the session, so it will not reappear until it's scheduled. Be careful how you rewrite your procedure so that the next day it wont happen again.

finding blocking sessions

Well I've told you how to kill a session from the database, whatever the reason. One of the reasons may be because it's a blocking session.
There are many ways of finding blocking sessions. I personally use Toad for Oracle for browsing the database, it has a Session Browser, and a Locks tab to find locking sessions.
 Also the following sql will give you the waiting and holding sessions on your instance:

SELECT w.*,
       (SELECT status
          FROM v$session ses
         WHERE ses.sid = w.holding_session)
          holding_ses_status,
       (SELECT sql_text
          FROM v$session ses, v$sql sq
         WHERE     ses.sql_address = sq.address
                and ses.sql_hash_value=sq.hash_value
               AND ses.sid = w.waiting_session
               AND ROWNUM = 1)
          waiting_sql,
       (SELECT sql_text
           FROM v$session ses, v$sql sq
         WHERE     ses.sql_address = sq.address
                and ses.sql_hash_value=sq.hash_value
               AND ses.sid = w.holding_session
               AND ROWNUM = 1)
          holding_sql
  FROM dba_waiters w
 
This sql will give you both the ORACLE SID of both the sessions and the current sql  both of them are currently running.

If you want to detect developer sessions on the production database, the following sql can be used:

 select ses.sid,pr.spid,  ses.status, ses.schemaname, ses.machine, ses.terminal, ses.osuser,ses.program,ses.module, ses.action , ses.logon_time
from v$session ses,
v$process pr
where ses.paddr=pr.addr
and lower(ses.program) in ('plsqldev.exe','toad.exe','sql developer')
order by  pr.pga_used_mem desc

It depends on the company profile if you are allowed or not to kill sessions, it's better to know that before you try to kill any session, or sometimes it's better to give them a report with what session is holding another session.

how to kill an Oracle session

If I want to kill an Oracle database session, in a UNIX environment, I usually do it like this:
1. I take the sessions SPID from an sql like this one:
SELECT ses.sid,
       ses.username,
       ses.status,
       pr.spid
  FROM v$session ses, v$process pr
 WHERE ses.paddr = pr.addr
Or with any other method to see the sessions SID and SPID.
Note down the SPID for the respective SID
2. Connect to the OS with a terminal and run the following command:

kill -9  noted_spid

The thing is that every session in the database is a process in the OS, on a DEDICATED server. So it can be killed from the OS directly.