11 Answered Test Questions:
1. Evaluate the following SQL:
CREATE USER sam IDENTIFIED BY sam;
GRANT
CREATE ANY MATERIALIZED VIEW,
CREATE ANY DIMENSION, DROP ANY DIMENSION, QUERY REWRITE, GLOBAL QUERY REWRITE
TO dw_manager
WITH ADMIN OPTION;
GRANT dw_manager TO sam WITH ADMIN OPTION;
Which three actions among the following is the user SAM able to perform?
Answers:
• Select from a table
• Create and drop a materialized view
• Alter a materialized view that was created
• Grant and revoke the role to and from other users
• Enable the role and exercise any privileges in the role's privilege domain
2. For which two tasks would it be best not to use the utility SQL*Loader?
3. For which two types of tablespaces can proactive tablespace alerts be set?
Answers:
• Locally managed
• Dictionary-managed
• Read-only
• Online
4. Examine the SQL statement:
CREATE TABLESPACE user_data
DATAFILE '/u01/oradata/user_data_0l.dbf' SIZE 100M
LOCALLY MANAGED UNIFORM SIZE 1M
AUTOMATIC SEGMENT SPACE MANAGEMENT;
Which part of the tablespace will be of a uniform size of 1MB?
Answers:
• Extent
• Segment
• Oracle block
• Operating system block
5. Which of the following is a new initialization parameter for Oracle Database 10g?
Answers:
• UNDO_SUPPRESS_ERRORS
• PARALLEL_AUTOMATIC_TUNING
• RESUMABLE_TIMEOUT
• SHARED_SERVER
6. A table is stored in a data dictionary managed tablespace. Which two columns are required from DBA_TABLES to determine the size of the extent when it extends?
Answers:
• BLOCKS
• PCT_FREE
• NEXT_EXTENT
• PCT_INCREASE
7. Consider this SQL statement:
UPDATE employees SET first_name = 'John'
WHERE emp_id = 1009;
COMMIT;
What happens when a user issues the COMMIT in the above SQL statement?
Answers:
• The server process places the commit record in the redo log buffer
• Log Writer (LGWR) writes the redo log buffer entries to the redo log files and data files
• The user process notifies the server process that the transaction is complete
• The user process notifies the server process that the resource locks can be released
8. For what purpose is the initialization parameter UNDO_RETENTION used?
Answers:
• To specify the length of time Oracle must retain undo data in the undo tablespace
• To specify the length of time Oracle must retain undo data in the flash recovery area
• To specify the length of time Oracle must retain undo data in the data files
• To specify the length of time Oracle will retain undo data in the flashback database logs
9. Which of the following initialization parameters would belong to the basic group of parameters?
Answers:
• SHARED_POOL
• CURSOR_SPACE_FOR_TIME
• DB_NAME
• CURSOR_SHARING
10. An index on the PASSPORT_RECORDS table has to be created. It contains 10 million rows of data. The key columns have low cardinality. The queries generated against this table use a combination of multiple WHERE conditions involving the OR operator.Which of the following index will be used for this criteria?
Answers:
• Bitmap
• Unique
• Partitioned
• Reverse key
11. Examine the following SQL statement:
CREATE TABLESPACE userdata
DATAFILE '/oracle/oradata/userdata_0l.dbf' SIZE 100M
LOCALLY MANAGED UNIFORM SIZE 1M
AUTOMATIC SEGMENT SPACE MANAGEMENT;
Which part of the tablespace will be of a uniform size of 1 MB?
Answers:
• Extent
• Segment
• Oracle block
• Operating system block
56 NOT Answered Yet Test Questions:
(hold on, will be updated soon)
12. Where is the free space managed, for a tablespace created with automatic segment-space management?
Answers:
• In the extent
• In the control file
• In the data dictionary
• In the undo tablespace
13. Which three out of the following are the physical structures that constitute the Oracle database?
Answers:
• Segment
• Data file
• Log file
• Tablespace
• Control file
14. When there is a monotonically increasing sequence number as an indexed column, which of
the following is true?
Answers:
• Hash-partitioned global indexes are useful
• Range-partitioned global indexes are better
• Hash-partitioned local indexes are better
• Range-partitioned local indexes are better
15. When is SGA created in an Oracle database environment?
Answers:
• When the database is created
• When the instance is started
• When the database is mounted
• When a user process is started
16. Which data dictionary view should be used to get a list of object privileges for all database users?
Answers:
• DBA_TAB_PRIVS
• ALL_TAB_PRIVS
• USER_TAB_PRIVS
• ALL_TAB_PRIVS_MADE
17. Which statement about the LOG_BUFFER initialization parameter is true?
Answers:
• The LOG_BUFFER parameter can be changed dynamically
• The minimum value for the LOG_FILE parameter is 512KB
• The LOG_BUFFER parameter value must be a multiple of the database block size
• The LOG_BUFFER parameter value must be a multiple of the operating system block size
18. Which of the following is true when John uses the RESETLOGS option?
Answers:
• He doesn't need to back up the database
• He must back up the database right away
• He can use archive redo logs from an older incarnation of the database
• He can't use archive redo logs from an older incarnation of the database
19. Which graphical DBA administration tool should be used to tune an Oracle database?
Answers:
• SQL*Plus
• Oracle Enterprise Manager
• Oracle Universal Installer
• Oracle Database Configuration Assistant
20. Which data dictionary view should be queried to find the following information about a user?
1.Whether the user's account has expired
2.The user's default tablespace name
3.The user's profile name
Answers:
• DBA_USERS only
• DBA_USERS and DBA_PROFILES
• DBA_USERS and DBA_TABLESPACES
• DBA_USERS, DBA_TS_QUOTAS, and DBA_PROFILES
21. Which three major tuning areas does OEM Database Control provide information about?
Answers:
• Most-used objects
• Top SQL statements
• Memory usage
• Top sessions affecting the instance
• CPU and wait classes
22. Which memory structure contains the information used by the server process to validate user privileges?
Answers:
• Buffer cache
• Library cache
• Data dictionary cache
• Redo log buffer cache
23. What are the two main advantages of using bitmap indexes?
Answers:
• Bitmap indexes use less storage space
• Bitmap indexes are easy to maintain when DML statements are issued
• Bitmap segments are updated upon COMMIT, at the end of the transaction
• Bitmap indexes work very fast with multiple predicates that are combined with AND, OR, and NOT operators
24. What statistics will Oracle collect If LIST subpartitioning is used, and the value of the GRANULARITY argument is specified as AUTO?
Answers:
• Global statistics only
• Global and partition statistics only
• Global, partition, and subpartition statistics
• No partitioned table statistics
25. Which of the following commands must be used to apply incremental backups?
Answers:
• RECOVER BACKUP OF DATABASE
• RECOVER DATAFILE
• RECOVER COPY OF DATAFILE
• RECOVER COPY OF DATABASE
26. Who will be the owner of the job, if you create a job in user SCOTT's schema?
Answers:
• SYS will be the owner of the job
• You will be the owner of the job
• PUBLIC will be the owner of the job
• SCOTT will be the owner of the job
27. Which statement is true when connecting to the Oracle instance using the multithreaded server configuration?
Answers:
• The User Global Area (UGA) may only contain sort areas
• The User Global Area (UGA) may be accessible to dedicated servers
• The User Global Area (UGA) components may reside in the large pool
• The User Global Area (UGA) components may reside in the buffer cache
28. Which three among the following dynamic performance views obtain information from the control file?
Answers:
• V$LOG
• V$SGA
• V$THREAD
• V$VERSION
• V$DATAFILE
29. Which option lists the correct hierarchy of storage structures, from largest to smallest?
Answers:
• Segment, extent, tablespace, data block
• Data block, extent, segment, tablespace
• Tablespace, extent, data block, segment
• Tablespace, segment, extent, data block
30. What is the priority level for a job in a job class?
Answers:
• From 1 to 5
• From 1 to 999
• From 1 to 10
• A job can only take a value of HIGH or LOW.
31. For which two constraints are indexes created when the constraint is added?
Answers:
• Check
• Unique
• Not null
• Primary key
32. Which conditions among the following must be met, if the tablespace is to be made read only?
Answers:
• The tablespace must contain an active rollback segments
• The tablespace must be online
• The tablespace must not contain any active rollback segments
• The tablespace must not be involved in an open backup
• The tablespace must be involved in an open backup
33. Which background process performs a checkpoint in the database,by writing modified blocks from the database buffer cache in the SGA to the data files?
Answers:
• LGWR
• SMON
• DBWn
• CKPT
34. Which of the following items are stored in the flash recovery area?
Answers:
• Datafiles
• Undo segments
• Datafile copies
• Archived redo log files
35. What is the purpose of the flashback technology in Oracle Database 10g?
Answers:
• Recovery of a data file from physical corruption
• Recovery of a data file from physical and logical corruption
• Recovery of a data file from logical corruption
• Recovery from a problem brought on by a damaged disk drive
36. Where can the nondefault parameters be found when the instance is started?
Answers:
• Alert log
• Online redo log
• Archiver redo log
• SYSTEM user's trace file
37. Which tablespace is used as the temporary tablespace if 'TEMPORARY TABLESPACE' is not specified for a user?
Answers:
• TEMP
• DATA
• SYSTEM
• ROLLBACK
38. Which of the following are considered types of segments?
Answers:
• Only LOBS
• Only nested tables
• Only index-organized tables
• Only LOBS and index-organized tables
• Nested tables, LOBS, index-organized tables, and boot straps
39. What does the RMAN CATALOG command do?
Answers:
• Helps create image copies of datafiles
• Helps create image copies of current control files
• Helps catalog any new files that aren't a part of the recovery catalog
• Helps catalog only the old files
40. Which command would revoke the ROLE_DATA role from all users?
Answers:
• REVOKE role_data FROM ALL;
• REVOKE role_data FROM PUBLIC;
• REVOKE role_data FROM default;
• REVOKE role_data FROM ALL_USERS;
41. Which of the following sets of tablespaces is mandatory for any Oracle 10g database?
Answers:
• SYSTEM, SYSAUX, and UNDOTBS
• SYSTEM and UNDOTBS
• SYSAUX and UNDOTBS
• SYSTEM and SYSAUX
42. To grant a system privilege with the GRANT statement, one should:
Answers:
• Have been granted the GRANT ROLE PRIVILEGE system privilege
• Have been granted the system privilege with the ADMIN OPTION
• Have been granted the GRANT ANY PRIVILEGE system privilege
• Have been granted the system privilege with the GRANT OPTION
43. Which two statements are true about rebuilding an index?
Answers:
• The resulting index may contain deleted entries
• A new index is built using an existing index as the data source
• Queries cannot use the existing index while the new index is being built
• During a rebuild, sufficient space is needed to accommodate both the old and the new index in their respective tablespaces
44. When a deadlock shutdown is detected by Oracle, where is the trace file generated?
Answers:
• SQL_TRACE
• TRACE_DEST
• USER_DUMP_DEST
• CORE_DUMP_DEST
45. How can the SQL Access Advisor be managed?
Answers:
• Through the DBMS_SQLTUNE package
• Through the DBMS_ADVISOR package
• Through the SQL_TUNE package
• Through the DBA_ADVISOR package
46. User Smith created indexes on some tables owned by user John. The following has to be displayed:
1.Index names
2.Index types
Which data dictionary view(s) should be queried?
Answers:
• DBA_INDEXES only
• DBA_IND_COLUMNS only
• DBA_INDEXES and DBA_USERS
• DBA_IND COLUMNS and DBA_USERS
47. Which of the following helps the user identify his statement in the DBA_TUNE_MVIEW view?
Answers:
• The STATEMENT variable
• The STATEMENT_ID column
• The VIEW_ID column
• The TASK_NAME variable
48. Which DBMS_LOGMNR feature facilitates the automatic adding of redo log files for mining?
Answers:
• REMOVE_LOGFILE
• CONTINUOUS_MINE
• NO_ROWID_IN_STMT
• AUTOMATIC_LOGFILE
49. Which of the following commands are used to copy ASM files?
Answers:
• RMAN commands
• OMF commands
• Operating system commands
• Logical Volume Manager commands
50. Which statement is valid regarding index clusters?
Answers:
• Index clusters can only be used for tables with low cardinality columns
• Index clusters are generally well suited for tables that have many full table scans
• Normal B-Tree indexes do not store null key values, whereas cluster indexes store null keys
• A cluster index always takes up much more storage space than a normal index for the same set of key values
51. Which three statements among the following are true about rebuilding indexes?
Answers:
• The ALTER INDEX REBUILD command is used to change the storage characteristics of an index
• Using the ALTER INDEX REBUILD is usually faster than dropping and recreating an index because it uses the fast full scan feature
• Oracle8i allows for the creation of an index or re-creation of an existing index while allowing concurrent operations on the base table
• When building an index, the NOLOGGING and UNRECOVERABLE keywords can be used concurrently to reduce the time it takes to rebuild itself
52. What do ASM disk groups provide?
Answers:
• Redundancy through striping
• Performance through mirroring
• Redundancy through mirroring
• Performance through striping
53. What happens once the DBA disables the flash recovery area?
Answers:
• The RMAN will continue to be able to access the files in the flash recovery area
• The RMAN will be unable to access the files in the flash recovery area
• The RMAN will be unable to access the automatic space management features of the flash recovery area
• The RMAN can use only the automatic space management features of the flash recovery area
54. Which initialization parameter specifies the location of the alert log file?
Answers:
• UTL_FILE_DIR
• USER_DUMP_DEST
• LOG_ARCHIVE_DEST
• BACKGROUND_DUMP_DEST
55. What will the command EXECUTE DBMS_SCHEDULER.PURGE_LOG(); do?
Answers:
• Purge only all the window logs
• Purge only all the job logs
• Purge all window and job logs
• Purge only yesterday's job logs
56. Which data dictionary view shows the available free space in a certain tablespace?
Answers:
• DBA_EXTENTS
• V$FREESPACE
• DBA_FREE_SPACE
• DBA_TABLESPACFS
57. What are the functions of an ASM instance?
Answers:
• Managing disk groups and communicating file metadata to the Oracle database using ASM files
• Managing database files and communicating file metadata to the ASM instance using ASM files
• Managing disk groups and communicating file metadata to the RMAN
• Protecting disk groups
58. If the DBCA is used to create a new database, when is the creation of the new flash recovery area mandatory?
Answers:
• Always
• Only if the automatic backup using the OEM is configured
• Only if user-managed backups is configured
• Only if the database is run in the archive log mode
59. What does the ARBn background process do in an ASM instance?
Answers:
• Coordinates disk activity
• Performs disk rebalance work
• Coordinates disk rebalance work
• Manages the RBAL process
60. What is the default value for the OPTIMIZER_MODE initialization parameter?
Answers:
• FIRST_ROWS
• ALL_ROWS
• CHOOSE
• COST
61. Which view provides the names of all the data dictionary views?
Answers:
• DBA_NAMES
• DBA_TABLES
• DICTIONARY
• DBA_DICTIONARY
62. How is the SQL profile used?
Answers:
• In the tuning mode of the optimizer only
• In the normal mode of the optimizer
• In neither the tuning nor the normal mode of the optimizer
• Along with existing statistics, to get better SQL execution plans
63. What should be confirmed before changing the CURSOR_SPACE_FOR_TIME parameter in the initialization file to TRUE?
Answers:
• The TIMED_STATISTICS parameter is set to TRUE
• The hit percentage in the buffer cache is at least 95%
• The OPEN_CURSOR parameter is set to at least twice the default value
• The value in the RELOADS column of V$LIBRARYCACHE is consistently zero
64. What will happen if the value of the SGA_TARGET parameter is set higher than the value of the SGA_MAX_SIZE parameter at instance startup?
Answers:
• The instance will not start
• SGA_TARGET will become equal to the SGA_MAX_SIZE value
• The database will ignore the SGA_TARGET parameter
• SGA_MAX_SIZE is automatically raised, so it is equal to the SGA_TARGET value
65. Which of the following is mandatory when using the easy connect naming method?
Answers:
• Host name
• Database name
• Service name
• Port number
66. Which of the following is true if the alter tablespace statement is used and the TEMPORARY clause is specified?
Answers:
• Oracle no longer performs any checkpoint for the online datafiles in the tablespace
• Oracle performs a checkpoint for all online datafiles in the tablespace
• Oracle does not ensure that all files are written
• The offline files may require media recovery before the tablespace is brought online
• The offline files may require media recovery after the tablespace is brought online
67. Where can a SQL Tuning Set(STS) be created from?
Answers:
• Several SQLs statements from the AWR
• One or more other STSs
• A single SQL statement
• Only the most high-load SQL statements in the database
No comments:
Post a Comment