title: Excluding MDRT% tables from ALL_TABLES view in Oracle
body: <p>I'm trying to retrieve all tables in a particular schema using the following query:</p>
  
  <pre><code>SELECT OWNER || '.' || TABLE_NAME
    from sys.all_tables
   where secondary = 'N' and owner = 'TEST'
  </code></pre>
  
  <p>This query worked perfectly for me until today when I realized that the same query is behaving differently between 11.2.0.3 and later versions of Oracle. From Oracle 11.2.0.4 onwards, the same query also returns some additional tables, all starting with name MDRT. </p>
  
  <p>I've verified that similar tables exist in both 11.2.0.3 and 11.2.0.4 DBs (noticed it on 12c also). A little googling about what these tables were revealed that they're created by Oracle whenever a spatial index is created. I looked at the schema of <code>all_tables</code> to see if I could use any of the columns to try and exclude these additional tables didn't succeed.</p>
  
  <p>The most surprising thing is that I couldn't find any articles on SO or Oracle forums discussing this. This is rather annoying.</p>