Exam 1z0-082 topic 1 question 51 discussion

A D E i think the correct answers

A - true; TRUNCATE command works in the same way for GTT as for standard tables, it removes all rows. However it is not recommended, because GTT are cleaned at the end of the transaction or the session. B - false; from CREATE TABLE documentation: The data in a temporary table is visible only to the session that inserts the data into the table. C - false; from documentation: Space is allocated for the table segment at the time of the first DML operation on the table. D - true; from documentation: A session becomes unbound to a temporary table with a TRUNCATE statement or at session termination, or, for a transaction-specific temporary table, by issuing a COMMIT or ROLLBACK statement. E - true; from documentation: Specify GLOBAL TEMPORARY to create a temporary table, whose definition is visible to all sessions with appropriate privileges. F - false; no mention in documentation about non-standard consequence of DELETE command for GTT. Documentation mentions ROLLBACK.

Only D & E are correct B tested and couldn't see the data A & F are not valid in case I create the GTT with a clause ON COMMIT PRESERVE in the end of the creation C is wrong as Franky_T said

Once again a question with four correct answers and not three. A is correct. Can be easily tested. The default behavior for the ON COMMIT action is to delete rows if no hold-able cursor is open on the table. A TRUNCATE (DDL) performs an implicit COMMIT which closes any open cursor in the session. B is wrong. You can grant other users SELECT privilege on your own GTT, but even if you do so the other user cannot see it. Can be easily tested. C is wrong. GTT space allocation (TEMP tablespace) occurs when it's created and not when the session starts. D is correct. The GTT exists at session level. E is correct. GTT's metadata is stored on disk and visible to all sessions, GTT's data however is session specific. F is correct. Can be easily tested. ON ROLLBACK DELETE ROWS is the default behavior for the NOT LOGGED option, thus removing all rows from a GTT when a ROLLBACK command is issued in the session.

D E F are correct answers.

At the termination of the session the rows are deleted but the table still exists: SQL> INSERT INTO temp1(id,description) VALUES(1,'Transaction specific global temp table'); 2 1 row created. SQL> SQL> select * from TEMP1; ID ---------- DESCRIPTION -------------------------------------------------------------------------------- 1 Transaction specific global temp table SQL> exit Disconnected from Oracle Database 19c Enterprise Edition Release 19.0.0.0.0 - Production Version 19.3.0.0.0 [oracle@localhost ~]$ sqlplus system/oracle SQL*Plus: Release 19.0.0.0.0 - Production on Wed Apr 6 00:41:30 2022 Version 19.3.0.0.0 Copyright (c) 1982, 2019, Oracle. All rights reserved. Last Successful login time: Wed Apr 06 2022 00:40:47 -04:00 Connected to: Oracle Database 19c Enterprise Edition Release 19.0.0.0.0 - Production Version 19.3.0.0.0 SQL> select * from TEMP1; no rows selected SQL>

A delete from temp table cannot be rolled back: SQL> select * from TEMP1; ID ---------- DESCRIPTION -------------------------------------------------------------------------------- 1 Transaction specific global temp table SQL> delete from temp1; 1 row deleted. SQL> select * from TEMP1; no rows selected SQL> rollback; Rollback complete. SQL> select * from TEMP1; no rows selected SQL>

ADE correct

A,D,E is correct

F is true SQL> select * from gtt1; ID ---------- 1 1 row selected. SQL> delete from gtt1; 1 row deleted. SQL> select * from gtt1; no rows selected SQL> rollback; Rollback complete. SQL> select * from gtt1; no rows selected

BCF - are definitly WRONG

A. GLOBAL TEMPORARY TABLE rows inserted by a session are available to any other session whose user has been granted select on the table -> FALSE, you cant view data from other session B. GLOBAL TEMPORARY TABLE space allocation occurs at session start. -> AT First dml sentence, then space is allocated ¿false? C. A DELETE command on a GLOBAL TEMPORARY TABLE cannot be rolled back. -> Depends. If you create with on commit preserve rows, you can rollback and commit. -> FALSE D. A GLOBAL TEMPORARY TABLE's definition is available to multiple sessions. -> Yes, with the appropiate permisions ¿TRUE? E. Any GLOBAL TEMPORARY TABLE rows existing at session termination will be deleted. -> TRUE F. A TRUNCATE command issued in a session causes all rows in a GLOBAL TEMPORARY TABLE for the issuing session to be deleted. -> TRUE, truncate delete rows from table ¿D,E,F? Very well presented explanations from Primisser https://www.examtopics.com/discussions/oracle/view/8895-exam-1z0-071-topic-1-question-298-discussion/

why is E correct? Reference: "Other users do not need the same table structure." "Multiple connections can define declared global temporary tables with the same name" https://docs.oracle.com/javadb/10.8.3.0/ref/rrefdeclaretemptable.html