Ytukyg

I apologize for my new status. I rarely ask for help because I try to exhaust all avenues before turning to burdening someone with my issue :)

Anyway, I am trying to figure out how this is possible. First let me explain how a particular job works and the DB I am dealing with

Oracle Database 11g Enterprise Edition Release 11.2.0.4.0 - 64bit Production
PL/SQL Release 11.2.0.4.0 - Production
CORE 11.2.0.4.0 Production
TNS for Linux: Version 11.2.0.4.0 - Production
NLSRTL Version 11.2.0.4.0 - Production

So we have this particular job under the data warehouse schema (DW_SCHEMA):

1. DW_SCHEMA runs scheduled job called JB_DW_WAIT_LIST


2. JB_DW_WAIT_LIST only runs a procedure called PROC_DW_WAIT_LIST


3. The procedure PROC_DW_WAIT_LIST only does two things
   
   
   
   
   • truncates a table DW_SCHEMA.DW_LIST_REPORT
   
   
   • inserts values into DW_SCHEMA.DW_LIST_REPORT from a view under a different schema LAST1 called LAST1.WAIT_LIST_VIEW
   
   
   
   

A little about LAST1.WAIT_LIST_VIEW. All this does is look for specific values from a partitioned table called LAST1.DW_TABLE. This table has two partitioned based on reason code. P1 is code = 10 and P2 is the rest.

This is where the issue shows up. The job should be able to produce approximately 21k rows to insert into DW_SCHEMA.DW_LIST_REPORT. The result was only 132 records. After doing some analysis, I come to find out that after the job is complete, it will only look at the first partition (which holds 132 records). We find out that select privileges was lacking under the schema DW_SCHEMA to LAST1.DW_TABLE. After granting it, and running the job under DW_SCHEMA the expected 21k records showed up.

So this fixed this issue, but then a new question came up. How did this even happen? If SELECT privileges were not there, the only thing it looked at was the first partition, but after granting the privilege, the whole table (both partitioned) were looked at. I have a feeling it might be privilege related, but I cannot see any reason why it is like this. Any help on this would be appreciated.