title: Oracle trouble in getting data from a partitioned table
body: <p>On a new job I have to figure out how some database reporting scripts are working.
  There is one table that is giving me some trouble. I see in existing scripts that it is a partitioned table.
  My problem is that whatever query I run on this table returns me "no rows selected".</p>
  
  <p>Here are some details about my investigation in this table:</p>
  
  <p><strong>Table size estimate</strong></p>
  
  <pre><code>SQL&gt; select sum(bytes)/1024/1024 Megabytes from dba_segments where segment_name = 'PPREC';
  
  MEGABYTES
  ----------
  45.625
  </code></pre>
  
  <p><strong>Partitions</strong></p>
  
  <p>There are a total of 730 partitions on date range.</p>
  
  <pre><code>SQL&gt; select min(PARTITION_NAME),max(PARTITION_NAME) from dba_segments where segment_name = 'PPREC';
  
  MIN(PARTITION_NAME)            MAX(PARTITION_NAME)
  ------------------------------ ------------------------------
  PART20110201                   PART20130130
  </code></pre>
  
  <p>There are several tablespaces and partitions are allocated in them</p>
  
  <pre><code>SQL&gt; select tablespace_name, count(partition_name) from dba_segments where segment_name = 'PPREC' group by tablespace_name;
  
  TABLESPACE_NAME                COUNT(PARTITION_NAME)
  ------------------------------ ---------------------
  REC_DATA_01                                       281
  REC_DATA_02                                        48
  REC_DATA_03                                        70
  REC_DATA_04                                        26
  REC_DATA_05                                        44
  REC_DATA_06                                        51
  REC_DATA_07                                        13
  REC_DATA_08                                        48
  REC_DATA_09                                        32
  REC_DATA_10                                        52
  REC_DATA_11                                        35
  REC_DATA_12                                        30
  </code></pre>
  
  <p>Additional query:</p>
  
  <pre><code>SQL&gt; select * from dba_segments where segment_name='PPREC' and partition_name='PART20120912';
  
  OWNER SEGMENT_NAME PARTITION_NAME SEGMENT_TYPE    TABLESPACE_NAME HEADER_FILE HEADER_BLOCK BYTES BLOCKS EXTENTS 
  ----- ------------ -------------- --------------- --------------- ----------- ------------ ----- ------ -------
  HIST  PPREC        PART20120912   TABLE PARTITION REC_DATA_01              13       475315 65536      8       1
  
  INITIAL_EXTENT NEXT_EXTENT MIN_EXTENTS MAX_EXTENTS PCT_INCREASE FREELISTS FREELIST_GROUPS RELATIVE_FNO BUFFER_POOL
  -------------- ----------- ----------- ----------- ------------ --------- --------------- ------------ -----------
    65536                              1  2147483645                                                 13    DEFAULT
  </code></pre>
  
  <p><strong>Tabespace usage</strong></p>
  
  <p>Here is a space summary (composite of dba_tablespaces, dba_data_files, dba_segments, dba_free_space)</p>
  
  <pre><code>TABLESPACE_NAME                TOTAL_MEGABYTES USED_MEGABYTES FREE_MEGABYTES
  ------------------------------ --------------- -------------- --------------
  REC_01_INDX                              30,700            250         30,449
  REC_02_INDX                               7,745              7          7,737
  REC_03_INDX                              22,692             15         22,677
  REC_04_INDX                              15,768             10         15,758
  REC_05_INDX                              25,884             16         25,868
  REC_06_INDX                              27,992             16         27,975
  REC_07_INDX                              17,600             10         17,590
  REC_08_INDX                              18,864             11         18,853
  REC_09_INDX                              19,700             12         19,687
  REC_10_INDX                              28,716             16         28,699
  REC_DATA_01                             102,718            561        102,156
  REC_DATA_02                              24,544          3,140         21,403
  REC_DATA_03                              72,710              4         72,704
  REC_DATA_04                              29,191              2         29,188
  REC_DATA_05                              42,696              3         42,692
  REC_DATA_06                              52,780            323         52,456
  REC_DATA_07                              16,536              1         16,534
  REC_DATA_08                              49,247              3         49,243
  REC_DATA_09                              30,848              2         30,845
  REC_DATA_10                              49,620              3         49,616
  REC_DATA_11                              40,616              2         40,613
  REC_DATA_12                             184,922        123,435         61,486
  </code></pre>
  
  <p>The tablespace usage seems to confirm that this table is not empty, in fact its last tablespace (REC_DATA_12) seems pretty busy.</p>
  
  <p><strong>Existing scripts</strong></p>
  
  <p>What I find puzzling is that there are some PL/SQL stored procedures that seem to work on that table and get data out of it.
  An example of such a stored procedure is as follows:</p>
  
  <pre><code>procedure FIRST_REC as
  vpartition varchar2(12);
  begin
  select 'PART'||To_char(sysdate,'YYYYMMDD') INTO vpartition FROM DUAL;
  
  execute immediate
  'MERGE INTO FIRST_REC_temp a
   USING (SELECT bno, min(trdate) mintr,max(trdate) maxtr
          FROM PPREC PARTITION ('||vpartition||') WHERE route_id IS NOT NULL AND trunc(trdate) &lt;= trunc(sysdate-1)
          GROUP BY bno) b
      ON (a.bno=b.bno)
      when matched then
      update set a.last_tr = b.maxtr
      when not matched then
      insert (a.bno,a.last_tr,a.first_tr)
      values (b.bno,b.maxtr,b.mintr)';
      commit;
  </code></pre>
  
  <p>However if I try using the same syntax manually on the table, here is what I get:</p>
  
  <pre><code>SQL&gt; select count(*) from PPREC PARTITION (PART20120912);
  
    COUNT(*)
  ----------
           0
  </code></pre>
  
  <p>I have tried a few random partitions and I always get the same 0 count.</p>
  
  <p><strong>Summary</strong>
  - I see a table that seems to contain data (space used, tablespaces, data files)
  - The table is partitioned (one partition per day over a period of 730 days ending end of January 2013)
  - Scripts are extracting data from that table somehow</p>
  
  <p><strong>Question</strong>
  - My queries using PARTITION are all returning me "no rows selected". What am I doing wrong? How could I find out how to extract data from this table?</p>