Oracle OCP 9i to 11g Space Management and Partitioning

  1. How does Oracle inform the DBA that there are tablespace space issues?
    Through alerts
  2. What are the types of threshold for tablespace space alerting?
    • Percent Full
    • Free Space Remaining
  3. What are the alert thresholds for tablespace space and what are the defaults?
    • Warning Default 85% full
    • Critical Default 97% full
  4. How is tablespace space alerting disabled?
    Set thresholds to 0
  5. Are the Percent Full, Free Space Remaining thresholds mutually exclusive?
  6. Which Oracle feature allows DBA to reclaim fragmented free space below the HWM in a segment?
    Online Segment Shrink
  7. What are the benefits of Online Segment Shrink?
    • Compaction leads to better cache utilization
    • Compaction leads fewer blocks being scanned
  8. What happens to indexes during Online Segment Shrink?
    They are maintained during the shrink and remain usable after
  9. From where does Online Segment Shrink reclaim space?
    Above and below the HWM
  10. What are the default operations of Online Segment Shrink?
    • Compacts the segment
    • Adjusts the HWM
    • Releases the unclaimed space
  11. What needs to be set on the table to allow Online Segment Shrink?
    Enable Row Movement
  12. What are the segment requirement for Online Segment Shrink?
    • Segment is in a Locally managed tablespace
    • Automatic Segment Space Management is enabled
  13. What segment types are NOT eligible for Online Segment Shrink?
    • IOT Mapped tables
    • Tables with rowid based materialised views
    • Tables with function-based indexes
    • Compressed tables
  14. How is Online Segment Shrink invoked?
  15. What does clause COMPACT to in Online Segment Shrink?
    • Divides the shrink operation into two phases
    • 1. Database defragments the segment space and compacts the table it does NOT reset the HWM
    • 2. The HWM can be reset with a SHRINK SPACE without COMPACT clause in off peak hours
  16. What does clause CASCADE to in Online Segment Shrink?
    Extends the Online Segment Shrink to dependent objects
  17. How is Online Segment Shrink implemented on a partitioned table?
  18. Which Oracle feature informs the DBA that a segment has space available for reclamation?
    Segment Advisor
  19. How does Segment Advisor collect information?
    From AWR reports and sampling data in a segment
  20. When does Segment Advisor run by default?
    During Automated Maintenance Window
  21. What advice does Segment Advisor proffer?
    • When an object has significant free space
    • When a table could benefit from compression
    • When a table has row chaining above a certain threshold
  22. What objects does Automatic Segment Advisor analyze?
    • Tablespaces that have passed warning thresholds
    • Segments with the most activity
    • Segments with the highest growth rate
    • Tables > 10MB that have >= 3 indexes
  23. What can be viewed using DBMS_SPACE.ASA_RECOMMENDATIONS?
    Segment Advisor results
  24. Where can Segment Advisor Results viewed?
    • OEM
    • DBA_ADVISOR_* tables
  25. Which view shows Segment Advisor Recommendations?
  26. Which view shows what the Segment Advisor observed?
  27. Which view provides the SQL to perform on a segment shrink?
  28. Which view shows all the findings, recommendations and actions associated with an object?
  29. Which Oracle utility offers advice on the Undo tablespaces?
    Undo Advisor
  30. How is undo advisor run?
    • OEM
    • DBMS_ADVISOR package
  31. What information does Undo Advisor use for its analysis?
    • AWR
    • NB The database should have been running for some time for accurate results
  32. Using Undo Advisor requires two estimates, what are they?
    • The length of the expected longest running query
    • The longest interval required for Oracle Flashback operations
  33. How does the Undo Advisor alter the size of an undo tablespace?
    It only returns the recommended size, it's a manual process
  34. How are the results for an Undo Advisor job viewed?
    • OEM
  35. When will Oracle overwrite information in the Undo which has not reached the retention period?
    By default it will be overwritten. Oracle will not allow a committing transaction to fail.
  36. Which parameter sets the minimum Undo retention period?
    UNDO_RETENTION (seconds)
  37. If the Undo tablespace is Autoextend and the space is low and UNDO_RETENTION has not been met, what happens?
    The tablespace will extend until the MAXSIZE has been reached. Only then will undo data be overwritten
  38. How is a table compressed?
  39. If ALTER TABLE...COMPRESS is compressed, what data is compressed?
    New Rows, older data remains uncompressed.
  40. What is the main gain from table compression?
  41. What is the main loss from table compression?
  42. How is table compression disabled?
  43. What happens to compressed data in table where ALTER TABLE....NOCOMPRESS is issued?
    It remains compressed.
  44. What init parameter must be set to allow for table compression?
    COMPATIBLE = 11.1 or higher
  45. What compression options are available to RMAN?
    • BZIP2
    • ZLIB
  46. What are pros/cons os BZIP2 in RMAN compression
    • PRO:Higher compression ratio 2:1
    • CON:Higher CPU
  47. What are pros/cons os ZLIB in RMAN compression
    • CON:Lower CPU, Faster
    • CON:Lower compression ratio 1.68:1
  48. What is the default compression algorithm for 11G?
  49. How is the compression algorithm changed in RMAN?
    RMAN> configure compression algorithm 'bzip2'
  50. What is interval partitioning?
    • An extension of range partitioning
    • Database creates a new partition of a specified interval when inserted data exceed the range partition.
  51. What are the restrictions of Interval Partitioning?
    • Can only have a single Column Partition key
    • Partition key must be Number or Date type
    • Not supported for IOT
    • Cannot create a domain index on Interval Partitioned table.
  52. What is the partitioning method when two tables are related by a referential constraints?
    Reference Partitioning
  53. Describe reference partitioning
    The child table is partitioned using the same partitioning key as the parent table without having to duplicate the key columns
  54. Which partitioning method has no partitioning key?
    System Partitioning
  55. Which partitioning is application controlled?
    System Partitioning
  56. If a row is inserted into a system partitioned table without specifying the partition, what will be the outcome?
    It will fail
  57. Describe Virtual Column-Based Partitioning.
    • Virtual columns are not physically stored in the table, but derived from data in the table.
    • Virtual Columns can be used in the partition key in all basic partitioning schemes.
  58. Which package is used to set tablespace space warning
  59. Which type of tablespace allow threshold alerts?
    Locally managed
  60. Which Online Segment Shrink does NOT affect the HWM?
  61. Which Online Segment Shrink should be used during peak times?
    ALTER TABLE...SHRINK SPACE.   NB without COMPACT. COMPACT changes the HWM and will take an exclusive lock on the table.
  62. Which package is used to list tablespace space warning?
  63. Which Shrink Space Command deletes space above and below the HWM?
  64. Which Shrink Space Command deletes space above the HWM?
  65. At which levels can compressions be enabled?
    • TABLE
  66. In Reference Partitioning what happens if the child table is dropped?
    The child table cannot be dropped, so an error
  67. In which kind of partitioning will Oracle create a new partition if required on an insert?
    Interval partitioning
  68. Does Oracle uncompress blocks into memory?
  69. What advice will Segment Advisor give about a partitioned table?
    • Advice about all partitions
    • Advice about dependent objects on the partitioned object
  70. Which clause will compress inserts only for direct loads?
  71. Which clause will compress inserts for all insert types?
  72. Which is the result of issuing ALTER TABLE....COMPRESS?
    Only for direct loads will be compressed
  73. For System Partitioning, when must the PARTITION clause be used?
    INSERT and MERGE operation
  74. Which view shows current undo space and additional space required for system growth?
  75. In Reference Partitioning, in which table(s) is the partition key stored?
    The parent table
  76. With Partitions on Virtual Columns which partition strategies are NOT permitted?
    There are no restrictions
Card Set
Oracle OCP 9i to 11g Space Management and Partitioning
Oracle OCP 9i to 11g Space Management and Partitioning