Exam 1z0-082 All Questions

View all questions & answers for the 1z0-082 exam

Exam 1z0-082 topic 1 question 42 discussion

Actual exam question from Oracle's 1z0-082

Question #: 42
Topic #: 1

The ORCL database has RESUMABLE__TIMEOUT = 7200 and DEFERRED_SEGMENT_CREATION = FALSE
User U1 has a 1 MB quota in tablespace DATA.
U1 executes this command:

SQL> CREATE TABLE t1 AS -
(SELECT object_name, sharing, created
FROM dba_objects);
U1 complains that the command is taking too long to execute.
In the alert log, the database administrator (DBA) finds this:
2017-03-06T12:15:17.183438+05:30
statement in resumable session 'User U1(136), Session 1, Instance 1' was suspended due to ORA-01536: space quota exceeded for tablespace 'DATA'
Which are three actions any one of which the DBA could take to resume the session? (Choose three.)

A. Add a data file to DATA
B. Drop other U1 objects in DATA
C. Increase U1's quota sufficiently in DATA
D. Set DEFERRED_SEGMENT_CREATION to TRUE
E. Grant UNLIMITED TABLESPACE to U1
F. Set AUTOEXTEND ON for data files in DATA

Show Suggested Answer

Suggested Answer: BCE 🗳️

by danito at June 10, 2020, 8:44 p.m.

Comments

Submit Cancel

elcaso34

Highly Voted 4 years ago

I think BCE Set DEFERRED_SEGMENT_CREATION to TRUE, when creating the table you are already inserting rows as select, therefore the segment has already been created the D is not. Configure Set AUTOEXTEND ON for data files in DATA, if you have 1M quota it will not work.

upvoted 18 times

ama

4 years ago

DEFERRED_SEGMENT_CREATION is Default TRUE. it doesnt make Change at all. so this Option is wrong and wont solve the problem

upvoted 1 times

SimoneF

3 years, 7 months ago

He didn't said that DEFERRED_SEGMENT_CREATION would help. And it wouldn't but not for the reason you're saying: in the question is written that the parameter is set at FALSE in this case. The reason for which it wouldn't help is that, with a CREATE AS SELECT, the first row is inserted immediately, so the segment is also created. That could have helped in the case of a normal create table.

upvoted 3 times

...

avanand

Highly Voted 3 years, 10 months ago

BCE, A. Add a data file to DATA -- Won't help as problem is quota to user not the space B. Drop other U1 objects in DATA -- Will help as it will reduce the used space from quota for that user C. Increase U1's quota sufficiently in DATA -- Will help. D. Set DEFERRED_SEGMENT_CREATION to TRUE -- Won't help, segments has to be created as its CTAS E. Grant UNLIMITED TABLESPACE to U1 -- Will help. F. Set AUTOEXTEND ON for data files in DATA -- Won't help as problem is quota to user not the space

upvoted 11 times

...

nautil2

Most Recent 9 months ago

Selected Answer: BCE

A - false; the problem (user quota exceeded) persists when any number of datafiles is added because user quota is not related to free space in the tablespace DATA B - true; dropping user objects decreases occupied space C - true; increasing a user quota allows session to continue in segment allocation for table t1 D - false; the table t1 is created with CTAS, therefore is filled with data at the time of creation, table segments are allocated at the time of creating the table and segment allocation deferment is not used E - true; when UNLIMITED TABLESPACE is granted to a user, any quotas are ignored, therefore the restriction "space quota exceeded" ceases to apply F - false; user quota is not related to free space in tablespace DATA. Even if the datafiles were huge and almost empty, the user quota would still prevent the data of table t1 from being written.

upvoted 1 times

...

auwia

1 year ago

Selected Answer: BCE

Provided answer are corrects.

upvoted 1 times

...

j_tw

1 year, 3 months ago

Selected Answer: BCE

BCE No matter how big the DATA datafile will be, U1 Only have 1MB quota at DATA datafile. So, adding a new datafile or setting autoextend on is not an important thing to solve this problem. We should do something regards to U1's quota.

upvoted 1 times

...

raferen10

1 year, 7 months ago

Selected Answer: BCE

B C E correct

upvoted 1 times

...

Xhost

2 years ago

Selected Answer: BCF

I would say B, C, F!

upvoted 1 times

...

Franky_T

2 years, 2 months ago

Selected Answer: BCE

The DEFERRED_SEGMENT_CREATION = FALSE was added to the question to throw you off. DEFERRED_SEGMENT_CREATION set to TRUE will only delay the allocation of the first extent (thus creating the segment) until the first row is inserted into the table. The issue here is simply space required for table creation vs quota allocated to the user. Answers can only be B, C and E.

upvoted 1 times

...

Aramazd

2 years, 3 months ago

I guess B C E too.

upvoted 1 times

...

ryuah

2 years, 6 months ago

B,C,E is correct

upvoted 1 times

...

Borislone

2 years, 12 months ago

CDE is correct.

upvoted 1 times

...

Ekos

3 years, 8 months ago

i think the answer is BCE

upvoted 1 times

...

Chansi

3 years, 11 months ago

B CE . D may help you get the table created but may fail while inserting. Also the question says " Any of the options should resolve the issue." B is not ideal but given the options that we have it is one of the solutions that work.

upvoted 4 times

...

ama

4 years ago

ORA-01536 space quota exceeded for tablespace ‘string’ Cause: The space quota for the segment owner in the tablespace has been exhausted and the operation attempted the creation of a new segment extent in the tablespace. Action: Either drop unnecessary objects in the tablespace to reclaim space or have a privileged user increase the quota on this tablespace for the segment owner

upvoted 3 times

SimoneF

3 years, 7 months ago

Yes, I agree on BCE

upvoted 1 times

...

adoptc94

3 years, 10 months ago

I'd also go with BCE as elcaso34 mentioned, your're not only creating the table but you're also inserting rows at the same time, so setting deferred_segment_creation to true won't help as rows are inserted therefore segments will be allocated which use up quota While B isn't really something I'd recommend it still would resume the session if enough quota is available again.

upvoted 2 times

...

Sharif1

3 years, 11 months ago

so BCE seems to be the right answers.

upvoted 8 times

...

ama

4 years ago

i think CDE … dropping existing objects is never a choice to solve a Problem! so B is false

upvoted 2 times

SimoneF

3 years, 7 months ago

It's probably not something you would do without consulting with the owner of the objects, or at least without knowing that it's disposable data. But it is indeed a course of action that would resume the session. Hence, it's a correct answer.

upvoted 1 times

...

danito

4 years, 1 month ago

I think BCD

upvoted 1 times

...