Force Oracle Drop Global Temp Table

Question

In our project I create some global temp table that will be like these:

CREATE GLOBAL TEMPORARY TABLE v2dtemp (
  id           NUMBER,
  GOOD_TYPE_GROUP       VARCHAR2(250 BYTE),
  GOOD_CODE             VARCHAR2(50 BYTE),
  GOOD_TITLE            VARCHAR2(250 BYTE)
)
ON COMMIT PRESERVE ROWS;

but the problem comes when I want to drop this table. Oracle will not let me to drop the table, and it says:

ORA-14452: attempt to create, alter or drop an index on temporary table already in use

I have to use this table in some procedure but it may be changed dependent to other reports. So I should always drop the table then I should recreate it with my needed fields.

I have to use this for some business reasons so it is not possible for me to use tables, or other things. I can use just temp tables. I tried on commit delete rows, but when I call my procedure to use the data in this table there are no more rows in the table and they have been deleted.

Any helps will greatly appreciated, thanks in advance

/// EDIT

public void saveJSONBatchOpenJobs(final JSONArray array, MtdReport report) {
    dropAndCreateTable();
    String sql = "INSERT INTO v2d_temp " +
            "(ID, KARPARDAZ, GOOD_TYPE_GROUP, GOOD_CODE, GOOD_TITLE, COUNT, "
            + "FACTOR_COUNT, GHABZ_COUNT, DEAL_NO, DEAL_DATE, REQUEST_NO, REQUEST_DATE, "
            + "REQUEST_CLIENT, STATUS, TYPE, MTDREPORT_ID, GEN_SECURITY_DATA_ID) " +
            "VALUES (MTD_KARPARDAZ_OPEN_JOBS_SEQ.nextval,?, ?, ?, ?, ?, ?, ?, ?, ?, ?, ?, ?, ?, ?, ?, ?)";

    getJdbcTemplate().batchUpdate(sql, new BatchPreparedStatementSetter() {

        @Override
        public void setValues(PreparedStatement ps, int i) throws SQLException {
            JSONArray values = array.getJSONArray(i);
            if(!values.get(0).equals("null"))
                ps.setString(1, values.get(0).toString());
            else
                ps.setNull(1, Types.VARCHAR);
            if(!values.get(1).equals("null"))
                ps.setString(2, values.get(1).toString());
            else
                ps.setNull(2, Types.VARCHAR);
            if(!values.get(2).equals("null"))
                ps.setString(3, values.get(2).toString());
            else
                ps.setNull(3, Types.VARCHAR);
            if(!values.get(3).equals("null"))
                ps.setString(4, values.get(3).toString());
            else
                ps.setNull(4, Types.VARCHAR);
            if(!values.get(4).equals("null"))
                ps.setBigDecimal(5, new BigDecimal(values.get(4).toString()));
            else
                ps.setNull(5, Types.NUMERIC);
            if(!values.get(5).equals("null"))
                ps.setBigDecimal(6, new BigDecimal(values.get(5).toString()));
            else
                ps.setNull(6, Types.NUMERIC);
            if(!values.get(6).equals("null"))
                ps.setBigDecimal(7, new BigDecimal(values.get(6).toString()));
            else
                ps.setNull(7, Types.NUMERIC);
            if(!values.get(7).equals("null"))
                ps.setString(8, values.get(7).toString());
            else
                ps.setNull(8, Types.VARCHAR);
            if(!values.get(8).equals("null"))
                ps.setDate(9, new Date(new Timestamp(values.getLong(8)).getDateTime()));
            else
                ps.setNull(9, Types.DATE);
            if(!values.get(9).equals("null"))
                ps.setString(10, values.get(9).toString());
            else
                ps.setNull(10, Types.VARCHAR);
            if(!values.get(10).equals("null"))
                ps.setDate(11, new Date(new Timestamp(values.getLong(8)).getDateTime()));
            else
                ps.setNull(11, Types.DATE);
            if(!values.get(11).equals("null"))
                ps.setString(12, values.get(11).toString());
            else
                ps.setNull(12, Types.VARCHAR);
            if(!values.get(12).equals("null"))
                ps.setString(13, values.get(12).toString());
            else
                ps.setNull(13, Types.VARCHAR);
            if(!values.get(13).equals("null"))
                ps.setString(14, values.get(13).toString());
            else
                ps.setNull(14, Types.VARCHAR);
            if(!values.get(14).equals("null"))
                ps.setLong(15, new Long(values.get(14).toString()));
            else
                ps.setNull(15, Types.NUMERIC);
            if(!values.get(15).equals("null"))
                ps.setLong(16, new Long(values.get(15).toString()));
            else
                ps.setNull(16, Types.NUMERIC);
        }

        @Override
        public int getBatchSize() {
            return array.size();
        }
    });

    String bulkInsert = "declare "
            + "type array is table of d2v_temp%rowtype;"
            + "t1 array;"
            + "begin "
            + "select * bulk collect into t1 from d2v_temp;"
            + "forall i in t1.first..t1.last "
            + "insert into vertical_design values t1(i);"
            + "end;";
    executeSQL(bulkInsert);
}

private void dropAndCreateTable() {
    String dropSql = "declare c int;"
            + "begin "
            + "select count(*) into c from user_tables where table_name = upper('v2d_temp');"
            + "if c = 1 then "
            + "truncate table v2d_temp"
            + "drop table v2d_temp;"
            + " end if;"
            + "end;";
    executeSQL(dropSql);

    String createSql = "CREATE GLOBAL TEMPORARY TABLE v2d_temp (\n"
            + "DEAL_ID               NUMBER,\n"
            + "id           NUMBER,\n"
            + "karpardaz  VARCHAR2(350),\n"
            + "GOOD_TYPE_GROUP       VARCHAR2(250 BYTE),\n"
            + "GOOD_CODE             VARCHAR2(50 BYTE),\n"
            + "GOOD_TITLE            VARCHAR2(250 BYTE),\n"
            + "COUNT                 NUMBER,\n"
            + "FACTOR_COUNT          NUMBER,\n"
            + "GHABZ_COUNT           NUMBER,\n"
            + "DEAL_NO               VARCHAR2(50 BYTE),\n"
            + "DEAL_DATE             DATE,\n"
            + "REQUEST_NO            VARCHAR2(50 BYTE),\n"
            + "REQUEST_DATE          DATE,\n"
            + "REQUEST_CLIENT        VARCHAR2(250 BYTE),\n"
            + "STATUS                VARCHAR2(250 BYTE),\n"
            + "TYPE                  VARCHAR2(250 BYTE),\n"
            + "GEN_SECURITY_DATA_ID  NUMBER(10),\n"
            + "MTDREPORT_ID          NUMBER\n"
            + ")\n"
            + "ON COMMIT PRESERVE ROWS";
    executeSQL(createSql);
}

private void executeSQL(String sql) {
    Connection con = null;
    try {
        con = getConnection();
        Statement st = con.createStatement();
        st.execute(sql);
    } catch (SQLException e) {
        e.printStackTrace();
    } finally {
        if(con != null) {
            try {
                con.close();
            } catch (SQLException e) {
                e.printStackTrace();
            }
        }
    }
}

Answer 1

Killing sessions is the only way to work around ORA-14452 errors. Use the data dictionary to find other sessions using the temporary table and kill them with a statement like alter system kill session 'sid,seriall#,instance_id';.

This is the "official" solution mentioned in the Oracle support document HOW TO DIAGNOSE AN ORA-14452 DURING DROP OF TEMPORARY TABLE (Doc ID 800506.1). I've successfully used this method in the past, for a slightly different reason. Killing sessions requires elevated privileges and can be tricky; it may require killing, waiting, and trying again several times.

This solution is almost certainly a bad idea for many reasons. Before you implement this, you should try to leverage this information as proof that this is the wrong way to do it. For example, "Oracle documentation says this method requires alter system privilege, which is dangerous and raises some security issues...".