java.sql.SQLException: ORA-01000: maximum open cursors exceeded while truncating tables

Question

I get this exception while truncating all table in a schema.
I truncate 3 schema in my Java code and first method get list of table names from given schema name and second method executes "TRUNCATE TABLE table_name" query.
I confused about my code always succesful while truncating first and third schema. But while executing on second schema I get ORA-01000 error.
My truncate code is

    private void truncateTable(Connection conn, String tableName) {
    PreparedStatement ps = null;
    try {
        ps = conn.prepareStatement(Utility.TRUNCATE_TABLE + tableName);
        ps.executeUpdate();
    } catch (SQLException e) {
        log.error("SQLException occured while getting table names from schema", e);
    } finally {
        Utility.free(ps, null, null);
    }
}


    private List<String> getAllTableNames(Connection conn) {
    PreparedStatement ps = null;
    ResultSet rs = null;
    List<String> list = new ArrayList<String>();
    try {
        ps = conn.prepareStatement(Utility.SELECT_ALL_TABLE_NAMES);
        rs = ps.executeQuery();
        while (rs.next()) {
            list.add(rs.getString("TABLE_NAME"));
        }
    } catch (SQLException e) {
        log.error("SQLException occured while getting table names from schema", e);
    } finally {
        Utility.free(ps, rs, null);
    }
    return list;
}

    public static void free(PreparedStatement ps, ResultSet rs, Connection conn) {
    if (rs != null) {
        try {
            rs.close();
        } catch (SQLException e) {
            log.error("Error occurred while closing ResultSet",e);
        }
    }
    if (ps != null) {
        try {
            ps.close();
        } catch (SQLException e) {
            log.error("Error occurred while closing PreparedStatement",e);
        }
    }
    if (conn != null) {
        try {
            conn.close();
        } catch (SQLException e) {
            log.error("Error occurred while closing Connection",e);
        }
    }
}

What is the wrong about code or is it about schema configuraiton in Oracle? How can I solve this?

Make sure you are closing all these result sets when you get the table names. — Thilo
Yes sure I added my code and after exception truncates tables of other other schema succesfully — Ugur Artun

Alex Poole Alex Poole · Accepted Answer · 2013-07-29T11:10:35

If are you iterating over the List generated by getAllTableNames and calling truncateTable in a tight loop, your free calls in the finally block might just be delayed and stacking up to an extent that they aren't clearing fast enough for the next iterations - since you only know the finally will be called at some point, not necessarily immediately and before control is returned to the caller.

The schema size would make a difference to that, so it might make sense that a small schema succeeds and a large one fails. If that is what's happening then you should call free inside the try, as well as in the finally:

    private void truncateTable(Connection conn, String tableName) {
    PreparedStatement ps = null;
    try {
        ps = conn.prepareStatement(Utility.TRUNCATE_TABLE + tableName);
        ps.executeUpdate();
        Utility.free(ps, null, null);
        ps = null;
    } catch (SQLException e) {
        log.error("SQLException occured while getting table names from schema", e);
    } finally {
        if (ps != null) {
            Utility.free(ps, null, null);
        }
    }
}

If Utility.free checks whether ps is null then that check in the finally block might be redundant, but without it, free would be called twice if there is no SQLException.

java.sql.SQLException: ORA-01000: maximum open cursors exceeded while truncating tables

2 Answers