一起学OCP：oracle-082题库及解析(21-40)

历史文章

一起学OCP：oracle-082题库及解析(1-20)
一起学OCP：oracle-082题库及解析(21-40)

---

第21题

In your database,you want to ensure that idle sessions that are blocking active are automatically terminated after a specified period of time.
How would you accomplish this?

A) Setting a metric threshold
B) Implementing Database Resource Manager
C) Enabling resumable timeout for user sessions
D) Decreasing the value of the IDLE_TIME resource limit in the default profile.
正确答案【B】

解析：题目的意思是你希望阻塞活动的空闲session在指定时间后自动终止，这个需求需要通过Resource Manager来实现。
相关知识点链接

第22题

Evaluate the following SQL statement:
SQL> select cust_id,cust_last_name “Last name” FROM customers WHERE country_id=10 UNION SELECT cust_id CUST_NO,cust_last_name FROM customers WHERE couontry_id=30

这里是引用
A) ORDER BY "Last name "
B) ORDER BY 2,cust_id
C) ORDER BY CUST_NO
D) ORDER BY 2,1
E) ORDER BY “CUST_NO”
正确答案【ABD】

第23题

Which statement is true about profiles?(Choose one)

A) Resource limits specified in a profile assigned to a user are always enabled
B) A profile can be assigned only to one user
C) A user can exist without any profile
D) Password managedment using profiles is always enabled.
正确答案【D】
解析：Resource_limit是parameter，不是在profile里设置的，所以A错；一个profile文件可以指定给多个用户，所以B错；任何oracle用户必然属于某一个profile，未指定的话则属于default，所以C错。D是正确的，profile里密码管理策略总是enable的

第24题

Which three instance situations are possible with the Oracle Database server without multi-tenant？（Choose three)

A) two or more instances on separate servers all associated with one database
B) one instance on one server associated with one database
C) one instance on one server associated with two or more databases on the same server
D) one instance on one server not associated with any database
E) one instance on one server associated with two or more databases on separate servers
正确答案【ABD】
解析：multi-tenant 多租户，是从ORACLE12c开始引进的。选项A两个或者多个实例都与同一个数据库关联，适合RAC的场景；相对的选项B适合单实例的场景；选项D适合实例和数据库不关联的场景。实例不可能跟多个database关联，所以CE错

第25题

Which two statements are true regarding single row functions? (Choose two.)

A. MOD : returns the quotient of a division.
B. TRUNC : can be used with NUMBER and DATE values.
C. CONCAT : can be used to combine any number of values.
D. SYSDATE : returns the database server current date and time.
E. INSTR : can be used to find only the first occurrence of a character in a string.
F. TRIM : can be used to remove all the occurrences of a character from a string.
正确答案【BD】
解析：A：返回除法的商；C：可用于组合任意数量的值；E: 只能用于查找字符串中第一个出现的字符；F：可用于从字符串中删除所有出现的字符），所以ACEF错
单行函数的知识卡片

第26题

Evaluate these commands which execute successfully:

Which two statements are true about the ORD_ITEMS table and the ORD_SEQ sequence? (Choose two.)

A. If sequence ORD_SEQ is dropped then the default value for column ORD_NO will be NULL for rows inserted into ORD_ITEMS
B. Any user inserting rows into table ORD_ITEMS must have been granted access to sequence ORD_SEQ
C. Column ORD_NO gets the next number from sequence ORD_SEQ whenever a row is inserted into ORD_ITEMS and no explicit value is given for ORD_NO
D. Sequence ORD_SEQ cycles back to 1 after every 5000 numbers and can cycle 20 times
E. Sequence ORD_SEQ is guaranteed not to generate duplicate numbers
正确答案：【BC】
解析：删除序列不一定会将ORD_ITEMS的default值设置为null，所以A错；CACHE是为了提升性能，预先生成的序列值，因为有CYCLE，所以到达100000时会返回起点重复执行，跟能够循环几次没有关系，所以D错。因为使用了了cycle，所以会返回起点重复执行，所以E错。
序列的知识卡片

第27题 单行函数

Which three statements are true regarding single-row functions?(Choose three)

A) The data type,can be differeent from the data type of the argument that is referenced.
B) They can return multiple values of more than one data type
C) They can accept only one argument.
D) They can be nested up to only two levels
E) They can be used in SELCT,WHERE,and ORDER BY clauses.
F) They can accept column names,expressions,variable names,or a user-supplied constants as arguments.
正确答案：【AEF】

解析：单行函数的返回类型可以和参数的引用类型不同，可以用于SELECT WHERE ORDER BY中，可以接受列名、表达式、变量名或者用户提供的常量名，所以AEF对B错；单行函数可以接受一个或者多个入参，所以C错，oracle中单行函数可以有两层以上的嵌套，所以D错。
单行函数参考

第28题

Which four account management capabilities can be configured using Oracle profiles? (Choose four.)

A) the number of hours for which an account is locked after the configured number of login attempts has been reached
B) the number of days for which an account may be inactive before it is locked
C) the maximum amount of CPU time allowed for a user’s sessions before their account is locked
D) the ability to prevent a password from ever being reused
E) the number of password changes required within a period of time before a password can be reused
F) the number of days for which an account is locked after the configured number of login attempts has been reached
G) the maximum number of sessions permitted for a user before the account is locked

正确答案：【BDEF】

第29题 行压缩

You want to use table compression suitable for OLTP that will:

1. Compress rows for all DML statements on that table
2. Minimize the overheads associated with compression
   Which compression option is best suited for this?

A. COLUMN STORE COMPRESS FOR QUERY LOW
B. ROW STORE COMPRESS BASIC
C. COLUMN STORE COMPRESS FOR ARCHIVE LOW
D. COLUMN STORE COMPRESS FOR ARCHIVE HIGH
E. ROW STORE COMPRESS ADVANCED
正确答案：【E】
答案解析：题目要求为所有的DML语句执行压缩操作，并且将与压缩相关的开销降到最低，最其实就是要求使用行压缩。
行压缩相关的知识点

第30题

Which two statements are true about space-saving features in an Oracle Database? (Choose two.)

A. Private Temporary Tables (PTTS) store metadata in memory only
B. An index created with the UNUSABLE attribute has no segment
C. If they exist for a session, Private Temporary Tables (PTTs) are always dropped at the next COMMIT OR ROLLBACK statement
D. An index that is altered to be UNUSABLE will retain its segment
E. A table that is truncated will always have its segment removed
正确答案：【AB】

第31题 ASSM

Which two statements are true regarding Oracle database space management within blocks managed by Automatic Segment Space Management (ASSM)? (Choose two.)

A. PCTFREE defaults to 10% for all blocks in all segments for all compression methods
B. ASSM assigns blocks to one of four fullness categories based on what percentage of the block is allocated for rows
C. Update operations always attempt to find blocks with free space appropriate to the length of the row being updated
D. Insert operations always attempt to find blocks with free space appropriate to the length of the row being inserted
E. A block will always be eligible for inserts if the row is short enough to fit into the block
正确答案：【AD】

第32题

ou are administering a database and you receive a requirement to apply t he following restrictions:

1. A connection must be terminated after four unsuccessful login attempts by user.
2. A user should not be able to create more than four simultaneous sessions.
3. User session must be terminated after 15 minutes of inactivity.
4. Users must be prompted to change their passwords every 15 days.

How would you accomplish these requirements?

A. by granting a secure application role to the users
B. by creating and assigning a profile to the users and setting the REMOTE_OS_AUTHENT parameter to FALSE
C. By creating and assigning a profile to the users and setting the FAILED_LOGIN_ATTEMPTS parameter to 4
D. By Implementing Fine-Grained Auditing (FGA) and setting the REMOTE_LOGIN_PASSWORD_FILE parameter to NONE.
E. By implementing the database resource Manager plan and setting the SEC_MAX_FAILED_LOGIN_ATTEMPTS parameters to 4.
正确答案：【C】

第33题

A user establishes a connection to a database instance by using an Oracle Net connection. You want to ensure the following:
1.The user account must be locked after five unsuccessful login attempts. 2.Data read per session must be limited for the user.
3. The user cannot have more than three simultaneous sessions.
4. The user must have a maximum of 10 minutes session idle time before being logged off automatically.
How would you accomplish this?

A. by granting a secure application role to the user
B. by implementing Database Resource Manager
C. by using Oracle Label Security options
D. by assigning a profile to the user
正确答案：【D】

第34题 DEFAULT profile

Which statement is true about the DEFAULT profile?(Choose one)

A) It can be dropped and recreated.
B) It must be explicitly assigned to a user.
C) The values assigned to the resource limits and password parameters in the default profile can be altered.
D) A different DEFAULT profile can be created for each user in a database.
正确答案：【C】

第35题

Examine this description of the PRODUCTS table:

Rows exist in this table with data in all the columns. You put the PRODUCTS table in read-only mode.
Which three commands execute successfully on PRODUCTS?

A) ALTER TAELE products DROP COLUMN expiry_date;
B) CREATE INDEX price_idx on products (price);
C) ALTER TABLE products SET UNUSED(expiry_date);
D) TRUNCATE TABLE products;
E) ALTER TABLE products DROP UNUSED COLUMNS
F) DROP TABLE products
正确答案：【BEF】

第36题

Which two statements are true about the ORDER BY clause when used with a SQL statement containing a SET operator such as UNION? (Choose two.)

A. Column positions must be used in the ORDER BY clause
B. Only column names from the first SELECT statement in the compound query are recognized
C. The first column in the first SELECT of the compound query with the UNION operator is used by default to sort output in the absence of an ORDER BY clause
D. Each SELECT statement in the compound query must have its own ORDER BY clause
E. Each SELECT statement in the compound query can have its own ORDER BY clause
正确答案：【BC】

第37题 PROFILE

Examine these statements:

ALTER SYSTEM SET RESOURCE_LIMIT=FALSE SCOPE=BOTH;
System altered.

CREATE PROFILE dev_users LIMIT SESSIONS_PER_USER 3
IDLE_TIME 10
FAILED LOGIN ATTEMPTS 3
PASSWORD_VERIFY_FUNCTION ora12c_verify_function; 
Profile altered.
ALTER USER hr PROFILE dev_users; 
User altered.

Which three are true about the effects of executing these statements?

A) User HR is permitted to create more than three concurrent sessions.
B) The password complexity will not enforced on the HR account.
C) Password complexity checking is enforced for user HR.
D) The HR account will get locked by Oracle after ten consecutive failed login attempts.
E) The HR account will be locked after three consecutive failed login attempts.
F) User HR sessions will terminate after ten minutes of continuous inactivity.
正确答案：【ACE】

第38题

Which two statements are true regarding single row functions? (Choose two.)

A. MOD : returns the quotient of a division.
B. TRUNC : can be used with NUMBER and DATE values.
C. CONCAT : can be used to combine any number of values.
D. SYSDATE : returns the database server current date and time.
E. INSTR : can be used to find only the first occurrence of a character in a string.
F. TRIM : can be used to remove all the occurrences of a character from a string.
正确答案：【BD】

第39题

Which three are true about dropping columns from a table?

A) A column can be removed only if it contains no data.
B) A column drop is implidtly committed
C) A column that is referenced by another column in any other table cannot be dropped.
D) A column must be set as unused before it is dropped from a table. E A primary key column cannot be dropped.
F) Multiple columns can be dropped simultaneously using the ALTER TABLE command.
正确答案：【BCF】

第40题

Which two statements are true about the PMON background process? (Choose two.)

A. It registers database services with all local and remote listeners known to the database instance
B. It frees resources held by abnormally terminated processes
C. It records checkpoint information in the control file
D. It frees unused temporary segments
E. It kills sessions that exceed idle time
正确答案：【BE】