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;

How can someone answer A and take this exam?

ANSWER CE

Here is why A is wrong: ❌ A. To drop the table in this session, you must first truncate it. False. Truncating is not required before dropping a table in Oracle. You can drop a temporary table at any time (assuming permissions). ✅ No dependency on truncation.

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.