1Z0-042 - Oracle Database 10g: Administration I

Go back to Oracle

Example Questions

You want to create a new optimized database for your transactional production environment to be used by a financial application. While creating the database, you want the Oracle software to take care of all basic settings to optimize the database performance. Which method would you use to achieve this objective? You have a text file that maintains information on thousands of items. The end-user application requires the transfer of that information into a table in the database. What would you use to achieve this task? (Choose two.) As a result of performance analysis, you created an index on the prod_name column of the prod_det table, which contains about ten thousand rows. Later, you updated a product name in the table. How does this change affect the index? You executed the following command to export the EMPLOYEES table from a remote machine: $ EXPDP hr/[email protected] DUMPFILE=my_dir:exp_hr.log LOGFILE=data_pump_dir:log_hr.log TABLES=employees What would be the outcome of this command? Your database instance is started using the server parameter file (SPFILE). Control files are multiplexed and stored on different disks. Because of a disk failure, you lost one of these control files. You replaced the damaged disk. What is the correct sequence of steps that you would follow to recover the control file? - 1. Shut down the instance, if not already done. - 2. Copy one of the remaining control files to a new location. - 3. Change the value of the CONTROL_FILES initialization parameter to correspond to the new location of the control files. - 4. Start up the database instance to the NOMOUNT stage. - 5. Recover the database to the point of failure of the control file. - 6. Open the database. You have been asked to create three users, PUB1, PUB2 and PUB3, for three newly hired employees and to allocate unlimited quota on their default tablespace PUBLISHER_TBS with no other privileges. After creating the users, which method can you use for granting the required privileges? While planning to manage more than one database in your system, you perform the following activities: - 1. Organize different categories of files into independent subdirectories. - 2. Use consistent naming convention for database files. - 3. Separate administrative information pertaining to each database. Which option corresponds to the type of activities performed by you? You have to shut down the database instance with the ABORT option because of a hardware failure. Which statement is true about the subsequent opening of the database? Which is the correct description of the significance of the ORACLE_HOME environmental variable? You created a response file and want to check it before starting installations in silent mode. You started installation of Oracle software in interactive mode by providing the response file. In the middle of the installation, you realize that the behavior of the Oracle Universal Installer (OUI) is not consistent with the response file. What action would you take to detect the cause of this behavior? User A executes the following command to update the TRANS table: SQL> UPDATE B.trans SET tr_amt=tr_amt+500 WHERE c_code='C005'; Before user A issues a COMMIT or ROLLBACK command, user B executes the following command on the TRANS table: SQl> ALTER TABLE trans MODIFY (tr_type VARCHAR2(3)); What would happen in this scenario? In your database, the STATISTICS_LEVEL initialization parameter is set to BASIC. What is the impact of this setting? Your database is functional with peak load for one hour. You want to preserve the performance statistics collected during this period so that they can be used for comparison while analyzing the performance of the database in the future. What action would you take to achieve this task? The application workload on your database is same between 10 a.m. and 11 a.m. on weekdays. Suddenly you observe poor performance between 10 a.m. and 11 a.m. in the middle of the week. How would you identify the changes in configuration settings, workload profile, and statistics to diagnose the possible causes of the performance degradation? You work as a database administrator for . You enabled tracing while creating a listener to your database. What additional information is available to you when you enable tracing? Which three statements are true regarding the logical structure of the Oracle database? (Choose three.) You work as a database administrator for . You find that reports are running for a long time in your data warehousing applications. You suspect a lack of indexes is the reason for the performance degradation. Which advisory component would you refer to, in order to determine the appropriate indexes? Which two statements about Flashback Query are true? (Choose two.) The HR user creates a view with this command: SQL> CREATE VIEW emp_v AS SELECT * FROM scott.emp; Now HR wants to grant the SELECT privilege on the EMP_V view to the JIM user. Which statement is true in this scenario? Which three statements are correct about temporary tables? (Choose three.) Which two statements are true about a bitmap index? (Choose two.) Two database users, Jack and Bill, are accessing the STAFF table of the DB database. When Jack modifies a value in the table, the new value is invisible to Bill. Which is the modified value invisible to Bill? Which two statements are true regarding the database in ARCHIVELOG mode? (Choose two.) Your application demands frequent connection and disconnection from the database. You have three listener processes that are listening for the database PROD. While setting up the connect string using Oracle Enterprise Manager 10g Database Control, which two options would you select to balance the connection load across all the listener processes? (Choose two.) Your database is having two control files, three redo log file groups with two members in each group. Failure of which file would cause an instance to shut down? Which two statements about Recovery Manager (RMAN) backups are true? (Choose two.) Your tnsnames.ora file has the following entry for the service alias ORCL: ORCL = ( DESCRIPTION = (ADDRESS = (PROTOCOL = TCP)(HOST = 10.156.24.216)(PORT = 1521)) (CONNECT_DATA = (SERVER = DEDICATED) (SERVICE_NAME = orcl.oracle.com))) The TNSPING command executes successfully when tested with ORCL but you are not able to connect to the database instance with the following command: SQL> CONNECT scott/[email protected] What could be the reason for this? You work in a data warehouse environment that involves the execution of complex queries. current content of the SQL cache holds the ideal workload for analysis. You want to analyze only few most resource-intensive statements. What would be your suggestion to receive recommendations on the efficient use of indexes and materialized views to improve query performance? You are working on the 24X7 database with high transaction volume, to ensure faster instance recovery on your database you set the FAST_START_MTTR_TARGET initialization parameter to a very low value. What effect it will have on the database? You work as a database administrator for . In your production database, the size of Database Buffer Cache needs to be increased immediately for the current as well as future instances of the database. The Oracle instance has been configured to accommodate any changes in the size of the memory structures. At this is production database, you want to accomplish this task with no impact on the user's connections. Which activity must you have completed before accomplishing this task? A constraint in a table is defined with the INITIALLY IMMEDIATE clause. You executed the ALTER TABLE command with the ENABLE VALIDATE option to enable the constraint that was disabled. What are the two effects of this command? (Choose two.) You work as a database administrator for . While granting a role, SELECT_CATALOG_ROLE, to a user, , using Oracle Enterprise Manager 10g Database Control, you have cleared the DEFAULT check box. What will be the result? You work as a database administrator for . You want to create a new optimized database for your transactional production environment to be used by a financial application. While creating the database, you want the Oracle Software to take care of all basic settings to optimize the database performance. Which method would you use to achieve this objective? You work as a database administrator for . You want to be notified when the number of disk I/O increases by 10% of what it was yesterday. You create a baseline metric for yesterday's disk I/O performance. How do you use the baseline metrics to achieve your objective? When you try to start the Database Control by using the emctl start dbconsole command the following error is displayed: TZ set to America/New_york OC4J Configuration issue. /u01/app/oracle/product/10.1.0/db_1/oc4j/j2ee/OC4J_DBConsole_orcl.us.oracle.com not found. Which two environment variables do you need to set appropriately to avoid such errors, and start the Database Control successfully? (Choose two.) Which two database operations can be performed at the MOUNT stage of database startup? (Choose two.) You work as a database administrator for . Your database is in the MOUNT state and you execute the following command to open it ALTER DATABASE OPEN; What two actions are performed as a result of this command? Choose two. What is the implication of setting the initialization parameter FAST_START_MTTR_TARGET to 0 in your database? You work as a database administrator for . Client connection requests are failing because the listener is not responding. You want to forward client connect requests to another listener if one listener is not responding. How do you achieve this? The HR user owns the EMP table. The HR user grants privileges to the SCOTT user by using this command: SQL> GRANT SELECT,INSERT,UPDATE ON emp TO scott WITH GRANT OPTION; The SCOTT user executes this command to grant privileges to the JIM user: SQL> GRANT SELECT,INSERT,UPDATE ON hr.emp TO jim; Now the HR user decides to revoke privileges from JIM by using this command: SQL> REVOKE SELECT,INSERT,UPDATE ON emp FROM jim; Which statement is true after HR issues the REVOKE command? You want the user APP_DBA to administer the Oracle database from a remote machine. APP_DBA is granted the SYSDBA privilege to perform administrative tasks on the database. Which file is used by the Oracle database server to authenticate APP_DBA? A user receives the following error while executing a query: ORA-01555: snapshot too old Which two options can be the solutions to avoid such errors in future? (Choose two.) You want to enforce a company's business policy on several objects by using a single policy function. Which two types of policies can be assigned to the policy_type argument in the dbms_rls.add_policy procedure to achieve the above objective? (Choose two.) The user SCOTT executes the following command successfully to increase the salary values in one of his sessions: SQL> UPDATE emp SET sal=sal*1.15 WHERE deptno=20; Before SCOTT ends the transaction, user HR who has the privileges on EMP table executes a query to fetch the salary details but finds the old salary values instead of the increased values. Why does HR still see the old data? You received complaints about the degradation of SQL query performance. You identified top SQL queries that consume time. What would be your next step to find out recommendations about statistics collection and restructuring of the SQL statement to improve query performance? Your database is started with SPFILE. You want the database instance to be dynamically registered with a listener L2 with the following details: Protocol: TCP Host: indl151e Port: 1525 Which is the correct order of the steps that you would follow to achieve this? 1. Set the LOCAL_LISTENER parameter to L2 dynamically. 2. Make an entry for L2 in tnsnames.ora on the database server. 3. Restart L2. 4. Modify the listener.ora file to add the instance name in SID_LIST of L2. You work as a database administrator for . Your database is in NOARCHIVELOG mode. The database has been configured with three redo logs groups, and there have been five log switches. You lost one non-system-critical data file from your database. Which method would you use to recover the data file? You have a large amount of historical data in an operating system file. Some analysts in your organization need to query this data. The file is too large to load into your current database. Which is the most effective method to accomplish the task? Which naming method uses the tnsnames.ora file to store the connect descriptor used by the client while connecting to the database instance from a remote machine? Which two statements are true about the roles in the Oracle database? (Choose two.)

Study Guides