Oracle_Db11g_实施专家认证考试1Z0-514模拟练习题[1].docx

注意：本材料仅供合作伙伴复习认证考试参考。本材料是针对Oracle DB 11g Implementation Specialist (1Z0-514)认证考试的模拟练习题，目的在于掌握本考试相关的一些知识点。在考试中遇到的题目如有雷同，切勿在不理解含义的情况下仅根据关键字眼直接选出答案。答案仅供参考，可能存在有些答案不完全正确的情况。1.Identify two situations in which you can use Data Recovery Advisor for recovery.(Choose two)A.The database files are corrupted when the database is open.B.The user has dropped an important table that needs to be recovered.C.The archived redo log files are missing for which the backup is not available.D.The database is not opening because the required database files are missing.Answer:AD2.You are managing an Oracle Database 11g database. The database is open, and you plan to perform Recovery Manager(RMAN) backups. Which three statements are true about these backups?(Choose three)A.The backups would be consistent.B.The backups would by inconsistent.C.The backups would be possible only if the database is running in ARCHIVELOG mode.D.The backups would be possible only if the database is running in NOARCHIVELOG mode.E.The backups need to be restored and the database has to be recovered in case of a media failure.RMAN就是Recovery MANanger, 是Oracle用来备份和恢复的，它有一套专门的语法和命令。Answer:BCE3.Which three statements are correct about temporary tables?(Choose three)A.Indexes and views can be created on temporary tables.B.Both the data and the structure of temporary tables can be exported.C.Temporary tables are always created in as users temporary tablespace.D.The data inserted into a temporary table in a session is available to other sessions.E.DML locks are never acquired on the data of temporary tables.Answer:ACE4.Which statement is true about the UNDO_RETENTION parameter when retention guarantee is not enabled?A.It is the time period after which the undo data becomes obsolete.B.It is the time period after which the committed undo data would be transferred to a temporary tablespace.C.It is the minimum time period up to which the committed undo data would be retained if free undo space is available.D.It is the time period after which the undo data is transferred to the Flash Recovery Area to provide read consistency.Answer:C5.You execute this command to drop the ITEM table, which has the primary key referred in the ORDERS table:SQLDROP TABLE scott.item CASCADE CONSTRAINTS PURGE; Which two statements are true about the effect of the command?(Choose two.)A.No flashback is possible to bring back the ITEM table.B.The ORDERS table is dropped along with the ITEM table.C.The dependent referential integrity constraints in the ORDERS table are disabled.D.The dependent referential integrity constraints in the ORDERS table are removed.E.The table definition of the ITEM table and associated indexes are placed in the recycle bin.Answer:AD6.Which three statements regarding the server parameter file(SPFILE) are true?(Choose three)A.An SPFILE is a binary file.B.An SPFILE cannot reside on a client.C.An SPFILE cannot contain static parameters.D.An SPFILE can store changes persistently across instance restarts.E.An SPFILE can be read by the database, but it is not written to by the server.F.An SPFILE must be created manually, before creating a database, even if you use the DBCA to create the DB.Answer:ABD7.You plan to move data from a flat file to a table in your database. You decide to use SQL*Loader direct path load method to perform this task. The table in which you plan to load data is an important table having various integrity constraints defined on it. Which constraints will remain enabled by default during this operation?(Choose all that apply)A.CHECKB.UNIQUEC.NOT NULLD.PRIMARY KEYE.FOREIGN KEYAnswer:BCD8.Which three operations require undo data?(Choose three)A.Committing a transaction.B.Flashing back a transaction.C.Recovering a failed transaction.D.Running a read-consistent query.E.Changing a tablespace status from READ ONLY to READ WRITE.Answer:BCD9.In an OLTP system, the user SCOTT has started a query on a table in the peak transactional hour that performs bulk inserts. The query runs for more than 15 minutes and then SCOTT receives the following error:ORA-01555:snapshot too oldWhat could be the reason for this error?A.The query is unable to get a read-consistent image.B.There is not enough space in Flash Recovery Area.C.There is not enough free space in the flashback archive.D.The query is unable to place data blocks in undo tablespace.Answer:A10.You have issued a SHUTDOWN ABORT command to bring down you database instance. Consider the steps that will be performed later when open the database:1.SGA is allocated.2.Control file is read.3.Redo log files are read.4.Instance recovery is started.5.Background processes are started.6.Data files are checked for consistency.7.Server parameter file or the initialization parameter file is read.Which option has the correct order in which these steps occur?A.7,1,5,2,3,6,4B.1,2,3,7,5,6,4C.7,1,4,5,2,3,6D.1,7,5,4,2,3,6Answer:A11.Your database instance is configured with automatic undo management and the UNDO_RETENTION parameter is set to 900 seconds. You executed the following command to enable retention gurantee:SQL ALTER TABLESPACE undotbs1 RETENTION GUARANTEE; What effect would this command have on database?A.The extents in the undo tablespace retain data until the next full database backup.B.The extents containing committed undo in the undo tablespace are not overwritten for at least 15 minutes.C.The extents containing committed data in the undo tablespace are not overwritten until the instance is shutdown.D.The extents containing committed undo in the undo tablespace are transferred to Flash Recovery Area before being overwritten.Answer:B12.The MGR_ROLE has below System Privileges:System PrivilegeAdmin OptionCREATE ROLENCREATE USERNSELECT ANY TABLENThe user CSC has beeg granted the CONNECT and RESOURSE roles only. The DBA grant MGR_ROLE to the user CSC by executing the command:SQL GRANT MGR_ROLE TO CSC WITH ADMIN OPTION; Which statement is true about the user CSC after he is granted this role?A.The user CSC can grant only the MGR_ROLE role to other users, but not the privilegs in it.B.The user CSC can revoke the MGR_ROLE from the users for whom he is the grantor.C.The user CSC can grant the privileges in the MGR_ROLE role to other users, but not with ADMIN OPTION.D.The user CSC can grant the priveleges in the MGR_ROLE role to other users, but cannot revoke privileges from them.Answer:B13.Which statement is true about a whole consistent database backup on a database running in ARCHIVELOG mode?A.The backup will consist of used data blocks only.B.The database must be shutdown to accomplish the backup.C.The backup can be accomplished without shutting down the database.D.The backup will contain all database files that have never been backed up.Answer:B14.Examine the following steps performed on a database instance:1.The DBA grants the CREATE TABLE system privilege to the CSC user with ADMIN OPTION.2.The CSC user creates a table.3.The CSC user grants the CREATE TABLE system privilege to the HR user.4.The HR user creates a table.5.The DBA revokes the CREATE TABLE system privilege from CSC.Which statement is true after step 5 is performed?A.The table created by CSC is not accessible and CSC cannot create new tables.B.The table created by CSC and HR remain, but both cannot create new tables.C.The table created by HR remains and HR still has the CREATE TABLE system privilege.D.The table created by HR remains and HR can grant the CREATE TABLE system privilege to other users.Answer:C15.Which two statements are true regarding listeners?(Choose two)A.Listeners use only the TCP/IP protocol.B.Multiple listener processes can run simultaneously on a host.C.Multiple database instances can be registered with a single listner.D.The listener-related errors can be traced only at the administrative level.E.Only one database instance can be registerd with a single listener at any time.Answer:BC16.You configured the Flash Recovery Area(FRA) for your database. The database instance is running in ARCHIVELOG mode. The default location for the archivelog is the FRA.Which two files are removed automatically if the space is required in the FRA as per the retention policy?(Choose two)A.Flashback log files.B.Backups that have become obsolete.C.User managed backups of the data file and control file.D.Archive redo log files that have multiple copies in a different archive location and not backup up.Answer:AB17.What two statements are true regarding the recommendations received from the SQL Access Advisor?(Choose two)A.It cannot generate recommendations that support multiple workload queries.B.It can recommend partitioning on tables provided that the workloads have some predicates and joins on the columns of the NUMBER or DATE typle.C.It can recommend partitioning only on tables that have at least 10,000 rows.D.It can recommend only B-tree indexes and not bitmap or function-based indexes.Answer:BC18.Which of the following identifies and creates an index to minimize the DB time for a particular SQL statement?A.The SGA Tuning AdvisorB.The SQL Access AdvisorC.The SQL Tuning AdvisorD.The Memory AdvisorAnswer:B19.In your database, the RESULT_CACHE_MODE parameter has been set to MANUAL in the initialization parameter file. You issued the following command:SQLSELECT /*+ RESULT_CACHE */ sale_category, sum(sale_amt) FROM sales GROUP BY sale_category;where would the result of this query be stored?A.database buffer cacheB.shared poolC.PGAD.large poolAnswer:B20.Which of the following segment types can you shrink using Oracles segment shrink functionality?(Choose all that apply)A.Undo segementsB.Index segmentsC.Temporary segmentsD.Data segmentsAnswer:BD21.Which of the following statements about conventional tablespaces(non-locally managed tablespaces) are true?Each correct answer represents a part of the solution.Choose twoA.Conventional tablespaces use data dictionary tables for storage space management.B.Conventional tablespaces use bitmaps for storage space management.C.In case of conventional tablespaces, Oracle reflects the allocatoin of a new extent by updating the associated segment header and the data dictionary table.D.In case of conventional tablespaces, Oracle reflects the allocatoin of a new extent by updating the associated segment header and the bitmap.Answer:AC22.Which two statements regarding a SQL profile are true?(Choose two)A.It is built by Automatic Tuning Optimizer.B.It cannot be stored persistently in the data dictionary.C.It can by used by the query optimizer automatically.D.It can be created manually by using the CREATE PROFILE command.Answer:AC23.Which two initialization parameters would you set to enable Automatic Shared Memory Management?(Choose two)A.set SHARED_POOL_SIZE to zeroB.set STATISTICS_LEVEL to BASICC.set SGA_TARGET to a non-zero valueD.set DB_CACHE_SIZE to a non-zero valueE.set STATISTICS_LEVEL to TYPICAL or ALLAnswer:CE24.Identify the activities performed as part of the Automatic SQL Tuning process in the maintenance window? (Choose all that apply.)A.Generating the SQL profileB.Testing and accepting the SQL profileC.Generating a list of candidate SQLs for tuningD.Adding tuned SQL plans into the SQL plan baselineE.Tuning each SQL statement in the order of importanceF.Generating baselines that include candidate SQL statement for tuningAnswer:ABCE25.Which tasks are run automatically as part of the Automated Maintenance task by default?(Choose all that apply)A.Sement AdvisorB.SQL Access AdvisorC.Optimizer statistics gatheringD.Automatic SQL Tuning AdvisorE.Automatic Database Diagnostics MonitorAnswer:ACD26.You set retain backups that are necessary for a recovery to any time within 31 days, what will happen after 31 days?A.If the backup set are under flashback area, the obsolete backup will be automatically deleted when need space.B.If the backup set are under flashback area, the obsolete backup will need manual delete when need space.C.If the backup set are under none- flashback area, the obsolete backup will be automatically deleted when need space.D.If the backup set are under none flashback area, the obsolete backup will need manual delete when need space.Answer:AD27.Which two statements are true about the UNDO_RETENTION parameter when retention guarantee is enabled?(Choose two)A.Undo tablespace protect long-running querys.B.Undo tablespace protect long-running querys and large transactions.C.Undo tablespace protect the commited trasactions.D.Undo tablespace protect the uncommited transactions.Answer:AC28.How and when can you access Enterprise Manager Database Control?A.You can access it by command line.B.Only when you can access 1158 port.C.You can access it whether your database is started or not.D.You cannot access it if your service name is not configured.Answer:C29.By Enterprise Manager Database Control, under Data Movement tab, you click the Load Data from User Files, what file do you need to be done before you excute this?A.Load Data only.B.Control File only.C.Load Data and Control File.D.Nothing should be done before that.Answer:A30.You updated your database to 11g Realease 2, and you have some os scripts to run in your database server, which will be called according to some data changes in your database server, which is the best way to enforce this?A.Use pl/sql to call the OS scripts.B.Use sql routine in the database.C.Modify the OS scripts to check data change in the database.D.Install a scheduler agent on the remote host, then run remote external jobs in you 11.2 database. Answer:D31.Which three statements are correct about database templete created by DBCA?(Choose three)A.The templete is stored as XML file.B.The templete is stored as plain file.C.A seed templete contains database structures and data.D.A seed templete can be created by default database templete.E.A seed templete can be created not only by default database templete by also existing database.Answer:ACE32.You want to improve your application performance by using Result Cache, which statements are correct to enforce this?A.Add a RESULT_CACHE hint in your sql statement.B.You must set the RESULT_CACHE_MODE to FORCE.C.set RESULT_CACHE_MAX_SIZE to a non zero valueD.You can use the Result Cache from the second time that the sql excuted.E.You cannot use Result Cache for bind variable statements.Answer:ACD33.The orders table stores all information about orders, manager can query all the order information, but sales can only query their own order information, how can we accomplish this?A.By Audit VaultB.By Database VaultC.By FGAD.By Label SecurityAnswer:D34.What can we do by SQL?A.Control and Access data.B.Set variable and use them to calculate in the session.C.Can control where the data should put in the physical device.D.Can modify the LOB data out of database.Answer:A35.What can we do by SQL*Plus?A.Manage the database.B.Excute OS commands.Answer:AB36.Take a look at the table definition of emp:emp_no char(5),name varchar2(10),salary number(4)We examined the data in it and find that all the data in column emp_no is number type, the longest name is 5 characters, which operations can we do to modify emp table?A.Modify emp_no number type.B.Modify name to varchar2(5).C.Modify salary to number(6,2).Answer:AB 37.When the recyclebin parameter is set to on, when you drop a table emp. Which statements are correct about emp?A.The emp table is dropped and the indexes built on it remains but will be set invalid state automatically.B.The indexes built on the table are been dropped accordingly.C.The views built on the table are been dropped accordingly.D.The views are invalid.Answer:BD38.When you flashback a table, which area will be used?A.UndoB.RedoC.Flashback directoryD.None of the aboveAnswer:A39.Your production database is in noarchivelog mode, for some reason if you want to modify it to archivelog mode, which backup should be taken before enforce this?A.A whole database backup.B.A Level 0 database backup.C.Use backup sets to backup the database.D.Use backup image to backup the database.Answer:A40.When you use DBCA to create a new database, what does it mean if you select file system as your data storage?A.Use Automatic Storage Management (ASM) to manage the database file.B.Let OS to manage the database file.C.Use Oracle-Managed Files.D.Use raw device to store the database files.Answer:B41.A performance sensitive DBA are worrying about that the automatic statistics collection will affect some excution plan in his database, which option should he select to enforce this?A.Disable automatic statistics collection.B.Use the 11g new feature to delay to PUBLISH the statistics.C.Use hint to fixup the excution plan.Answer:B42.When you are helping upgrade your production database to 11g Release 2, which features can you use to make sure that theres no performance degression.A.Use Database Replay to capture your production database workload.B.Use SQL Performance Advisor to list the performance degression sql.Answer:AB43.Which two statements are correct about sql tuning?A.SQL Access Advisor can tuning query and DML sql statement.B.SQL Access Advisor can tun