Managing Data and Concurrency

Posted on
Share on Google+Share on LinkedInShare on FacebookShare on RedditTweet about this on TwitterEmail this to someone

These questions are derived from the Self Test Software Practice Test for 1Z0-042 – Oracle Database 10g: Administration I.

 

Objective: Managing Data and Concurrency
SubObjective: Manipulate data through the use of SQL

 

Item Number: 1Z0-042.8.1.3
Single Answer, Multiple Choice

User A attempts to insert data into the user JOHN’s EMP table by issuing the following statement:

 

SQL> INSERT INTO JOHN.EMP VALUES (4563,’ADAM’, 5000, ‘ACCOUNTS’, 125);

 

This statement returns the following error:

 

ORA-01653: unable to allocate extent table JOHN.EMP by 8 in tablespace EXAMPLE

 

What should you do to resolve this problem and ensure that User A can insert data into the EMP table?

 

 

  1. Add more space to User A’s default tablespace.
  2. Grant the INSERT ANY TABLE privilege to the user.
  3. Add more space to the user JOHN’s default tablespace.
  4. Allocate additional quota to User A on Sser A’s default tablespace.
  5. Add more space to the tablespace in which the EMP table resides.
  6. Allocate quota to User A on the tablespace in which the EMP table resides.

 

Answer:
E. Add more space to the tablespace in which the EMP table resides.

 

Tutorial:
You should add more space to the tablespace in which the EMP table resides. The error occurs because Oracle is unable to allocate an extent to the EMP table. The EXAMPLE tablespace, in which the EMP table resides, does not contain enough space. To solve the problem, you must allocate more space to the EXAMPLE tablespace to ensure that extents can be allocated and data inserted into the table. Additional space can be allocated to the tablespace by adding more datafiles, manually allocating extents, or by enabling the autoextensible feature for the datafiles that exist in the tablespace.

 

The option to add more space to User A’s default tablespace is incorrect because the error is due to lack of enough space in the tablespace in which the EMP table resides. Allocating more space to the user’s default tablespace will not solve the problem.

 

The option to grant the INSERT ANY TABLE privilege to the user is incorrect because the user already has the INSERT privilege on the EMP table owned by JOHN. If user A does not have the required privileges, a different error will be generated. The error generated in this scenario is due to space problems.

 

The option to add more space to the user JOHN’s default tablespace is incorrect because it is not mentioned that the EMP table resides in the user JOHN’s default tablespace. Therefore, allocating more space to the user JOHN’s default tablespace might not solve the problem. It would solve the problem only if the EMP table resides in JOHN’s default tablespace.

 

The option to allocate additional quota to User A on User A’s default tablespace is incorrect because the error is due to the lack of enough space in the tablespace in which the EMP table resides. Allocating additional quota to User A on his default tablespace will not solve the problem.

 

The option to allocate quota to User A on the tablespace in which the EMP table resides is incorrect because the error is due to the lack of enough space in the tablespace in which the EMP table resides. Allocating more space to User A will not solve the problem. The problem can be solved by adding more space to the tablespace in which the EMP table resides, EXAMPLE.

 

Reference:
1. Oracle Database Error Messages 10g Release 2 (10.2)
Part Number B14219-01
Chapter: 4 ORA-01500 to ORA-02098
Topic: ORA-01653

http://download-east.oracle.com/docs/cd/B19306_01/server.102/b14219/e1500.htm#sthref32

Share on Google+Share on LinkedInShare on FacebookShare on RedditTweet about this on TwitterEmail this to someone
cmadmin

ABOUT THE AUTHOR

Posted in Archive|

Comment:

Leave a comment

Your email address will not be published. Required fields are marked *

You may use these HTML tags and attributes: <a href="" title=""> <abbr title=""> <acronym title=""> <b> <blockquote cite=""> <cite> <code> <del datetime=""> <em> <i> <q cite=""> <strike> <strong>