CSC454 Oracle11g HW | Complete Solution

 CSC454 Oracle11g  HW                   Name:_______________________

                                                                                        30 points

 1.  Lisa granted the CREATE TABLE privilege WITH ADMIN OPTION to Sharon.  Sharon granted the privilege to Bob.   If Lisa’s privileges are revoked, who else will lose their privileges? 

A. Only Bob

B. Only Sharon

C. Both Bob and Sharon

D. No one else will lose their privileges

2.  Which of the following is not a SQL*Loader file?

A. Bad file

B. Control file

C. Discard file

D. Good file

E. Log file

3.  Which SQL*Loader load keyword deletes data in table before loading?

A. APPEND

B. DELETE

C. SUBSTITUTE

D. REPLACE

4.  You need to insert a large amount of data through the HR_EMP_HIST table.  You are concerned about the amount of time the data load may take.  Although you will only be using one processor to load the data, you would like to make the load as quick as possible.  You are NOT concerned about the available storage space or data integrity.  Which SQL*Loader data loading method should you use?

A. Parallel conventional

B. Parallel direct

C. Serial conventional  

D. Serial direct

5.  Which of the following commands is a key step in multiplexing control files using an SPFILE?

A. ALTER SYSTEM SET CONTROL_FILES= ‘C:/app/product/11.2.0/oradata/INST1/control01.ctl’,

     ‘D:/oracle/oradata/INST1/control02.ctl’, SCOPE=MEMORY

B. ALTER SYSTEM SET CONTROL_FILES= ‘C:/app/product/11.2.0/oradata/INST1/control01.ctl’,

     ‘D:/oracle/oradata/INST1/control02.ctl’, SCOPE=BOTH

C. ALTER SYSTEM SET CONTROL_FILES= ‘C:/app/product/11.2.0/oradata/INST1/control01.ctl’,

     ‘D:/oracle/oradata/INST1/control02.ctl’ 

D. ALTER SYSTEM SET CONTROL_FILES= ‘C:/app/product/11.2.0/oradata/INST1/control01.ctl’,

     ‘D:/oracle/oradata/INST1/control02.ctl’, SCOPE=SPFILE

6.   If you have two redo log groups with four members each, what’s the optimal number of disks you need to provide appropriate protection on the redo log files?

A. Eight

B. Four

C. Two

D. One

E. Operating system dependent

7.  When is the redo log buffer written to the redo log file?  

A. A transaction changes data.

B. The redo log buffer becomes one-third full.

C. A redo log group is archived.

D. The database starts up.

8.   You have two redo log groups.  Each group has three members that are 150 MB in size.  The members reside on the E, F, and G drives.  You issue this command:

ALTER DATABASE ADD LOGFILE MEMBER ‘G:\app\oradata\INST1\redo04.log’ SIZE 200M TO GROUP 1;

The statement will fail because:

A. All redo log groups must have the same number of members.

B. The size cannot be specified in this command.

C. You cannot add a member to a redo log group.

D. The size is incorrect.

9.   What is the correct method for moving a control file (assume you use PFILE)?

A. Issue the ALTER DATABASE RENAME FILE command.

B. Shut down the database; move the control file; modify the CONTROL_FILES parameter in PFILE; and start up the database using PFILE.

C. Copy the control file to a new location; shut down the database; modify the CONTROL_FILES parameter; and start up the database.

D. Shut down the database; delete the control file; start up the database in NOMOUNT mode; issue the CREATE CONTROL FILE command; and start up the database.

10.   Which statement will add a member C:\app\oradata\INST1\redo2A.log  to log file group 2?

A. ALTER DATABASE ADD LOGFILE ‘C:\app\oradata\INST1\redo2A.log’ TO GROUP 2;

B. ALTER DATABASE ADD LOGFILE MEMBER ‘C:\app\oradata\INST1\redo2A.log’ TO GROUP 2;

C. ALTER DATABASE ADD MEMBER ‘C:\app\oradata\INST1\redo2A.log’ TO GROUP 2;

D. ALTER DATABASE ADD LOGFILE ‘C:\app\oradata\INST1\redo2A.log’;

11.  Which statement is false about block size?

A. It can be set at the tablespace level

B. It can be set at the database level

C. Settings at the database level take precedence over settings at the tablespace level

D. Settings at the tablespace level take precedence over settings at the database level

12.  You are using Data Pump to upload rows into a table, and you wish to use the direct path. Which of the following statements is correct?  

A. You must include the “DIRECT” keyword in the Data Pump control file.

B. You must enable insert triggers on the table first.

C. You have no control over this; Data Pump will use the direct path automatically if it can.

D. Direct path is not available in Data Pump.

13.   Which of the following is not a Data Pump file type?

A. Dump file

B. Log file

C. Control file

D. SQL file

14. You have decided to implement automatic undo management. Which of these steps is optional?  

A. Set the parameter UNDO_MANAGEMENT.

B. Create an undo tablespace.

C. Set the parameter UNDO_RETENTION.

D. Restart the instance

15.   You want to add a control file copy. Which of these sequences is correct and sufficient?  

A. Copy the control file, and issue an alter system... command to change the CONTROL_FILES parameter.

B. Mount the database to read the control file, and issue an alter database... command to add a new copy.

C. Issue an alter system... with scope=spfile command to change the CONTROL_FILES parameter, shut down the database, copy the control file, and start up the database.

D. Copy the control file, shut down the database, start up in nomount mode, issue an alter system... command to change the CONTROL_FILES parameter, and open the database.

E. Start up in nomount mode, copy the control file, issue an alter system... command to change the CONTROL_FILES parameter, and open the database.

16.   The following command was issued on the tablespace containing the ZIP_CODE table.

DROP TABLESPACE USER_REF;

What happens?

A. The tablespace and the ZIP_CODE table are dropped.

B. The command fails because the tablespace contains data.

C. The command fails because the ZIP_CODE table was not listed in the command.

D. The tablespace is dropped, and all references to the ZIP_CODE table are removed.

17.   User JOHN updates some rows and asks user DAMIR to log in and check the changes before he commits them.     Which of the following statements is true? 

A. DAMIR can see the changes but cannot alter them because JOHN will have locked the rows.

B. DAMIR will not be able to see the changes.

C. JOHN must commit the changes, so that DAMIR can see them and if necessary roll them back.

D. None of the above

18.   Which line of code will cause the following SQL statement to fail?

1 CREATE USER Sam

2 IDENTIFIED EXTERNALLY BY $amP@ssw0rd

3 DEFAULT TABLESPACE Users

4 TEMPORARY TABLESPACE User_Temp

5 QUOTA 2048M ON APPS_DATA

A. 1

B. 2

C. 3

D. 4

E. 5

F. The statement will succeed

19.  You want to multiplex a redo log group. Will this involve downtime?  

A. Yes, because logfiles can’t be manipulated while the database is open.

B. No, you can always reconfigure logfiles online.

C. No, if your database is in archivelog mode.

D. Yes, because database logging is controlled by static parameters.

20.   If your database is in archivelog mode, how can you force an archive?

A. Issue an alter database switch logfile command.

B. Issue an alter system switch logfile command.

C. Issue an alter system log_archive_start command.

D. There is no command to force an archive; they happen automatically.

21.   Which of the following statements are true?  (Choose all correct answers.)

A. You can create more than one UNDO tablespace

B. UNDO_MANAGEMENT=Yes will let Oracle automatically manage UNDO tablespace

C. You can change the value of UNDO_MANAGEMENT dynamically after the database starts.

D. You can use ALTER SYSTEM SET to switch between undo tablespaces     

22.  You have been asked to provide additional information to your manager on how system privileges can be assigned and behave. Which of the following statements about system privileges are true? (Choose all correct answers.)

A. System privileges can be granted to others if you use the WITH ADMIN OPTION.

B. System privileges can be granted to others if you use the WITH GRANT OPTION.

C. Only the DBA can grant system privileges, since the DBA owns the database.

D. When revoked for a user, system privileges will not be revoked for any user to whom that user granted  

E. System privileges can be granted only by the owner of the database.

23.  Assume Redo log group 1 is the current active one.  According to this diagram, which of the following statements are true?  (Choose all correct answers.)

A. LGWR fills Redo3.log before writing to Redo2.log.

B. A log switch changes from Drive E to Drive F.

C. LGWR fills Redo1.log before writing to Redo3.log.

D. A log switch changes from Group 1 to Group 2.

24.  Which of these initialization parameters in used for Oracle Managed Files (OMF)?  (Choose all correct answers.)

A. DB_CREATE_FILE_DEST

B. DB_CREATE_ONLINE_FILE_DEST

C. DB_CREATE_ONLINE_LOG_DEST

D. DB_CREATE_ONLINE_LOG_DEST_n

E. CONTROL_FILES

25.   You launch a Data Pump job with expdp and then exit from the session.  Which of the following is true?  (Choose all correct answers.)

A. The job will continue running in the background.

B. The job will terminate.

C. You cannot monitor the job once you have exited.

D. You can reattach to the job to monitor it.

E. The job will pause but can be restarted.

26. Under which of these circumstances might Flashback Database be of use?

A. To recover a dropped table

B. To recover a dropped schema

C. To recover a damaged datafile

D. To reverse a bad transaction

E. All of the above

27.  Which of the following files should be multiplexed, for safety? (Choose all correct answers.)

A. System tablespace datafiles

B. Control file

C. Initialization parameter file

D. Archive logfiles

28. Which of the following is correct about Flashback Database? (Choose two correct answers.)

A. You should set the FLASHBACK_BUFFER_SIZE parameter.

B. You must create the flashback log files.

C. You must set the DB_RECOVERY_FILE_DEST parameter.

D. You must issue ALTER SYSTEM FLASHBACK ON.

E. You must issue ALTER DATABASE FLASHBACK ON.

29.  If your Oracle instance is started with the default initialization file (SPFILE), what are the necessary steps needed to rename C:\app\oradata\INST1\Control01.ctl to D:\oracle\oradata\INST1\Control05.ctl?  Please use SQL statement to describe in a proper sequence. (2 points)

  CSC454 11g HW Answer Sheet

                                                                        30 points

   Name:  Mohammed Abdullah A Alshehri                 

Recommendations: 1.  By default, each question has only one answer unless I specified otherwise.

                                  2.  Only turn in the Answer Sheet (this page) by midnight on Sunday, 8/23.

  1. 

  2. 

  3. 

  4. 

  5. 

  6. 

  7. 

  8. 

  9. 

10. 

11. 

12. 

13. 

14. 

15.

16.

17.

18.

19.
20.

21.

22.

23.

24.

25.

26.

27.

28.

29.