I am presenting here the 100 Basics oracle dba interview questions. I hope you like it
Question 1 You issue the following statement from SQL*Plus: startup mount. Where does Oracle obtain values for starting the instance?
A. From the default settings for the tablespace
B. From your init.ora file or spfile
C. From Oracle default values in OS
D. From the default settings in your redo log file
Question 2. You are analyzing the components of the redo log mechanisms in your Oracle database. Which of the following purposes does the CKPT process serve?
A. Writes dirty buffers to disk
B. Writes current redo log number to datafile headers
C. Writes redo log information to disk
D. Reads information into memory for users
Question 3. You are attempting to clear an unarchived redo log file. In order to manually enact a log switch, which of the following statements is appropriate?
A. alter user
B. alter system
C. alter database
D. alter redo log
Question 4. Which of the following clauses are available in alter user statements but not in create user statements?
B. identified by
C. default role
D. temporary tablespace
Question 5. Which of the following choices lists an alter user option that can be executed by the user herself or himself?
A. temporary tablespace
B. default tablespace
D. identified by
Question 6. When choosing a character set and national character set, which of the following factors should not enter into consideration?
A. Your national character set and character set should be closely related where possible.
B. Your character set must either be US7ASCII or a superset of it.
C. You can use variable-length multibyte character sets as both character sets on your database.
D. Oracle supports only English-like languages as its character set for entering SQL and PL/SQL commands
Question 7. Which two of the following items are required for killing a user session?
B. Serial number
Solution (B, D)
Question 8. We are designing the physical database layout on your host machine. What is the relationship between tablespaces and datafiles in the Oracle database?
A. Many tablespaces can share one datafile.
B. One tablespace has only one datafile.
C. One tablespace can have many datafiles.
D. One datafile can contain many tablespaces.
Question 9. Which of the following is the initsid.ora parameter that indicates the size of each buffer in the buffer cache?
Question 10.You are about to drop a tablespace. Which of the following statements can be used for dropping tablespaces that contain parent tables in foreign key relationships?
A. alter tablespace offline immediate
B. drop tablespace cascade constraints
C. alter database datafile offline drop
D. drop tablespace including contents
Question 11.You want to find out the names and locations of all control files in your database. Which two of the following performance views would you query?
Solution (B, C)
Questions 12.You are implementing control file multiplexing. Which of the following choices identifies how Oracle knows the locations of the control files it is supposed to maintain?
A. Values specified for BACKGROUND_DUMP_DEST.
B. Values specified for CONTROL_FILES.
C. Values specified in V$DATABASE.
D. Oracle knows automatically where to look.
Questions 13.You are identifying dictionary objects in the Oracle database. Which of the following is a view in the data dictionary?
Questions 14.You are attempting to locate your control files on an Oracle database called TEST. In which of the following files might you look for this information?
Question 15. User JOHN has create any table privilege with administrative abilities on that privilege. Which of the following statements show how to revoke the administrative component from JOHN without limiting her overall ability to create tables?
A. revoke admin option from create any table; then grant create any table to JOHN;
B. revoke admin option from create any table;
C. revoke create any table from JOHN; then grant create any table to JOHN;
D. None of these
Question 16 To allocate another role to a user, which command is most appropriate?
B. alter user
C. alter database
D. alter system
Question 17 Which of the following operations do not require Oracle to store information in an undo segment as part of the transaction?
Question 18 You are running Oracle in America in support of a financial analysis project for the government of Egypt. In order to produce reports that display money amounts as Egyptian pounds, rather than dollars, which of the following initialization parameters would be useful?
Question 19 You are trying to find the ALERT file on a host machine for a database (11g and below) you have never administered before. Which of the following initialization parameters is used to identify the location of the ALERT file?
Question 20 You issue the alter tablespace read only command against an Oracle database. Which of the following choices best describes what happens next?
A. Oracle returns an error.
B. Oracle puts the tablespace into read-only mode after the last user logs off.
C. Oracle puts the tablespace into read-only mode after the last prior transaction against that tablespace commits while preventing subsequent DML until the change happens.
D. Oracle immediately puts the tablespace into read-only mode.
Question 21 You are using locally managed tablespaces in Oracle. Which of the following choices best describes the way Oracle implements this feature in the database?
A . Using the data dictionary on the local database
B. Using a bitmap in the space header segment
C. Using a flat file in the local directory storing the datafile
D. Using the data dictionary in a distributed database
Question 22. Your Oracle EMP table contains many unique values in the FIRSTNAME column. You want to index that column to take advantage of this fact in query access. Which of the following indexes might you use?
A. reverse key index
B. Function-based index
C. Bitmap Index
D. Simple B-tree index
Questions 23. Session information when dedicated servers are being used is stored where in the Oracle database?
A. Large area
B. In the redo log buffer
C. In the PGA
D. In the buffer cache keep area
E. In the shared pool area
Question 24 Which of the following clauses in a create user statement restricts the number of tables a user can add to a tablespace?
A. default tablespace
B. quota on
D. identified by
Question 25 You are considering using the MTS architecture on the Oracle database. Session information when shared servers are being used is stored where in the Oracle database?
A. In the redo log buffer
B. In the buffer cache keep area
C. In the shared pool
D. Large pool area
E. In the PGA
Question 26. You are working in an organization which is situated in many countries. You maintain databases in multiple countries in multiple languages. To determine the date conventions for a database in a particular country, you might use which of the following database views?
Question 27. You need to remove a column from the database. Which of the following choices best identifies how to do so if your objective is to quickly execute the task without necessarily freeing up space in your tablespace?
A. truncate table
B. alter table drop column
C. alter table set unused column
D. alter table modify column
Question 28. You have assigned 10 tables to the keep pool. How should you determine the appropriate size for your keep pool?
A. Based on the number of blocks in the table plus blocks in associated indexes
B. Based on the size of your shared pool
C. Based on the number of blocks in associated indexes only
D. No Answer is Correct
E. Based on the number of blocks in the table only
30 Frequently asked DBA questions
Question 29. The best choice for decreasing size requirements for tables that need only be accessed via the primary key is which of the following?
A. Drop the primay key
B. Create an index-organized table to store the data..
C. Create more indexes on the table.
D. Increase the PCTFREE value set for table blocks.
Question 30. . Which of the following choices identifies a constraint on performing alter tablespace resize operaton if the intended size of the tablespace is larger than the tablespace’ s current size?
A. Presence of objects in the datafile resized
B. Whether AUTOEXTEND is in use on datafiles for the tablespace
C. Availability of space in memory for temporary storage of blocks in tablespace
D. Availability of space on disk where datafiles are added
Question 31. You attempt to issue the alter tablespace rename datafile command in the Oracle database. Which of the following choices indicates a step that must take place after this command is issued?
A. Bring the tablespace offline
B. Physically move the datafile to the new location
C. Bring the tablespace online
D. Execute IMPORT to load the new metadata
Question 32. If you wished to make it so that every user in Oracle could have only one connection to the database at a time, which of the following choices identifies how you would do it?
A. Set SESSIONS_PER_USER in the DEFAULT profile to 1
B. Set LICENSE_MAX_SESSIONS = 1 in init.ora
C. Set IDLE_TIME in the DEFAULT profile to 1
D. Set SESSIONS_PER_USER = 2 in init.ora
Question 33. Records from the data dictionary information are stored in which of the following database memory areas?
A. Buffer cache
B. Session UGA
C. Library cache
D. Row cache
Solution ( d)
Question 34. The location of indexes in a database and the size of those indexes is information that can be found in which of the following dictionary views?
Question 35. You alter a tablespace’ s default storage settings in the Oracle database to increase the size of initial extents. Which of the following choices identifies when the change will take effect for tables that already exist in that tablespace?
A. The change takes effect when data is added to the table.
B. The change takes effect immediately.
C. The change takes effect when data is removed from the table.
D. The change will not take effect for existing tables.
Question 36 Which of the following choices identify a method that you could use when creating an index on the LASTNAME column that would improve performance without sacrificing recoverability?
A. Use the nosort keyword when creating the index after loading the table.
B . Use the nologging keyword when creating the index after loading the table.
C . Create the index before loading the table with low storage clause settings.
D . Create the index before loading the table with high storage clause settings.
Question 37 You are performing the steps that will create your Oracle data dictionary. The objects in the Oracle data dictionary are part of which of the following schemas?
B . SYSTEM
C . PUBLIC
D . SYS
Question 38 You are analyzing how Oracle processes user statements. SQL and PL/SQL parse information is stored in which of the following database memory areas?
A . Row cache
B Library cache
C . Dictionary cache
D . Buffer cache
E . PGA
You plan to store large blocks of text in your table. You want the column to be large enough to store about ten sentences. The column must also be fixed width. Which of the following datatypes are most appropriate?
A . VARCHAR2
B . LONG
C . CLOB
You want to perform some maintainance and You issue the shutdown command at 9 p.m. on a Saturday. One hours later, the database is still in the process of shutting down. Which of the following options did you most likely use in order to shut down the database?
A . shutdown immediate
B . shutdown transactional
C. shutdown normal
D . shutdown abort
The user is selecting data from the Oracle database. Which of the following processes handles obtaining data from Oracle for that user?
A . The user process obtains information on its own.
B . The listener process obtains information for the user.
C. The DBW0 process obtains information for the user
D . The server process obtains information for the user.
After starting SQL*Plus in line mode, you issue the shutdown immediate command. What most likely will happen next?
A . The database does not shut down because users have to disconnect.
B . The database shuts down.
C . Nothing happens. SQL*Plus is not a line-mode tool.
D. SQL*Plus returns an error saying you need to connect to Oracle first.
You issue the following statement in Oracle:
CREATE UNIQUE BITMAP INDEX empl_lastname_indx_01
ON employee (lastname ASC);
What is wrong with this statement for dictionary-managed tablespaces?
A. Bitmap indexes cannot be unique.
B . You cannot use the nosort keyword in creating an index.
C . The tablespace clause must be omitted.
D . You should omit the asc keyword.
When we issue the commit statement in the session, which of the following things will not occur?
A . Acquired row or table locks are released.
B. Cached data is saved immediately to disk.
C . Redo entry generated for committed transaction.
D . Acquired undo segment locks are released.
You are processing an update statement. At what point in SQL statement processing is the data change actually made to block buffers?
A . When data is fetched from the cursor
B . When the statement is parsed
C . When the cursor is opened
D. When the statement is executed
You are defining storage for various segment types in the Oracle database. Which of the following is not a valid type of segment in Oracle?
A . Temporary segment
B . Undo segment
C. Sequence segment
D . Data segment
You need to identify the remaining free space in a tablespace. From which of the following views would you get this information most easily?
A . DBA_TABLESPACES
B . V$TABLESPACE
D . DBA_FREE_SPACE
You are adding redo logs to the Oracle database. Creating a new redo log adds information to which of the following Oracle resources?
A . Shared pool
B . SGA
C. Control file
D . Library cache
As the DBA, you are attempting to limit user’s misuse of Oracle’s ability to use host machine resources. Which of the following features of the Oracle database is useful for this purpose?
B . Roles
C . Parameter files
D . Undo segments
During regular database operation, which background process will take smaller blocks of free space in a dictionary-managed tablespace and move things around to make bigger pieces of free space?
A . DBW0
B . ARCH
D . SMON
E . PMON
Information in the buffer cache is saved back to disk in each of the following situations except one. In which situation does this not occur?
A . When a time-out occurs
B . When a log switch occurs
C. When the shared pool is flushed
D . When a checkpoint occurs
The DBA issues the following statement:
CREATE USER APPLDBA
IDENTIFIED BY APPLDBA;
What profile will user APPLDBA have?
A . DBA
C . CONNECT
D . None
A disk crashes that contains the only copies of all four of your online redo log files. How would you alter your Oracle database to prevent this from causing much damage in the future?
A . Set LOG_BLOCK_CHECKSUM in the init.ora file.
B . Use the alter database add LOGFILE GROUP 5;.
C. Create multiple members for each of your four groups and place them on different disks.
D . Change the CONTROL_FILES parameter in the init.ora file.
After creating a new user for your Oracle database, a user still complains he or she cannot log in because of insufficient privileges errors. Which of the following actions should you take?
A . Reset the user’s password
B . Grant create table privileges to the user
C. Grant the CONNECT role to the user
D . Unlock the user’s account
On an Oracle server installation, which of the following reorganizations of your indexes would be appropriate in order to improve performance of queries on tables containing all words in the dictionary starting with the letter ‘S’?
A . Convert your reverse-key index to a B-tree index.
B . Convert your bitmap index to a B-tree index.
C . Convert your B-tree index to a bitmap index.
D. Convert your B-tree index to a reverse-key index.
Which one of the following statements is incorrect with respect to RMAN’s CROSSCHECK command?
A . It identifies the backup sets or image copies that are either corrupted or have been accidentally deleted.
B. RMAN automatically deletes information pertaining to the image copy from the repository if it is physically not available.
C . It updates the non-availability of the files by updating the information in the recovery catalog with the appropriate status.
D . RMAN crosschecks the availability of the backup sets by verifying the information stored in its repository with the backup sets that are physically available.
Question 57 Which one of the following RMAN views must you query to view the code of an existing stored script in the recovery catalog?
B . RC_STORED_SCRIPT
C . RC_STORED_SCRIPT_CODE
D . RC_SOURCE_SCRIPT
Question 58. Which one of the following statements is incorrect about direct-path insert operations?
A . Direct-path insert can be performed when using the INSERT INTO… SELECT statement.
B . Direct-path insert enables you to copy data from one table to another table within the same database.
C . It speeds up the insert operation, bypassing the buffer cache.
D . Direct-path insert can be implemented either in the serial mode or the parallel DML mode.
E. To load data using the serial mode, you must activate the direct-path insert by specifying the SERIAL hint.
Question 59. Which Oracle background process is responsible for providing information about the instance name associated with the service handlers and services to the listener?
A . LCK
B . PMON
D . CKPT
Question 60. A User accidentally drops a very critical table. What kind of failure just occurred?
A . Process failure
B . Instance failure
C. Human error
D . Statement failure
Question 61.Which one of the following statements is true while taking tablespace backups using RMAN?
A . RMAN generates extra redo information during online backups when compared with user-managed online backups.
B . You need to keep the tablespace in backup mode explicitly.
C . RMAN does not require the tablespace to be in backup mode
D. You need to make the tablespace offline.
Question 62. A DBA manages a database that is operating in NOARCHIVELOG mode. Which one of the following options should he include in his backup strategy?
A . Cannot perform any backups in this mode
B . Whole database backup while the database is open
C . Can only perform logical backups
D. Whole database backup after the database is gracefully closed
Question 63 A orginization is taking online backups of the database, with the database operating in ARCHIVELOG mode. Due to a media failure they lost the SYSTEM tablespace data files. What must they do to recover the data files?
A.Take the tablespace offline, restore the lost file, and issue the RECOVER TABLESPACE command
B . Shut down the database, restore all the Oracle files, and open the database with the RESETLOGS option
C . Take the tablespace offline; restore the lost file, and issue the RECOVER DATAFILE command
D . Shut down the database, restore the lost file, mount the database, and recover the database
Question 64. Which one of the following RMAN commands is equivalent to the SQL statement ALTER DATABASE RENAME FILE ?
B . RENAME
C . CHANGE
D . SWITCH
Question 65. Prior to performing an incomplete database recovery, what is the most important task that you are supposed to perform as a DBA?
A . Open the database in NOMOUNT mode.
B. Perform a full backup.
C . Restore the control files.
D . Restore the archived redo log files.
Question 66. Which one of the following views must you query from the recovery catalog database to obtain information about the backup sets that were created using RMAN?
A . V$BACKUP_SET
B . V$BACKUPSET
C . RC_DATABASE
Question 67 .In which one of the following loading methods does SQL*Loader compete with the other processes to acquire buffer resources?
A. Conventional path load
B . Distributed path load
C . Direct path load
D . None of the above
Question 68. Which one of the following statements about the tnsping utility is correct?
A . It establishes a session with the database and checks the connectivity of the service.
B. It does not require the username and password to check the connectivity of the service.
C . It helps you to determine whether or not the Oracle database is running.
D . It requires the username and password to check the connectivity of the service.
Question 69. Which Oracle background process frees SGA resources that were allocated to a user process that failed?
B . LGWR
C . SMON
D . PMON
E . ARCn
Question 70 .Assume that you need to restore and recover a database that’s operating in ARCHIVELOG mode. The archive log files available for recovery start from log sequence 21156 to 21190. If the archived log files with sequence number 21187, 21184, and 21180 are corrupted, then until which log sequence can you recover the database?
B . 21187
C . 21184
D . 21190
Question 71. Which one of the following views would you query to obtain information about data files that need recovery?
A . V$DATAFILE
C . V$DATAFILE_COPY
D . V$DATAFILE_RECOVERY
Question 72. Which one of the following commands must be issued to remove a stored script from the recovery catalog?
A . REMOVE SCRIPT
B . UNCATALOG SCRIPT
C. DELETE SCRIPT
D . ERASE SCRIPT
Question 73. Which one of the following files contains records that are rejected by SQL*Loader due to invalid input format?
A . Parameter file
C . Control
D . Discard
E . Log
Question 74. Which one of the following statements is true about the LogMiner utility?
A. LogMiner generates SQL_REDO as well as SQL_UNDO with primary key information.
B . LogMiner can be used for media recovery.
C . LogMiner can be used by Oracle for log recovery.
D . LogMiner can be used for instance recovery.
Question 75. While performing an online backup, the database crashed due to a media failure. DBA kept several tablespaces in backup mode, and each tablespace had several data files. Which one of the following statements is most appropriate to use for taking the tablespaces out of backup mode?
A. ALTER DATABASE END BACKUP
B . ALTER TABLESPACE END BACKUP
C . ALTER DATABASE DATAFILE END BACKUP
D . RECOVER DATABASE
Question 76. Due to a media failure, all the members that are part of an online redo log group are lost. Under what conditions can you still recover the database without data loss?
A . The lost redo log group is not the CURRENT group but has not yet been archived.
B . The lost redo log group is the CURRENT group.
C. The lost redo log group has already been archived.
D . You cannot perform recovery when all the members associated with an online redo log group are lost.
Question 77. What is the significance of using the command SET NEWNAME in the script shown in the following code?
allocate channel c1 type disk;
set newname for datafile 1 to ‘+DATA’;
restore datafile 1;
switch datafile 1;
recover datafile 1;
sql ‘Alter database open’;
A. Specifies a new location in the RUN block where the data file needs to be restored
B . Control file is updated with the new location specified in the RUN block
C . Renames the data file in the RUN block
D . Prompts the user to enter a new name after the execution of the RUN block
Question 78. What is the most important action a DBA must perform after changing the database from NOARCHIVELOG TO ARCHIVELOG?
A . Shutdown normal and restart the database
B . Perform a full database logical backup
C. Perform a full database backup
D . Manually switch the log files
Question 79. Which one of the following statements enables the control file AUTOBACKUP feature?
A . RMAN>CONFIGURE CONTROLFILE AUTO ON;
B. RMAN>CONFIGURE CONTROLFILE AUTOBACKUP ON;
C . RMAN>CONFIGURE CONTROLFILE BACKUP AUTO;
D . RMAN>CONTROLFILE AUTO BACKUP;
E. RMAN>SHOW ALL;
Question 80. Which one of the following options would you use with RMAN to back up archive log files along with other files?
A. ADD archivelog
B . ENABLE archivelog
C . INCLUDE archivelog
D . PLUS archivelog
Question 81. Which one of the following parameters when specified determines the maximum number of the files that can be stored in each backup set?
A . BACKUPPERSET
B . MAXSETSIZE
D . FILESPERBACKUP
Question 82.What must you do after the completion of copying the data files while performing online database backup?
A. Execute the ALTER SYSTEM SWITCH LOGFILE command and copy the archive log files since the beginning of online backup.
B . Shut down the database gracefully and restart.
C . Back up the online redo log files.
D . Execute the ALTER SYSTEM CHECKPOINT command.
Question 83. Which one of the following options of the export utility enables data to be directly transferred to the client without the intermediate evaluation buffer?
A . CONVENTIONAL=Y
C . DIRECT=N
Question 84. When can you perform a database backup using RMAN if the database is in ARCHIVLOG mode?
A . CLOSED
C . NOMOUNT
E. RESTRICTED SESSION
Question 85. Due to a media failure the user10.dbf data file was lost. Which one of the following commands must DBA use at the RMAN prompt, to copy the data files from the backup location to their default location before performing recovery operations?
A . ALLOCATE
B . REINSTATE
C . RECOVER
Question 86. Which one of the following Oracle components or products must be installed and configured to enable the secure communications between the client and the server over the network?
A. Advanced network option
B . Intelligent agent
C . Connection Manager
D . Security server
Question 87. Which Oracle process is responsible for archiving the online redo log files when the automatic archiving feature is enabled in ARCHIVELOG mode?
A . PMON
B . SMON
D . DBWn
E . LGWR
Question 88.Among the listed recovery method types, which recovery requires the DBA to explicitly issue a recovery command?
A . Crash recovery
B . Thread recovery
C. Media recovery
D . Instance recovery
Question 89. What is the result of issuing the following command? SQL> ALTER SYSTEM SET SHARED_SERVERS=0;
A . The minimum number of shared server processes will be run.
B. It disables the shared server.
C . All the existing connections will be terminated immediately.
D . The database will be restarted.
Question 90. What is the default value for the initialization parameter CONTROL_FILE_RECORD_KEEP_TIME?
A . Nine days
B . One day
C. Seven days
D . Five days
E . Ten days
Question 91. Which one of the following configuration files can be used to implement the host naming method?
B . tnsnames.ora, sqlnet.ora, host.ora
C . sqlnet.ora, tnsnames.ora
D . host.ora, tnsnames.ora
E . sqlnet.ora, names.ora
Question 92. The buffers in the database buffer cache that are currently being accessed by a server process are referred to as?
A. Pinned buffers
B . Reuse buffers
C . Free buffers
D . Dirty buffers
Question 93 Which one of the following commands can you use to display whether or not the automatic archiving feature is enabled?
A . ARCHIVE LOG NEXT
B . ARCHIVE LOG REPORT
C . ARCHIVE LOG STATUS
D ARCHIVE LOG LIST
Question 94 Which three statements about fine-grained access control are true?
A. The application context is required.
B. The application context is not required, but recommended.
C. Security cannot be bypassed as it is built into each application.
D. Security cannot be bypassed as it built into the database server.
E. Security can be built in the database server instead of each application.
Solution: A, D, E
Question 95 Which two statements about dimensions are true?
A. A hierarchy can consist of multiple levels.
B. A dimension can be based on multiple tables.
C. A dimension can contain only a single hierarchy.
D. No special privileges are required to create a dimension.
Solution A, B
Question 96 Which two statements are true when corrupted blocks are skipped?
A. All rows in a corrupt block are inaccessible.
B. Referential integrity may be violated.
C. If the freelist is corrupt the table has to be re-created.
D. Only the corrupted rows within a block will be inaccessible.
E. Queries have to be rewritten so they will skip corrupted blocks.
F. Indexes pointing to corrupt blocks will be marked as invalid.
Solution A & B
Question 97 What can you see when querying V$LOGMNR_CONTENTS?
A. The SQL statement run and the old image.
B. The SQL statement run, and the new image.
C. Only the redo generated by the current schema.
D. The SQL statement run, and the relevant undo SQL statement.
E. The old image and the new image of the row that was modified.
Question 98 Which command will open a standby database in read-only mode?
A. ALTER DATABASE OPEN READ ONLY;
B. STARTUP READ ONLY;
C. ALTER DA MOUNT READ ONLY;
D. ALTER DATABASE READ ONLY;
Question 99 To enable a database to have more than one service associated with it, the connect descriptor SID parameter in the tnsnames.ora file has been replaced by which two parameters?(Choose two)
A. SERVICE _NAME.
Question 100 Which type of LOBs cannot be declared as temporary?
Hope you like the compilation of 100 Basics oracle dba interview questions. Please do provide the feedback