Force Oracle Drop Global Temp Table

问题:

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();
            }
        }
    }
}

回答1:

Oracle global temporary tables are not transient objects. They are proper heap tables. We create them once and any session can use them to store data which is visible only to that session.

The temporary aspect is that the data is not persistent beyond one transaction or one session. The key implementation detail is that the data is written to a temporary tablespace not a permanent one. However, the data is still written to - and read from - disk, so there is a notable overhead to the use of global temporary tables.

The point is we are not supposed to drop and recreate temporary tables. If you're trying to port SQL Server style logic into Oracle then you should consider using PL/SQL collections to maintain temporary data in-memory. Find out more.

The specific cause of ORA-14452 is that we cannot drop a global temporary table which has session scope persistence if it has contained data during the session. Even if the table is currently empty...

SQL> create global temporary table gtt23 (col1 number)
  2  on commit preserve rows
  3  /

Table created.

SQL> insert into gtt23 values (1);

1 row created.

SQL> commit;

Commit complete.

SQL> delete from gtt23;

1 row deleted.

SQL> commit;

Commit complete.

SQL> drop table gtt23;
drop table gtt23
           *
ERROR at line 1:
ORA-14452: attempt to create, alter or drop an index on temporary table already in use

SQL>

The solution is to end the session and re-connect, or (somewhat bizarrely) to truncate the table and then drop it.

SQL> truncate table gtt23;

Table truncated.

SQL> drop table gtt23;

Table dropped.

SQL> 

If some other session is using the global temporary table - and that is possible (hence the global nomenclature) then you won't be able to drop the table until all the sessions disconnect.

So the real solution is to learn to use global temporary tables properly: create specific global temporary tables to match each report. Or, as I say, use PL/SQL collections instead. Or, even, just learn to write well-tuned SQL. Often we use temporary tables as a workaround to a poorly-written query which could be saved with a better access path.

---

Having looked at your full code, the flow seems even more bizarre:

1. Drop and re-create a global temporary table
2. Populate temporary table
3. Select from temporary table into PL/SQL array
4. Insert into actual table using bulk insert from PL/SQL array

There's so much overhead and wasted activity in here. All you need to do is take the data you insert into v2d_temp and directly populate vertical_design, ideally with an INSERT INTO ... SELECT * FROM statement. You will require some pre-processing to convert a JSON array into a query but that is easy to achieve in either Java or PL/SQL.

It seems certain to me that global temporary tables are not the right solution for your scenario.

---

"our boss or other persons persist to do something through their way, so you cannot change that"

What you have is a Boss Problem not a Programming Problem. Consequently it is off-topic as far as StackOverflow goes. But here are some suggestions anyway.

The key thing to remember is that we are not talking about a compromise on some sub-optimal architecture: what your boss proposes clearly won't work in a multi-user environment. so, your options are:

1. Ignore the ORA-14452 error, proceed into production and then use the "but you told me to" defence when it all goes horribly wrong. This is the weakest play.
2. Covertly junk the global tables and implement something which will work in a multi-user scenario. This is high-risk because you have no defence if you botch the implementation.
3. Speak to your boss. Tell them you're running into the ORA-14452 error, say you have done some investigation and it appears to a fundamental issue with using global temporary tables in this fashion but obviously you've overlooked something. Then, ask them how they got around this problem when they've implemented it before. This can go several ways, maybe they have a workaround, maybe they'll realise that this is the wrong way to use global temporary tables, maybe they'll tell you to get lost. Either way, this is the best approach: you've raised concerns to the appropriate level.

Good luck.

回答2:

Another approach worth considering here is to rethink whether you need a temporary table at all.

It is a very common programming practice among those who transition from other RDBMSs to Oracle to overuse them, because they do not understand that you can use such features as Common Table Expressions to implicitly materialise a temporary result set that can be referenced in other parts of the same query, and on other systems it has become natural to write data to a table and then select from it.

The failure is usually compounded by not understanding that PL/SQL-based row by row processing is inferior in almost every respect to SQL-based set processing -- slower, more complex to code, more wordy, and more error prone -- but Oracle presents so many other powerful features for SQL processing that even when it is required it can generally be integrated directly into a SQL SELECT statement anyway.

As a side-note, in 20 years of writing Oracle code for reporting and ETL, I only needed to do use row-by-row processing once, and never needed to use a temporary table.

回答3:

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...".

回答4:

You can see all running sessions by running:

SELECT * FROM V$SESSION

To kill a session, you have a few options. The following command waits for the current in-flight transactions to complete before disconnecting:

ALTER SYSTEM DISCONNECT SESSION ‘sid,serial#’ POST_TRANSACTION

While the following command is just like kill -9; it wipes the O/S process out:

ALTER SYSTEM DISCONNECT SESSION ‘sid,serial#’ IMMEDIATE

The latter is the most efficient in killing the session that is preventing you from removing a temp table. However, use with caution, as it's a pretty brute force function. Once the session is terminated, you can remove the temp table without getting errors.

You can read more about the different ways of killing a session here (I'm not affiliated with this website, I came across it myself when I had a similar problem to yours): https://chandlerdba.wordpress.com/2013/07/25/killing-a-session-dead/