Αποτελέσματα Αναζήτησης
9 Αυγ 2017 · Is it possible to change a global temporary table in Oracle from PRESERVE ROWS to DELETE ROWS? I have tried the following command and I get a syntax error. If it is possible, what is the correct syntax? ALTER TABLE BLOCKING_RESULTS ON COMMIT DELETE ROWS. SQL Error: ORA-01735: invalid ALTER TABLE option 01735. 00000 - "invalid ALTER TABLE option"
The ON COMMIT DELETE ROWS clause specifies that the global temporary table is transaction-specific. It means that Oracle truncates the table (remove all rows) after each commit.
10 Μαΐ 2016 · SQL> create global temporary table gtt 2 on commit delete rows 3 as select * from dba_objects where 1=0; Table created. SQL> insert into gtt 2 select * from dba_objects; 87250 rows created.
The problem comes when that has to be scaled up, and amount of rows to delete in dependent tables become too large for one transaction (i.e. the estimated size of UNDO data is > 100 Gb). Is there a way to break such a multi-table DELETE into several transactions?
The session-specific rows in a global temporary table can be preserved for the whole session, or just for the current transaction. The ON COMMIT DELETE ROWS clause indicates the data should be deleted at the end of the transaction, or the end of the session. CREATE GLOBAL TEMPORARY TABLE my_temp_table (.
29 Ιουλ 2015 · select sid from v$lock where type = 'TO' and id1 = (select object_id from dba_objects where object_name = '&temp_table_name'); Then you can kill these sessions or have them release the locks gracefully.
26 Σεπ 2022 · Delete Rows: All rows in the table are deleted at the end of the transaction. Drop: The temporary table is dropped at the end of the transaction. For example, to delete rows at the end of the transaction: CREATE TEMPORARY TABLE temp_customers SELECT id, cust_name FROM customer ON COMMIT DELETE ROWS; Drop a Temporary Table in PostgreSQL
