1Z0-042 - Oracle Database 10g: Administration I
Go back to Oracle
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?
Use Database Configuration Assistant (DBCA) to create the database with Transaction Processing template.
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?
A leaf row in the index will be deleted and inserted.
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?
The command would execute successfully and the export dump file would be created in the destination of the directory object MY_DIR.
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?
execute ALTER USER .. QUOTA UNLIMITED ON publisher_tbs command for all of the three users
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?
Optimal Flexible Architecture
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?
The database would open after automatically performing instance recovery.
Which is the correct description of the significance of the ORACLE_HOME environmental variable?
It specifies the directory containing the Oracle software.
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?
Compare the contents of the installActions.log file with that of the response file.
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?
The ALTER TABLE command fails due to the resource being busy.
In your database, the STATISTICS_LEVEL initialization parameter is set to BASIC. What is the impact of this setting?
The snapshots for the Automatic Workload Repository (AWR) are not generated automatically.
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?
Create a baseline on a pair of snapshots that have the statistics of the peak-load period in the database.
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?
by using the Automatic Workload Repository Compare Period report
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?
Information about each Oracle Net connection.
Which three statements are true regarding the logical structure of the Oracle database? (Choose three.)
It is possible to have tablespaces of different block sizes in a database.
A data block is the smallest unit of I/O for data files.
Each segment contains one or more extents.
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?
SQL Tuning and Access Advisors
Which two statements about Flashback Query are true? (Choose two.)
It helps in row-level recovery from user errors.
It fails when undo data pertaining to the transaction is overwritten.
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?
HR needs the SELECT privilege on the EMP table with GRANT OPTION from SCOTT for this operation.
Which three statements are correct about temporary tables? (Choose three.)
Temporary tables are always created in a user's temporary tablespace.
Indexes and views can be created on temporary tables.
Data manipulation language (DML) locks are never acquired on the data of temporary tables.
Which two statements are true about a bitmap index? (Choose two.)
It has a bitmap segment for each distinct value in the key column, containing a string of bits in which each bit represents the presence or absence of a key column value.
Updating the key column locks the whole bitmap segment that contains the bit for the key value to be updated.
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?
Jack has not committed the changes after modifying the value.
Which two statements are true regarding the database in ARCHIVELOG mode? (Choose two.)
You can perform complete database backups without closing the database.
All the previous database backups become invalid after you configure the database to ARCHIVELOG mode.
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.)
Try one address, selected at random.
Try each address, randomly, until one succeeds.
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?
any control file
Which two statements about Recovery Manager (RMAN) backups are true? (Choose two.)
Archived redo log files are backed up.
Only used data blocks can be backed up as backup sets.
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?
The orcl.oracle.com database service is not registered with the listener.
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?
Run the SQL Access Advisor.
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?
The overall database performance would be degraded.
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?
You must have started the database instance with server parameter file.
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.)
It prevents insert, update, and delete operations on the table while the constraint is in the process of being enabled.
It fails if any existing row violates the constraint.
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?
The user needs to enable the role explicitly.
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?
Use Database Configuration Assistant (DBCA) to create the database with Transaction Processing template.
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?
Apply the baseline to the disk I/O metrics in the Mange Metrics page
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.)
configuring the database in ARCHIVELOG mode
renaming of data files
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.
All redo files are opened
All online data files are opened
What is the implication of setting the initialization parameter FAST_START_MTTR_TARGET to 0 in your database?
Automatic tuning of checkpoint would be disabled.
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?
Enable connect-time failover
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?
The command fails because HR cannot revoke the privileges from JIM.
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.)
increase the size of the undo tablespace
enable the retention guarantee for the undo tablespace
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?
because of undo data from the undo tablespace
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?
run SQL Tuning Advisor on top SQL statements
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.
2, 1; 3 and 4 are not required.
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?
Restore all the data files and open the database
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?
Use an external table so you can have the metadata available in your database, but leave the data in the operating system files.
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?
local naming method
Which two statements are true about the roles in the Oracle database? (Choose two.)
Roles can be granted to other roles.
A role can contain both system and object privileges.