Exam Prep Questions


Use the following command and its output information to answer questions 1 and 2:

 SELECT TABLESPACE_NAME, CONTENTS, FROM DBA_TABLESPACES 

TABLESPACE_NAME

CONTENTS

SYSTEM

PERMANENT

MYDATA

PERMANENT

MYINDEXES

PERMANENT

UNDOTBS1

UNDO

TEMP

TEMPORARY

USERS

PERMANENT


1:

Your database has the TEMP tablespace set as the database default temporary tablespace. If you run the following command, where will the user's temporary segments be built?

 CREATE USER amandya IDENTIFIED BY squirrel; 

  • A. SYSTEM

  • B. MYDATA

  • C. TEMP

  • D. UNDOTBS1

A1:

Answer C is correct. Based on the data returned by the query, the user's temporary segments will be located in the default temporary tablespace. Answer A is incorrect; if there were no temporary tablespace in the database, the user's temporary segments would be created in the SYSTEM tablespace, but because there is a temporary tablespace, that is where they are created. Answer B is incorrect; temporary segments are not usually created in a permanent tablespace. Answer D is incorrect; temporary segments are never located in undo tablespaces.

2:

Where will the newly created amandya user's data be stored?


  • A. UNDOTBS1

  • B. MYDATA

  • C. USERS

  • D. There is no way to tell for sure.

A2:

Answer D is correct; there is no way, based on the given information, to determine where the user's data will be created by default. One could infer that it might likely be the SYSTEM tablespace, but there is no way to tell for sure based just on the information provided. Answer A is incorrect; no user data is stored in the undo tablespace. Answers B and C are potential places, but without further information there is no way to be sure.

3:

You are working for an extremely security conscious company, and no one is permitted to know anyone else's password, not even the administrator. Adam has just joined your organization and needs to be given access to the database. Which of the following clauses will allow you to create the userid for Adam in the database and still provide the level of security necessary?

  • A. CREATE USER

  • B. IDENTIFIED BY nutsy

  • C. QUOTA

  • D. PASSWORD EXPIRE

  • E. ACCOUNT LOCK

A3:

Answer D is correct; PASSWORD EXPIRE requires that the new user change his password as soon as he logs in the first time with SQL*Plus. Answer A is incorrect; there are no security or password ramifications tied directly to the CREATE USER clause. Answer B is incorrect; the IDENTIFIED BY clause sets the initial password that you now know and signifies that the user is database authenticated. Answer C is incorrect; the QUOTA clause sets the amount of space allowed to be used by the new userid. Answer E is incorrect; if the account is in the lock status, the user will not be able to log in to the database regardless of what his password is.

4:

User Larry has decided to take on a new job opportunity in a new organization. His manager has requested that you remove his userid from the database. Upon analysis of the database, you determine that user Larry has 75 tables in the MYDATA tablespace and 82 indexes in the MYINDEXES tablespace. Along with his data in these tablespaces, there exists several hundred megabytes of data for other users. What is the most expedient way to quickly remove Larry from your database without severely impacting other users?

  • A. DROP USER larry;

  • B. Move everyone else's tables and indexes into new tablespaces, drop the MYDATA and the MYINDEXES tablespaces, and then drop the Larry user.

  • C. Individually disable all constraints on Larry's tables and then drop each one individually. Then drop all the indexes individually and drop any other objects that Larry owned. Then drop the user Larry.

  • D. DROP USER larry CASCADE;

A4:

Answer D is correct; DROP USER...CASCADE is the most expedient way to drop the user because all the user's objects have to be dropped before the user can be dropped. Answer A is incorrect because we are told that the user has a significant number of tables, and there is no way to drop the user without dropping all the user's objects. Answer B could allow you to drop the user, but would impact all the users in the tablespaces and would take a considerable amount of time. Answer C would likewise work but again would take a considerable amount of time and research effort.

5:

User Lynn asked her boss, user Lonny, to change her password while she was out of town on vacation, but she did not give him her old password. What command would user Lonny use when logged in to the database as himself to change her password:

  • A. ALTER USER lynn IDENTIFIED BY newpassword

  • B. ALTER USER lynn IDENTIFIED EXTERNALLY

  • C. CREATE USER lynn identified by newpassword

  • D. None of the above

A5:

Answer D is correct; users can only alter the userid that they are logged in with or must have been deliberately granted ALTER ANY USER privilege or DBA role (none of these were indicated). Lonny didn't know Lynn's password, so he could not change her password. Answer A is incorrect because Lonny has no way to log in as Lynn and therefore cannot change her password. Answer B is incorrect; if Lynn has a password, it isn't likely that she is using OS authentication. Answer C is incorrect; Lonny would neither want nor would he be able to create a user called lynn because she already exists in the database.

6:

What is the name of the initialization parameter that you can set to allow users to use remote operating system authentication?

  • A. OS_AUTHENT_PREFIX

  • B. REMOTE_OS_USER

  • C. REMOTE_OS_AUTHENT

  • D. REMOTE_OS_AUTHENTICATION

A6:

Answer C is correct. You can set REMOTE_OS_AUTHENT to TRUE to enable users to use remote operating system authentication. Answer A is incorrect; it sets a default prefix for all OS authenticated userids. Answer B is incorrect; there is no such parameter. Answer D is incorrect for the same reason; however, don't be fooled because it looks more accurate than C.

7:

If the DBA is using OS authentication, which of the following clauses will cause an error?

  • A. CREATE USER ops$freeman

  • B. IDENTIFIED EXTERNALLY

  • C. DEFAULT TABLESPACE mydata

  • D. There are no errors.

A7:

Answer D is correct; there are no errors in this statement.

8:

Which of the following aspects of a user account can an individual user change?

  • A. DEFAULT TABLESPACE

  • B. TEMPORARY TABLESPACE

  • C. IDENTIFIED BY

  • D. QUOTA

A8:

Answer C is correct; the only aspect of a user's account that he has any direct control over is the password, which is changed with the IDENTIFIED BY clause.

9:

User joy is created in the database. She is database authenticated, but no defaults have been set. When she attempts to create her first table, where will it be created?

  • A. Temp tablespace

  • B. SYSTEM tablespace

  • C. Her default tablespace

  • D. No table will be created.

A9:

Answer D is correct; no table will be created because no default quota was specified on any tablespace. Answer A is incorrect; permanent objects can't be created in a temporary tablespace. Answer B is incorrect; the SYSTEM tablespace will become her default tablespace, but she as yet has no quota on anything. Answer C is incorrect; although SYSTEM is her default tablespace, she has yet to be allocated a quota.

10:

User crystal has had the following ALTER USER statement run against her userid:

 ALTER USER crystal QUOTA MYDATA 0K QUOTA MYDATA2 100M; 

As soon as the command has finished, crystal attempts to add data to her address table that already existed in MYDATA and was at an extent barrier. What is the effect of the insert?

  • A. Because the table already existed, the insert was successful.

  • B. The table was automatically moved when the ALTER USER command was run, so the insert was successful.

  • C. The table still exists in the MYDATA tablespace but cannot be added to because the quota is now 0.

  • D. The table has been dropped as a side effect of the ALTER USER command, so the insert was unsuccessful.

A10:

Answer C is correct; the table exists and can be selected from, but no inserts can occur because the quota is 0. Answer A is incorrect; the quota was reduced to 0, therefore the insert failed. Answer B is incorrect; the ALTER USER statement will not move a table. Answer D is incorrect; the ALTER USER statement will not delete a table.



    Oracle 9i Fundamentals I Exam Cram 2
    Oracle 9i Fundamentals I Exam Cram 2
    ISBN: 0789732653
    EAN: 2147483647
    Year: 2004
    Pages: 244
    Authors: April Wells

    flylib.com © 2008-2017.
    If you may any questions please contact us: flylib@qtcs.net