讲义062-题库解析16

1、QUESTION 16Examine the structure of the sales table, which is stored in a locally managed tablespace with Automatic Segment Space Management (ASSM) enabled.Nall?Type- - -PROD_ID CUST_ID TIME_ID CHANNEL_ID PROMO_IDTYS OL D AMOUNT SOLDYou want to perform onli he high water mark.A. Row movement is enab

2、led.NOT NOT NOT NOT NOT NOT NOTNULL NULL NULL NULL NULL NULL NULLNUMBER NUMBER DATE NUMBER NUMBERNUMBER (10, 2)NUMBER (10, 2)gment shrink to reclaim fragmented free space below tB. Referentialegrity constras for t he table are disabled.C. No queries are running on this table.D. Extra disk space equi

3、valent to the size of t he segment is available tablespace.heE. No pending tran Correct Answer: A Section: (none)ion exists on t he table.Explanation 使用 shrink 的前提条件：1）表所在的表空间必须使用 ASSM（自动段空间管理）2、在收缩表上必须启用 row movement 选项QUESTION 17Which task would youmend before using the Database Upgrade Assistant

4、(DBUA) toupgrade a single-instance Oracle 11g R2 database to Oracle Database 12c?A.B.C.D.shutting down the database instancet is being upgradedexecuting the catctl.pl script to run the upgrade prorunning the Pre-Upgrade Information Toolses in parallelcopying the listener.ora file to the new ORACLE_H

5、OMECorrect Answer: C Section: (none) ExplanationExplanation/Reference:Reference:http/cd/E11882_01/server.112/e23633/upgrade.htm#UPGRD12395QUESTION 18Your database is open and the listener LISTNENER is up. You ie thed:LSNRCTL RELOADWhat is the effect of reload on sesst were originally established by

6、listener?A. Only sess based on sic listener registrations are disconnected.B. Existing connections are not disconnected; however, they cannot perform any operations until the listener completes the re-registration of the database instance and service handlers.C. The ses s are not affected and contin

7、ue to function normally.D. All the sess are terminated and active tranions are rolled back.Correct Answer:C Section: (none)Explanation:In addition, the database servi, instan, service handlers, andlistening endpost were dynamically registered with thelistener will be unregistered and subsequently re

8、gistered again.但是没有说当前的会话不能执行操作QUESTION 19Which sement is true regarding the startup of a database instance?A. The instance does not start up normally and requires manual media recovery after a shutdown using the abort option.B.mitted tranions are rolled back during the startup of the database insta

9、nce after ashutdown using the immediate option.C. There is no differencehe underlying mechanics of the startup whether the database is shutdown by using the immediate option or the abort option.D. Media recovery is required when the database is shut down by using either the immediate option or the a

10、bort option.E. Instance recovery is not required if the database instance was shut down by using SHUTDOWN IMMEDIATE.Correct Answer: E Section: (none) ExplanationExplanation/Reference:Reference: http/cd/A87860_01/doc/server.817/a76956/start.htmQUESTION 20Examine the memory-related parameters sethe SP

11、FILE of an Oracle database:memory_max_ memory_6G=5Gpga_aggregate_ sga_max_size=0=500Msga_=0Which sement is true?A.B.C.Only SGA components are sized automatically-Memory is dynamically re-allocated betn the SGA and PGA as needed.The size of the PGA cannot grow automatically500 MB.D. The value of the

12、MEMORY_Correct Answer: CSection: (none) Explanation在Oracle 12cparameter cannot be changed dynamically.R1之前，没有选项可以用来限制和控制PGA的大小。虽然你设置某个大小为PGA_AGGREGATE_的初始参数，Oracle会根据工作负载和需求来动态地增大或减小PGA的大小。而在12c中，你可以通过开启自动PGA管理来对PGA设定硬性限制，这需要对PGA_AGGREGATE_LIMIT参数进行设置。因此，你现在可以通过设置新的参数来对PGA设定硬性限制以避免过度使用P GA。QUESTION

13、21Which two sements are true about extents?A.B.C.Blocks belonging to an extent can be spread across multiple data files.Data blocks in an extent are logically contiguous ban be non-contiguous on disk.The blocks of a newly allocated extent, although free, may have been used before.D. Data blocks in a

14、n extent are automatically reclaimed for use by other objects in a tablespaee when all the rows in a table are deleted.Correct Answer: BC Section: (none) Explanation(：该题属于传统的体系架构范围的知识点)Explanation/Reference:QUESTION 22You execute theds:SQLCREATE USER sidney IDENTIFIED BY out_standing1 DEFAULT TABLES

15、PACE usersQUOTA 10M ON users TEMPORARY TABLESPACE temp ACCOUNT UNLOCK;SQL GRANT CREATE SESTO Sidney;Which two sements are true?A. The create userd fails if any role with the name Sidney existshe database.B. The user sidney can connect to the database instance bannot perform sort operations because n

16、o space quota is specified for the temp tablespace.C. The user sidney is created bannot connect to the database instance because no profile isD. The user sidney can connect to the database instance but requires relevant privileges to create objectshe users tablespace.E. The user sidney is created an

17、d authenticated by the operating system.Correct Answer: AD Section: (none) Explanation（：该题也是属于体系范畴的知识点，与 11g 的一样）QUESTION 23Examine the query and its output:SQL SELECT REASON, metric_value FROM dba_outstanding_alerts;REASON-METRIC_VALUE-Tablespace TEST is 28 perce28.125nt fullMetrics Current Logons

18、Count29Metrics Database Time Spent waiting (%) is at 99.03754 for event class Application99.0375405db_recovery_file_dest_size of remaining bytes available.974294967296 bytes is 97.298 used and has 116228096After 30 minutes, you execute the same query:SQL SELECT reason, metric_value FROM dba_outstand

19、ing_alerets;REASON METRIC_VALUETablespace TEST is 28 percs 28.125nt fullWhat mighve caused three of the alerts to disappear?A. The threshold alerts were cleared and transferred to dba_alert_history.B. An Automatic Workload Reseconditory (AWR) snapshot was taken before the execution of t heC. An Auto

20、matic Database Diagnostic Monitor (ADDM) report was generated before the execution of t he second query.D. The database instance was restarted before the execution of t he second query. Correct Answer:ASection: (none)Explanation 有状态显示在DBA_OUTSTANDING_ALERTS 中，清空后会转到DBA_ALERT_HISTORY。QUESTION 24Which

21、 two sements are true?A.B.C.A role cannot be assigned external authentication. A role can be granted to other roles.A role can contain both system and object privileges.The predefined resource role includes the unlimited_tablespace privilege.All roles are owned by the sys user.The predefined connect

22、 role is always automatically granted to all new users at the time of their creation.Correct Answer: BC（：该题属于传统体系范畴内的知识点）QUESTION 25Identify three valid options for adding a pluggable database (PDB) to an existing multitenant container database (CDB).Use the CREATE PLUGGABLE DATABASE sement to creat

23、e a PDB using the files from the SEED.Use the CREATE DATABASE . . . ENABLE PLUGGABLE DATABASE sement to provi a PDB by copying file from the SEED.Use the DBMS_PDB package to clone an existing PDB.D. Use the DBMS_PDB package to plug an Oracle 12c non-CDB databaseo an existinB.E. Use the DBMS_PDB pack

24、age to plug an Oracle 11 g Release 2 (.0) non-CDBdatabaseo an existinB.Correct Answer: ACD Section: (none)Explanation:11g 的数据库不能直接到CDB，需要先升级到 12c 后才可以。QUESTION 26Your database supports a DSS workloadt involves the execution of complex queries:Currently, the library cache contains the ideal workload

25、forysis. You want toyze someof the queries for an applicationt are cachedhe library cache.What must you do to receivemendations about the efficient use of indexes andmaterialized views to improve query performance?A. Create a SQL Tuning Set (STS)t contains the queries cachedhe library cache and runt

26、he SQL Tuning Advisor (STA) on the workload capturedhe STS.B. Run the Automatic Workload Reitory Monitor (ADDM).C. Create an STSt contains the queries cachedhe library cache and run the SQLPerformanceyzer (SPA) on the workload capturedhe STS.D. Create an STSt contains the queries cachedhe library ca

27、che and run the SQL AcsAdvisor on the workload capturedhe STS.Correct Answer: D（：有关 SQL Tuning Advisor 和SQL Acs Advisor 的调优范围从 10g 版本就开始有了，必考。）QUESTION 27The following parameter are set for your Oracle 12c database instance:OPTIMIZER_CAPTURE_SQL_PLAN_BASELINES=FALSE OPTIMIZER_USE_SQL_PLAN_BASELINES=

28、TRUEYou want to manage the SQL plan evolution task manually. Examine the following steps:Set the evolve task parameters.Create the evolve task by using the DBMS_SPM.CREATE_EVOLVE_TASK function.3. Implement themendationshe task by using theDBMS_SPM.IMPLEMENT_EVOLVE_TASK function.4. Execute the evolve

29、 task by using the DBMS_SPM.EXECUTE_EVOLVE_TASK function.5. Report t he taske by using the DBMS_SPM.REPORT_EVOLVE_TASK function.Identify the correct sequence of steps: A. 2, 4, 5B. 2, 1, 4, 3, 5C. 1, 2, 3, 4, 5D. 1, 2, 4, 5Correct Answer: B Section: (none) ExplanationExplanation/Reference:（：* Evolvi

30、ng SQL Plan Baselines2. Create the evolve task by using the DBMS_SPM.CREATE_EVOLVE_TASK function. This function creates an advisor task to prepare the plan evolution of one ormore plans for a specified SQL sement. The input parameters can be a SQL handle, plan name or a list of plan names, time limi

31、t, task name, and description.1. Set the evolve task parameters. SET_EVOLVE_TASK_PARAMETERThis function updates the value of an evolve task parameter. parameter is TIME_LIMIT.his release, the only valid4. Execute the evolve task by using the DBMS_SPM.EXECUTE_EVOLVE_TASK function. This function execu

32、tes an evolution task. The input parameters can be thetask name, execution name, and execution description. If not specified, the advis name, which is returned by the function.3: IMPLEMENT_EVOLVE_TASKenerates theThis function implements allmendations for an evolve task. Essentially, this function is

33、equivalent to using ACCEPT_SQL_PLAN_BASELINE for allmended plans. Input parameters include task name, plan name, owner name, andexecution name.5. Report the taske by using the DBMS_SPM_EVOLVE_TASK function. This functiondisplays the results of an evolve task as a CLOB. Input parametersinclude the ta

34、sk name and section of the report to include.）QUESTION 28In a recent Automatic Workload Reitory (AWR) report for your database, you notice a highnumber of buffer busy waits. The database consists of locally managed tablespawith free listmanaged segments.On further investigation, you findt buffer bus

35、y waits is caused by contention on data blocks.Which option would you considerto decrease the wait event immediay?A.B.C.Decreasing PCTUSED Decreasing PCTFREEIncreasing the number of DBWN prosD. Using Automatic Segment Space Management (ASSM)E. Increasing db_buffer_cache based on the V$DB_CACHE_ADVIC

36、EmendationCorrect Answer: B Section: (none) ExplanationB 就可以解决问（：因为题目讲的是数据块，没有特别讲明是段头块，所有用题。如果是段头块，那么准确应该是 D）Automatic segment space management (ASSM) is a simpler and more efficient way *of managing space within a segment. It compley eliminates any need to specify and tune the pctused,freelists, an

37、d freelist groups storage parameters for schemaobjects createdhe tablespace. If any of these attributes are specified, they are ignored.Oracleroduced Automatic Segment Storage Management (ASSM) as a *replacement for traditional freelists management which useday linked-lists to manage freeblocks with

38、 tables and indexes. ASSM is commonly called bitmapfreelists because management.t is how Oracle implement theernal data structures for free blockNote:* Buffer busy waits are most commonly asso data buffool (db_cache_size, etc.).ted with segment header contention onside the* The most common remedies

39、for high buffer busy waits include database writer (DBWR)contention tuning, adding freelists (or ASSM), and adding missing indexes）Ame many modifications have been made to the employees table since the last time sisticswere gathered. To ensuret the cost-based optimizer is stillpicking the best plan,

40、 sistics should be gathered once again; however, t he user is concernedt new sistics will cause the optimizer to choose blans whenthe current ones are acceptable. The user can do the following:QUESTION 29 Examine thisd:SQL exec DBMS_SS.SET_TABLE_PREFS (SH, CUSTOMERS, PUBLISH, false);Which three seme

41、nts are true about the effect of thisd?Sistics collection is not done for the CUSTOMERS table when schema ss are gathered.Sistics collection is not done for the CUSTOMERS table when database ss are gathered.Any existing sistics for the CUSTOMERS table are still available to the optimizer at parse ti

42、me.Sistics gathered on the CUSTOMERS table when schema ss are gathered are stored as pending sistics.Sistics gathered on the CUSTOMERS table when database ss are gathered are stored as pending sistics.Correct Answer: CDE Section: (none) ExplanationExplanation/Reference:* SET_TABLE_PREFS ProcedureThi

43、s procedure is used to set the sistics preferen schema.of the specified tablehe specified* Exle:Using Pending SisticsAme many modifications have been made to the employees table since the last timesistics were gathered. To ensuret the cost-based optimizer is stillpicking the best plan, sistics should be g