General Oracle 9i Administration MCQs

1. Extents are logical collection of contiguous _________________.

2. You check the alert log for your database and discover that there are many lines that say 'Checkpoint Not Complete' Which of the following options help solve this problem?

3. With the help of the Database Configuration Assistant, you can create a template using an existing database structure. Which of the following will be included in this template?

4. Which of the following methods enforce resource limits?

5. Which of the following helps a DBA examine the performance of the database?

6. You issue the following command: STARTUP MOUNT Which of the following events occur when the instance is started and database is mounted?

7. Examine the following TRUNCATE TABLE command: TRUNCATE TABLE departments; Which of the following statements are correct with regard to this command?

8. Which of the following memory areas is not part of SGA?

9. Which of the following becomes available when an Oracle instance is started?

10. You have two undo tablespaces defined for your database. The instance is currently using the undo tablespace named UNDO_TBS1. You issue the following command to switch to UNDO_TBS2, while there are still transactions using UNDO_TBS1: ALTER SYSTEM SET UNDO_TABLESPACE = undo_tbs2 What will be the result?

11. Which of the following types of files is a part of the Oracle database?

12. You need to create an index on the ITEM table, which is 10 GB in size. You want your index to be spread across many tablespaces, decrease contention for index lookup, and increase scalability and manageability. Which of the following types of indexes would be the best for this table?

13. Which of the following tables help build scalable applications and also proves to be useful for large tables that can be queried or manipulated using several processes concurrently?

14. Examine the following SQL statement: CREATE TABLESPACE user_data DATAFILE '/u01/oradata/userdata_0l.dbf' SIZE 100M LOCALLY MANAGED UNIFORM SIZE 1M AUTOMATIC SEGMENT SPACE MANAGEMENT; Which part of the tablespace will have a uniform size of 1 MB?

15. Which of the following helps determine the initial size of a Tablespace?

16. When is SGA created in an Oracle database environment?

17. Your database is in ARCHIVELOG mode. Which of the following must be true before the Log Writer (LGWR) can reuse a filled online redo log file?

18. Examine the following syntax: CREATE INDEX emp_last_name_idx ON emp(last_name) PCTFREE 30 STORAGE(INITIAL 200K NEXT 200K PCTINCREASE 0 MAXEXTENTS 50) TABLESPACE indx; What type of Index does this syntax create?

19. Which of the following constitute the logical components of the Oracle database?

20. While preparing to create a database, you must ensure that there is sufficient disk space for the database files. When you calculate the space requirements, you may need to multiplex some of the files. Which of the following types of files would you plan to multiplex?

21. Which of the following storage structures provides a way to manually store the rows (from more than one table) in the same data block?

22. Which of the following constraint states prevents new data (that violates the constraint) from entering into the table, but allows invalid data to exist in the table?

23. The current password file allows five entries. New DBAs have been hired and five more entries need to be added to the file. How can you increase the allowed number of entries in the password file?

24. Which of the following constitute the advantages that the server parameter file (SPFILE) provides while managing initialization parameters?

25. Where is free space managed for a tablespace created with automatic segment-space management?

26. Which of the following memory areas is used to cache the Data Dictionary Information?

27. A DBA has issued the following SQL statement: SELECT max_blocks FROM dba_ts_quotas WHERE tablespace_name='USER_TBS' AND username='MARRY'; User Marry has unlimited quota on the USER_TBS tablespace. Which of the following values will the query return?

28. You need to drop two columns from a table. Which of the following sequence of SQL statements is used to drop the columns and limit the number of times the rows are updated?

29. Which of the following methods is correct for starting an instance to create a database?

30. The information for a dynamic performance view is gathered from the control file.

31. You have a database with DB_NAME set to STUD and ORACLE_SID set to STUD. These files are in default location for the initialization files: -init.ora -initSTUD.ora -spfile.ora -spfileSTUD.ora The database is started with the command: SQL> startup Which of the following options contain the correct names and sequence of the initialization files that Oracle Server attempts to read?

32. When is SGA created in an Oracle database environment?

33. A Company shifts all its branches to one office building, so the very large EMPLOYEES table no longer requires the BUILDING_LOCATION column. The DBA decides to drop that column using the syntax given below: ALTER TABLE hr.employees DROP COLUMN building_location CASCADE CONSTRAINTS; Dropping this column has turned out to be time consuming and at the same time it requires a large amount of undo space. What should a DBA do to minimize the problem regarding time and undo space consumption?

34. The constraints for which Indexes are created by default are:

35. You started your database with the following command: STARTUP PFILE=initSAMPLE.ora One of the values in the initSAMPLE.ora parameter file is: LOG_ARCHIVE_START=false While your database is open, you issue the following command to start the Archiver process: ALTER SYSTEM ARCHIVE LOG START; You shut down your database to take the back up and restart it, using the initSAMPLE.ora parameter file again. On checking the status of the Archiver, you find that it is disabled. Why is the Archiver disabled?

36. Miller is an Administrator who has FULL DBA privileges. When he attempts to drop the DEFAULT profile (as illustrated below), he receives an error message (shown below): SQL> drop profile SYS.DEFAULT; drop profile SYS.DEFAULT * ERROR at line 1: ORA-00950: invalid DROP option Which of the following options best explains this error?

37. You are creating a new database. You do NOT want users to use the SYSTEM tablespace for sorting operations.

38. You just issued the STARTUP command. Which of the following files is checked to determine the state of the database?

39. What provides for recovery of data that has not been written to the data files prior to a failure?

40. When does the Log Writer (LGWR) process write the redo entries from the redo log buffer to the current online redo log group?

41. Which of the following statements should you use to obtain information about the names, status of the control files?

42. Which of the following statements is correct with regard to an Oracle instance?

43. Which of the following options enables you to increase the size of online redo log groups?

44. Consider the under mentioned syntax, which creates a DEPARTMENT table: CREATE TABLE department (DeptnoNUMBER(4), DnameVARCNAR2(30), mgr NUMBER(6), LocNUMBER(4)) STORAGE(INITIAL 200K NEXT 200K PCTINCREASE 50 MINEXTENTS 1 MAXEXTENTS 5) TABLESPACE userdata; What is the size defined for the fifth extent?

45. Which of the following background processes performs a checkpoint in the database by writing modified blocks from the database buffer cache in SGA to the data files?

46. A new user, Smith, has just joined the organization. You need to create a User Account for him in the database. The points given below are to be kept in mind: 1. Create a user who is authenticated externally. 2. Make sure that the user has CONNECT and RESOURCE privileges. 3. Make sure the user does NOT have DROP TABLE and CREATE USER privileges. 4. Set a quota of 100 MB on the default tablespace and 500 K on the temporary tablespace. 5. Assign the user to the DATA_TS default tablespace Which of the following statements would you use to create the User Account for Smith?

47. The database needs to be shut down for hardware maintenance. All the user sessions except one have either voluntarily logged off or have been forcibly killed. The remaining one user session is running a business critical Data Manipulation Language (DML) statement, and it must complete prior to shutting down the database. Which of the following shutdown statements prevents the new user connections, logs off the remaining user, and shuts down the database after the DML statement completes?

48. Examine the following statements: 1)MOUNT mounts the database for certain DBA activities, but does not provide user access to the database. 2)The NOMOUNT command creates only the Data Buffer, but does not provide access to the database. 3)The OPEN command enables users to access the database. 4)The STARTUP command starts an instance. Which of these statements are correct?

49. You issue the following SQL statement to re-create your database and reuse all the existing database files: CREATE DATABASE Sales DATAFILE '/u01/oradata/Sales/system0l.dbf' SIZE 100M REUSE LOGFILE GROUP 1 ('/u01/oradata/Sales/logla.rdo', '/u02/oradata/Sales/loglb.rdo') SIZE 50K REUSE, GROUP 2 ('/u01/oradata/Sales/log2a.rdo', '/u02/oradata/Sales/log2b.rdo') SIZE 50K REUSE MAXLOGFILES 5 MAXLOGHISTORY 100 MAXDATAFILES 10; The CREATE DATABASE statement fails. Why?

50. Your company hires a DBA named Allen. He will be starting the database remotely, as he is going to work from home. You created a password file for your database and set REMOTE_LOGIN_PASSWORDFILE = EXCLUSIVE in the parameter file. Which of the following commands will help you add Allen to the password file to allow him to have remote DBA access?