Monday, January 25, 2010

SQL based export data to file

Many times in my praxis I had situation that customer(developers) asks for table export to plain text like file. Here are tipical examples of that need:
  • This is situation when Oracle exp or expdp tools are not possible to use because data should be imported to some other database type (MSSQL, dBase, MySQL, MSAccess etc.)
  • Heterogeneous services are not available (data has to transported from one place to another).
  • Data comes from several tables (not only one table)
  • Toad or other similar tools cannot be used for export because tables size are several GB (so they cannot be run from client side) and server is not possible (Linux!).
  • You want to have table exported to text file on disk and use it as external table in Oracle when appropriate.

To make this real I have invented my own function sql_to_csv which allows me to do all that.

Implementation

Because I use utl_file I need to define output directory. For that I use one directory which I grant to PUBLIC user (for re usability in any schema):
CREATE OR REPLACE DIRECTORY 
EXPDP_DIR AS 
'D:\share';

GRANT READ, WRITE ON DIRECTORY SYS.EXPDP_DIR TO PUBLIC;
If you all ready have other directory definition this can be avoided of course.
Remember that function can be run from anywhere but result is always in directory which must be available from instance server side.

Next is to create function sql_to_csv which will do the export:
/*---------------------------------------------------------------------
 NAME    : sql_to_csv.fnc
 PURPOSE : export data retrieved from plain sql result set
 Date    : 22.10.2007.
 Author  : Damir Vadas, damir.vadas@gmail.com

 Remarks : more tables resultset is supported as well

 Changes (DD.MM.YYYY, Name, CR/TR#):
-----------------------------------------------------------------------*/
create or replace function sql_to_csv (p_query     in varchar2, 
                                       p_dir       in varchar2,
                                       p_filename  in varchar2,
                                       p_col_separator in varchar2 default ';',
                                       p_rec_separator  in varchar2 default chr(10)
                                      ) return PLS_INTEGER
AUTHID CURRENT_USER
IS
  line_term_lin CONSTANT VARCHAR2(2) := chr(10);
  line_term_win CONSTANT VARCHAR2(2) := chr(10)||chr(13);
  
  cur_da_cursor   PLS_INTEGER DEFAULT dbms_sql.open_cursor;
  l_column_value  VARCHAR2(4000);
  l_table_desc    dbms_sql.desc_tab;
  l_status        PLS_INTEGER;
  l_column_count  PLS_INTEGER;
  l_nls_date      VARCHAR2(32);
  l_separator     VARCHAR2(10) default '';
  l_cnt           PLS_INTEGER default 0;
  l_output        utl_file.file_type;
begin
  select value into l_nls_date
    from nls_session_parameters
   where lower(parameter) = 'nls_date_format';

  l_output := utl_file.fopen(p_dir,p_filename,'w');

  execute immediate 'alter session set nls_date_format=''dd-mon-yyyy hh24:mi:ss'' ';

  dbms_sql.parse(cur_da_cursor,p_query,dbms_sql.native);

  dbms_sql.describe_columns (cur_da_cursor,l_column_count,l_table_desc);

  for i in 1 .. l_column_count loop
    dbms_sql.define_column (cur_da_cursor,i,l_column_value,4000);
  end loop;

  l_status := dbms_sql.execute(cur_da_cursor);
 
  while (dbms_sql.fetch_rows(cur_da_cursor) > 0) loop
    l_separator := '';
    for i in 1 .. l_column_count loop
      dbms_sql.column_value (cur_da_cursor,i,l_column_value);
      if l_table_desc(i).col_type in (8, 23, 24, 112, 113, 114, 115) then
        l_column_value:= '***UNPRINTABLE***';
      end if;
      utl_file.put(l_output, l_separator || l_column_value);
      l_separator := p_col_separator;     
    end loop;
    -- for default record terminators platform line termination is enough!!
    if NOT ( (p_rec_separator = line_term_win) OR (p_rec_separator = line_term_lin)) then
      utl_file.put (l_output, p_rec_separator);
    end if;
    utl_file.new_line(l_output);   
    l_cnt := l_cnt+1;
  end loop;
  dbms_sql.close_cursor(cur_da_cursor);
  utl_file.fclose(l_output); 
  execute immediate 'alter session set nls_date_format='''||l_nls_date||'''';
  return l_cnt;
exception
  when others then
    begin
      l_cnt := sqlcode ;
      dbms_output.put_line (chr(9));
      dbms_output.put_line (p_query);
    exception
      when others then
        null;
    end;   
    execute immediate 'alter session set nls_date_format='''||l_nls_date||'''';
    return l_cnt;
end sql_to_csv;
Regardless that function name sql_to_csv suggests that output is only comma separated values (what is default), as you can see, column separator and record separator can be redefined as you wish.
Use non default record separator value (p_rec_separator) if you have multi line values in resulting columns.

Example I

Let us see export from sys.dba_source table:
SQL> desc dba_source;
 Name         Null?    Type
 ------------ -------- ------------------
 OWNER                 VARCHAR2(30)
 NAME                  VARCHAR2(30)
 TYPE                  VARCHAR2(12)
 LINE                  NUMBER
 TEXT                  VARCHAR2(4000)

SQL>

To make this example more easy, only first five rows will be exported:
SQL> declare
  2    l_rows pls_integer;
  3  begin
  4    l_rows := sql_to_csv('select * from dba_source where rownum<=5',
                            'EXPDP_DIR', 
                            'dba_source_csv.csv'
                           );
  5    dbms_output.put_line('exported rows:'||l_rows);
  6  end;
  7  /
exported rows:5

PL/SQL procedure successfully completed.

SQL>
Now let's see the result of export:
D:\share>dir
 Volume in drive D is System
 Volume Serial Number is 5045-B4D2

 Directory of D:\share

25.01.2010  09:38    <dir>          .
25.01.2010  09:38    <dir>          ..
25.01.2010  09:37               374 dba_source_csv.csv
               1 File(s)            374 bytes
               2 Dir(s)  37.724.327.936 bytes free

D:\share>
Content of "dba_source_csv.csv" is:
D:\share>more dba_source_csv.csv
SYS;RE$NV_LIST;TYPE;1;TYPE     re$nv_list
SYS;RE$NV_LIST;TYPE;2;                                                        AS OBJECT
SYS;RE$NV_LIST;TYPE;3;( actx_list sys.re$nv_array,
SYS;RE$NV_LIST;TYPE;4;  MEMBER PROCEDURE add_pair(name IN varchar2, value IN sys.anydata),
SYS;RE$NV_LIST;TYPE;5;  MEMBER PROCEDURE remove_pair(name IN varchar2),
D:\share>

Example II

If you have table that has some fields which cannot be exported (i.e. LONG, LOB etc.) these columns in output will be automatically skipped and written as "***UNPRINTABLE***" in output file. In such a way you do not have to think about columns types and gives you opportunity lately edit output file as you like. However such a approach ensure that external tables will properly function (number of fields are the same).
SQL> desc tab.t_dnevnik_troskova;
 Name                                      Null?    Type
 ----------------------------------------- -------- ----------------------------
 DATUM                                     NOT NULL DATE
 ID                                        NOT NULL NUMBER(6)
 BR_RAC                                    NOT NULL VARCHAR2(15)
 TSR_ID_TSR                                NOT NULL VARCHAR2(5)
 PAR_SIFPP                                 NOT NULL NUMBER(8)
 TSR_TIP                                   NOT NULL VARCHAR2(2)
 IZNOS                                              NUMBER(16,2)
 NAPOMENA                                           LONG
 DAT_DOK                                            DATE
 DAT_KRE                                            DATE
 OPE_KRE                                            VARCHAR2(30)
 DAT_PRO                                            DATE
 OPE_PRO                                            VARCHAR2(30)
SQL>
Notify column "NAPOMENA" which is LONG type:
Export data through mine function:
SQL> declare
  2    l_rows pls_integer;
  3  begin
  4    l_rows := sql_to_csv('select * from tab.t_dnevnik_troskova', 'EXPDP_DIR', 't_dnevnik_csv.csv');
  5    dbms_output.put_line('exported rows:'||l_rows);
  6  end;
  7  /
exported rows:3

PL/SQL procedure successfully completed.

SQL>
Result looks like:
D:\share>type t_dnevnik_csv.csv

19-svi-2008 00:00:00;1;2356;297;1822;1;1688;***UNPRINTABLE***;;19-svi-2008 13:42:11;ZAST;19-svi-2008 16:00:30;TAB
19-svi-2008 00:00:00;3;549;298;1822;1;1568;***UNPRINTABLE***;;19-svi-2008 16:13:28;TAB;;
19-svi-2008 00:00:00;5;3548/2008;291;1822;5;522;***UNPRINTABLE***;18-svi-2008 00:00:00;19-svi-2008 16:37:16;TAB;;

D:\share>

Example III

As said at the beginning, data can be exported from more then one table. Here is code that use join query definition:
select DC.CUST_FIRST_NAME||' '||DC.CUST_LAST_NAME, 
       DO.ORDER_TIMESTAMP, DO.ORDER_TOTAL
  from DEMO_CUSTOMERS dc, DEMO_ORDERS do
 where DC.CUSTOMER_ID=DO.CUSTOMER_ID
Implementation in function looks like:
SQL> declare
  2    l_rows pls_integer;
  3    l_stmt varchar2(4000);
  4  begin
  5    l_stmt := 'select DC.CUST_FIRST_NAME ||' ||''' '''|| '|| DC.CUST_LAST_NAME CUSTOMER, '||
  6              '       DO.ORDER_TIMESTAMP, DO.ORDER_TOTAL '||
  7              '  from DEMO_CUSTOMERS dc, DEMO_ORDERS do '||
  8              ' where DC.CUSTOMER_ID=DO.CUSTOMER_ID ';
  9    l_rows := sql_to_csv (l_stmt, 'EXPDP_DIR', 't_complex_query.csv');
 10    dbms_output.put_line('exported rows:'||l_rows);
 11  end;
 12  /
exported rows:10

PL/SQL procedure successfully completed.

SQL>
And the result is:
D:\share>type t_complex_query.csv
Eugene Bradley;22-srp-2009 14:58:35;500
John Dulles;05-ruj-2009 14:58:35;1200
William Hartsfield;31-kol-2009 14:58:35;599
William Hartsfield;26-kol-2009 14:58:35;1999
Fiorello LaGuardia;06-kol-2009 14:58:35;3800
Albert Lambert;01-kol-2009 14:58:35;40
Albert Lambert;27-srp-2009 14:58:35;450
Edward Logan;21-kol-2009 14:58:35;750
Edward Logan;16-kol-2009 14:58:35;40
Edward "Butch" OHare;11-kol-2009 14:58:35;250

D:\share> 

Error control

If you put query (previously one correct query) and result is like:
...
DC.CUSTOMER_ID=DO.CUSTOMER_ID
exported rows:-29283

PL/SQL procedure successfully completed.
SQL>
Then negative number of rows "exported rows:-29283" indicate indicates ORA-29283 error. In this case "invalid file operation" what says that directory doesn't exists. In this way it is easy to debug the problem.

Cheers!

Sunday, January 24, 2010

Handling errors in PL/SQL

Handling errors in Oracle is very easy when you know the right path to (not so known) ready made procedures for that.

Small example

Suppose we have three simple PL/SQL procedures declared in next way:
create or replace procedure first_level_call as
  l_error varchar2(4000);
begin
  dbms_output.put_line('first_level call!');
  second_level_call;  
end;
/ 
"first_level_call" procedure calls "second_level_call" procedure:
create or replace procedure second_level_call as
begin
  dbms_output.put_line(chr(9)||'second_level call!');
  force_ora_error;
end;
/
"second_level_call" procedure calls "force_ora_error" procedure:
create or replace procedure force_ora_error as
  i pls_integer;
  j pls_integer;
begin
  i := 1;
  j := 0;
  dbms_output.put_line(chr(9)||chr(9)||' force_ora_error call!');
  i := 1/j;
  dbms_output.put_line('This line you will never see!');
end;
/

When you run "first_level_call" procedure, result looks like:
SQL> exec first_level_call;
first_level call!
        second_level call!
                 force_ora_error call!
BEGIN first_level_call; END;

*
ERROR at line 1:
ORA-01476: divisor is equal to zero
ORA-06512: at "DAMIRV.FORCE_ORA_ERROR", line 8
ORA-06512: at "DAMIRV.SECOND_LEVEL_CALL", line 4
ORA-06512: at "DAMIRV.FIRST_LEVEL_CALL", line 5
ORA-06512: at line 1


SQL> 

All looks clear and easy to understand:
  1. ORA-01476: divisor is equal to zero (ORA error with description)
  2. procedure "DAMIRV.FORCE_ORA_ERROR" line 8 (this is the line where error happened)
  3. procedure "DAMIRV.SECOND_LEVEL_CALL" line 4 (this is the line where "force_orra_error" has been called)
  4. procedure "DAMIRV.FIRST_LEVEL_CALL" line 5 (this is the line where "first_level_call" has been called)
  5. at line 1 this is anonymous call from SQL*Plus ("exec first_level_call"-what started all other calls)
Remember that dbms_output.put_line commands gives output ONLY from of sqlplus like tools. When you run the same code from other Oracle tools (i.e. in forms or reports etc.) dbms_output.put_line makes no output-like it doesn't exist at all!

So if we want to make persistent error logging-we have to save error to some variable.
create or replace procedure first_level_call as
  l_error varchar2(4000);
begin
  dbms_output.put_line('first_level call!');
  second_level_call;  
exception
  when others then
    l_error:=SQLERRM;
    dbms_output.put_line(l_error);
end;
/ 
When you show the result of saved variable (l_error), result looks like:
SQL> exec first_level_call;
first_level call!
        second_level call!
                 force_ora_error call!
ORA-01476: divisor is equal to zero

PL/SQL procedure successfully completed.

SQL> 
Hmmmmmmm! ORA-01476: divisor is equal to zero is what we have saved! All important data like line numbers where error happened or even in what procedure error happened are lost for good!!

Solution

Solution is to use Oracle several predefined DBMS_UTILITY calls.

Place additional code in exception part of "first_level_call" procedure. "first_level_call" procedure now looks like:
create or replace procedure first_level_call as
  l_error varchar2(4000);
begin
  dbms_output.put_line('first_level call!');
  second_level_call;  
exception
  when others then
    l_error:=DBMS_UTILITY.FORMAT_ERROR_BACKTRACE||
             chr(10)||
             DBMS_UTILITY.format_call_stack;
    dbms_output.put_line(l_error);
end;
/ 
When you run "first_level_call" procedure again, all data are saved much more better then before:
SQL> exec first_level_call;
first_level call!
        second_level call!
                 force_ora_error call!
ORA-06512: at "DAMIRV.FORCE_ORA_ERROR", line 8
ORA-06512: at "DAMIRV.SECOND_LEVEL_CALL", line 4
ORA-06512: at "DAMIRV.FIRST_LEVEL_CALL", line 5

----- PL/SQL
Call Stack -----
  object      line  object
  handle    number  name
000007FF0DFD45A8         8  procedure DAMIRV.FIRST_LEVEL_CALL
000007FF0DF4FB58         1  anonymous block

PL/SQL procedure successfully completed.

SQL> 

Cheers!

Friday, January 22, 2010

Prevent sqlplus access to database

Preventing user access with non application tool (i.e. SQL*Plus or Toad) is one of very important task in proper security setup. Here is briefly how can one pretty easy implement straight control for that.

Implementation

As sysdba create role "SQLPLUS_ROLE":
CREATE ROLE SQLPLUS_ROLE NOT IDENTIFIED;

BEGIN
  SYS.DBMS_RESOURCE_MANAGER.clear_pending_area();
  SYS.DBMS_RESOURCE_MANAGER.create_pending_area();
  SYS.DBMS_RESOURCE_MANAGER_PRIVS.GRANT_SWITCH_CONSUMER_GROUP (
   'SQLPLUS_ROLE', 'DEFAULT_CONSUMER_GROUP', false);
  SYS.DBMS_RESOURCE_MANAGER.submit_pending_area();
END;
/
This role must be added to every user or roles (execept ones with sysdba role) that want to access to database outside default application. Here are some examples.
First for users:
GRANT SQLPLUS_ROLE TO DAMIRV WITH ADMIN OPTION;
GRANT SQLPLUS_ROLE TO SCOTT;
and one for roles (works also for all users that own role "DBA" in this case):
GRANT SQLPLUS_ROLE TO DBA;

Then create database trigger (after logon) whose job is to check EVERY incoming connection to database:
CREATE OR REPLACE TRIGGER block_tools_TGB$ALG
  AFTER LOGON ON DATABASE
DECLARE
  v_prog sys.v_$session.program%TYPE;
  l_count pls_integer;
  
  my_forced_exception EXCEPTION;
  PRAGMA EXCEPTION_INIT(MY_FORCED_EXCEPTION, -20101);  
BEGIN
  SELECT program 
    INTO v_prog 
    FROM sys.v_$session
   WHERE      audsid = USERENV('SESSIONID')
         AND  audsid != 0  -- Don't Check SYS Connections
         AND  ROWNUM = 1;  -- Parallel processes will have the same AUDSID's
  IF  UPPER(v_prog) LIKE '%SQL%' OR        -- SQL like programs!
      UPPER(v_prog) LIKE '%TOAD%' OR       -- Toad
      UPPER(v_prog) LIKE '%BUSOBJ%' OR     -- Business Objects
      UPPER(v_prog) LIKE '%EXCEL%'         -- MS-Excel plug-in
  THEN
    SELECT COUNT (*)
      INTO l_count
      FROM dba_role_privs
     WHERE     granted_role = 'SQLPLUS_ROLE'
           AND grantee = SYS_CONTEXT ('userenv', 'session_user');    
     IF l_count=0 THEN
       RAISE my_forced_exception;
     END IF;
  END IF;
EXCEPTION
  WHEN my_forced_exception THEN 
    RAISE_APPLICATION_ERROR(-20101, 'Action not allowed. Please contact your DBA to help you!');
  WHEN OTHERS THEN
    null;
END;
/
As you can see mine restrictions are pretty exact. However test these settings on test before implementing this on production.
Placing host names or IP addresses is also very common method to restrict access. For that use sys_context values:
sys_context('USERENV','IP_ADDRESS') -- IP address
sys_context('USERENV','HOST')       -- host name of client
Sometimes those values might be hidden (ah those Linux admins) and if this is not the case for your normal environment - terminate such a session immediate!!

Here is very important to understand that this trigger must not raise any error (except wanted one) because no one can logon to database. This is why my_forced_exception is used. With mine exception I can easy ensure proper functioning of database regardless any other error that might raised in this trigger!

In praxis...

SQL> grant connect,resource to xxyy identified by qw;

Grant succeeded.

SQL> conn xxyy/qw@some_db;
ERROR:
ORA-00604: error occurred at recursive SQL level 1
ORA-20001: Action not allowed. Please contact your DBA to help you!
ORA-06512: at line 25


Warning: You are no longer connected to ORACLE.
SQL>

Now add role SQLPLUS_ROLE to user xxyy and try to reconnect again:
SQL> grant SQLPLUS_ROLE to xxyy;

Grant succeeded.

SQL> conn xxyy/qw;
Connected.
SQL>
Works!

Cheers!

Thursday, January 21, 2010

Trace Analyzer "Directory alias does not exist in DBA_DIRECTORIES"

My previous post (Trace for dummies) remind me to post additional topic for those who find tkprof result too complicated. The solution is Trace Analyzer (AKA "trca"). Trace Analyzer is original Oracle product and can be downloaded from MetaLink note 224270.1.

Trca is not just "another replacement" for tkprof but big enhancement! Here is briefly why:
  1. Result is saved in directory where sqlplus was started. This eliminate needs to connect to database server side what is for many developer not possible! (tkprof is server only based tool)
  2. Output is full featured html with links and easy navigation through file. (tkprof is text only)
  3. trca outputs bind variables values (tkprof doesn't at all)
  4. Separates user recursive and internal recursive calls and provides more detailed wait event information
  5. Gives optimizer statistics for indexes and tables (tkprof doesn't at all)
  6. Provides information about the hottest blocks (tkprof doesn't at all)
On net there are plenty of documents that clearly describe installation (and readme.txt file is OK also) but nowhere to find solution for problem that arise after successful installation on Windows platforms only (AFAIK).

Problem reproduction

As said, regardless successful instalation, when you first time start trca in a way:
SQL> @trcanlzr.sql xe_ora_356.trc
end of output looks like:
Value passed to trcanlzr.sql:
~~~~~~~~~~~~~~~~~~~~~~~~~~~~
TRACE_FILENAME: xe_ora_356.trc

...analyzing xe_ora_356.trc


Trace Analyzer executed successfully.
There are no fatal errors in trcanlzr_error.log file.
Review file trcanlzr_87242.log for parsing messages and totals.

...copying reports into local SQL*Plus client directory


...trcanlzr 43161 reports were copied from server to local SQL*Plus directory


...review file trcanlzr_87242.log for parsing messages and totals.

SQL> 
All looks OK. But when you look in directory (where html should be generated) you see:
C:\oracle\product\11g\trca\run>dir
 Volume in drive C has no label.
 Volume Serial Number is 40A5-D38E

 Directory of C:\oracle\product\11g\trca\run

21.01.2010  21:26    </dir>         .
21.01.2010  21:26    </dir>         ..
06.09.2008  09:57             5.683 trcanlzr.sql
06.09.2008  09:57             4.876 trcanlzrnc.sql
20.01.2010  23:20                56 trcanlzr_87242.html
20.01.2010  23:20                56 trcanlzr_87242.log
20.01.2010  23:20                56 trcanlzr_87242.txt
20.01.2010  23:20             8.297 trcanlzr_error.log
02.07.2008  11:28             1.967 trcapurge.sql
               7 File(s)         20.991 bytes
               2 Dir(s)  29.721.235.456 bytes free

C:\oracle\product\11g\trca\run>
In all files with 56 bytes there is same content:
*** Directory alias  does not exist in DBA_DIRECTORIES
And there is no html generated content! Something is wrong...

Analyze trace analyzer

Mine approach was to look first in dba_directories:
SQL> select DIRECTORY_NAME, DIRECTORY_PATH 
  2    from dba_directories
  3   where DIRECTORY_NAME like 'TRC%';

DIRECTORY_NAME                 DIRECTORY_PATH
------------------------------ --------------------------------------------------
TRCA$INPUT                     C:\oracle\product\diag\rdbms\xe\xe\trace
TRCA$OUTPUT                    C:\oracle\product\diag\rdbms\xe\xe\trace

SQL> 
Because default directory (input and output) for trca is user_dump_dest, on on 11g it looks like:
SQL> show parameter user_dump_dest  

NAME            TYPE     VALUE  
--------------- -------- ----------------------------------------
user_dump_dest  string   C:\oracle\product\diag\rdbms\xe\xe\trace
SQL>
And this is exactly what trace directories point to. Grants are also OK so all looks OK also!

Now it's time to see what trca is performing. Analyzing trcanlzr.sql and resulting output in trcanlzr_error.log I found one interesting part. For source code in trcanlzr.sql:
SELECT column_value FROM TABLE(trcanlzr.trca$g.directories);
result in trcanlzr_error.log looks like:
Directories
-------------------------
()
()
user_dump_dest
When you look in source (trace analyzer packages are not wrapped!), you see:
FUNCTION directories
RETURN varchar2_table PIPELINED
IS
BEGIN /* directories */
  PIPE ROW (RPAD(SUBSTR(g_input_dir||'('||get_object_status(g_input_dir, 'DIRECTORY')||')', 1, 22), 24)||get_directory_path(g_input_dir));
  PIPE ROW (RPAD(SUBSTR(g_output_dir||'('||get_object_status(g_output_dir, 'DIRECTORY')||')', 1, 22), 24)||get_directory_path(g_output_dir));
  PIPE ROW (RPAD('user_dump_dest', 24)||g_udump);
  RETURN;
END directories;
Obviously our first two rows (which are empty) come as a result for g_input_dir=TRCA$INPUT and g_output_dir=TRCA$OUTPUT.

Analyzing get_directory_path and get_object_status functions, very soon I realize that they operate with upper values ... and this is the core of the problem! Do not forget that our path values from dba_directories are in lower case (as they should be!).

Resolution

Run again as priviledged user tacdiri.sql and tacdiro.sql scripts from installation dir and put the whole path in upper case (in our case "C:\ORACLE\PRODUCT\DIAG\RDBMS\XE\XE\TRACE").

When you check dba_directories again you must see all values in upper case:
SQL> select DIRECTORY_NAME, DIRECTORY_PATH 
  2    from dba_directories
  3   where DIRECTORY_NAME like 'TRC%';

DIRECTORY_NAME                 DIRECTORY_PATH
------------------------------ --------------------------------------------------
TRCA$INPUT                     C:\ORACLE\PRODUCT\DIAG\RDBMS\XE\XE\TRACE
TRCA$OUTPUT                    C:\ORACLE\PRODUCT\DIAG\RDBMS\XE\XE\TRACE

SQL> 

Rerun trcanlzr.sql and result is finally there!

P.S.
For some slower connections to server (or bigger trace files) do not panic if you do not get result on your PC immediatelly-just wait a while. They'll come.

Cheers!

Wednesday, January 20, 2010

Trace for rookies

Recent event from some days ago when I meet one lady developer with Oracle certificate in Forms and reports, remind me how many developers do not know how is easy to find the cause of performance problems in their code. Result is of course...Oracle trace. Here are very short and easy explanation how to do that. This blog covers 10g and 11g versions and will focus to easiness as much as possible with minimal divergence to become a deeper tutorial.
Tracing can be done in next five steps:
  1. Find session to trace
  2. Start trace
  3. Find the trace file
  4. Stop trace
  5. Analyze trace file

1. Find session to trace

When you want to trace any session, you have to find two values that uniquely identified one session. sid and serial# Here is example how to find it from Oracle username:
col sid_serial for a11;
undefine ora_user;
select s.username,
       s.sid||','||s.serial# sid_serial,
       s.audsid,
       p.spid,
       to_char(s.logon_time, 'dd.mm.yyyy hh24:mi.hh') logon_time, 
       s.status 
from v$session s,
     v$process p
where s.username = '&ora_user'
  and s.paddr = p.addr;
If you have problems look in v$session and look what other data might identified more easy session you look for (MACHINE, PROGRAM or other columns).

2. Start trace

With sid and serial# from previous query, start trace:
EXECUTE DBMS_MONITOR.SESSION_TRACE_ENABLE(session_id => SID, serial_num => serial#, waits => TRUE, binds => TRUE);
If you are in sqlplus session you want to trace then it is even more easy:
ALTER SESSION SET sql_trace = true;
But I prefer previous version (do not want to explain because this is over the scope of this topic)!

3. Find the trace file

Trace file is always in user dump directory, which is on the server, where database (instance) is installed (I have avoided to use $ORACLE_HOME and all other terms in a case of easiness to all kind of user's level of knowledge). Keep in mind that the default name for a trace file is in form INSTANCE_PID_ora_TRACEID.trc where:
  • INSTANCE is the name of the Oracle instance,
  • PID is the operating system process ID (column from V$PROCESS.OSPID)
  • TRACEID is a character string of your choosing.
With audsid from the first query find exact location and name of file that is created:
undefine audsid;
SELECT    u_dump.VALUE
       || CASE
            WHEN INSTR (DBMS_UTILITY.port_string, 'WIN_NT') > 0 THEN '\'
            ELSE '/'
          END
       || db_name.VALUE
       || '_ora_'
       || v$process.spid
       || NVL2 (v$process.traceid, '_' || v$process.traceid, NULL)
       || '.trc'
         "Trace File"
  FROM v$parameter u_dump
       CROSS JOIN v$parameter db_name
       CROSS JOIN v$process
       JOIN v$session
         ON v$process.addr = v$session.paddr
 WHERE u_dump.name = 'user_dump_dest'
   AND db_name.name = 'instance_name'
   AND v$session.audsid = &audsid
If you are in sqlplus session you want to trace then change last line (nr. 17) to:
v$session.audsid=sys_context('userenv','sessionid');  
On Windows server version there is a known bug with trace file name. Here is one example of output from previous query:
Enter value for audsid: 706927

Trace File
-------------------------------------------------------------
c:\oracle\product\diag\rdbms\xe\xe\trace/XE_ora_2316_SID.trc

1 row selected.

SQL> 
Look for name "trace/XE_ora_2316_SID.trc" and the way that "/" is instead of "\"!

If you are using RAC then trace filename is a little bit different because we have instances instead of database name, which is in RAC not the same. Example (for instance nr. 4):
c:\oracle\product\diag\rdbms\xe\xe\trace/xe4_ora_2316_SID.trc

Now let us wait (run problematic part)....trace data are generating. Keep in mind that is absolutely normal that ".trc" files are several hundreds of MB in size!

4. Stop trace

When user is finished with problematic part of code, we have to close the trace:
EXECUTE DBMS_MONITOR.SESSION_TRACE_DISABLE (session_id => SID, serial_num => serial#);
Where sid and serial# are data from first query.
If you are in sqlplus session where you started trace, then:
ALTER SESSION SET sql_trace = false;
User must end the session so trc file is dumped to disk. If user ends session (logout), trace will be automatically terminated and closed!

5. Analyze trace file

To analyze data from trace file, call oracle tkprof utility in a way (exactly):
tkprof 'trace file.trc' 'trace file.trc.ela' sys=no explain=username/pwd sort=exeela,prsela,fchela
where:
  • "trace file.trc" is the file that was generated with trace (in our case XE_ora_2316_SID.trc)
  • "trace file.trc.ela" is the name of analyzed file with tkprof-the result (in our case XE_ora_2316_SID.trc.ela)
  • "username/pwd" are authentication data of user whose session was traced. If this is a problem ask DBA to explain how to overcome this problem.
This would be sufficient that you can see the problem. Here is an example of tkprofed (analyzed trace file) on famous "emp dept" tables:
SELECT * FROM emp, dept 
WHERE emp.deptno = dept.deptno;

call   count      cpu    elapsed     disk    query current    rows
---- -------  -------  --------- -------- -------- -------  ------
Parse      1     0.16      0.29         3       13       0       0
Execute    1     0.00      0.00         0        0       0       0
Fetch      1     0.03      0.26         2        2       4      14 

Misses in library cache during parse: 1 
Parsing user id: (8) SCOTT 

Rows     Execution Plan
-------  --------------------------------------------------- 

14  MERGE JOIN
 4   SORT JOIN
 4     TABLE ACCESS (FULL) OF 'DEPT'
14    SORT JOIN
14      TABLE ACCESS (FULL) OF 'EMP'
Reason is obvious ... or ?

And that is all! Not so hard!??
;-)

For the end

For full specification of tkprof use Oracle documentation.

There might be others needed parameters that might be defined (i.e. MAX_DUMP_FILE_SIZE) but for that, ask your DBA for help!

Cheers!

Wednesday, January 13, 2010

Compile blocked package/procedure/function (Part II)

In one of mine previous post (Compile blocked package/procedure/function) I have shown how to determine who is blocking compiling of some package, procedure or function.

After I get several mails and saw that this approach is still "fog and pain", I have decided to show how this could be fully automated.

The implementation

CREATE OR REPLACE procedure kill_compile_blocker (
/* ---------------------------------------------------------------------------

 Copyright(C) 1995-2010 TEB Informatika d.o.o.


 Filename: kill_compile_blocker.sql (AKA check_blocking_objects)
 CR/TR#  : 
 Purpose : Show blocking session on non "table" objects
           Session which prevent compile of some prc/fnc/package etc.
           
           If passed, p_waiter_sid, is SID that is waiter! All result is then 
           based only on this one session. 
           
 Date    : 13.01.2010.
 Author  : Damir Vadas, damir.vadas@gmail.com
 
 Remarks : Tested on 10g/11g 
           No RAC support
           
 Changes (DD.MM.YYYY, Name, CR/TR#):
          23.09.2010  Damir Vadas
                      consolidated code
--------------------------------------------------------------------------- */
                                                   p_waiter_sid number DEFAULT null) 
AS
  l_waiter_sid         NUMBER;
  l_waiter_lock_addr   VARCHAR (40);
  stmt                 VARCHAR2(256);
  l_inst_id            number;
  
   -- list of all blockers that holds our proc/fnc/pkg
  CURSOR cur_blockers (pc_waiter_sid NUMBER, pc_lock_id  VARCHAR2)
  IS
    SELECT SESSION_ID blocker_sid, 
           MODE_HELD,    
           LOCK_ID1   locked_object
      FROM dba_lock_internal
     WHERE mode_held NOT IN ('None', 'Null')
       AND lock_id2 = pc_lock_id
       AND SESSION_ID != pc_waiter_sid
    -- ORDER BY SESSION_ID
  ;
  
  db_ver            VARCHAR2(128);
  db_ver2           VARCHAR2(128);  
BEGIN
  dbms_output.put_line (chr(9));
  dbms_utility.db_version(db_ver,db_ver2);
  dbms_output.put_line ('Oracle version: '||db_ver|| ' ('||db_ver2||')');
  dbms_output.put_line (chr(9));

  IF p_waiter_sid is null THEN
    -- find first blocked sid and release him! 
    SELECT DISTINCT 
           SESSION_ID waiter_sid, 
           lock_id2 waiter_lock_addr
       INTO l_waiter_sid, l_waiter_lock_addr
       FROM dba_lock_internal
      WHERE mode_requested = 'Exclusive'
        AND mode_requested <> mode_held
        AND LOCK_TYPE ='Table/Procedure/Type Definition Pin'
    ORDER BY 1 DESC
    ;
  ELSE
    -- find exact blocked sid
    SELECT SESSION_ID, lock_id2
      INTO l_waiter_sid, l_waiter_lock_addr
      FROM dba_lock_internal
     WHERE SESSION_ID = p_waiter_sid
       AND mode_requested = 'Exclusive'
       AND mode_requested <> mode_held
       AND LOCK_TYPE ='Table/Procedure/Type Definition Pin'
    ;
  END IF;
  stmt := null;
  FOR i IN cur_blockers (l_waiter_sid,l_waiter_lock_addr) LOOP
    dbms_output.put_line('-------------------------------------');
    SELECT    'alter system disconnect session '''
           || s.sid
           || ','
           || s.serial#
           || ''' immediate ;'
      INTO stmt
      FROM v$session s
     WHERE s.sid = i.blocker_sid 
       AND s.serial# != 1
    ;
    dbms_output.put_line('Blocked object:'||i.locked_object || ' ('||i.MODE_HELD||')');
    DBMS_OUTPUT.put_line(chr(9)||'On current instance perform:');
    DBMS_OUTPUT.put_line(chr(9)|| chr(9)|| stmt);
  END LOOP; 
  dbms_output.put_line('-------------------------------------');
EXCEPTION
  WHEN no_data_found then
    dbms_output.put_line('No blocking session found!');
END;
/
As you can see procedure has one parameter p_waiter_sid. This parameter (if defined) determine session_id that is blocked (waiting for compile).
If you pass no parameter, then procedure find first blocked session_id as use it as it is defined as paraemter.
Procedure results are "kill session" statements for ALL sessions that are blocking identified session_id.

Procedure works only for wait events Table/Procedure/Type Definition Pin type and it will not disconnect any other blocking session. If you want to unblock that "other kind of block", explanation is on previous blog Blocking session ... detect, unblock, notify.

Real example

Let us suppose that two session are using package tools_pkg:




User try to compile package tools_pkg and realize he/she is blocked. Run procedure and execute it's output :


Result on previous two session is obvious:





"Compile" session is unblocked and package is compiled.

In this example RAC instances are not covered...a challenge for someone of you ... or me when I get some free time.

Cheers!

Sunday, January 10, 2010

DDL in PL/SQL

DDL is for in experienced users sometimes a problem how o perform and even more-how to control result. Here is one way of solution for that. In mine case ddl is inside package tools_pkg.

Implementation

procedure ddl (ddl_text in varchar2, rows_processed OUT integer) is
AUTHID CURRENT_USER
  c            integer;
begin
  c := dbms_sql.open_cursor;
  dbms_sql.parse(c, ddl_text, dbms_sql.native);
  rows_processed := dbms_sql.execute(c);
  dbms_sql.close_cursor(c);
  -- if not DML then enlarge result for 1 (regular result returns zero)!
  if (INSTR (upper(ltrim(ddl_text)),'INSERT ')<=0) AND 
     (INSTR (upper(ltrim(ddl_text)),'UPDATE ')<=0) AND
     (INSTR (upper(ltrim(ddl_text)),'DELETE ')<=0) then
    rows_processed := 1;
  end if; 
exception
  when others then
    begin
      dbms_sql.close_cursor(c);
    exception
      when others then
        null;
    end;
    rows_processed:=-1;
end ddl;

"ddl" procedure in action

Here are some brief examples that will show DDL in action...in PL/SQL code. Important is that we can see the number of executed actions.

CREATE PUBLIC SYNONYM example

Let us create 90 public synonyms (XXX10..XXX99):
SQL> declare
  2    i   pls_integer; 
  3    tot pls_integer default 0;
  4  begin
  5    for j in 10..99 loop
  6      tools_pkg.ddl('CREATE OR REPLACE PUBLIC SYNONYM XXX'||j||' for damirv.tools_pkg',i);
  7      tot := tot+i;
  8    end loop;
  9    dbms_output.put_line('Synonyms created: '||tot);
 10  end;
 11  /
Synonyms created: 90

PL/SQL procedure successfully completed.

SQL> 

DROP PUBLIC SYNONYM example

Let's drop PUBLIC SYNONYMS:
SQL> declare
  2    i   pls_integer; 
  3    tot pls_integer default 0;
  4  begin
  5    for rec_cur_synonyms in (select synonym_name 
  6                               from dba_synonyms 
  7                              where OWNER='PUBLIC' and synonym_name like 'XXX__') loop
  8      tools_pkg.ddl('DROP PUBLIC SYNONYM ' || rec_cur_synonyms.synonym_name,i);  
  9      tot := tot+i;
 10    end loop;
 11    dbms_output.put_line('Synonyms dropped: '||tot);
 12  end;
 13  /
Synonyms dropped: 90

PL/SQL procedure successfully completed.

SQL> 

CREATE USER example

Let us create user:
SQL> set serveroutput on size 1000000;
SQL> declare
  2    i pls_integer;
  3  begin
  4    tools_pkg.ddl('grant resource, connect to user1 identified by q',i);
  5    dbms_output.put_line('Users created: '||i);
  6  end;
  7  /
Users created: 1

PL/SQL procedure successfully completed.

SQL>

SQL> select username, DEFAULT_TABLESPACE, TEMPORARY_TABLESPACE 
  2  from dba_users 
  3  where username='USER1';

USERNAME     DEFAULT_TABLESPACE   TEMPORARY_TABLESPACE
------------ -------------------- ---------------------
USER1        USERS                TEMP

DROP USER example

Here we made a deliberate mistake (user2 doesn't exists):
SQL> set serveroutput on size 1000000;
SQL> declare
  2    i pls_integer;
  3  begin
  4    tools.ddl('drop user USER2 cascade',i);
  5    dbms_output.put_line('Users dropped: '||i);
  6  end;
  7  /
Users dropped: -1

PL/SQL procedure successfully completed.

Now let's drop created user "USER1":
SQL> declare
  2    i pls_integer;
  3  begin
  4    tools_pkg.ddl('drop user USER1 cascade',i);
  5    dbms_output.put_line('Users dropped: '||i);
  6  end;
  7  /
Users dropped: 1

PL/SQL procedure successfully completed.

SQL> select username, DEFAULT_TABLESPACE, TEMPORARY_TABLESPACE
  2  from dba_users 
  3  where username='USER1';

no rows selected

SQL>

DML in action

DML statements sre also supported in DDL procedure. We'll use rownum to control number of inserted records):
SQL> declare
  2    i pls_integer;
  3  begin
  4    tools_pkg.ddl('INSERT INTO damirv.xx (select object_name, owner from dba_objects where rownum<=5)',i);
  5    dbms_output.put_line('Records inserted: '||i);
  6  end;
  7  /
Records inserted: 5

PL/SQL procedure successfully completed.

SQL>

Cheers!

Saturday, January 9, 2010

Columns to rows (print_record)

In my blog (Compile blocked package/procedure/function) I used procedure tools.print_record which I use a lot of cases when I need nice formatted output of record with more columns without any manual formatting. Idea is to transform columns to rows and keep easiness of use.

The problem

If you need to see (some restore point) state of v$database data, then you probably might use query like:
select * from v$database;
Problem is bad readability of such an output:

And here is where print_record comes at it's best.

This is v1.1 thanks to Marko Sutic and his comments with url to original postings. In fact this code come originally from Tom's Kyte page - print_table procedure. Mine part is small adding at the begginig to store session parameters.

I was not aware of that before so now I do appologize for this to all authors ...

Implementation

Implementation is more then easy-install the procedure. In my case it is part of tools package but it can be standalone as well:
procedure print_record ( p_query in varchar2 )
is
/* 
   these two constants manage output formatting for strings.
   adjust them as you wish.
*/
  c_text_seg_len       CONSTANT NUMBER        := 80;   -- do not exceed 224
  c_cont_char          CONSTANT VARCHAR2(30)  := '~';  -- for continuation

  cur_da_cursor                 INTEGER DEFAULT dbms_sql.open_cursor;
  l_column_value                VARCHAR2(4000);
  l_column_title                VARCHAR(30);
  l_status                      INTEGER;
  l_table_desc                  dbms_sql.desc_tab;
  l_column_count                NUMBER;
  l_nls_date                    VARCHAR2(32);
begin
  select value into l_nls_date
    from nls_session_parameters
   where parameter = 'nls_date_format';

  execute immediate 'alter session set nls_date_format=''dd-mon-yyyy hh24:mi:ss'' ';

  dbms_sql.parse(cur_da_cursor,p_query,dbms_sql.native);

  dbms_sql.describe_columns (cur_da_cursor,l_column_count,l_table_desc);

  for i in 1 .. l_column_count loop
    dbms_sql.define_column (cur_da_cursor,i,l_column_value,4000);
  end loop;

  l_status := dbms_sql.execute(cur_da_cursor);

  while (dbms_sql.fetch_rows(cur_da_cursor) > 0) loop
    for i in 1 .. l_column_count loop
      dbms_sql.column_value (cur_da_cursor,i,l_column_value);
      if l_table_desc(i).col_type = 1 then
        if length(l_column_value) > 0 then
          l_column_title    := rpad(l_table_desc(i).col_name, 30);
          for j in 1 .. ceil(length(l_column_value)/c_text_seg_len) loop
            dbms_output.put_line(l_column_title || ': ' || 
                                 substr(l_column_value, (((j-1)*c_text_seg_len)+1), c_text_seg_len)
                                );
            l_column_title := rpad(c_cont_char, 30);
          end loop;
        else
          dbms_output.put_line (rpad( l_table_desc(i).col_name, 30) || ': ' || l_column_value);
        end if;
      elsif l_table_desc(i).col_type in (8, 23, 24, 112, 113, 114, 115) then
        dbms_output.put_line (rpad(l_table_desc(i).col_name, 30 ) || ': ** UNPRINTABLE **');
      else
        dbms_output.put_line (rpad( l_table_desc(i).col_name, 30) || ': ' || l_column_value );
      end if;
    end loop;
  dbms_output.put_line( '--------------------------------------------------------------------' );
  end loop;
  execute immediate 'alter session set nls_date_format='''||l_nls_date||'''';
exception
  when others then
    begin
      dbms_output.put_line(sqlerrm);
      dbms_output.put_line (chr(9));
      dbms_output.put_line (p_query);
    exception
      when others then
        null;
    end;    
    execute immediate 'alter session set nls_date_format='''||l_nls_date||'''';
end print_record;

"print_record" in action

If you run the same query as before "through" print_records:
exec tools.print_record('select * from v$database');
Result looks like:


And here is another enchantment for columns that are longer then 255 chars. Output is devided into several columns where length is defined with c_text_seg_len constant.

For the end

As you can see the main idea to keep easiness is full filled-you can use the same query in both cases!

Remember that every record will produce one set of rows....regardless how many they are in result.

Cheers!

Compile blocked package/procedure/function

Recent blog topic (Unlocking the locked table) remind me to write another (and think more mysterious) blocking problem-how to compile blocked package/procedure/function?

Problem arise when you have to compile package/procedure/function on database where many users in the same time use the same package/procedure/function.

Problem

Let us suppose that some user has started his session and execute test procedure from package tools. Because procedure test is some bigger transaction it takes longer time to finish.


A little bit later developer has tried to compile the same package (in both cases picture shows session_id and serial# for more easy reference later):

Unfortunately developer compile is blocked … and might be very dangerous!

To make situation more real, let us suppose that soon after developer tried to compile the package, another user has started from package tools any procedure/function (unimportant which one!!):
 
He is blocked also! Why?

Cause of the problem

First let us find that problem is real. Look for waiter sessions, sessions who are "handicapped":
select to_char(SESSION_ID,'99999') sid ,     
   substr(LOCK_TYPE,1,30) Type,     
   substr(lock_id1,1,20) Object_Name,     
   substr(mode_held,1,4) HELD,     
   substr(mode_requested,1,4) REQ,     
   lock_id2 lock_addr     
FROM dba_lock_internal     
WHERE     
   mode_requested <> 'None'     
   and mode_requested <> mode_held     
;     

SID    TYPE                           OBJECT_NAME  HELD REQ  LOCK_ADDR     
------ ------------------------------ ------------ ---- ---- ----------------
   125 Table/Procedure/Type Definitio DAMIRV.TOOLS None Shar 000007FF10E0A298     
   118 Table/Procedure/Type Definitio DAMIRV.TOOLS None Excl 000007FF10E0A298  
   
Our session (sid) 118 is blocked because it holds less lock rights then requested (held=None and requested=Exclusive). Exclusive lock is always needed for compiling package/procedure/function. And this is explanation why developer is waiting for compile-his session cannot obtain exclusive lock on package tools.

Third session (125) is another execution of same package (same LOCK_ADDR) but as mentioned before, this execution came after our recompile request has been placed, and this session (HELD=None) so it is not blocking us!

To find blocker, use lock_id2 (presented through alias LOCK_ADDR for easy reference) from same view dba_lock_internal as before, with changed where condition:
select to_char(SESSION_ID,'99999') sid ,     
   substr(LOCK_TYPE,1,30) Type,     
   substr(lock_id1,1,20) Object_Name,     
   substr(mode_held,1,4) HELD,     
   substr(mode_requested,1,4) REQ,     
   lock_id2 lock_addr     
FROM dba_lock_internal     
WHERE     
     lock_id2='000007FF10E0A298'     
  and session_id != 118     
order by sid     
;     

SID    TYPE                           OBJECT_NAME  HELD REQ  LOCK_ADDR     
------ ------------------------------ ------------ ---- ---- ----------------
   122 Table/Procedure/Type Definitio DAMIRV.TOOLS Null None 000007FF10E0A298     
   122 Table/Procedure/Type Definitio DAMIRV.TOOLS Shar None 000007FF10E0A298     
   125 Table/Procedure/Type Definitio DAMIRV.TOOLS None Shar 000007FF10E0A298     

Now it is more then clear that session 122 holds "Share" lock type, what is blocking session 118 (us). Even more, if here exist any other session that hold any type of lock (except "null" or "None"), this session will be blocking our session as well!

Other session (125, the one that has been executed after our compile request has been placed) is waiting to place the "Share" lock. Because session 125 cannot perform lock as requested, this session is blocked also. And it has been blocked by our session which waits also for other session! Ant this will happened to all sessions that started to use tools package after our compile request!

Now you see why this blocked package compile is dangerous action-it may blocked very large part of database if we want to compile very popular object! Consequence is that users would soon bag that all in database is hanged! And it is!

So DBA has to react very quick to prevent that!

Solution

According mentioned, let us find more information about session 122 (to be sure we will not kill "good guy"):
SQL> col iid for 990
SQL> col sid_serial for a11;
SQL> col sql_text for a80;
SQL> SELECT
  2         t.sql_text,
  3         s.inst_id iid,
  4         lpad(s.sid,5)||','|| Lpad(s.serial#,5) sid_serial       
  5    FROM gv$sqlarea t,
  6         gv$session s
  7   WHERE t.address = s.sql_address
  8     AND t.hash_value = s.sql_hash_value
  9     AND s.inst_id=&1
10     AND s.SID = &2
11     AND s.sql_hash_value != 0
12  order by 2,3
13  ;
Enter value for 1: 1
old   9:    AND s.inst_id=&1
new   9:    AND s.inst_id=1
Enter value for 2: 122
old  10:    AND s.SID = &2
new  10:    AND s.SID = 122

SQL_TEXT                  IID  SID_SERIAL
------------------------- ---- -----------
BEGIN tools.test; END;       1   122,  192 
This is really our blocker session so we have to kill it. Here is an example of my script which is working on "RAC" and "non RAC" (iid=1) environment and this is the reason why iid is also parameter:
SQL> alter system disconnect session '122,192' immediate;     
alter system disconnect session '122,192' immediate     
*     
ERROR at line 1:     
ORA-00031: session marked for kill     

SQL>     

When you kill session 122 and perform the same query as before, no rows should be shown:
SQL> select to_char(SESSION_ID,'99999') sid ,     
  2     substr(LOCK_TYPE,1,30) Type,     
  3     substr(lock_id1,1,20) Object_Name,     
  4     substr(mode_held,1,4) HELD,     
  5     substr(mode_requested,1,4) REQ,     
  6     lock_id2 lock_addr     
  7  FROM dba_lock_internal     
  8  WHERE     
  9     mode_requested <> 'None'     
10     and mode_requested <> mode_held     
11  ;     

no rows selected     

SQL>

If there exists more records (more users has started to use package tools before our compile request) then you have to kill them all!

For the end

I know that many of you might say that developers should not have access on production instance-true! But sometimes is that just a nice wish.

Other thing is that some quick bug fixing (why they happened...another discussion!) always happened exactly in shown way...so prevent that is sometimes more then fairy tale!

Cheers!

P.S.
Prove that "print_record" procedure (in third session-waiting) is one easy and quick finishing action, is explained in my topic Columns to rows (print_record)

P.S. II
Fully automated procedure (kills sessions automatically) can be found Compile blocked package/procedure/function II

Sunday, January 3, 2010

ORA-00257: archiver error. Connect internal only, until freed.

If you work on any Oracle database you might get in situation like this:
C:\Users\DamirV>sqlplus / as sysdba

SQL*Plus: Release 11.1.0.7.0 - Production on Sri Pro 23 20:25:11 2009

Copyright (c) 1982, 2008, Oracle.  All rights reserved.


Connected to:
Oracle Database 11g Enterprise Edition Release 11.1.0.7.0 - 64bit Production
With the Partitioning, OLAP, Data Mining and Real Application Testing options

20:25:14 SQL> conn damirv/qw
ERROR:
ORA-00257: archiver error. Connect internal only, until freed.


Warning: You are no longer connected to ORACLE.
20:25:20 SQL>
Or worse situation when you get from users (help desk):
"DBA-database is frozen! Heeeeeeeeelp!!"

Alert log

As always, first step in troubleshooting Oracle is to check an alert.log. If you do not know where is it(!?) then perform next two steps:
SQL> select value from v$parameter where name = 'background_dump_dest';

VALUE
--------------------------------------------------------------------------------
c:\oracle\product\diag\rdbms\xe\xe\trace

SQL> exit
Disconnected from Oracle Database 11g Enterprise Edition Release 11.1.0.7.0 - 64bit Production
With the Partitioning, OLAP, Data Mining and Real Application Testing options

C:\Users\DamirV>dir c:\oracle\product\diag\rdbms\xe\xe\trace\al*.log
 Volume in drive C has no label.
 Volume Serial Number is 40A5-D38E

 Directory of c:\oracle\product\diag\rdbms\xe\xe\trace

23.12.2009  20:53           312.008 alert_xe.log
               1 File(s)        312.008 bytes
               0 Dir(s)  36.568.383.488 bytes free
C:\Users\DamirV>
In this case at the end of alert log file there are significant number of messages which will explain what goes wrong.
Errors in file c:\oracle\product\diag\rdbms\xe\xe\trace\xe_arc1_3176.trc:
ORA-19809: limit exceeded for recovery files
ORA-19804: cannot reclaim 47379968 bytes disk space from 2147483648 limit
ARC1: Error 19809 Creating archive log file to 'C:\ORACLE\PRODUCT\FLASH_RECOVERY_AREA\XE\ARCHIVELOG\2009_12_23\O1_MF_1_145_%U_.ARC'
ARCH: Archival stopped, error occurred. Will continue retrying
Errors in file c:\oracle\product\diag\rdbms\xe\xe\trace\xe_arc1_3176.trc:
ORA-16038: log 1 sequence# 145 cannot be archived
ORA-19809: limit exceeded for recovery files
ORA-00312: online log 1 thread 1: 'C:\ORACLE\PRODUCT\ORADATA\XE\REDO01.LOG'
Thread 1 cannot allocate new log, sequence 147

To be certain in solving any situation, always look in trace files until you are sure what they are signalizing. Here is the content, in this case, "xe_arc1_3176.trc" file:
*** 2009-12-23 20:23:57.253
ORA-19815: WARNING: db_recovery_file_dest_size of 2147483648 bytes is 100.00% used, and has 0 remaining bytes available.
************************************************************************
You have following choices to free up space from flash recovery area:
1. Consider changing RMAN RETENTION POLICY. If you are using Data Guard,
   then consider changing RMAN ARCHIVELOG DELETION POLICY.
2. Back up files to tertiary device such as tape using RMAN
   BACKUP RECOVERY AREA command.
3. Add disk space and increase db_recovery_file_dest_size parameter to
   reflect the new space.
4. Delete unnecessary files using RMAN DELETE command. If an operating
   system command was used to delete files, then use RMAN CROSSCHECK and

*** 2009-12-23 20:23:57.530
   DELETE EXPIRED commands.
************************************************************************

Resolution

All looks that your db_recovery_file_dest_size is full with archive logs. To check that and be certain, perform:
SQL> show parameter db_recovery_file_dest_size;

NAME                                 TYPE        VALUE
------------------------------------ ----------- ------------------------------
db_recovery_file_dest_size           big integer 2G

SQL> set lines 100
SQL> col name format a60
SQL> select     name
  2  ,  floor(space_limit / 1024 / 1024) "Size MB"
  3  ,  ceil(space_used  / 1024 / 1024) "Used MB"
  4  from       v$recovery_file_dest
  5  order by name
  6  /

NAME                                                            Size MB    Used MB
------------------------------------------------------------ ---------- ----------
C:\oracle\product\flash_recovery_area                              2048       2007

SQL>
Because Oracle has no space to create new archived log file it freeze all operations and could wait in this state until eternity if you do not help him!

So you have two (three) solutions to overcome this.
1) Enlarge db_recovery_file_dest_size (from 2 to 3 GB in our case)
alter system set db_recovery_file_dest_size=3G scope=both;
2) Backup and delete archive logs
rman target / nocatalog

run {
allocate channel t1 type disk;
backup archivelog all delete input format '/arch_%d_%u_%s';
release channel t1;
}

3) Just delete archive logs (what I'll show as easiest way but I prefer previous two options on any production installations). I'll do that with RMAN as best way for that:
C:\Users\DamirV>rman target / nocatalog

Recovery Manager: Release 11.1.0.7.0 - Production on Sri Pro 23 20:52:08 2009

Copyright (c) 1982, 2007, Oracle.  All rights reserved.

connected to target database: XE (DBID=2578577487)
using target database control file instead of recovery catalog

RMAN> delete archivelog all;

allocated channel: ORA_DISK_1
channel ORA_DISK_1: SID=116 device type=DISK
List of Archived Log Copies for database with db_unique_name XE
=====================================================================

Key     Thrd Seq     S Low Time
------- ---- ------- - --------
1       1    99      A 25.11.09
        Name: C:\ORACLE\...\2009_11_25\O1_MF_1_99_5JV7D7M1_.ARC

2       1    100     A 25.11.09
        Name: C:\ORACLE\...\2009_11_26\O1_MF_1_100_5JXFJXH5_.ARC

3       1    101     A 26.11.09
        Name: C:\ORACLE\...\2009_11_27\O1_MF_1_101_5JZ0C9TO_.ARC

...
...

43      1    141     A 23.12.09
        Name: C:\ORACLE\...\2009_12_23\O1_MF_1_141_5M4V7S2J_.ARC

44      1    142     A 23.12.09
        Name: C:\ORACLE\...\2009_12_23\O1_MF_1_142_5M4V7ZNR_.ARC

45      1    143     A 23.12.09
        Name: C:\ORACLE\...\2009_12_23\O1_MF_1_143_5M4V876L_.ARC

46      1    144     A 23.12.09
        Name: C:\ORACLE\...\2009_12_23\O1_MF_1_144_5M4V8JJH_.ARC


Do you really want to delete the above objects (enter YES or NO)? yes
deleted archived log
archived log file name=C:\ORACLE\...\2009_11_25\O1_MF_1_99_5JV7D7M1_.ARC RECID=1 STAMP=703894295
deleted archived log
archived log file name=C:\ORACLE\...\2009_11_26\O1_MF_1_100_5JXFJXH5_.ARC RECID=2 STAMP=703966128
deleted archived log
archived log file name=C:\ORACLE\...\2009_11_27\O1_MF_1_101_5JZ0C9TO_.ARC RECID=3 STAMP=704018171
deleted archived log

...
...

archived log file name=C:\ORACLE\...\2009_12_23\O1_MF_1_141_5M4V7S2J_.ARC RECID=43 STAMP=706393099
deleted archived log
archived log file name=C:\ORACLE\...\2009_12_23\O1_MF_1_142_5M4V7ZNR_.ARC RECID=44 STAMP=706393105
deleted archived log
archived log file name=C:\ORACLE\...\2009_12_23\O1_MF_1_143_5M4V876L_.ARC RECID=45 STAMP=706393113
deleted archived log
archived log file name=C:\ORACLE\...\2009_12_23\O1_MF_1_144_5M4V8JJH_.ARC RECID=46 STAMP=706393122
Deleted 46 objects

RMAN>

Now, let us look again in alert log to see that situation has changed (as we expect):
Wed Dec 23 20:52:57 2009
db_recovery_file_dest_size of 2048 MB is 2.21% used. This is a
user-specified limit on the amount of space that will be used by this
database for recovery-related files, and does not reflect the amount of
space available in the underlying filesystem or ASM diskgroup.
kcrrdmx: Successful archiving of previously failed ORL
Archiver process freed from errors. No longer stopped
Wed Dec 23 20:53:00 2009
AUD: Audit Commit Delay exceeded, written a copy to OS Audit Trail
Wed Dec 23 20:53:00 2009
Thread 1 advanced to log sequence 148 (LGWR switch)
  Current log# 1 seq# 148 mem# 0: C:\ORACLE\PRODUCT\ORADATA\XE\REDO01.LOG
As you can see db_recovery_file_dest_size of 2048 MB is 2.21% used shows that we have emptied all!

Trust but verify!

Before you send an e-mail (or telephone call) that all is OK, verify the case and try o connect as anon sysdba user:
C:\Users\DamirV>sqlplus / as sysdba

SQL*Plus: Release 11.1.0.7.0 - Production on Sri Pro 23 20:55:25 2009

Copyright (c) 1982, 2008, Oracle.  All rights reserved.


Connected to:
Oracle Database 11g Enterprise Edition Release 11.1.0.7.0 - 64bit Production
With the Partitioning, OLAP, Data Mining and Real Application Testing options

SQL> conn damirv/pwd
Connected.
SQL>

Conclusion

Remember that in situations all you have to do is be calm. Oracle will not crashed or anything like this so you have time until the end of the world-so do not hurry too much. 3 minutes more or less will not worse the situation. So react smoothly!

If you want to prevent situations like this there are two general suggestions:
  1. Either increase your file system (or db_recovery_file_dest_size size like in this case) to accommodate more archived logs. And check free space in both part.
  2. Take more frequent backups of archived logs and delete them after backing them up.
  3. Monitor alert.log all the time.

Cheers!

ORA-00942 and "AUTHID CURRENT_USER"

Many non experienced developers and DBAs could fall in the ORA-00492 errors in some very strange situations where Oracle security behave in a little different way then they would expect.

This example is not connected to any specific Oracle version but mostly on fixed tables/views which are placed in sys schema. Let me explain how sometimes things might look crazy on first look and might happened even to schema owner with DBA roles.

Reproduction of the problem

Let us create one powerful user with DBA role. This example is also one nice trick how to create a user in just one line.
grant dba to scott_dba identified by tiger;

Resulting create user script in Oracle looks like:
CREATE USER SCOTT_DBA
  IDENTIFIED BY 
  DEFAULT TABLESPACE USERS
  TEMPORARY TABLESPACE TEMP
  PROFILE DEFAULT
  ACCOUNT UNLOCK;
  -- 1 Role for SCOTT_DBA 
  GRANT DBA TO SCOTT_DBA;
  -- 1 System Privilege for SCOTT_DBA 
  GRANT UNLIMITED TABLESPACE TO SCOTT_DBA;
It is imported to see that all what SCOTT_DBA can do is enabled through DBA role only ("unlimited tablespace" has no influence in this terms).

Quick prove that SCOTT_DBA can select two objects (views) which we will use later in example:
SQL> select count(*) from gv$event_name;

  COUNT(*)
----------
       995

SQL> select count(*) from gv$active_session_history;

  COUNT(*)
----------
       277

Create procedure which will use before tested two views and retrieve sql_id column from one of them. BTW, this query shows top 10 interesting queries from certain period (it works from 10g and afterward) but any other example with fixed views/tables behave in the same way.
SQL> CREATE OR REPLACE PROCEDURE TEST
  2  AUTHID CURRENT_USER
  3  IS
  4    CURSOR C IS
  5     SELECT sql_id
  6      FROM gv$active_session_history ash,
  7            gv$event_name evt
  8      WHERE ash.event_id = evt.event_id
  9        AND evt.wait_class = 'User I/O'
 10      GROUP BY sql_id
 11      having COUNT(*) >= 10
 12    ;
 13  begin
 14    for i in c loop
 15      dbms_output.put_line('Queries are: ' || i.sql_id);
 16    end loop;
 17  end;
 18  /

Warning: Procedure created with compilation errors.

SQL> show errors;
Errors for PROCEDURE TEST:

LINE/COL ERROR
-------- -----------------------------------------------------------------
5/4      PL/SQL: SQL Statement ignored
7/11     PL/SQL: ORA-00942: table or view does not exist
15/5     PL/SQL: Statement ignored
15/45    PLS-00364: loop index variable 'I' use is invalid
SQL>
Oooups! In line 7 Oracle says that gv$event_name doesn't exist! Other error (ORA-00364) is just circumstance of the first one which invalidate cursor.

Regardless SCOTT_DBA user has through DBA role many object privileges it looks like this is not enough of them to compile one simple procedure!??

And this is true in this case!

Cause

Any stored procedure in Oracle runs either with the rights of the caller (authid current_user) or with the rights of the procedure's owner (authid definer) what is default option if you do not specify anything.

Obviously, our example was using first (non default) way.

Brief explanation

For such a cases explanation is that a procedure with authid current_user "disables all roles" that are granted to the creator in the compile time. This can lead to ORA-00942 errors in compiling packages-what is our case!

Let us look a little bit closer what is gv$event_name object?
SQL> select OWNER,OBJECT_NAME,OBJECT_TYPE from dba_objects where object_name='GV$EVENT_NAME';

OWNER              OBJECT_NAME                    OBJECT_TYPE
------------------ ------------------------------ -------------------
PUBLIC             GV$EVENT_NAME                  SYNONYM

SQL> select OWNER,SYNONYM_NAME, TABLE_OWNER, TABLE_NAME from dba_synonyms where synonym_name='GV$EVENT_NAME';

OWNER              SYNONYM_NAME                   TABLE_OWNER                    TABLE_NAME
------------------ ------------------------------ ------------------------------ ------------------------------
PUBLIC             GV$EVENT_NAME                  SYS                            GV_$EVENT_NAME

SQL> select OWNER,OBJECT_NAME,OBJECT_TYPE from dba_objects where object_name='GV_$EVENT_NAME';

OWNER              OBJECT_NAME                    OBJECT_TYPE
------------------ ------------------------------ -------------------
SYS                GV_$EVENT_NAME                 VIEW

SQL>

How GV$EVENT_NAME is defined may be retrieved through
select VIEW_DEFINITION
from V$FIXED_VIEW_DEFINITION
where view_name='GV$EVENT_NAME';

Formatted result of previous query looks like:
SELECT inst_id,
       indx,
       ksledhash,
       kslednam,
       ksledp1,
       ksledp2,
       ksledp3,
       ksledclassid,
       ksledclass#,
       ksledclass
  FROM x$ksled 

So x$ksled is fixed X$ view which we are quering in real case. And we cannot query it directly!

This is proven very easy. If you execute query ... you'll get an error:
SQL> desc x$ksled;
ERROR:
ORA-04043: object x$ksled does not exist

SQL> desc sys.x$ksled;
ERROR:
ORA-04043: object sys.x$ksled does not exist

SQL> 

Ability to query this fixed view is inherited from mentioned view through SELECT_CATALOG_ROLE role! This role is, as mentioned before, multiple part of DBA role privileges.

So let us prove what SCOTT_DBA user really own of roles and it's privileges. Once again all what SCOTT_DBA can do is inherited from DBA role only!

For that I'll use mine script user_roles .
SQL> @user_roles
Enter value for enter_username: SCOTT_DBA
old  12:       username = upper('&enter_username')
new  12:       username = upper('SCOTT_DBA')

User: roles
-----------------------------------------------------
  SCOTT_DBA
    DBA
      DATAPUMP_EXP_FULL_DATABASE
        EXP_FULL_DATABASE
          EXECUTE_CATALOG_ROLE
            HS_ADMIN_ROLE
          SELECT_CATALOG_ROLE
            HS_ADMIN_ROLE
      DATAPUMP_IMP_FULL_DATABASE
        EXP_FULL_DATABASE
          EXECUTE_CATALOG_ROLE
            HS_ADMIN_ROLE
          SELECT_CATALOG_ROLE
            HS_ADMIN_ROLE
        IMP_FULL_DATABASE
          EXECUTE_CATALOG_ROLE
            HS_ADMIN_ROLE
          SELECT_CATALOG_ROLE
            HS_ADMIN_ROLE
      DELETE_CATALOG_ROLE
      EXECUTE_CATALOG_ROLE
        HS_ADMIN_ROLE
      EXP_FULL_DATABASE
        EXECUTE_CATALOG_ROLE
          HS_ADMIN_ROLE
        SELECT_CATALOG_ROLE
          HS_ADMIN_ROLE
      GATHER_SYSTEM_STATISTICS
      IMP_FULL_DATABASE
        EXECUTE_CATALOG_ROLE
          HS_ADMIN_ROLE
        SELECT_CATALOG_ROLE
          HS_ADMIN_ROLE
      JAVA_ADMIN
      JAVA_DEPLOY
      OLAP_DBA
      OLAP_XS_ADMIN
      SCHEDULER_ADMIN
      SELECT_CATALOG_ROLE
        HS_ADMIN_ROLE
      WM_ADMIN_ROLE
      XDBADMIN
      XDB_SET_INVOKER

43 rows selected.

SQL> 
It is really easy to see that SELECT_CATALOG_ROLE is under DBA role and inherited from many other subroles that DBA role has.

So when AUTHID CURRENT_USER is used, according previously said, when all roles are temporary revoked in compile time, SCOTT_DBA really cannot compile that procedure because he has not a single privilege to do that!

Which views are involved also?

Not only X$ fixed views are involved in case like this. Much better known ordinary G$ and V$ fixed views are involved as well. Quick example on very often used v$session view:
SQL> CREATE OR REPLACE PROCEDURE TEST2
  2  AUTHID  CURRENT_USER
  3  IS
  4    v_SID varchar2(24);
  5  begin
  6    select SID into v_sid
  7      from v$session
  8     where rownum<=1
  9    ;
 10    dbms_output.put_line(v_sid);
 11  end;
 12  /

Warning: Procedure created with compilation errors.

SQL> show errors;
Errors for PROCEDURE SCOTT_DBA.TEST2:

LINE/COL ERROR
-------- -----------------------------------------------------------------
6/3      PL/SQL: SQL Statement ignored
7/10     PL/SQL: ORA-00942: table or view does not exist
SQL>
To be general in statement all fixed views/tables suffer from this kind of error in a cases like shown.

Solution

To overcome ORA-00942 problems with AUTHID, grant select any dictionary privileges to user that own schema object with error.

So, SCOTT_DBA just need to get just one additional system privilege (not any role again!!):
SQL> grant select any dictionary to scott_dba;

Grant succeeded.

SQL>

Just to remind that particular grant select on fixed tables/views is not possible:
SQL> grant select on sys.x$ksled to scott_dba;
grant select on sys.x$ksled to scott_dba
*
ERROR at line 1:
ORA-02030: can only select from fixed tables/views

SQL> 
More on this view and problems on fixed X$ views can be found at nice blog post Granting Access to X$ Tables.

This will will be enough to compile problematic TEST procedure:
SQL> SQL> alter procedure TEST compile;

Procedure altered.

SQL>

For the end

Regardless SCOTT_DBA could not compile TEST procedure without additional privileges, executor could inherited rights from role ONLY! Here is the prove for that:
SQL> grant dba to dba2 identified by dba2;

Grant succeeded.

SQL> conn dba2/dba2
Connected.
SQL> set serveroutput on size unl;
SQL> execute scott_dba.test;
Queries are:
Queries are: 1h50ks4ncswfn

PL/SQL procedure successfully completed.

SQL>

Cheers!

Zagreb u srcu!

Copyright © 2009-2018 Damir Vadas

All rights reserved.


Sign by Danasoft - Get Your Sign