Oracle 10g New Features - Ch 8 - Security and SQL Enhancements

Chapter 8 - Security and SQL Enhancements


Review Questions
1. Column-level privacy enforces row-level access control only when a statement accesses security relevant columns. Which procedure is used to set up column-level VPD in Oracle 10g?
A. DBMS_VPD.ADD_POLICY
B. DBMS_RLS.ADD_POLICY
C. DBMS_POLICY.ADD_RLS
D. DBMS_POLICY.ADD_VPD

----
Ans: B.
The DBMS_RLS (row level security) package is used for fine-grained auditing. The ADD_POLICY procedure has a new parameter, SEC_RELEVANT_COLUMNS, that specifies the column names to be secured.


2. In fine-grained auditing, which one of the following is not a valid statement type?
A. DELETE
B. INSERT
C. UPDATE
D. MERGE
E. INDEX
F. SELECT

---
Ans: D.
MERGE is not a valid statement type in DBMS_RLS.ADD_POLICY. MERGE is enforced by the underlying INSERT and UPDATE statement types. By default the policy applies to SELECT, INSERT, UPDATE, and DELETE. Default does not apply to INDEX.

3. Which policy type in fine-grained auditing reevaluates the policy function at statement execution for context changes since the last use of the cursor and looks for a cached predicate generated by the same policy function of the same policy type in the same session?
A. STATIC
B. DYNAMIC
C. CONTEXT_SENSITIVE
D. SHARED_STATIC
E. SHARED_CONTEXT_SENSITIVE

---
Ans: E.
For shared policy types, Oracle server looks for cached predicate generated by the same policy function. For STATIC policies, VPD always enforces the same predicate for access control regardless of the user. STATIC policies are executed once, and the predicate result is cached in the SGA. For CONTEXT_SENSITIVE policies, VPD reevaluates the policy function at statement execution time for context changes and executes the policy function if it detects any context changes.
E.

4. Which table or view contains audit trail from standard database auditing and fine-grained auditing?
A. AUD$
B. FGA_LOG$
C. DBA_AUDIT_TRAIL
D. DBA_COMMON_AUDIT_TRAIL

----
Ans: D.
In Oracle 10g, you have uniform audit trail, which means the same type of information is available for standard and fine-grained audit trails. The standard audit trail information is available in AUD$ and DBA_AUDIT_TRAIL. The fine-grained audit trail information is available in FGA_LOG$ and DBA_FGA_AUDIT_TRAIL. DBA_COMMON_AUDIT_TRAIL has information on both standard and fine-grained auditing.

5. In the MERGE statement, you can optionally use the DELETE clause when which of the following is true?
A. In the WHEN MATCHED section
B. In the WHEN NOT MATCHED section
C. In both the WHEN MATCHED and WHEN NOT MATCHED sections
D. By itself, without an INSERT or UPDATE clause

----
Ans: A.
Oracle 10g introduced the DELETE clause in the WHEN MATCHED THEN UPDATE section of the MERGE statement. The DELETE clause can optionally delete rows that match the ON condition and the WHERE condition.
B.

6. Which clause in Oracle 10g makes the sparse data dense?
A. MODEL
B. PARTITION BY
C. PARTITION BY…OUTER JOIN
D. PARTITIONED OUTER JOIN

----
Ans: C.
The partitioned outer join clause makes sparse data dense. It is used to replace missing values, mostly along the time dimension. The Oracle database logically partitions the rows in the query based on the expression in the PARTITION BY clause.

7. The MODEL clause enables spreadsheet-like array computations using SQL. Choose the three groups that define the multidimensional array.
A. Facts
B. Measures
C. Partitions
D. Blocks
E. Dimensions

---
Ans: B, C, E.
Partitions, measures, and dimensions are the three groups. Partitions define logical blocks of the result set. Measures typically contain numerical data and are analogous to the measures of a fact table in a star schema. Dimensions identify each measure cell within a partition.

8. Which procedure can obtain the materialized views definition that is eligible for a fast refresh?
A. DBMS_MVIEW.EXPLAIN_MVIEW
B. DBMS_MVIEW.EXPLAIN_REWRITE
C. DBMS_MVIEW.TUNE_MVIEW
D. DBMS_ADVISOR.TUNE_MVIEW
E. DBMS_ADVISOR.EXPLAIN_MVIEW

----
Ans: D.
The DBMS_ADVISOR.TUNE_MVIEW procedure shows how to restate the materialized view in a way that is more advantageous for fast refreshes and query rewrites. It also shows how to fix materialized view logs and to enable query rewrites. Using the DBMS_MVIEW.EXPLAIN_MVIEW procedure, you can determine if a materialized view is fast refreshable and what types of query write you can perform. You can then use the DBMS_MVIEW.EXPLAIN_REWRITE procedure to learn why a query failed to rewrite or which MV will be used to rewrite.

9. Which physical attribute is ignored by the Oracle database when creating a table?
A. INITRANS
B. MAXTRANS
C. PCTFREE
D. PCTUSED
E. STORAGE

---
Ans: B.
In Oracle 10g, objects are preconfigured for maximum concurrency, and the system allows for up to 255 concurrent update transactions per block, depending on the available space.



10. Which four methods enable resumable space allocation?
A. ALTER SYSTEM ENABLE RESUMABLE
B. ALTER SESSION ENABLE RESUMABLE
C. ALTER SYSTEM SET RESUMABLE_TIMEOUT = 72000
D. ALTER SYSTEM SET RESUMABLE_TIMEOUT = 72000 SCOPE=BOTH
E. ALTER SESSION SET RESUMABLE_TIMEOUT = 72000

---
Ans: B, C, D, E.
The RESUMABLE_TIMEOUT parameter can be set at the instance level to enable resumable timeout. The parameter is dynamic and can be modified using ALTER SYSTEM and ALTER SESSION. The ALTER SESSION ENABLE RESUMABLE syntax used in Oracle 9i is still supported in Oracle 10g.

11. Which is the new syntax available to connect to a database using the Oracle 10g client?
A. username/password@hostname:port:service_name
B. username/password@//hostname:port:service_name
C. username/password@//hostname/port/service_name
D. username/password@//hostname:port/service_name

---
Ans: D.
The new syntax does not rely on configuration files such as tnsnames.ora. The hostname is the only mandatory part after @. The default port is 1521, and the default service_name is whatever the host is. The new connect identifier can be used only on platforms that support TCP/IP.


12. What is the maximum supported size for a LOB column?
A. 128GB
B. 4GB
C. 128TB
D. Unlimited

---
Ans: C.
For the database with 32KB block size, the maximum size for a LOB column is 128TB. For a 2KB block size database, the maximum LOB size is 8TB. You can use the function DBMS_LOB.GET_STORAGE_LIMIT to find the storage limit for the database.

13. Which function is best suitable to find if the phone numbers in a table follow the nnn-nnn-nnnn format, where n is a digit?
A. SUBSTR
B. REGEXP_SUBSTR
C. LIKE
D. REGEXP_LIKE

---
Ans: D.
Oracle 10g supports regular expressions using the functions REGEXP_LIKE, REGEXP_SUBSTR, REGEXP_INSTR, and REGEXP_REPLACE. Regular expressions are best suited for pattern matching.

14. Which parameter would you set to enable case-insensitive sorts?
A. NLS_SORT
B. NLS_LANGUAGE
C. NLS_SORT_CI
D. NLS_COMP

---
Ans: A.
The NLS_SORT parameter specifies the linguistic sort name. Adding the suffix _CI to the linguistic sort name enables case-insensitive sorts. For accent-insensitive sorts, add suffix _AI as in, NLS_SORT= JPAPANESE_CI or NLS_SORT=BINARY_AI.

15. Which option in the DBMS_LOGMNR.START_LOGMNR procedure automatically adds redo log files for mining if the redo log files belong to the same database where mining is done?
A. DBMS_LOGMNR.AUTO_ADD_REDO
B. DBMS_LOGMNR.CONTINUOUS_MINE
C. DBMS_LOGMNR.STARTTIME
D. DBMS_LOGMNR.STARTSCN

---
Ans: B.
You use the DBMS_LOGMNR.CONTINUOUS_MINE option with the STARTIME or STARTSCN parameter to add redo log files automatically for mining.

16. Which data dictionary view contains information on the progress of the rollback transactions the Oracle server (SMON) is recovering?
A. V$FAST_START_SERVERS
B. V$FAST_START_TRANSACTIONS
C. V$FAST_START_ROLLBACK
D. V$WAITSTAT

--
Ans B. The V$FAST_START_TRANSACTIONS view has information about the transactions the Oracle server is recovering and recovered.

17. Oracle 10g is shared-server aware by default. Which parameter replaces the MTS_SESSIONS parameter in Oracle 10g?
A. The SESSIONS parameter.
B. The SHARED_SESSIONS parameter.
C. The SHARED_SERVER_SESSIONS parameter.
D. Oracle 10g has no equivalent parameter, because the database is shared-server aware by default.

--
Ans: C.
The MTS_SESSIONS parameter is replaced by the SHARED_SERVER_SESSIONS parameter in Oracle 10g. The dynamic parameter SHARED_SERVERS can be adjusted to enable and disable the shared-server architecture.

18. Which data dictionary view contains information on the traces enabled in the database?
A. V$SESSION
B. V$TRACE
C. V$SESSION_TRACE
D. DBA_ENABLED_TRACES

---
Ans: D.
You can determine the DBA_ENABLED_TRACES view to determine the traces enabled. It also shows the trace type and whether waits and binds are being included in the trace file.

19. Choose three dimensions where statistics aggregation can be enabled using the DBMS_MONITOR package.
A. Session
B. Client Identifier
C. Service name
D. Service name, module name, action name
E. Instance name

--
Ans: B, C, D.
Statistics aggregation is provided by default at the session, instance, and SQL levels. Additional statistics aggregation can be enabled by using the DBMS_MONITOR.CLIENT_ID_STAT_ENABLE and DBMS_MONITOR.SERV_MOD_ACT_STAT_ENABLE procedures.

20. Which one of the following is a valid use of the quote operator to assign John's book to a variable in PL/SQL?
A. VS := quoteJohn's Bookendquote
B. VS := bq'John's Book'eq
C. VS := q'[John's Book]'
D. VS := q'[John's Book]'q

---
Ans C.
The quote operator eliminates the need for additional quotation strings in character literals by choosing your own quotation mark delimiter. The quotation mark delimiter is defined using the quote operator q followed by single quote.