1Z0-052 · Question #189
User SCOTT wants to perform a bulk insert operation in the EMP_DEP table. SCOTT receives the following error after the INSERT statement is issued and few rows are inserted: INSERT INTO EMP_DEP…
The correct answer is B. Add data files to the USERS tablespace. E. Increase the size of the data file associated with the USERS tablespace. ORA-01653 indicates the USERS tablespace lacks physical space to extend the EMP_DEP segment. Adding or resizing data files in the tablespace directly resolves the storage shortage.
Question
User SCOTT wants to perform a bulk insert operation in the EMP_DEP table. SCOTT receives the following error after the INSERT statement is issued and few rows are inserted:
INSERT INTO EMP_DEP (emp_id,name,salary,dep_name,mgr_id) * ERROR at line 1:
ORA-01653: unable to extend table SCOTT.EMP_DEP by 128 in tablespace USERS Identify two actions either of which will help you resolve this problem. (Choose two.)
Options
- AGrant the RESOURCE role to SCOTT.
- BAdd data files to the USERS tablespace.
- CGrant the CREATE ANY TABLE privilege to SCOTT.
- DIncrease the space for SCOTT on the USERS tablespace.
- EIncrease the size of the data file associated with the USERS tablespace.
How the community answered
(63 responses)- A3% (2)
- B73% (46)
- C8% (5)
- D16% (10)
Why each option
ORA-01653 indicates the USERS tablespace lacks physical space to extend the EMP_DEP segment. Adding or resizing data files in the tablespace directly resolves the storage shortage.
The RESOURCE role grants object creation privileges such as CREATE TABLE but does not allocate or expand physical tablespace storage.
Adding a new data file to the USERS tablespace increases the total physical space available, giving the segment room to extend beyond the current limit.
CREATE ANY TABLE is a DDL privilege that allows table creation in any schema, which has no effect on the storage capacity of a tablespace.
Increasing a user's tablespace quota controls how much of the existing space a user may consume, but does not add new space to the tablespace itself; ORA-01653 is a tablespace-level space error, not a quota error (which would produce ORA-01536).
Increasing the size of an existing data file in the USERS tablespace also expands available storage, allowing Oracle to allocate the additional 128 blocks required by the INSERT operation.
Concept tested: Oracle tablespace space management and ORA-01653
Source: https://docs.oracle.com/en/database/oracle/oracle-database/19/admin/managing-tablespaces.html
Topics
Community Discussion
No community discussion yet for this question.