Oracle Database Administration I — Question 27

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.)

Answer options

• 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

Correct answer: B, C, E

Explanation

The correct answers are B, C, and E because they directly address the issue of exceeding the quota in the DATA tablespace. Dropping other U1 objects (B) frees up space, increasing U1's quota (C) allows for more space usage, and granting UNLIMITED TABLESPACE (E) removes any restrictions on space use. The other options do not resolve the immediate issue of the space quota being exceeded.