Oracle OCP 9i to 11g Miscellaneous New Features

Card Set Information

Author:
Tralala
ID:
207461
Filename:
Oracle OCP 9i to 11g Miscellaneous New Features
Updated:
2014-06-02 11:04:45
Tags:
Oracle OCP 9i 11g Miscellaneous New Features
Folders:

Description:
Oracle OCP 9i to 11g 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. What feature allows a table to be redefined while online?
    Enhanced online table redefinition
  2. Is a table locked during Enhanced online table redefinition?
    Yes, but only for a very short time, depending on the table complexity.
  3. Can a table be redefined if it has logs or MVs?
    Yes, since 11g
  4. What happens to dependent PL/SQL packages during online table redefinition?
    • They are not affected, recompilation is NOT required.
    • This is true for all table-dependent objects except triggers
  5. What feature allows for the creation of policies that define specific conditions that trigger an audit record?
    Fine Grained Auditing
  6. What is required to create a fine-grained auditing policy?
    Execute privilege on DBMS_FGA package
  7. What can Fine Grained Auditing audit?
    • Among Others...
    • Table access during a specific time
    • Access from a specific IP
    • Selecting or Updating of a specific column
  8. What advantages does Fine Grained Auditing have over standard auditing?
    • Performs a Boolean check, record only created is a condition is met
    • Can capture the SQL that triggered the audit (with bind variables)
    • Can audit columns with sensitive data
    • Can create event handler to send an email alert
  9. What is required to capture SQL in fine grained audit?
    DBMS_FGA.ADD_POLICY to DB+EXTENDED or XML+EXTENDED
  10. What Initialization parameter are required to enable fine grained auditing?
    None
  11. What relation does Fine Auditing Policy have with Audit_Trail?
    None
  12. What formats can the audit_trail be specified for fine-grained auditing?
    • XML
    • XML+EXTENDED
    • DB
    • DB+EXTENDED
  13. Where are DB and DB+EXTENDED audit records written?
    SYS.FGA_LOG$
  14. Where are XML and XML+EXTENDED audit records written?
    OS Level in XML format
  15. How do you modify a fine grained audit policy after it's created?
    You can't, need to drop and recreate it.
  16. In which table are Fine Grained Auditing Records stored?
    SYS.FGA_LOG$
  17. Which view shows information about audit policies currently in effect?
    DBA_FGA_AUDIT_TRAIL
  18. Which view combines both the standard and fine-grained audits?
    DBA_COMMON_AUDIT_TRAIL
  19. Which view contains fine-grained audit records written in XML format?
    V$XML_AUDIT_TRAIL
  20. What is an invisible index?
    One which the optimizer will ignore
  21. How does one make invisible indexes visible to the optimizer?
    Use the OPTIMIZER_USE_INVISIBLE_INDEXES Parameter
  22. What’s a good method for checking the impact of dropping an index?
    Make the index invisible.
  23. What is the syntax for making an index visible/invisible?
    ALTER INDEX.....(IN)VISIBLE
  24. How can one find the if an index is visible or not?
    Check the VISIBILTY column of USER|ALL|DBA_INDEXES
  25. Which cache stores the results from SQL queries and PL/SQL Functions?
    The Results Cache
  26. When are the results in the Result Cache invalidated?
    When dependent database objects are validated
  27. Which initialization parameter controls the behaviour of the Result Cache?
    RESULT_CACHE_MODE
  28. Which part of the Result Cache stores the results of SQL Queries?
    SQL Query Result Cache
  29. Which part of the Result Cache stores the results of PL/SQL Functions?
    PL/SQL Function Result Cache
  30. What happens to Queries results that are bigger than the space available in the Result Cache?
    They are not cached
  31. Where are the possible values of the init parameter RESULT_CACHE_MODE?
    • MANUAL - A hint must be used for query to be cached
    • AUTO - Let Optimizer decide
    • FORCE - All SQL results use the cache by default
  32. What is the hint to add to SQL when RESULT_CACHE_MODE=Manual and it's desired that the result be placed in the result cache?
    SELECT /*+ result_cache */......
  33. What is the hint to add to SQL when RESULT_CACHE_MODE=Force and it's desired that the result be NOT placed in the result cache?
    SELECT /*+ no_result_cache */......
  34. What objects and functions will NOT allow SQL results to be cached?
    • Dictionary and Temporary Tables
    • Sequence CURRVAL and NEXTVAL
    • SQL functions current_date, current_timestamp, local_timestamp, sys_guid, sysdate and sys_timestamp
    • Non-deterministic PL/SQL
  35. Which parameter allow DBA to change the memory allocated to the Result Cache?
    RESULT_CACHE_MAX_SIZE
  36. Which parameter specifies the maximum percentage of the Result Cache can be used by a single result? What is the default?
    • RESULT_CACHE_MAX_RESULT
    • Default 5%
  37. Which parameter specifies the time for which a result that access remote database objects remains valid? What is the default?
    • RESULT_CACHE_REMOTE_EXPIRATION
    • Default 0 - The result is NOT cached
  38. which packages allow the DBA to administer the portion of the shared pool used by SQL and PL/SQL function result caches?
    DBMS_RESULT_CACHE
  39. Which of the Result Cache parts SQL and PL/SQL Function is affected by DBMS_RESULT_CACHE?
    Both simultaneously
  40. Which DBMS_RESULT_CACHE element stops/starts existing Result Cache results being used and new ones being added?
    DBMS_RESULT_CACHE.BYPASS
  41. Which DBMS_RESULT_CACHE element cleans out the Result Cache
    DBMS_RESULT_CACHE.FLUSH
  42. Which DBMS_RESULT_CACHE element invalidates all the result-set objects dependent on a specified object
    DBMS_RESULT_CACHE.INVALIDATE
  43. How is a memory report of the memory cache produced?
    DBMS_RESULT_CACHE.MEMORY_REPORT
  44. Which cache allows results to be stored in the memory of a client
    Client Result Cache
  45. Which init parameter set the size of the Client Result Cache?
    CLIENT_RESULT_CACHE_SIZE
  46. What is the default CLIENT_RESULT_CACHE_SIZE?
    0 - Disabled
  47. What init parameter sets a lag time for the client result cache?
    CLIENT_RESULT_CACHE_LAG
  48. Which view list Result Cache setting and memory usage stats?
    V$RESULT_CACHE STATISTICS
  49. Which view lists memory blocks and corresponding stats?
    V$RESULT_CACHE MEMORY
  50. Which view lists all the cached objects along with their attributes?
    V$RESULT_CACHE_OBJECTS
  51. Which view lists the dependence details between the cached results and dependencies?
    V$RESULT_CACHE_DEPENDENCY
  52. How is a tablespace group defined?
    When creating or altering a tablespace and assigning it to a group
  53. When a tablespace is the last to be deleted or moved out a group, what happens to the group?
    It is implicitly dropped
  54. How is a tablespace removed from a tablespace group?
    • Specify a group name as '' (ie empty string)
    • ALTER TABLESPACE tbsname TABLESPACE GROUP '';
  55. What is the syntax to assign a tablespace to a tablespace group?
    • CREATE TABLESPACE TBSNAME .....TABLESPACE GROUP TBSGRP1
    • ALTER TABLESPACE TBSNAME .....TABLESPACE GROUP TBSGRP1
  56. How is a user assigned to a temporary tablespace group?
    • Substituting the group name for the filename
    • CREATE USER user.....TEMPORARY TABLESPACE TBSGRP1
    • ALTER USER user.....TEMPORARY TABLESPACE TBSGRP1
  57. How is database assigned to a default temporary tablespace group?
    • Substituting the group name for the filename
    • ALTER DATABASE.....DEFAULT TEMPORARY TABLESPACE TBSGRP1
  58. Define the characteristics of a tablespace group
    • Contains at least one tablespace (no maximum)
    • Cannot have the same name as any tablespace
    • The group name can appear whenever a tablespace name is used
  59. How does DBA shrink a temporary tablespace?
    ALTER TABLESPACE temp SHRINK SPACE [KEEP 30M]
  60. Which view shows information about temporary tablespace usage?
    DBA_TEMP_FREE_SPACE
  61. What does DDL_LOCK_TIMEOUT do?
    Specifies the wait time for a DDL is an object has a DML lock on it.
  62. Which take precidence OCI_RESULT_CACHE_MAX_SIZE or CLIENT_RESULT_CACHE_SIZE?
    OCI_RESULT_CACHE_MAX_SIZE
  63. Which file holds parameter OCI_RESULT_CACHE_MAX_SIZE?
    sqlnet.ora on the client
  64. Can the results of flashback queries be cached?
    Yes
  65. When the audit trail is set to DB+Extended where is the data written to? What is written?
    • SYS.AUD$
    • SQL Command Text
  66. In Fine Grained Auditing, can the values before and after an update be tracked?
    No
  67. If a table redefinition logically affect dependent PL/SQL, is recompilation needed?
    Yes
  68. When a table is redefined, how are triggers affected?
    They are invalidated, but revalidated on the next DML on the table.
  69. If a column is added to a table, what is the resulting state of the dependent objects?
    They remain valid
  70. If a global temporary tables is created in a non default tablespace, where are indexes on the table created?
    In the same temporary tablespace as the table
  71. If RESULT_CACHE_MODE=FORCE, are results from a no_result_cache hint cached?
    No
  72. Desc DBA_TEMP_FREE_SPACE
    • TABLESPACE_NAME
    • TABLESPACE_SIZE
    • ALLOCATED_SPACE
    • FREE_SPACE
  73. What happens if a you drop a temporary tablespace from a group defined as the default temporary tablespace?
    An error is generated, the table must first be removed from the group

What would you like to do?

Home > Flashcards > Print Preview