26 September, 2016

SQLLoader DIRECT option and Unique Index

The DIRECT parameter for SQLLoader Command-Line enables Direct Path Load which uses a Direct Path API instead of a regular INSERT statement to load data into the target table.

However, one needs to know how it handles a Unique Index on the target table.
It actually leaves the  UNUSABLE if, duplicate values are loaded. The Index rebuild fails but the duplicate values remain in the table.

Here is a quick demo  (this in 12.1.0.2 MultiTenant). I first setup the target table with a Unique Index.

[oracle@ora12102 Desktop]$ sqlplus hemant/hemant@PDB1

SQL*Plus: Release 12.1.0.2.0 Production on Mon Sep 26 22:36:51 2016

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

Last Successful login time: Mon Sep 26 2016 22:26:16 +08:00

Connected to:
Oracle Database 12c Enterprise Edition Release 12.1.0.2.0 - 64bit Production
With the Partitioning, Automatic Storage Management, OLAP, Advanced Analytics
and Real Application Testing options

SQL> show con_id

CON_ID
------------------------------
3
SQL> create table test_sqlldr_direct
  2  (id_column number,
  3  data_column varchar2(15))
  4  /

Table created.

SQL> create unique index test_sqlldr_direct_u1 on test_sqlldr_direct(id_column);

Index created.

SQL> insert into test_sqlldr_direct values (1, 'First Row');

1 row created.

SQL> commit;

Commit complete.

SQL> exit
Disconnected from Oracle Database 12c Enterprise Edition Release 12.1.0.2.0 - 64bit Production
With the Partitioning, Automatic Storage Management, OLAP, Advanced Analytics
and Real Application Testing options
[oracle@ora12102 Desktop]$ 

Next, I setup the datafile with a duplicate record and the controlfile.

[oracle@ora12102 Desktop]$ ls -l
total 8
-rw-r--r-- 1 oracle oinstall   40 Sep 26 22:40 load_data.dat
-rw-r--r-- 1 oracle oinstall  165 Sep 26 22:45 load_control.ctl
[oracle@ora12102 Desktop]$ cat load_data.dat
2,'Second Row'
3,'Third Row'
3,'Oops !'
[oracle@ora12102 Desktop]$ cat load_control.ctl
LOAD DATA
INFILE load_data.dat
APPEND  INTO TABLE TEST_SQLLDR_DIRECT
FIELDS TERMINATED BY ',' OPTIONALLY ENCLOSED BY '"'
TRAILING NULLCOLS
(
id_column,
data_column)
[oracle@ora12102 Desktop]$ 

I am now ready to run a Direct Path Load.

[oracle@ora12102 Desktop]$ sqlldr hemant/hemant@pdb1 control=load_control.ctl direct=TRUE

SQL*Loader: Release 12.1.0.2.0 - Production on Mon Sep 26 22:47:09 2016

Copyright (c) 1982, 2014, Oracle and/or its affiliates.  All rights reserved.

Path used:      Direct

Load completed - logical record count 3.

Table TEST_SQLLDR_DIRECT:
  3 Rows successfully loaded.

Check the log file:
  load_control.log
for more information about the load.
[oracle@ora12102 Desktop]$ 

What is that ? 3 rows loaded successfully ?  So, the duplicate row also did get loaded ?  Let's check the log file.

[oracle@ora12102 Desktop]$ ls -ltr
total 12
-rw-r--r-- 1 oracle oinstall   40 Sep 26 22:40 load_data.dat
-rw-r--r-- 1 oracle oinstall  165 Sep 26 22:45 load_control.ctl
-rw-r--r-- 1 oracle oinstall 1833 Sep 26 22:47 load_control.log
[oracle@ora12102 Desktop]$ cat load_control.log

SQL*Loader: Release 12.1.0.2.0 - Production on Mon Sep 26 22:47:09 2016

Copyright (c) 1982, 2014, Oracle and/or its affiliates.  All rights reserved.

Control File:   load_control.ctl
Data File:      load_data.dat
  Bad File:     load_data.bad
  Discard File:  none specified
 
 (Allow all discards)

Number to load: ALL
Number to skip: 0
Errors allowed: 50
Continuation:    none specified
Path used:      Direct

Table TEST_SQLLDR_DIRECT, loaded from every logical record.
Insert option in effect for this table: APPEND
TRAILING NULLCOLS option in effect

   Column Name                  Position   Len  Term Encl Datatype
------------------------------ ---------- ----- ---- ---- ---------------------
ID_COLUMN                           FIRST     *   ,  O(") CHARACTER            
DATA_COLUMN                          NEXT     *   ,  O(") CHARACTER            

The following index(es) on table TEST_SQLLDR_DIRECT were processed:
index HEMANT.TEST_SQLLDR_DIRECT_U1 was made unusable due to:
ORA-01452: cannot CREATE UNIQUE INDEX; duplicate keys found

Table TEST_SQLLDR_DIRECT:
  3 Rows successfully loaded.
  0 Rows not loaded due to data errors.
  0 Rows not loaded because all WHEN clauses were failed.
  0 Rows not loaded because all fields were null.

Bind array size not used in direct path.
Column array  rows :    5000
Stream buffer bytes:  256000
Read   buffer bytes: 1048576

Total logical records skipped:          0
Total logical records read:             3
Total logical records rejected:         0
Total logical records discarded:        0
Total stream buffers loaded by SQL*Loader main thread:        1
Total stream buffers loaded by SQL*Loader load thread:        0

Run began on Mon Sep 26 22:47:09 2016
Run ended on Mon Sep 26 22:47:11 2016

Elapsed time was:     00:00:01.88
CPU time was:         00:00:00.01
[oracle@ora12102 Desktop]$ 

Did you notice the section in the log file that says :
The following index(es) on table TEST_SQLLDR_DIRECT were processed:
index HEMANT.TEST_SQLLDR_DIRECT_U1 was made unusable due to:
ORA-01452: cannot CREATE UNIQUE INDEX; duplicate keys found

Apparently, the Index is left UNUSABLE.

[oracle@ora12102 Desktop]$ sqlplus hemant/hemant@pdb1

SQL*Plus: Release 12.1.0.2.0 Production on Mon Sep 26 22:50:51 2016

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

Last Successful login time: Mon Sep 26 2016 22:47:09 +08:00

Connected to:
Oracle Database 12c Enterprise Edition Release 12.1.0.2.0 - 64bit Production
With the Partitioning, Automatic Storage Management, OLAP, Advanced Analytics
and Real Application Testing options

SQL> select status from user_indexes     
  2  where index_name = 'TEST_SQLLDR_DIRECT_U1'        
  3  /

STATUS
--------
UNUSABLE

SQL> select * from test_sqlldr_direct order by 1;

 ID_COLUMN DATA_COLUMN
---------- ---------------
  1 First Row
  2 'Second Row'
  3 'Third Row'
  3 'Oops !'

SQL> alter index test_sqlldr_direct_u1 rebuild;
alter index test_sqlldr_direct_u1 rebuild
*
ERROR at line 1:
ORA-01452: cannot CREATE UNIQUE INDEX; duplicate keys found


SQL> 

We can see the duplicated row for ID_COLUMN=3 and find that the Index cannot be rebuilt.  Oracle has allowed duplicate rows to load and left the Index UNUSABLE.

So, if you are planning to use DIRECT=TRUE and have a Unique Index, make sure you check the status of the Index and/or check the Log file before you proceed with processing the data.


Conversely, here is how the data is handled without DIRECT=TRUE :.

SQL> truncate table test_sqlldr_direct;

Table truncated.

SQL> insert into test_sqlldr_direct values (1,'First Row');

1 row created.

SQL> select status from user_indexes
  2  where index_name = 'TEST_SQLLDR_DIRECT_U1'
  3  /

STATUS
--------
VALID

SQL> 

[oracle@ora12102 Desktop]$ rm load_control.log
[oracle@ora12102 Desktop]$ sqlldr hemant/hemant@PDB1 control=load_control.ctl

SQL*Loader: Release 12.1.0.2.0 - Production on Mon Sep 26 22:59:58 2016

Copyright (c) 1982, 2014, Oracle and/or its affiliates.  All rights reserved.

Path used:      Conventional
Commit point reached - logical record count 3

Table TEST_SQLLDR_DIRECT:
  2 Rows successfully loaded.

Check the log file:
  load_control.log
for more information about the load.
[oracle@ora12102 Desktop]$ 
[oracle@ora12102 Desktop]$ ls -ltr
total 16
-rw-r--r-- 1 oracle oinstall   40 Sep 26 22:40 load_data.dat
-rw-r--r-- 1 oracle oinstall  165 Sep 26 22:45 load_control.ctl
-rw-r--r-- 1 oracle oinstall   11 Sep 26 22:59 load_data.bad
-rw-r--r-- 1 oracle oinstall 1668 Sep 26 22:59 load_control.log
[oracle@ora12102 Desktop]$ cat load_data.bad
3,'Oops !'
[oracle@ora12102 Desktop]$ cat load_control.log

SQL*Loader: Release 12.1.0.2.0 - Production on Mon Sep 26 22:59:58 2016

Copyright (c) 1982, 2014, Oracle and/or its affiliates.  All rights reserved.

Control File:   load_control.ctl
Data File:      load_data.dat
  Bad File:     load_data.bad
  Discard File:  none specified
 
 (Allow all discards)

Number to load: ALL
Number to skip: 0
Errors allowed: 50
Bind array:     64 rows, maximum of 256000 bytes
Continuation:    none specified
Path used:      Conventional

Table TEST_SQLLDR_DIRECT, loaded from every logical record.
Insert option in effect for this table: APPEND
TRAILING NULLCOLS option in effect

   Column Name                  Position   Len  Term Encl Datatype
------------------------------ ---------- ----- ---- ---- ---------------------
ID_COLUMN                           FIRST     *   ,  O(") CHARACTER            
DATA_COLUMN                          NEXT     *   ,  O(") CHARACTER            

Record 3: Rejected - Error on table TEST_SQLLDR_DIRECT.
ORA-00001: unique constraint (HEMANT.TEST_SQLLDR_DIRECT_U1) violated


Table TEST_SQLLDR_DIRECT:
  2 Rows successfully loaded.
  1 Row not loaded due to data errors.
  0 Rows not loaded because all WHEN clauses were failed.
  0 Rows not loaded because all fields were null.


Space allocated for bind array:                  33024 bytes(64 rows)
Read   buffer bytes: 1048576

Total logical records skipped:          0
Total logical records read:             3
Total logical records rejected:         1
Total logical records discarded:        0

Run began on Mon Sep 26 22:59:58 2016
Run ended on Mon Sep 26 22:59:58 2016

Elapsed time was:     00:00:00.07
CPU time was:         00:00:00.00
[oracle@ora12102 Desktop]$ 

SQL> select * from test_sqlldr_direct 
  2  order by id_column
  3  /

 ID_COLUMN DATA_COLUMN
---------- ---------------
  1 First Row
  2 'Second Row'
  3 'Third Row'

SQL> 
SQL> select status from user_indexes
  2  where index_name = 'TEST_SQLLDR_DIRECT_U1'
  3  /

STATUS
--------
VALID

SQL> 

The duplicate row was rejected and went to the BAD file and was REJECTED.
.
.
.
So, the next time you chose to use SQLLDR DIRECT=TRUE for its performance benefits, make sure you know how to validate the status of Unique Indexes and check the log file ! Else, you might allow duplicates if the incoming data has not been cleansed before loading into the database.
.
.
.
If you are running 11g, the behaviour is the same in 11g/
.
.
.
.

18 September, 2016

SQL*Net Message Waits

Here are some extracts from an 11.2.0.4 AWR Report  I ran a simulated workload on this server for about 40minutes and generated this report.  I understand that some DBAs may be misinterpreting SQL*Net message time.

Host Name        Platform                         CPUs Cores Sockets Memory(GB)
---------------- -------------------------------- ---- ----- ------- ----------
ora11204         Linux x86 64-bit                    2     2       1       3.04

              Snap Id      Snap Time      Sessions Curs/Sess
            --------- ------------------- -------- ---------
Begin Snap:       158 18-Sep-16 21:42:34        36       1.0
  End Snap:       159 18-Sep-16 22:23:01        33       1.0
   Elapsed:               40.45 (mins)
   DB Time:               42.04 (mins)

Load Profile                    Per Second   Per Transaction  Per Exec  Per Call
~~~~~~~~~~~~~~~            ---------------   --------------- --------- ---------
             DB Time(s):               1.0              10.2      0.12      0.07
              DB CPU(s):               0.0               0.1      0.00      0.00
      Redo size (bytes):           2,523.6          24,793.8
  Logical read (blocks):              41.7             409.6


Top 10 Foreground Events by Total Wait Time
~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~
                                            Tota    Wait   % DB
Event                                 Waits Time Avg(ms)   time Wait Class
------------------------------ ------------ ---- ------- ------ ----------
log file sync                         1,095 2508    2291   99.5 Commit
db file sequential read                 162 37.5     231    1.5 User I/O
DB CPU                                      19.1             .8
SQL*Net message to client            31,579   .1       0     .0 Network
Disk file operations I/O                103    0       0     .0 User I/O


Wait Classes by Total Wait Time
~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~
                                                        Avg             Avg
                                        Total Wait     Wait   % DB   Active
Wait Class                  Waits       Time (sec)     (ms)   time Sessions
---------------- ---------------- ---------------- -------- ------ --------
Commit                      1,095            2,509     2291   99.5      1.0
System I/O                 12,899            2,210      171   87.6      0.9
User I/O                    1,866               38       20    1.5      0.0
DB CPU                                          19              .8      0.0
Network                    33,651                9        0     .4      0.0


Foreground Wait Class                       DB/Inst: ORCL/orcl  Snaps: 158-159
-> s  - second, ms - millisecond -    1000th of a second
-> ordered by wait time desc, waits desc
-> %Timeouts: value of 0 indicates value was < .5%.  Value of null is truly 0
-> Captured Time accounts for        101.7%  of Total DB time       2,522.36 (s)
-> Total FG Wait Time:             2,546.18 (s)  DB CPU time:          19.14 (s)


                                                                  Avg
                                      %Time       Total Wait     wait
Wait Class                      Waits -outs         Time (s)     (ms)  %DB time
-------------------- ---------------- ----- ---------------- -------- ---------
Commit                          1,095     0            2,509     2291      99.5
User I/O                          276     0               38      136       1.5
DB CPU                                                    19                0.8
Network                        31,579     0                0        0       0.0
Concurrency                        21     0                0        0       0.0

Foreground Wait Events                     DB/Inst: ORCL/orcl  Snaps: 158-159
-> s  - second, ms - millisecond -    1000th of a second
-> Only events with Total Wait Time (s) >= .001 are shown
-> ordered by wait time desc, waits desc (idle events last)
-> %Timeouts: value of 0 indicates value was < .5%.  Value of null is truly 0


                                                             Avg
                                        %Time Total Wait    wait    Waits   % DB
Event                             Waits -outs   Time (s)    (ms)     /txn   time
-------------------------- ------------ ----- ---------- ------- -------- ------
log file sync                     1,095     0      2,509    2291      4.4   99.5
db file sequential read             162     0         37     231      0.7    1.5
SQL*Net message to client        31,579     0          0       0    127.9     .0
Disk file operations I/O            103     0          0       0      0.4     .0
latch: shared pool                    2     0          0       4      0.0     .0
direct path sync                      2     0          0       2      0.0     .0
db file scattered read                6     0          0       0      0.0     .0
jobq slave wait                   5,522   100      2,770     502     22.4
SQL*Net message from clien       31,577     0      2,404      76    127.8


Wait Event Histogram                       DB/Inst: ORCL/orcl  Snaps: 158-159
-> Units for Total Waits column: K is 1000, M is 1000000, G is 1000000000
-> % of Waits: value of .0 indicates value was <.05%; value of null is truly 0
-> % of Waits: column heading of <=1s is truly <1024ms>1s is truly >=1024ms
-> Ordered by Event (idle events last)

                                                    % of Waits
                                 -----------------------------------------------
                           Total
Event                      Waits  <1ms ms="" s="">1s
-------------------------- ----- ----- ----- ----- ----- ----- ----- ----- -----
ARCH wait on ATTACH           37                                      97.3   2.7
ARCH wait on DETACH           37 100.0
Disk file operations I/O     920  99.9                            .1
LGWR wait for redo copy       54 100.0
Parameter File I/O           640 100.0
SQL*Net break/reset to cli     6 100.0
SQL*Net message to client  33.6K 100.0
...
SQL*Net message from clien 34.2K  82.7   9.5   2.7   1.6    .7   2.4    .3    .0


Other Instance Activity Stats              DB/Inst: ORCL/orcl  Snaps: 158-159
-> Ordered by statistic name

Statistic                                     Total     per Second     per Trans
-------------------------------- ------------------ -------------- -------------
SQL*Net roundtrips to/from clien             31,579           13.0         127.9



Should I be worried about the SQL*Net message waits (to client, from client) ? How should I interpret them ?  I have more than 30K SQL*Net messages and roundtrips.  Apparently, messages from client are very slow -- at 76ms/message.  Apparently, round-trips are very slow -- at 13 round-trips per second.  Here's a hint :  In this workload that I created, all the client sessions were on the database server -- so  you shouldn't be expecting such large wait times !

SQL*Net message to client waits are not really measured in terms of the time it took for the message to reach the client.  Oracle doesn't know how long the transmission took.  It only knows the time taken to put the message onto the TCP stack on the database server itself.  Subsequent network transmission time is unknown. That is why SQL*Net message to client will always be an extremely low figure in terms of time -- because it isn't a true measure of elapsed time sending a message to a client.

SQL*Net message from client is not  just the time spent on the network.  It is the time between the last message to the client upto the next message from the client.  Thus, it also includes client overheads (like "think time", CPU time, network stack on the client) besides transmission over the network.  In most cases, the major component of SQL*Net message from the client is client "think time" -- how long the client spent before formatting and sending the next SQL call to the database server.   This should also mean that it is not always true that SQL*Net message from client is an idle event.

If I have a single client that is either or some or all of :
a. Loading data in sets of rows (e.g. an ETL client)
b. Extracting data in sets of rows
c. Retrieving results and formatting the results for presentation to a user
b. Running a batch job that majorly consists of SQL calls, not PLSQL
the SQL*Net message from client is majorly the time spent by the client  (--- unless you really have a high latency network.).  In most such cases, when tracing the individual session, this wait event is NOT an idle event.  However, when reading an AWR, you cannot isolate such a session from the other sessions that are doing a mix of activity -- some with real interactive end-users, some sending periodic "heart-beat" messages, some completely idle waiting for a request from a user.  In this AWR report, there are a mix of clients with different "think-times", some completely idle for 40minutes.  We can't identify them in the AWR report.

Can you use the SQL*Net roundtrips to/from client figure from the AWR ?  Not if you have a mix of different clients doing different forms of activity.  Idle clients will have very few roundtrips in the 40minutes while clients extracting data row-by-row (not in PLSQL, but SQL or some other client like Java) would have a high number of roundtrips.  So, you can't separate the two types of behaviour in an AWR.

If you are really concerned about identifying SQL*Net overheads and/or round-trips, you should *trace* the specific individual session of interest and extract figures from the trace file.
.
.
.


05 September, 2016

CODE : View My Source Code -- a Function

If you need to view the source code of a stored program you need to either :
a.  Be the owner of the program
b.  Have EXECUTE privilege on the program
c.  Have EXECUTE ANY ... privilege or the DBA role

If you are not the owner o the program, the owner can grant you access to view but not modify the program.

Here's code for a PL/SQL Function that allows this.  (I wrote this as a Function --- and not as a PL/SQL Procedure -- to be similar to the DBMS_METADATA.GET_DDL Function).

Imagine  that HR is the Application Schema and the owner of Tables and Programs.  Imagine that HEMANT has not been granted the DBA role or an EXECUTE privilege but needs access to view the source code.

Here's a quick method.  ("Quick" meaning that this code can be improved further -- e.g. by adding an Audit Trail and by better Error Handling).

SQL> connect hr/oracle
Connected.
SQL>
SQL> drop table authorized_view_source purge;

Table dropped.

SQL>
SQL> create table authorized_view_source
  2     (username  varchar2(30),
  3      object_type varchar2(23),
  4      object_name varchar2(30))
  5  /

Table created.

SQL>
SQL> create or replace function view_my_source(object_type_in in varchar2, object_name_in in varchar2)
  2  return clob
  3  as
  4  return_clob clob;
  5  line_out varchar2(4000);
  6  line_count pls_integer;
  7  line_no pls_integer;
  8  verify_count pls_integer;
  9  return_source  clob;
 10
 11  begin
 12   select count(*) into verify_count from authorized_view_source
 13  --  check if any of these three predicates fail
 14   where username = user
 15   and object_type = object_type_in
 16   and object_name = object_name_in;
 17
 18   if verify_count = 0 then
 19  -- don't tell if the object exists or not
 20   raise_application_error(-20001,'You are not authorized to view the source code of this object');
 21   return('FAILURE');
 22
 23   else
 24
 25   select count(*) into line_count from user_source
 26   where 1=1
 27   and type = object_type_in
 28   and name = object_name_in;
 29
 30   return_clob := ' ';
 31
 32   for line_no in 1..line_count
 33   loop
 34   return_clob := return_clob || line_out;
 35   select text into line_out from user_source
 36   where 1=1
 37   and type = object_type_in
 38   and name = object_name_in
 39   and line = line_no;
 40   end loop;
 41   return_clob := return_clob || line_out;
 42
 43   return return_clob;
 44   end if;
 45
 46  end view_my_source;
 47  /

Function created.
                                                                                                    
SQL>
SQL> show errors
No errors.
SQL> grant execute on view_my_source to hemant;

Grant succeeded.

SQL>
SQL> -- list all code objects
SQL> col object_name format a30
SQL> select object_type, object_name
  2  from user_objects
  3  where object_type not in ('TABLE','INDEX','VIEW')
  4  order by object_type, object_name
  5  /

OBJECT_TYPE             OBJECT_NAME
----------------------- ------------------------------
FUNCTION                VIEW_MY_SOURCE
PACKAGE                 ANOTHER_PKG
PACKAGE                 DEFINE_MY_VARIABLES
PACKAGE                 DUMMY_PKG
PACKAGE BODY            ANOTHER_PKG
PACKAGE BODY            DUMMY_PKG
PROCEDURE               ADD_JOB_HISTORY
PROCEDURE               SECURE_DML
SEQUENCE                DEPARTMENTS_SEQ
SEQUENCE                EMPLOYEES_SEQ
SEQUENCE                LOCATIONS_SEQ
TRIGGER                 SECURE_EMPLOYEES
TRIGGER                 UPDATE_JOB_HISTORY

13 rows selected.

SQL>
SQL> -- store list of authorzed access
SQL> -- e.g. HEMANT can't view the source for
SQL> --    "ANOTHER_PKG" and "VIEW_MY_SOURCE"
SQL> insert into authorized_view_source
  2  select 'HEMANT', object_type, object_name
  3  from user_objects
  4  where object_type not in ('TABLE','INDEX','VIEW')
  5  and object_name not in ('ANOTHER_PKG','VIEW_MY_SOURCE')
  6  /

10 rows created.

SQL> commit;

Commit complete.

SQL>
SQL> select count(*) from authorized_view_source
  2  where username = 'HEMANT'
  3  /

  COUNT(*)
----------
        10

SQL>
SQL>


So, HR has created a Table to list the Users that are authorized to view a certain list of stored programs.  This table, AUTHORIZED_VIEW_SOURCE excludes "ANOTHER_PKG" and "VIEW_MY_SOURCE" from the authorized list  for HEMANT.

Let's see what HEMANT can do :

SQL>
SQL> connect hemant/hemant
Connected.
SQL> -- the return type is a CLOB, so we SET LOMG
SQL> set long 1000000
SQL>
SQL> select hr.view_my_source('PACKAGE','DEFINE_MY_VARIABLES') from dual ;

HR.VIEW_MY_SOURCE('PACKAGE','DEFINE_MY_VARIABLES')
--------------------------------------------------------------------------------
 package
define_my_variables
authid definer
is
  my_application varchar2(25) := 'Human Resources';                             
  my_base_schema varchar2(25) := 'HR';                                          
end;                                                                            


SQL>
SQL> select hr.view_my_source('PACKAGE BODY','DUMMY_PKG') from dual ;

HR.VIEW_MY_SOURCE('PACKAGEBODY','DUMMY_PKG')
--------------------------------------------------------------------------------
 package body dummy_pkg as
procedure dummy_proc is
 begin
  raise_application_error (-20001,'Dummy Procedure');                           
  null;                                                                         
 end;                                                                           
end;                                                                            


SQL>
SQL> select hr.view_my_source('TRIGGER','SECURE_EMPLOYEES') from dual;

HR.VIEW_MY_SOURCE('TRIGGER','SECURE_EMPLOYEES')
--------------------------------------------------------------------------------
 TRIGGER secure_employees
  BEFORE INSERT OR UPDATE OR DELETE ON employees
BEGIN
  secure_dml;                                                                   
END secure_employees;                                                           


SQL>
SQL> -- these two should raise an error
SQL> select hr.view_my_source('PACKAGE','ANOTHER_PKG') from dual;
ERROR:
ORA-20001: You are not authorized to view the source code of this object
ORA-06512: at "HR.VIEW_MY_SOURCE", line 20



no rows selected

SQL> select hr.view_my_source('FUNCTION','VIEW_MY_SOURCE') from dual;
ERROR:
ORA-20001: You are not authorized to view the source code of this object
ORA-06512: at "HR.VIEW_MY_SOURCE", line 20



no rows selected

SQL>
SQL> select hr.view_my_source('PACKAGE','NONEXISTENT') from dual;
ERROR:
ORA-20001: You are not authorized to view the source code of this object
ORA-06512: at "HR.VIEW_MY_SOURCE", line 20



no rows selected

SQL> 


This shows that HEMANT can view the source code of programs listed in the AUTHORIZED_VIEW_SOURCE table and not any others.   The way I have defined the "Your are not authroized ..." message, it doesn't even show whether the requested program exists or not.
.
.
.

Index of Posts

My friend, Ravi Muthupalani has created an Index of my Blog Posts.


Hemant's Oracle DBA Blog

As on 03-Oct-16

2016-10
OBJ# and DATAOBJ# in 12c AWR

2016-09
SQLLoader DIRECT Option and Unique Index
SQL*Net Message Waits
CODE :  View My Source Code -- a Function

2016-07
CODE : Persistent Variables via PL/SQL Package and DBMS_APPLICATION_INFO
Loading SQL*Plus HELP into the Database
ACS, SQL Patch and SQL Plan Baseline

2016-06
Services -- 4 : Using the SERVICE_NAMES parameter (non-RAC, PDB)
Services -- 3 : Monitoring Usage of Custom Services
Services -- 2 : Starting and Connecting to Services (non-RAC)
Services -- 1 : Services in non-RAC 12c MultiTenant
Data Recovery Advisor (11g)
Blog Series on 11gR2 RAC, GI, ASM
Compression -- 8 : DROPping a Column of a Compressed Table

2016-05
Restore and Recovery from Incremental Backups : Video
Recent Blog Series on Partition Storage
TRUNCATEing a Table makes an UNUSABLE Index VALID again
Partition Storage -- 8 : Manually Sizing Partitions
Compression -- 7 : Updating after BASIC Compression
Compression -- 6b : Advanced Index Compression (revisited)
Compression -- 6 : Advanced Index Compression
FBDA -- 7 : Maintaining Partitioned Source Table
Partition Storage -- 7 : Revisiting HWM - 2 (again)

2016-04
Partition Storage -- 6 : Revisiting Partition HWM
Partition Storage -- 5 : Partitioned Table versus Non-Partitioned Table ? (in 12.1)
Partition Storage -- 4 : Resizing Partitions
Partition Storage -- 3 : Adding new Range Partitions with SPLIT
Partition Storage -- 2 : New Rows Inserted in 12.1 Partitioned Table
Partition Storage -- 1 : Default Partition Sizes in 12c
Online Relocation of Database File : ASM to FileSystem and FileSystem to ASM
FBDA -- 6 : Some Bug Notes
Recent Blog Series on Compression
FBDA -- 5 : Testing AutoPurging
FBDA -- 4 : Partitions and Indexes
FBDA -- 3 : Support for TRUNCATEs
FBDA -- 2 : FBDA Archive Table Structure
FBDA -- 1 : Testing Flashback Data Archive in 12c (NonCDB)

2016-03
Now an OCP 12c
Compression -- 5 : OLTP Compression
Compression -- 4 : RMAN (BASIC) Compression
Compression -- 3 : Index (Key) Compression
COMPRESSION -- 2 : Compressed Table Partitions
Recent Blog Series on (SQL) Tracing
Recent Blog Series on RMAN

2016-02
Compression -- 1b : (more on) BASIC Table Compression
Compression -- 1 : BASIC Table Compression
RMAN : Unused Block Compression and Null Block Compression
Trace Files -- 12 : Tracing a Particular Process
Trace Files -- 11b : Using DBMS_SQLDIAG to trace the Optimization of an SQL Statement

2016-01
Trace Files -- 11 : Tracing the Optimization of an SQL Statement
Trace Files -- 10c : Query and DML (INSERT)

2015-12
Oracle High Availability Demonstrations
Trace Files -- 10b : More DML Tracing
Trace Files -- 10 : Tracing DML
Trace Files -- 9 : Advantages
Trace Files -- 8d : Full Table Scans
Auditing DBMS_STATS usage

2015-11
Trace Files -- 8c : Still More Performance Evaluation from Trace File
Trace Files -- 8b : More Performance Evaluation from Trace File
Trace Files -- 8a : Using SQL Trace for Performance Evaluations
Trace Files -- 7 : SQL in PL/SQL
SSL Support
Trace Files -- 6 : Multiple Executions of the same SQL

2015-10
Trace Files -- 5.2 : Interpreting the SQL Trace Summary level
Trace Files -- 5.1 : Reading an SQL Trace
Trace Files -- 4 : Identifying a Trace File
Trace Files -- 3 : Tracing for specific SQLs

2015-09
Trace Files -- 2 : Generating SQL Traces (another session)
Trace Files -- 1 : Generating SQL Traces (own session)
My YouTube Videos as introductions to Oracle SQL and DBA
RMAN -- 10 : VALIDATE
RMAN -- 9 : Querying the RMAN Views / Catalog

2015-08
RMAN -- 8 : Using a Recovery Catalog Schema
RMAN -- 7 : Recovery Through RESETLOGS -- how are the ArchiveLogs identified ?
RMAN -- 6 : RETENTION POLICY and CONTROL_FILE_RECORD_KEEP_TIME

2015-07
RMAN -- 5c : (Some More) Useful KEYWORDs and SubClauses
RMAN -- 5b : (More) Useful KEYWORDs and SubClauses
Monitoring and Diagnostics without Oracle Enterprise Manager
RMAN -- 5 : Useful KEYWORDs and SubClauses
RMAN -- 4b : Recovering from an Incomplete Restore with OMF Files
RMAN -- 4 : Recovering from an Incomplete Restore

2015-06
RMAN - 3 : The DB_UNIQUE_NAME in Backups to the FRA
RMAN -- 2 : ArchiveLog Deletion Policy
RMAN -- 1 : Backup Job Details

2015-05
Parallel Execution -- 6 Parallel DML Restrictions
Parallel Execution -- 5b Parallel INSERT Execution Plan
Status Of My SlideShare Material
Parallel Execution -- 5 Parallel INSERT

2015-04
Parallel Execution -- 4 Parsing PX Queries
Parallel Execution -- 3b Limiting PX Servers with Resource Manager

2015-03
1 million page views in less than 5 years
Parallel Execution -- 3 Limiting PX Servers
Parallel Execution -- 2c PX Servers
Parallel Execution -- 2b PX Servers
Parallel Execution -- 2 PX Servers
Parallel Execution -- 1b The PARALLEL Hint and AutoDoP (contd)

2015-02
Parallel Execution -- 1 The PARALLEL Hint and AutoDoP
Database Flashback -- 5
Database Flashback -- 4
Database Flashback -- 3
Database Flashback -- 2
Database Flashback -- 1

2015-01
A blog on Oracle Standard Edition
Inserting into a table with potentially long rows

2014-12
Statistics on this blog
StatsPack and AWR Reports -- Bits and Pieces -- 4

2014-11
StatsPack and AWR Reports -- Bits and Pieces -- 3
StatsPack and AWR Reports -- Bits and Pieces -- 2

2014-10
StatsPack and AWR Reports -- Bits and Pieces -- 1
Bandwidth and Latency
11g Adaptive Cursor Sharing --- does it work only for SELECT statements ? Using the BIND_AWARE Hint for DML

2014-09
The ADMINISTER SQL MANAGEMENT OBJECT Privilege
EXECUTE Privilege on DBMS_SPM not sufficient
Index Growing Larger Than The Table
RAC Database Backups

2014-08
ASM Commands : 2 -- Migrating a DiskGroup to New Disk(s)
ASM Commands : 1 -- Adding and Using a new DiskGroup for RAC
GI Commands : 2 -- Managing the Local and Cluster Registry

2014-07
GI Commands : 1 -- Monitoring Status of Resources
RAC Commands : 2 -- Updating Configuration for Services
RAC Commands : 1 -- Viewing Configuration
Installing OEL 6 and Database 12c
Passed the 11g RAC and Grid Expert Exam

2014-06
Gather Statistics Enhancements in 12c
Getting your Transaction ID
Guenadi Jilevski's posts on building RAC Clusters on VM Virtual Box

2014-05
Oracle Diagnostics Presentations
Partitions and Segments and Data Objects
(Slightly Off Topic) Spurious Correlations

2014-04
PageView Count
Upgrading Certification to 12c

2014-03
Storing Trailing NULLs in a table
Plan HASH_VALUE remains the same for the same Execution Plan, even if ROWS and COST change
My slideshare site has had 1000 views
Dropping an Index Partition

2014-02
RMAN Image Copy File Names
SQL Analytics
An SQL Performance Quiz
login.sql does not require a login
Database Technology Index
The difference between SELECT ANY DICTIONARY and SELECT_CATALOG_ROLE

2014-01
My first Backup and Recovery Quiz
LAST_CALL_ET in V$SESSION
OTNYathra 2014

2013-12
INTERVAL Partitioning
DEFAULT ON NULL on INSERT
GATHER_TABLE_STATS : What SQLs does it call ?. 12c

2013-11
Gather Statistics Enhancements in 12c -- 5
AIOUG Sangam'13 Day Two 09-Nov-13
AIOUG Sangam'13 Day One 08-Nov-13

2013-10
Gather Statistics Enhancements in 12c -- 4
The DEFAULT value for a column

2013-09
AIOUG Sangam 13
RMAN (and DataPump) book

2013-08
Sins of Software Deployment
Gather Statistics Enhancements in 12c -- 3
Common Mistakes Java Developers make when writing SQL
Gather Statistics Enhancements in 12c -- 2
Gather Statistics Enhancements in 12c -- 1
12c New Features to "Watch Out For"
Re-CATALOGing BackupPieces ??

2013-07
What happens if a database (or tablespace) is left in BACKUP mode
Interesting Bugs in 12cR1
Concepts / Features overturned in 12c
12c RMAN Restrictions -- when connected to a PDB
Information on 12c
Dynamic SQL

2013-06
A Function executing DML in a View
Oracle Database 12c Learning Library
Networking in Oracle Virtual Box
Upcoming Blog Post : DML when querying a View
Getting the ROWIDs present in a Block
DROP A Tablespace After a Backup
Bug 10013177 running Aggregation on Expression indexed by an FBI

2013-05
BACKUP CURRENT CONTROLFILE creates a Snapshot Controlfile
Games for DBAs

2013-04
Preparing for Oracle Certification
SSD Performance for Oracle Databases
Single Row Fetch from a LOB
Oracle Forums due for Upgrade

2013-03
Useful Oracle Youtube videos
Segment Size of a Partition (11.2.0.2 and above)
Short-Circuiting the COST

2013-02
Moving a Partition to an Archival Schema and Tablespace
Backup and Recovery with intermediate NOARCHIVELOG

2013-01
Oracle 11g Anti-Hackers Cookbook
Podcast on the Oracle ACE program
Oracle's Advisory On Certification Integrity

2012-12
Book on OEM 12c

2012-11
Oracle 11g Anti-Hackers Cookbook

2012-10
Separate Child Cursor with varying bind allocation length

2012-09
Cardinality Decay
IT Contracting in Singapore
Open Invitation from Packt Publishing

2012-08
Storage Allocation
Issue a RECOVER for a Tablespace/Datafile that does not need recovery

2012-07
How to use Oracle Virtual Box templates
Database Specialists in Singapore
ON COMMIT Refresh without a Primary Key
Materialized View Refresh ON COMMIT
WizIQ Tutorials
An Oracle Installer that automatically switches to Console mode

2012-06
OOW 2012 Content Catalog
CONTROLFILE AUTOBACKUPs are OBSOLETE[d]
RMAN BACKUP AS COPY
OEM 12c : New Book
Java for PLSQL Developers

2012-05
SQL written by Lisbeth Salander
CHECKPOINT_CHANGE#
CURRENT_SCN and CHECKPOINT_CHANGE#
Index Block Splits
RMAN Tips -- 4
Debugging stories
A Poll on the usage of SQL Plan Management
USER_TAB_MODIFICATIONS -- 1

2012-04
Create Histogram without having to gather Table Stats
AIOUG Sangam '12 -- CFP
When is an ArchiveLog created ?
Parameters for COMMIT operations
Primary Key name appears to be different
TOO_MANY_ROWS and Variable Assignment

2012-03
The Hot Backup "myth" about Datafiles not being updated
Relocating a datafile using RMAN
More than 250K page views
OOW 2012 CFP now open.
Oracle Database Performance Diagnostics -- before you begin
Another example of COST in an Explain Plan
Packt Publishing's Oracle Packtpot

2012-02
Two Partitioned Indexes with different HIGH_VALUEs
CURSOR_SHARING FORCE and Child Cursors
Archived Logs after RESETLOGS
SLOB
RESETLOGS

2012-01
Understanding RESETLOGS
Oracle Wiki Relaunched
Departmental Analytics -- a "pro-local" approach ?
Refreshing an MV on a Prebuilt Table
SQL in Functions
Growing Materialized View (Snapshot) Logs
Datafiles not Restored -- using V$DATAFILE and V$DATAFILE_HEADER

2011-12
Does a STARTUP MOUNT verify datafiles ?
DROP TABLESPACE INCLUDING CONTENTS drops segments
(Off-Topic) How NOT to make a chart
AIOUG Sangam '11 photographs
AIOUG Sangam 11 content

2011-11
Constraints and Indexes
Oracle PreConfigured Templates
SSDs for Oracle
ROWIDs from an Index
RESTORE, RECOVER and RESETLOGS
Grid and RAC Notes
Oracle's Best-Of-Breed Strategy
Tablespace Recovery in a NOARCHIVELOG database
CTAS in a NOARCHIVELOG database is a NOLOGGING operation
Index Organized Table(s) -- IOT(s)
An ALTER USER to change password updates the timestamp of the password file
Handling Exceptions in PLSQL

2011-10
AIOUG : Sangam '11
The impact of ASSM on Clustering of data -- 2
DBMS_REDEFINITION to redefine a Partition -- and the impact of deferred_segment_creation
The impact of ASSM on Clustering of data
Controlfiles : Number and Size
Oracle OpenWorld and JavaOne Announcements
RMAN Tips -- 3

2011-09
Another example of GATHER_TABLE_STATS and a Histogram
Oracle Android App
An Index that is a "subset" of a pre-existing Index
RMAN Tips -- 2
Very successful Golden Gate workshop at SG RACSIG
RMAN Tips -- 1
Outer Join Queries
Splitting a Range Partitioned Table
Understanding Obsolescence of RMAN Backups

2011-08
CREATE INDEX ..... PARALLEL
Gather Column (Histogram) Stats can use an Index
Does GATHER_TABLE_STATS update Index Statistics ?
Reading an AWR Report -- 3
Singapore RACSIG meeting today
Reading an AWR -- 2
Oracle 11g RAC Essentials

2011-07
More on COUNT()s -- 2
More on COUNT()s
Data Quality Issues cannot always be addressed by programming
Running a COUNT(column) versus COUNT(*)
Oracle Database "Performance" - A Diagnostics Method
ENABLE ROW MOVEMENT with MSSM
Virtathon Sessions Schedule
ENABLE ROW MOVEMENT
Using WGET to download Patches
Reading an AWR - 1
Multiple Channels in RMAN are not always balanced

2011-06
DDL Triggers
Are you ready ? (to buy bigger hardware or review your code ?)
How Are Students Learning Programming ?
Oracle APAC Developer Program
OOW 2011 Content Catalog
(OT) : Dead Media Never Really Die
Precedence in Parallel Query specifications
Inequality and NULL
SQL Injection
New Presentation : On Nested Loop and Hash Join
Getting the right statistics
Nested Loop and Consistent Gets

2011-05
Interpreting an AWR report when the ArchiveLog Dest or FRA was full
Deleting SQL Plan Baselines
Database Audit setup -- 2 interesting findings
Capturing SQL PLAN Baselines
11g OCP
Getting all the instance parameters
RMAN's COPY command
Collection of my Oracle Blog posts on Backup and Recovery

2011-04
SELECT FOR UPDATE with SubQuery (and "Write Consistency")
ArchiveLogs in the controlfile
DETERMINISTIC Functions - 3
Standby Databases (aka "DataGuard") -1
DETERMINISTIC Functions -- 2
DETERMINISTIC Functions

2011-03
OuterJoin with Filter Predicate
I/O for OutOfLine LOBs
Oracle Enterprise Cloud Summit in Singapore
Cardinality Estimates in Dynamic Partition Pruning
Primary Key and Index
Most Popular Posts - Feb 11

2011-02
ITIL v3 Foundation
Index Block Splits --- with REVERSE KEY Index
Qualifying Column/Object names to set the right scope
Cardinality Feedback in 11.2
Oracle Diagnostics Presentations
Locks and Lock Trees
Most Popular Posts - Jan 11

2011-01
Gather Stats Concurrently
Synchronising Recovery of two databases
Transaction Failure --- when is the error returned ?
GLOBAL TEMPORARY TABLEs and GATHER_TABLE_STATS
Rollback of Transaction(s) after SHUTDOWN ABORT
Latches and Enqueues
Incomplete Recovery
ZDNet Asia IT Salary Benchmark 2010
Most Popular Posts - Dec 10

2010-12
Using V$SESSION_LONGOPS
Most Popular Posts - Nov 10

2010-11
Oracle VM Templates released
Some Common Errors - 7 - "We killed the job because it was hung"
SET TIME ON in RMAN
Most Popular Posts - Oct 10

2010-10
How the Optimizer can use Constraint Definitions
Featured in Oracle Magazine
Data Skew and Cardinality Changing --- 2
Most Popular Posts

2010-09
Data Skew changing over time --- and the Cardinality Estimate as well !
Index Skip Scan
Deadlocks : 2 -- Deadlock on INSERT
Deadlocks

2010-08
Adding a DataFile that had been excluded from a CREATE CONTROLFILE
Trying to understand LAST_CALL_ET -- 3
Trying to understand LAST_CALL_ET -- 2
Trying to understand LAST_CALL_ET -- 1
Oracle Mergers and Acquisitions
Creating a "Sparse" Index

2010-07
Oracle switching to non-sequential logs
(Off Topic): "What's the body count ?"
Preserving the Index when dropping the Constraint
V$DATABASE.CREATED -- is this the Database Creation timestamp ?
(Off Topic) : "Now Is That Architecture ?"

2010-06
Some Common Errors - 6 - Not collecting Metrics
Know your data and write a better query
RECOVER DATABASE starts with an update -- 2

2010-05
RECOVER DATABASE starts with an update
Database Links
Cardinality Estimation
Read Only Tablespaces and BACKUP OPTIMIZATION
Database and SQL Training

2010-04
Oracle Database History
AutoTune Undo
Data Warehousing Performance
SQLs in PLSQL -- 2
SQLs in PLSQL
Partitions and Statistics
11gR2 Recursive SubQuery Factoring

2010-03
Extracting Application / User SQLs from a TraceFile
A large index for an empty table ?
ALTER INDEX indexname REBUILD.
Adaptive Cursor Sharing explained
An "unknown" error ?
Misinterpreting RESTORE DATABASE VALIDATE

2010-02
Some Common Errors - 5 - Not reviewing the alert.log and trace files
Something Unique about Unique Indexes
Some Common Errors - 4 - Not using ARRAYSIZE
Using Aliases for Columns and Tables in SQLs
Table and Index Statistics with MOVE/REBUILD/TRUNCATE
Some Common Errors - 3 - NOLOGGING and Indexes
An Oracle DBA Interview
Some Common Errors - 2 - NOLOGGING as a Hint
Multiple Block Sizes

2010-01
Common Errors Series
DDL on Empty Partitions -- are Global Indexes made UNUSABLE ?
Some Common Errors - 1 - using COUNT(*)
Adding a PK Constraint sets the key column to NOT NULL

2009-11
MOS Survey Results
SIZE specification for Column Histograms
Sample Sizes : Table level and Column level

2009-10
Some MORE Testing on Intra-Block Row Chaining
Some Testing on Intra-Block Row Chaining
Indexes Growing Larger After Rebuilds

2009-09
SQLs in Functions : Performance Impact
SQLs in Functions : Each Execution is Independent
RMAN can identify and catalog / use ArchiveLogs automagically
Table and Partition Statistics
I am an Oracle ACE, officially

2009-08
Histograms on "larger" columns
Counting the Rows in a Table
Using an Index created by a different user

2009-07
A PACKT book on Oracle Database Utilities
Direct Path Read cannot do delayed block cleanouts
The difference between NOT IN and NOT EXISTS
Simple Tracing
Sizing OR Growing a Table in AUTOALLOCATE

2009-06
AUTOEXTEND ON Next Size
Why EXPLAIN PLAN should not be used with Bind Variables

2009-05
Backup Online Redo Logs ? (AGAIN ?!)
Index Block Splits and REBUILD
Index Block Splits : 50-50
Database Recovery with new datafile not present in the controfile
Index Block Splits : 90-10
Rename Database while Cloning it.
Incorrectly using AUTOTRACE and EXPLAIN PLAN
Ever wonder "what if this database contains my data ?"

2009-04
Bringing ONLINE a Datafile that is in RECOVER mode because it was OFFLINE
Controlfile Backup older than the ArchiveLogs
RMAN Backup and Recovery for Loss of ALL files
Incorrect Cardinality Estimate of 1 : Bug 5483301

2009-03
Database Independence Anyone ?
Columnar Databases
Materialized View on Prebuilt Table
Materialized Views and Tables
Checking the status of a database
Logical and Physical Storage in Oracle

2009-02
CLUSTERING_FACTOR
RDBMS Software, Database and Instance
Restore or Create Controlfile
Full Table Scan , Arraysize etc
Array Processing, SQL*Net RoundTrips and consistent gets
MIN/MAX Queries, Execution Plans and COST

2009-01
Faulty Performance Diagnostics based on initial set of rows returned
When NOT to use V$SESSION_LONGOPS

2008-11
Database Event Trigger and SYSOPER
Tracing a Process -- Tracing DBWR
expdp to the default directory without the DBA role
Data Pump using default directory
Histogram (skew) on Unique Values
OPEN RESETLOGS without really doing a Recovery
Numbers and NULLs

2008-10
Using STATISTICS_LEVEL='ALL' and 10046, level 8
Delayed Block Cleanout -- through Instance Restart
Delayed Block Cleanout

2008-09
Relational Theory and SQL

2008-08
ASSM or MSSM ? -- DELETE and INSERT
The once again new forums.oracle.com
Testing Bug 4260477 Fix for Bug 4224840
ASSM or MSSM ? -- The impact on INSERTS
VMWare Bug presents a nightmare scenario
Preventing a User from changing his password
More Tests of COL_USAGE
Testing Gather Stats behaviour based on COL_USAGE

2008-07
More Tests on DBMS_STATS GATHER AUTO
Testing the DBMS_STATS option GATHER AUTO
Cardinality Estimate : Dependent Columns --- Reposted
Table Elimination (aka "Join Elimination")
Bind Variable Peeking

2008-06
Cardinality Estimates : Dependent Columns
Delete PARENT checks every CHILD row for Parent Key !
Monitoring "free memory" on Linux
A long forums discussion on Multiple or Different Block Sizes
Tuning Very Large SELECTs in SQLPlus
MVs with Refresh ON COMMIT cannot be used for Synchronous Replication

2008-05
Ever heard of "_simple_view_merging" ?
Tracing a DBMS_STATS run
Creating a COMPRESSed Table
Passwords are One Way Hashes, Not Encrypted
APPEND, NOLOGGING and Indexes
RMAN Consistent ("COLD" ?) Backup and Restore
DBAs working long hours
One Thing Leads to Another ....
TEMPORARY Segments in Data/Index Tablespaces

2008-04
Row Sizes and Sort Operations
Using SYS
Indexed column (unique or not) -- What if it is NULLable
The Worst Ever SQL Rewrite
Complex View Merging -- 7
Complex View Merging - 4,5,6
Programming for MultiCore architectures
Complex View Merging -- 3
Complex View Merging -- 2
Complex View Merging -- 1

2008-03
Example "sliced" trace files and tkprof
tkprof on partial trace files
Backup the Online Redo Logs ?
Rebuilding Indexes - When and Why ?
Rebuilding Indexes
ALTER TABLE ... SHRINK SPACE

2008-02
OS Statistics from AWR Reports
Database Recovery : RollForward from a Backup Controlfile
Indexing NULLs -- Update

2008-01
Is RAID 5 bad ? Always bad ?
The Impact of the Clustering Factor
Examples Of Odd Extent Sizes In Tablespaces With AUTOALLOCATE
When Should Indexes Be Rebuilt

2007-12
Using an Index for a NOT EQUALS Query
Always Explicitly Convert DataTypes

2007-11
Are ANALYZE and DBMS_STATS also DDLs ?

2007-10
Flush Buffer_Cache -- when Tracing doesn't show anything happening
Inserts waiting on Locks ? Inserts holding Locks ?

2007-09
More on Bind Variable Peeking and Execution Plans
ATOMIC_REFRESH=>FALSE causes TRUNCATE and INSERT behaviour in 10g ??

2007-08
When "COST" doesn't indicate true load
NULLs are not Indexed, Right ? NOT !
NLS_DATE_FORMAT
Shared Nothing or Shared Disks (RAC) ?
LGWR and 'log file sync waits'

2007-07
Using ARRAYSIZE to reduce RoundTrips and number of FETCH calls
Parse "Count"

2007-06
Read Consistency across Statements
Some observations from the latest Oracle-HP Benchmark
A Bug in OWI
Oracle Books in the Library

2007-05
AUTOALLOCATE and Undo Segments
Where's the Problem ? Not in the Database !
RollForward from a Cold Backup
Another Recovery from Hell story
SQL Statement Execution Times
Recovery in Cold Backup
Stress Testing

2007-04
DBA Best Practices
Recovery without UNDO Tablespace DataFiles
Programs that expect strings to be of a certain length !
UNDO and REDO for INSERTs and DELETEs
Snapshot Too Old or Rollback Segment Too Small

2007-03
Backups and Recoveries, SANs and Clones, and Murphy
Optimizer Index Cost Parameters
Understanding "Timed Events" in a StatsPack Report
Database Error Exposed on the Internet
Throughput v Scalability
Using Normal Tables for Temporary Data

2007-02
Interpreting Explain Plans
Creating Database Links
Buffer Cache Hit Ratio GOOD or BAD ?

2007-01
Sequences : Should they be Gap Free ?
Using Partial Recoveries to test Backups
Deleting data doesn't reduce the size of the backup
Large (Growing) Snapshot Logs indicate that you have a problem
Sometimes you trip up on Triggers
Views with ORDER BY
Building Materialized Views and Indexes

2006-12
ArchiveLogs and Transaction Volumes
ORA-1555 and UNDO_RETENTION
Why an Oracle DBA Blog ?