OCP 6 Miscellaneous New Features

Card Set Information

Author:
Tralala
ID:
213625
Filename:
OCP 6 Miscellaneous New Features
Updated:
2013-04-15 09:08:58
Tags:
OCP Miscellaneous New Features
Folders:

Description:
OCP 6 Miscellaneous New Features
Show Answers:

Home > Flashcards > Print Preview

The flashcards below were created by user Tralala on FreezingBlue Flashcards. What would you like to do?


  1. Which four columns are available in the DBA_TEMP_FREE_SPACE view?
    TABLESPACE_NAME, TABLESPACE_SIZE, ALLOCATED_SPACE, and FREE_SPACE
  2. If result-caching is enabled for a PL/SQL function, where are cached results stored?
    In the SGA
  3. When using client-side result caching, where are query results cached?
    In OCI client memory
  4. Which clause of the ALTER TABLESPACE statement can you specify to release physical space for all tempfiles in a locally managed temporary tablespace?
    The SHRINK SPACE clause
  5. Which action should you take after an online redefinition of a table that contains a materialized view?
    Perform a refresh because rowid logs are invalidated
  6. Which three values are valid for the RESULT_CACHE_MODE parameter?
    AUTO, MANUAL, and FORCE
  7. What is the result if you attempt to make an index invisible and include additional clauses in the ALTER INDEX statement?
    An error results, and the index remains visible.
  8. If you specify a value for both the CLIENT_RESULT_CACHE_SIZE and the OCI_RESULT_CACHE_MAX_SIZE parameters, what is the result?
    The OCI_RESULT_CACHE_MAX_SIZE will take precedence.
  9. Which clause should you use in a CREATE FUNCTION statement to enable result-caching for the function?
    The RESULT_CACHE clause
  10. Which package can you use to manage memory in the query result cache?
    The DBMS_RESULT_CACHE package
  11. If you create a global temporary table with a TABLESPACE clause, in which location are indexes created on the table stored?
    In the tablespace specified in the TABLESPACE clause
  12. If you set the RESULT_CACHE_MODE initialization parameter to MANUAL, which action must you take to cache results from a query?
    Specify the RESULT_CACHE hint in the query.
  13. If result-caching is enabled for a PL/SQL function, what happens if the function is called and results from a previous function call with identical parameters do not exist in the cache?
    The function is re-executed, and the results are returned to the caller.
  14. When you shrink a temporary tablespace using either the SHRINK SPACE or SHRINK TEMPFILE clause, is the operation performed online or offline?
    Online
  15. If you create a result-cached PL/SQL function and specify the RELIES_ON clause, what is the result if the structure of the database object specified in the RELIES_ON clause is changed?
    The cached results become invalid.
  16. To which tablespace group will the temporary tablespace belong if you create a temporary tablespace by including the TABLESPACE GROUP clause in the CREATE TEMPORARY TABLESPACE statement?
    None
  17. Which data dictionary view can you query to display temporary space usage for a tablespace, including the total space and free space available?
    The DBA_TEMP_FREE_SPACE view
  18. Which clause can you include in an ALTER INDEX statement to make an invisible index visible?
    The VISIBLE clause
  19. What does the RESULT_CACHE_MAX_RESULT initialization parameter specify?
    The maximum amount of cache memory that a single result can use
  20. Which clause can you specify with the LOCK TABLE statement so that the statement will wait for a period of time to acquire a lock before erroring out?
    The WAIT clause
  21. If result-caching is enabled for a PL/SQL function, are the cached results available to multiple sessions or only a single session?
    Multiple sessions
  22. To which value should you set the RESULT_CACHE_MODE parameter if you want the optimizer to cache all query results?
    FORCE
  23. What information is captured by Fine-Grained Auditing (FGA) policy when it is used to audit Data Manipulation Language (DML) statements?
    The Structured Query Language (SQL) operations being performed and the SQL statements used in the audited operations
  24. What does the ALLOCATED_SPACE column of the DBA_TEMP_FREE_SPACE view contain?
    The temporary tablespace's total allocated space, including what is currently used and what is available for reuse
  25. For what purpose would you use the INVALIDATE procedure of the DBMS_RESULT_CACHE package?
    To make cached results that depend on specific objects invalid
  26. Why will an error be raised if you attempt to create a temporary tablespace with the same name as an existing temporary tablespace group?
    Because the temporary tablespace and the temporary tablespace group names are the same, and must be unique
  27. What is the minimum number of temporary tablespaces contained in a temporary tablespace group?
    One
  28. How can you determine if a query's performance would be improved by not using an index, without dropping the index?
    Make the index invisible by using the INVISIBLE clause of the ALTER INDEX statement.
  29. If result-caching is enabled for a PL/SQL function, what happens if the function is called and results from a previous function call with identical parameters exist in the cache?
    The function is not re-executed, and the cached results are returned to the caller.
  30. What is the result of setting the RESULT_CACHE_MAX_SIZE initialization parameter to 0?
    The SQL query result cache is disabled.
  31. If you do not specify a TABLESPACE clause with the CREATE GLOBAL TEMPORARY TABLE statement, in which location are rows in the temporary table stored by default?
    The default temporary tablespace of the user creating the table
  32. Are visible indexes maintained during DML operations?
    Yes
  33. Which new Oracle 11g feature allows a DBA to easily toggle a table between updateable and non-updateable?
    Read-only tables
  34. Which situation causes cached query results to become invalid?
    When data is changed in any of the database objects used to create the results
  35. Which initialization parameter is used to configure how caching occurs in the SQL query result cache?
    The RESULT_CACHE_MODE parameter
  36. In an ALTER TABLESPACE statement, what is the difference between using the SHRINK SPACE clause and the SHRINK TEMPFILE clause?
    SHRINK SPACE shrinks the entire tablespace and SHRINK TEMPFILE shrinks only the specified file.
  37. If you call a PL/SQL function for which result-caching is enabled, and it generates an unhandled exception with no results, what is cached?
    Nothing; the exception result is not cached.
  38. What happens if you attempt to shrink a tablespace and there are sort operations that are currently using extents allocated above the targeted size?
    Oracle waits until these extents are released and then continues with the shrink.
  39. Which initialization parameter can you specify to cause DDL commands to wait for DML locks to be released before erroring out with a resource busy error?
    The DDL_LOCK_TIMEOUT initialization parameter
  40. When performing online table redefinition, what is the result on the table's triggers if they are not logically affected?
    They are invalidated and automatically revalidated the next time a DML statement is executed on the table.
  41. If you add a column to a table, what is the effect on dependent objects?
    They remain valid.
  42. If you set the OCI_RESULT_CACHE_MAX_SIZE initialization parameter to a nonzero value, what is the effect on performance?
    It is improved because query results are cached in OCI client memory and fewer round trips to the server are needed.
  43. When creating a result-cached PL/SQL function, which optional clause can you specify to identify any tables or views on which the results of the function depend?
    The RELIES_ON clause
  44. Without making an index invisible, how can you cause the optimizer to ignore the index when running a query?
    Include an optimizer hint in the query.
  45. When using SQL query result cache, what is the result if a user in a different session executes an identical query for which results have been cached by a user in another session?
    The results are retrieved from cache.
  46. What does the CLIENT_RESULT_CACHE_SIZE specify?
    The maximum number of bytes in the client per-process result set
  47. How are fine-grain dependencies tracked?
    Automatically
  48. The SHRINK SPACE clause of the ALTER TABLESPACE statement is only allowed for which type of tablespaces?
    Locally managed temporary tablespaces
  49. When performing online table redefinition, what is the effect on views, synonyms, and PL/SQL packages if they are not logically affected?
    They remain valid.
  50. What SQL statements can be audited using Fine-grained auditing (FGA) policy?
    SELECT, INSERT, UPDATE, MERGE, and DELETE statements
  51. When not using a client configuration file, which action should you take to enable the client result cache?
    Set the CLIENT_RESULT_CACHE_SIZE initialization parameter to a nonzero value
  52. If you have not specified the OCI_RESULT_CACHE_MAX_SIZE initialization parameter, to what value should you set the CLIENT_RESULT_CACHE_SIZE initialization parameter to enable the client query cache feature?
    A nonzero value
  53. Results for queries based on which two types of tables cannot be cached?
    Temporary tables and dictionary tables
  54. Which clause can you include with the CREATE GLOBAL TEMPORARY TABLE statement to explicitly specify a nondefault temporary tablespace to use for the table and its indexes?
    The TABLESPACE clause
  55. What does the TABLESPACE_SIZE column of the DBA_TEMP_FREE_SPACE view contain?
    The temporary tablespace's total size in bytes
  56. When shrinking a temporary tablespace using either the SHRINK SPACE or SHRINK TEMPFILE clause, what does the KEEP clause specify?
    The smallest size to which the tablespace or tempfile can be shrunk
  57. Which type of auditing technique enables you to capture the SQL statements that query or manipulate the data?
    Fine-Grained Auditing (FGA)
  58. When using the SQL query result cache, in which location are the cached results stored?
    The shared pool in the SGA
  59. Must a temporary tablespace be locally managed or dictionary managed to use the SHRINK SPACE clause to shrink all tempfiles in the tablespace?
    Locally managed

What would you like to do?

Home > Flashcards > Print Preview