webentwicklung-frage-antwort-db.com.de

Erzwingen Sie die globale Temp-Tabelle für Oracle Drop

In unserem Projekt erstelle ich eine globale Temp-Tabelle, die wie folgt aussehen wird:

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;

das Problem tritt jedoch auf, wenn ich diese Tabelle löschen möchte. Oracle lässt mich die Tabelle nicht fallen, und es heißt:

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

Ich muss diese Tabelle in einem bestimmten Verfahren verwenden, kann aber abhängig von anderen Berichten geändert werden. Also sollte ich die Tabelle immer löschen und sie dann mit meinen benötigten Feldern neu erstellen.

Ich muss dies aus geschäftlichen Gründen verwenden, daher ist es mir nicht möglich, Tabellen oder andere Dinge zu verwenden. Ich kann nur temporäre Tabellen verwenden. Ich habe versucht, Löschzeilen festzuschreiben, aber wenn ich meine Prozedur zur Verwendung der Daten in dieser Tabelle aufrufe, sind keine weiteren Zeilen in der Tabelle vorhanden, und sie wurden gelöscht.

Jede Hilfe wird sehr geschätzt, Vielen Dank im Voraus

/// BEARBEITEN

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

Globale temporäre Oracle-Tabellen sind keine vorübergehenden Objekte. Sie sind richtige Heap-Tische. Wir erstellen sie einmal und jede Sitzung kann sie zum Speichern von Daten verwenden, die nur für diese Sitzung sichtbar sind.

Der temporäre Aspekt ist, dass die Daten nicht über eine Transaktion oder eine Sitzung hinaus bestehen. Das wichtigste Detail der Implementierung ist, dass die Daten in einen temporären Tabellenbereich geschrieben werden, nicht in einen permanenten. Die Daten werden jedoch immer noch auf die Festplatte geschrieben und von dieser gelesen, so dass die Verwendung globaler temporärer Tabellen einen erheblichen Aufwand verursacht.

Der Punkt ist, dass wir temporäre Tabellen nicht löschen und neu erstellen sollen. Wenn Sie versuchen, die SQL Server-Stillogik in Oracle zu portieren, sollten Sie PL/SQL-Sammlungen verwenden, um temporäre Daten im Arbeitsspeicher zu verwalten. Finde mehr heraus.

Die spezifische Ursache von ORA-14452 besteht darin, dass wir keine globale temporäre Tabelle löschen können, deren Persistenz im Sitzungsbereich besteht, wenn sie während der Sitzung Daten enthielt. Auch wenn die Tabelle momentan leer ist ...

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>

Die Lösung besteht darin, die Sitzung zu beenden und erneut eine Verbindung herzustellen oder (etwas bizarr) die Tabelle abzuschneiden und sie dann zu löschen.

SQL> truncate table gtt23;

Table truncated.

SQL> drop table gtt23;

Table dropped.

SQL> 

Wenn eine andere Sitzung die globale temporäre Tabelle verwendet und dies möglich ist (daher die Nomenklatur global), können Sie die Tabelle erst dann löschen, wenn alle Sitzungen getrennt werden.

Die wirkliche Lösung besteht also darin, zu lernen, wie globale temporäre Tabellen richtig verwendet werden: Erstellen Sie spezifische globale temporäre Tabellen, die mit jedem Bericht übereinstimmen. Oder verwenden Sie stattdessen PL/SQL-Sammlungen. Oder lernen Sie einfach, gut abgestimmtes SQL zu schreiben. Oft verwenden wir temporäre Tabellen als Workaround für eine schlecht geschriebene Abfrage, die mit einem besseren Zugriffspfad gespeichert werden könnte.


Nachdem Sie sich Ihren vollständigen Code angesehen haben, erscheint der Ablauf noch bizarrer:

  1. Löschen Sie eine globale temporäre Tabelle, und erstellen Sie sie erneut
  2. Temporäre Tabelle befüllen
  3. Wählen Sie aus einer temporären Tabelle in ein PL/SQL-Array
  4. Einfügen in die tatsächliche Tabelle unter Verwendung der Masseneinfügung aus einem PL/SQL-Array

Es gibt so viel Aufwand und verschwendete Aktivität hier. Alles, was Sie tun müssen, ist, die eingegebenen Daten in v2d_temp zu übernehmen und vertical_design direkt mit Daten zu füllen, idealerweise mit einer INSERT INTO ... SELECT * FROM-Anweisung. Sie benötigen einige Vorverarbeitung, um ein JSON-Array in eine Abfrage zu konvertieren. Dies ist jedoch in Java oder PL/SQL leicht zu erreichen.

Es scheint mir sicher, dass globale temporäre Tabellen nicht die richtige Lösung für Ihr Szenario sind.


"Unser Chef oder andere Personen beharren darauf, etwas auf ihre Art zu tun, damit Sie das nicht ändern können"

Was Sie haben, ist ein Boss Problem nicht ein Programmierproblem . Infolgedessen ist StackOverflow nicht thematisch. Aber hier sind trotzdem einige Vorschläge.

Es ist wichtig, sich daran zu erinnern, dass wir nicht über einen Kompromiss in einer suboptimalen Architektur sprechen: Was Ihr Chef eindeutig vorschlägt funktioniert nicht in einer Umgebung mit mehreren Benutzern. Ihre Optionen sind also:

  1. Ignorieren Sie den ORA-14452-Fehler, fahren Sie mit der Produktion fort und verwenden Sie dann die "aber Sie haben mir gesagt" -Abwehr, wenn alles schrecklich schief geht. Dies ist das schwächste Spiel.
  2. Verstecken Sie die globalen Tabellen verdeckt und implementieren Sie etwas, das in einem Mehrbenutzer-Szenario funktionieren wird. Dies ist ein hohes Risiko, da Sie keine Verteidigung haben, wenn Sie die Implementierung missbrauchen.
  3. Sprechen Sie mit Ihrem Chef. Sagen Sie ihnen, dass Sie auf den ORA-14452-Fehler stoßen. Nehmen Sie an, Sie haben einige Nachforschungen angestellt und es scheint ein grundlegendes Problem bei der Verwendung globaler temporärer Tabellen auf diese Art zu sein, aber offensichtlich haben Sie etwas übersehen. Fragen Sie sie dann, wie sie dieses Problem umgangen haben, nachdem sie es zuvor implementiert haben. Dies kann mehrere Wege gehen, vielleicht haben sie eine Problemumgehung, vielleicht erkennen sie, dass dies der falsche Weg ist, globale temporäre Tabellen zu verwenden, vielleicht sagen sie Ihnen, dass Sie sich verlaufen sollen. In beiden Fällen ist dies der beste Ansatz: Sie haben Bedenken auf die entsprechende Ebene gebracht.

Viel Glück.

16
APC

Ein weiterer Ansatz, der in Betracht gezogen werden sollte, ist zu überdenken, ob Sie überhaupt einen temporären Tisch benötigen.

Es ist eine weit verbreitete Programmierpraxis unter denen, die von anderen RDBMSs zu Oracle wechseln, um sie zu überlasten, da sie nicht verstehen, dass Sie Funktionen wie Common Table-Ausdrücke verwenden können, um implizit eine temporäre Ergebnismenge zu verwirklichen, auf die in anderen Teilen von verwiesen werden kann Dieselbe Abfrage, und auf anderen Systemen ist es üblich geworden, Daten in eine Tabelle zu schreiben und diese dann auszuwählen.

Der Fehler wird normalerweise dadurch verursacht, dass nicht verstanden wird, dass die PL/SQL-basierte zeilenweise Verarbeitung in fast jeder Hinsicht der SQL-basierten Satzverarbeitung unterlegen ist - langsamer, komplexer Code, wortreicher und fehleranfälliger - jedoch Oracle stellt so viele andere leistungsstarke Funktionen für die SQL-Verarbeitung bereit, dass sie selbst bei Bedarf ohnehin direkt in eine SQL SELECT-Anweisung integriert werden kann.

Als Randbemerkung musste ich in 20 Jahren, in denen ich Oracle-Code für das Reporting und ETL geschrieben hatte, nur einmal eine zeilenweise Verarbeitung durchführen und nie eine temporäre Tabelle verwenden.

4
David Aldridge

Das Beenden von Sitzungen ist die einzige Möglichkeit, ORA-14452-Fehler zu umgehen. Verwenden Sie das Datenwörterbuch, um andere Sitzungen anhand der temporären Tabelle zu suchen, und beenden Sie sie Mit einer Anweisung wie alter system kill session 'sid,seriall#,instance_id';.

Dies ist die "offizielle" Lösung, die im Oracle-Supportdokument SO ERWÄHNEN EINES ORA-14452 WÄHREND DES TROPFS VON TEMPORARY TABLE (Dok.-ID 800506.1) beschrieben wird. Ich habe diese Methode in der Vergangenheit aus einem etwas anderen Grund erfolgreich angewendet. Das Töten von Sitzungen erfordert erhöhte Berechtigungen und kann schwierig sein. Es kann erforderlich sein, mehrmals zu töten, zu warten und erneut zu versuchen.

Diese Lösung ist sicherlich aus vielen Gründen eine schlechte Idee. Bevor Sie dies implementieren, sollten Sie versuchen, diese Informationen als Beweis dafür zu nutzen, dass dies Der falsche Weg ist. Zum Beispiel "Die Oracle-Dokumentation besagt, dass diese Methode das alter system-Privileg erfordert, was gefährlich ist und einige Sicherheitsprobleme mit sich bringt ...".

2
Jon Heller

Sie können alle laufenden Sitzungen anzeigen, indem Sie Folgendes ausführen:

SELECT * FROM V$SESSION

Um eine Sitzung zu beenden, haben Sie einige Möglichkeiten. Der folgende Befehl wartet, bis die aktuellen Transaktionen während des Fluges abgeschlossen sind, bevor die Verbindung getrennt wird:

ALTER SYSTEM DISCONNECT SESSION ‘sid,serial#’ POST_TRANSACTION

Während der folgende Befehl wie kill -9 ist; Es löscht den O/S-Prozess:

ALTER SYSTEM DISCONNECT SESSION ‘sid,serial#’ IMMEDIATE

Letzteres ist die effizienteste Methode, um die Sitzung zu beenden, die Sie daran hindert, eine temporäre Tabelle zu entfernen. Seien Sie jedoch vorsichtig, da dies eine ziemlich rohe Kraftfunktion ist. Sobald die Sitzung beendet ist, können Sie die temporäre Tabelle ohne Fehler entfernen.

Mehr über die verschiedenen Arten, eine Sitzung zu beenden, können Sie hier nachlesen (ich bin mit dieser Website nicht verbunden, ich habe sie selbst entdeckt, als ich ein ähnliches Problem hatte wie Sie): https: // chandlerdba .wordpress.com/2013/07/25/töten einer Sitzung tot/

0
dearsina