Search My Oracle Blog

Custom Search

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>
QL> 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.
                                                                                                    92,1          40%
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.
.
.
.

Aggregated by orafaq.com

Aggregated by orafaq.com
This blog is being aggregated by orafaq.com

Top 50 Oracle SQL Blogs 2016

Top 50 Oracle SQL Blogs 2016
Top 50 Oracle SQL Blogs 2016