i agree with AB
A - https://oracle-base.com/articles/18c/private-temporary-tables-18c
Oracle 18c introduced the concept of a private temporary table, a memory-based temporary table that is dropped at the end of the session or transaction depending on the setup.
B - https://docs.oracle.com/html/E25494_01/indexes003.htm#CIHFIGDG
Beginning with Oracle Database 11g Release 2, the database does not create an index segment when creating an unusable index.
C - not always, depends on the setup of the table creation
A - TRUE; PTT stores metadata and data in memory
B - TRUE; Release 19, SQL Reference, CREATE INDEX: When an index, or some partitions or subpartitions of an index, are created UNUSABLE, no segment is allocated for the unusable object. The unusable index or index partition consumes no space in the database.
C - FALSE; Release 19, SQL Reference, CREATE TABLE: The ON COMMIT clause used with keywords PRESERVE DEFINITION creates a session-specific table whose definition is preserved when the transaction commits.
D - FALSE; Release 19, SQL Reference, ALTER INDEX: The space allocated for an index or index partition or subpartition is freed immediately when the object is marked UNUSABLE.
E - FALSE; elease 19, SQL Reference, TRUNCATE TABLE: Deallocates all space used by the removed rows except that specified by the MINEXTENTS storage parameter - space specified by the MINEXTENTS parameter is not removed
[A]: https://gavinsoorma.com.au/knowledge-base/oracle-18c-new-feature-private-temporary-tables/
Well, D is correct. You can try:
create table teste(
id number,
id2 number);
create index idx_teste01 on teste(id);
create index idx_teste02 on teste(id2) unusable;
col segment_name for a15
select segment_name, segment_type, extents, blocks, bytes from dba_segments where segment_name in ('IDX_TESTE01', 'IDX_TESTE02');
You can see that the segments exist in dba_segments for the valid and the unusable index.
Another example is to switch the valid index to unusable and repeat the query. Also the segments still there.
alter index IDX_TESTE01 unusable;
col segment_name for a15
select segment_name, segment_type, extents, blocks, bytes from dba_segments where segment_name in ('IDX_TESTE01', 'IDX_TESTE02');
A is correct. PTT's exist in memory only.
B is correct. Indexes created this way will not have a segment. Query user_segments.
C is wrong. PTT's will exist for the session if they are created with the ON COMMIT PRESERVE DEFINITION clause.
D is wrong. When an existing index is made unusable then the index segment is dropped.
E is wrong. Truncated tables will release the space that was used by the removed rows EXCEPT that which is specified by the MINEXTENTS storage parameter.
A and B are correct.
E is incorrect:
TRUNCATE TABLE statement:
Deallocates all space used by the removed rows except that specified by the MINEXTENTS storage parameter
I am not sure about E, the wording is important here.
When truncating a table you can use the option REUSE STORAGE, f.e. TRUNCATE TABLE table1 REUSE STORAGE;
REUSE STORAGE specifies that all space currently allocated for the table or cluster remains allocated to it. Which means a table that is being truncated has not always all it's segments removed!
I'd go with answers: A,B
Correct B &C.
Oracle 18c introduced the concept of a private temporary table, a memory-based temporary table that is dropped at the end of the session or transaction depending on the setup.
Private temporary tables are dropped at the end of the session, Global temporary tables have their data definition preserved.
so C is incorrect
Private Temporary Tables (PTTs) are ***NOT*** always dropped at the next COMMIT OR ROLLBACK statement because you can create PTTs with ON COMMIT PRESERVE DEFINITION;
what?
The ON COMMIT DROP DEFINITION clause, the default, indicates the table should be dropped at the end of the transaction, or the end of the session.
see here https://oracle-base.com/articles/18c/private-temporary-tables-18c
Exactly, as per that link also, you may choose "ON COMMIT PRESERVE DEFINITION" also. So You have a choice you may preserve or you may drop.
So "C" is not true, as its not always dropped.
upvoted 5 times
...
...
...
...
Log in to ExamTopics
Sign in:
Community vote distribution
A (35%)
C (25%)
B (20%)
Other
Most Voted
A voting comment increases the vote count for the chosen answer by one.
Upvoting a comment with a selected answer will also increase the vote count towards that answer by one.
So if you see a comment that you already agree with, you can upvote it instead of posting a new comment.
avanand
Highly Voted 3 years, 7 months agoNowOrNever
3 years, 3 months agonautil2
Most Recent 3 months, 4 weeks agopiontk
8 months, 1 week agoFranky_T
1 year, 9 months agoryuah
2 years agoioio
2 years, 9 months agokhalilshahin01
3 years, 4 months agoadoptc94
3 years, 4 months agoEkos
3 years, 1 month agoyou1234
3 years, 5 months agoama
3 years, 5 months agoFayK
3 years, 6 months agoNiciMilo
3 years, 5 months agoama
3 years, 5 months agoavanand
3 years, 4 months ago