OCP-1Z0-052-V8.02-10-14题

10. Examine the following statement that is used tomodify the constraint on the SALES table:

SQL> ALTER TABLE SALES MODIFY CONSTRAINT pk DISABLEVALIDATE;

Which three statements are true regarding the abovecommand? (Choose three.)

A.The constraint remains valid.

B.The index on the constraint is dropped.

C.It allows the loading of data into the table using SQL*Loader.

D.New data conforms to the constraint, but existing datais not checked

E.It allows the data manipulation on the table usingINSERT/UPDATE/DELETE SQL statements.

Answer: ABC

答案解析：

这道题，可用排除法来做，D:属于ENABLE NOVALIDATE，E，插入/更新/删除等操作

所以，只能选择ABC

题中是DISABLE VALIDATE即是针对新增的数据可以不符合约束，但是已经存在的数据要满足约束。

可以使用排除法，D是属于.ENABLENOVALIDATE

E,不能修改已经存在数据，错误。

C正确，是因为对新增的数据不限制，没有约束。

A正确，是因为DISABLE VALIDATE对现有的数据还有约束。

B呢？

约束条件状态

    简单来讲，DISABLE 和ENABLE 是针对新增的数据是否符合约束；NOVALIDATE 和VALIDATE是针对已经存在的数据是否符合约束。

    为了更好地处理数据可能暂时违反约束条件的情况，可将约束条件指定为不同的状态。可以启用(ENABLE) 或禁用(DISABLE) 完整性约束条件。

    如果启用约束条件，在数据库中输入或更新数据时就会检查数据。此时，禁止输入不符合约束条件规则的数据。

    如果禁用约束条件，则可以在数据库中输入不符合规则的数据。

    完整性约束条件可处于以下一种状态：

    • DISABLE NOVALIDATE --删除索引

    • DISABLE VALIDATE --删除索引

    • ENABLE NOVALIDATE--创建索引

    • ENABLE VALIDATE--创建索引

• DISABLE NOVALIDATE：不检查新数据和现有数据，因此这些数据可能不符合约束条件。当数据来自验证过的源，而且表是只读表时，通常会使用此状态。因此，不会将新数据输入表中。在已清理了数据的数据仓库环境中使用NOVALIDATE。此时不需要进行验证，因而可以节省很多时间。

• DISABLE VALIDATE：如果约束条件处于此状态，则不允许对有约束条件的列进行任何修改。因为如果在验证现有数据后，又允许将未经检查的数据输入表中，就会出现不一致的情况。通常，在必须验证现有数据但不进行修改时，以及不需要索引来提高性能时，可使用此状态。
• ENABLE NOVALIDATE：新数据符合约束条件，但现有数据处于未知状态。通常在确信表中只存在纯净数据和符合规则的数据的情况下使用该状态，此时不需要进行验证。但是，不允许将违反规则的新数据输入到系统中。
• ENABLE VALIDATE：新数据与现有数据均符合约束条件。这是约束条件的典型状态和默认状态。

官方原文：

    You can specify that a constraint isenabled (ENABLE) or disabled (DISABLE). If a constraint is enabled, data is checked as it is entered or updated in the database, and data that does not conform to the constraint is prevented from being entered. If a constraint is disabled, then data that does not conform can be allowed to enter the database.

    Additionally, you can specify that existing data in the table must conform to the constraint (VALIDATE). Conversely, if you specify NOVALIDATE, you are not ensured that existing data conforms.

An integrity constraint defined on a table can be in one of the following states:

• ENABLE, VALIDATE
• ENABLE, NOVALIDATE
• DISABLE, VALIDATE
• DISABLE, NOVALIDATE

11. Identify the memory component from which memory maybe allocated for:

1: Session memory for the shared server

2: Buffers for I/O slaves

3: Oracle Database Recovery Manager (RMAN) backup andrestore operations

A.Large Pool

B.Redo Log Buffer

C.Database Buffer Cache

D.Program Global Area (PGA)

Answer: A

答案解析：

大型池

数据库管理员可以配置称为“大型池”的可选内存区，以便为以下对象提供大型内存分配：

• 共享服务器的会话内存和Oracle XA 接口（在事务处理与多个数据库交互时使用）

•I/O 服务器进程

• OracleDB 备份和还原操作

通过从大型池中为共享服务器、Oracle XA 或并行查询缓冲区分配会话内存，Oracle DB 可以主要使用共享池来缓存共享SQL，并避免由于收缩共享SQL 高速缓存而导致的性能开销。

此外，用于Oracle DB 备份和还原操作、I/O 服务器进程以及并行缓冲区的内存以数百KB 的缓冲区进行分配。与共享池相比，大型池可以更好地满足此类大型内存请求。

大型池没有LRU 列表。它与共享池中的保留空间不同，后者与从共享池中分配的其它内存使用相同的LRU 列表。

12. Which is the correct description of a pinned bufferin the database buffer cache?

A.The buffer is currently being accessed.

B.The buffer is empty and has not been used.

C.The contents of the buffer have changed and must beflushed to the disk by the DBWn process.

D.The buffer is a candidate for immediate aging out andits contents are synchronized with the block

contents on the disk.

Answer: A

答案解析：

Every buffer has an access mode: pinnedor free(unpinned). A buffer is "pinned" in the cacheso that it does not age out of memory while a user session accesses it.Multiple sessions cannot modify a pinned buffer at the same time.

 

13. You have executed this command to change the size ofthe database buffer cache:

SQL> ALTER SYSTEM SET DB_CACHE_SIZE=2516582;

System altered.

To verify the change in size, you executed this command:

SQL> SHOW PARAMETER DB_CACHE_SIZE

NAME TYPE VALUE

------------------- ----------- ------------------

db_cache_size big integer 4194304 4M

Why is the value set to 4194304 and not to 2516582?

A.because 4194304 is the granule size

B.because 4194304 is the standard block size

C.because 4194304 is the largest nonstandard block sizedefined in the database

D.because 4194304 is the total size of data alreadyavailable in the database buffer cache

Answer: A

答案解析：

[email protected]> select 2516582/1024/1024from dual;

2516582/1024/1024

-----------------

2.39999962

[email protected]> select 4194304/1024/1024from dual;

4194304/1024/1024

-----------------

4

All SGA components allocate and deallocatespace in units of granules. OracleDatabase tracks SGA memory use in internal numbers of granules for each SGAcomponent.

The memory for dynamic components in theSGA is allocated in the unit of granules. The granule size is determined by theamount of SGA memory requested when the instance starts. Specifically, thegranule size is based on the value of the SGA_MAX_SIZE initialization parameter. Table 6-1shows the granule size for differentamounts of SGA memory.

Table 6-1 Granule Size

SGA Memory Amount	Granule Size
Less than or equal to 1 GB	4 MB
Greater than 1 GB and less than or equal to 8 GB	16 MB
Greater than 8 GB and less than or equal to 16 GB	32 MB
Greater than 16 GB and less than or equal to 32 GB	64 MB
Greater than 32 GB and less than or equal to 64 GB	128 MB
Greater than 64 GB and less than or equal to 128 GB	256 MB
Greater than 128 GB	512 MB

You can query the V$SGAINFO view to see the granule size that is being used by aninstance. The same granule size is used for all components in the SGA.

If you specify a size for a component thatis not a multiple of granule size, Oracle Database rounds the specified size upto the nearest multiple. For example, if the granule size is 4 MB and youspecify DB_CACHE_SIZE as 10 MB, the database actually allocates 12 MB.

如果指定的size不是granulesize的整数倍，则rounds到整数倍，例如，如果granule size为4M，指定DB_CACHE_SIZE as 10 MB，不是4的整数倍，则实际上DB_CACHE_SIZE as 为12M。

一下是我的环境，因为我的sga大于1G，故granule size为16M。

[email protected]> desc V$SGAINFO

Name Null? Type

------------------------------------------------------------- ------------------------------------

NAME VARCHAR2(32)

BYTES NUMBER

RESIZEABLE VARCHAR2(3)

[email protected]>select * from v$sgainfo;

NAME BYTES RES

------------------------------------------ ---

Fixed SGA Size 2230952 No

Redo Buffers 20078592 No

Buffer Cache Size 1862270976 Yes

Shared Pool Size 570425344 Yes

Large Pool Size 16777216 Yes

Java Pool Size 16777216 Yes

Streams Pool Size 16777216 Yes

Shared IO Pool Size 0 Yes

Granule Size16777216 No

Maximum SGA Size 2505338880 No

Startup overhead in Shared Pool 117947968No

Free SGA Memory Available 0

12 rows selected.

14. These are points that describe the contents ofdifferent memory components:

1: Descriptive information or metadata about schemaobjects that are queried by using SQL statements

2: The run-time area for data manipulation language (DML)or data definition language (DDL) statements

3: Results of SQL queries and PL/SQL functions

4: Executable forms of SQL cursors, PL/SQL programs, andJava classes

5: The information necessary to reconstruct changes madeto the database by a transaction

Which of these will be stored in the Shared Pool if the necessary configurations are done?

A.1 and 2

B.2 and 5

C.1, 3, and 4

D.3, 4, and 5

E.1, 2, 3, and 4

Answer:C  

答案解析：

Shared Pool

    The shared pool caches various types of program data. For example, the shared poolstores parsed SQL, PL/SQL code, system parameters, and data dictionary information.The shared pool is involved in almost every operation that occurs in thedatabase. For example, if a user executes a SQL statement, then Oracle Databaseaccesses the shared pool.

从下图可知：

 

The library cache is a sharedpool memory structure that stores executable SQL and PL/SQL code.

The data dictionary is accessed so oftenby Oracle Database that the following special memory locations are designatedto hold dictionary data:

• Data dictionary cache

This cache holds information about database objects. The cache is also known as the row cache because it holds dataas rows instead of buffers.

• Library cache

          Allserver processes share these caches for access to data dictionary information.

he server result cache containsthe SQL query result cache andPL/SQL function result cache, whichshare the same infrastructure.