Oracle 12c Upgrade: 08 – In-Database Archiving and Valid-Time Temporal

Card Set Information

Author:
Tralala
ID:
296587
Filename:
Oracle 12c Upgrade: 08 – In-Database Archiving and Valid-Time Temporal
Updated:
2015-03-17 03:58:00
Tags:
Database Archiving Valid Time Temporal
Folders:

Description:
Oracle 12c Upgrade: 08 – In-Database Archiving and Valid-Time Temporal
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 Oracle feature manages historical data in a database?
    In Database Archiving
  2. What happens to archived data in In Database Archiving?
    It remains in the database
  3. What effect does Temporal Validity have on storage usage and performance?
    None, the data still exists, Temporal Validity is a filter
  4. What rows does Temporal Validity return?
    Those that are currently valid. That fall within a data range.
  5. What does Temporal Validity use for definition?
    Two date columns in a table.
  6. How can the date columns for Temporal Validity be defined?
    Either explicitly or implicitly
  7. Which Oracle feature add a "valid time" dimension to the data by adding columns to table indicating if the row is still valid?
    Temporal Validity
  8. What is the syntax for Explicit Temporal Validity?
    • CREATE TABLE MYTAB
    • (...
    • USER_TIME_START DATE,
    • USER_TIME_END DATE,
    • PERIOD FOR MY_USER_TIME (USER_TIME_START , USER_TIME_END))
  9. What is the syntax for Implicit Temporal Validity?
    • CREATE TABLE MYTAB
    • (...
    • PERIOD FOR MY_USER_TIME)
  10. In Temporal Validity, when can the valid time period be defined?
    • CREATE TABLE
    • ALTER TABLE
  11. What would happen if this was run. CREATE TABLE MYTAB2 (ID NUMBER, PERIOD FOR MY_USER_TIME)?
    Hidden columns MY_USER_TIME_START and MY_USER_TIME_END would be created
  12. What is an PERIOD FOR query?
    One that uses Temporal Validity.
  13. How is a PERIOD FOR query used?
    • SELECT ....
    • FROM TABLE_NAME PERIOD FOR [TEMPORAL_DEFINITION] [DATE]
  14. Using Temporal Validity, how is data returned for a range of dates?
    Using VERSIONS PERIOD FOR ... BETWEEN
  15. How is VERSIONS PERIOD FOR ... BETWEEN used?
    • SELECT ....
    • FROM TABLE_NAME VERSIONS PERIOD FOR [TEMPORAL_DEFINITION] BETWEEN [DATE1] AND [DATE2]
  16. Can Temporal Validity be used in conjunction with Flashback?
    YES
  17. Where is Temporal validity not supported?
    In a multitenant container database
  18. Which Oracle feature allows rows to be kept in a production database, but be kept invisible from the application?
    The In-Database Archiving
  19. To use In-Database Archiving, what must the table must have?
    • ROW_ARCHIVAL enabled
    • ORA_ARCHIVE_STATE hidden column set to a non-zero value
  20. What does the parameter ROW_ARCHIVAL_VISIBILITY do?
    • ACTIVE: only columns with a zero ORA_ARCHIVE_STATE column are displayed.
    • ALL: all rows are returned
  21. What is the syntax to add In-Database Archiving to a table?
    • CREATE TABLE TABLE_NAME (.....) ROW ARCHIVAL;
    • ALTER TABLE TABLE_NAME ROW ARCHIVAL;
  22. What is the syntax to disable In-Database Archiving for a table?
    ALTER TABLE TABLE_NAME NO ROW ARCHIVAL;
  23. What column get added to a table when In-Database Archiving is enabled
    ORA_ARCHIVE_STATE
  24. How can one see the value of ORA_ARCHIVE_STATE in In-Database Archiving?
    By explicitly stating ORA_ARCHIVE_STATE in a SELECT statement
  25. What is the default setting for parameter ROW_ARCHIVAL_VISIBILITY
    ACTIVE
  26. How is In-Database Archiving retrospectively added to a table?
    ALTER TABLE [TABLE_NAME] ROW ARCHIVAL;
  27. If ORA_ARCHIVE_STATE is 0, is the row visible or not?
    Visible
  28. How is the ORA_ARCHIVE_STATE altered manually
    • UPDATE [TABLE_NAME] SET ORA_ARCHIVE_STATE = DBMS_ILM.ARCHIVESTATENAME(1)
    • WHERE......
  29. To what values can DBMS_ILM.ARCHIVESTATENAME procedure set ORA_ARCHIVE_STATE?
    • Row active 0
    • Row archived 1
  30. To what values can ORA_ARCHIVE_STATE be set?
    Any, but anything > 0 renderers the row as in a archived state.
  31. Hybrid Columnar Compression is available for tablespaces residing on what hardware?
    • Exadata storage
    • ZFS
    • Pillar Axiom 600
  32. What is a Compression Units (CU)?
    Data reorganised by Hybrid Columnar Compression
  33. What are the two types of Hybrid Columnar Compression?
    • Query
    • Archive
  34. What is Query type in Hybrid Columnar Compression?
    • The less aggressive type of compression
    • Available as LOW and HIGH
    • For use in data warehousing
  35. What is Archive type in Hybrid Columnar Compression?
    • The more aggressive type of compression
    • Available as LOW and HIGH
    • For use in long term archiving, where the data will never be updated
  36. How is In Database Archiving enabled for a table?
    • CREATE TABLE TABLE_NAME
    • ....
    • ROW ARCHIVAL;
  37. What column does In Database Archiving add to a table?
    The hidden column ORA_ARCHIVE_STATE
  38. What does alter session set row archival visibility = all; do?
    Ignores ORA_ARCHIVE_STATE, all rows are visible
  39. Which Oracle feature defines flags rows that are archived?
    • In Database Archiving
    • ROW ARCHIVAL
  40. In Temporal Validity, where is inactive data moved to?
    It isn't moved, it remains in the table
  41. How are the time dimension for a table created?
    • implicitly - The PERIOD FOR columns are started user_time_start, user_time_end
    • explicitly - The PERIOD FOR value user_time generated hidden columns user_time_start, user_time_end
  42. If this was run, create table emp2....PERIOD FOR user_time, what would happen?
    • Oracle would implicitly create two columns
    • USER_TIME_START
    • USER_TIME_END
  43. If a table has implicitly defined Temporal Validity, how is DML/SELECT executed?
    The hidden columns must be explicitly defined in SQL....They are hidden!!

What would you like to do?

Home > Flashcards > Print Preview