Exam 1z0-071 topic 1 question 44 discussion

1. CREATE GLOBAL TEMPORARY TABLE my_temp_table ( id NUMBER, description VARCHAR2(20) ) ON COMMIT PRESERVE ROWS 2. INSERT INTO my_temp_table VALUES (1, 'ONE'); 3. Commit; 4. ALTER TABLE my_temp_table ADD ID_1 VARCHAR(20); --> ORA-14450: attempt to access a transactional temp table already in use 5. drop table my_temp_table; --> ORA-14452: attempt to create, alter or drop an index on temporary table already in use 6. truncate table my_temp_table; 7. drop table my_temp_table;

ANSWER CE

A. Incorrect. Dropping a table is unrelated to whether it has been truncated. If you have sufficient privileges, you can drop the table directly without needing to truncate it first. B. Incorrect. In a global temporary table, data is isolated per session, and other sessions cannot see the data in the current session, even if it has been committed. C. Correct. If you have sufficient privileges, you can modify the table structure in the current session, including adding new columns. D. Incorrect. Generally, you cannot add a foreign key to a global temporary table because a foreign key requires referencing a permanent table, and the rows in a global temporary table are cleared at the end of the session. E. Correct. With a global temporary table using ON COMMIT PRESERVE ROWS, all data inserted during the session will be automatically cleared when the session ends.

At least in 23c C and E are correct. I haven't faced any error when adding a column, so C correct I could drop the table without truncating it first -- A incorrect

A and E, test in 19c. C in the session error ORA 14450 attempt to access a transactional temp table already in use, FIRST end session and add column, but no in the same session

You get the following error if you don't truncate table invoices_gtt drop table invoices_gtt Error report: SQL Error: ORA-14452: attempt to create, alter or drop an index on temporary table already in use 14452. 00000 - "attempt to create, alter or drop an index on temporary table already in use" *Cause: An attempt was made to create, alter or drop an index on temporary table which is already in use. *Action: All the sessions using the session-specific temporary table have to truncate table and all the transactions using transaction specific temporary table have to end their transactions.

Should be A and E tried it out

why is C wrong? google tells me its possible 'A temporary table can be altered in the same way as a permanent base table although there is no official support to toggle the behavior of the ON COMMIT clause.' -some article

You cant add an fk to a temporary table. Can't refer to it and cannot refer to another table from the temporary table. Tried it out!

AE is the answer

A is correct ,you can try execute the sql ,and you would get the err msg:ORA-03290 E is correct,when you terminate your session,the row will be deleted,you can try it.

DDL operation on global temporary tables It is not possible to perform a DDL operation (except TRUNCATE) on an existing global temporary table if one or more sessions are currently bound to that table.

for me , correct are A. To drop the table in this session, you must first truncate it. E. When you terminate your session, the row will be deleted.

I think A and E are correct. You have a row inserted, so first you have to truncate the table in order to drop it. When you end the session, the table will be dropped, what it means that the row will be also deleted. Please correct me if I am wrong.

The answers are AB. C- false. You CAN'T add a column, and the rows are preserved. D-false. You CAN"T add a foreign key to a temp table. E-false. Rows are preserved.

Here is an example of the differences between session-specific and transaction-specific GTT with comments: drop table temp1 purge; CREATE GLOBAL TEMPORARY TABLE temp1( id INT, description VARCHAR2(100) ) ON COMMIT DELETE ROWS; INSERT INTO temp1(id,description) VALUES(1,'Transaction specific global temp table'); SELECT id, description FROM temp1; -- 1 row is there commit; SELECT id, description FROM temp1; -- row is gone after the transaction ALTER TABLE temp1 Add ID_1 char(20); -- will add column after commit because it is transaction specific drop table temp1; CREATE GLOBAL TEMPORARY TABLE temp2( id INT, description VARCHAR2(100) ) ON COMMIT PRESERVE ROWS; INSERT INTO temp2(id,description) VALUES(1,'Session specific global temp table'); SELECT id, description FROM temp2; --1 row is there commit; SELECT id, description FROM temp2; -- 1 row is still there ALTER TABLE temp2 Add ID_1 char(20); --will not work with or without commit after INSERT. It's in session drop table temp2; -- won't work. session specific truncate table temp2; drop table temp2; -- now it works