title: Dropping a table partition avoiding the error ORA-00054
body: <p>I need your opinion in this situation. I’ll try to explain the scenario. I have a Windows service that stores data in an Oracle database periodically. The table where this data is being stored is partitioned by date (Interval-Date Range Partitioning). The database also has a dbms_scheduler job that, among other operations, truncates and drops older partitions. </p>
  
  <p>This approach has been working for some time, but recently I had an <a href="http://ora-00054.ora-code.com" rel="nofollow">ORA-00054</a> error. After some investigation, the error was reproduced with the following steps:</p>
  
  <ol>
  <li>Open one sqlplus session, disable auto-commit, and insert data in the
  partitioned table, <em>without committing</em> the changes;</li>
  <li>Open another sqlplus session and truncate/drop an old partition (DDL
  operations are automatically committed, if I’m not mistaken). We
  will then get the ORA-00054 error.</li>
  </ol>
  
  <p>There are some constraints worthy to be mentioned:</p>
  
  <ul>
  <li>I don’t have DBA access to the database;</li>
  <li>This is a legacy application and a complete refactoring isn’t
  feasible;</li>
  </ul>
  
  <p>So, in your opinion, is there any way of dropping these old partitions, without the risk of running into an ORA-00054 error and without the intervention of the DBA? I can just delete the data, but the number of empty partitions will grow everyday.</p>
  
  <p>Many thanks in advance.</p>