-
What Oracle feature manages historical data in a database?
In Database Archiving
-
What happens to archived data in In Database Archiving?
It remains in the database
-
What effect does Temporal Validity have on storage usage and performance?
None, the data still exists, Temporal Validity is a filter
-
What rows does Temporal Validity return?
Those that are currently valid. That fall within a data range.
-
What does Temporal Validity use for definition?
Two date columns in a table.
-
How can the date columns for Temporal Validity be defined?
Either explicitly or implicitly
-
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
-
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))
-
What is the syntax for Implicit Temporal Validity?
- CREATE TABLE MYTAB
- (...
- PERIOD FOR MY_USER_TIME)
-
In Temporal Validity, when can the valid time period be defined?
-
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
-
What is an PERIOD FOR query?
One that uses Temporal Validity.
-
How is a PERIOD FOR query used?
- SELECT ....
- FROM TABLE_NAME PERIOD FOR [TEMPORAL_DEFINITION] [DATE]
-
Using Temporal Validity, how is data returned for a range of dates?
Using VERSIONS PERIOD FOR ... BETWEEN
-
How is VERSIONS PERIOD FOR ... BETWEEN used?
- SELECT ....
- FROM TABLE_NAME VERSIONS PERIOD FOR [TEMPORAL_DEFINITION] BETWEEN [DATE1] AND [DATE2]
-
Can Temporal Validity be used in conjunction with Flashback?
YES
-
Where is Temporal validity not supported?
In a multitenant container database
-
Which Oracle feature allows rows to be kept in a production database, but be kept invisible from the application?
The In-Database Archiving
-
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
-
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
-
What is the syntax to add In-Database Archiving to a table?
- CREATE TABLE TABLE_NAME (.....) ROW ARCHIVAL;
- ALTER TABLE TABLE_NAME ROW ARCHIVAL;
-
What is the syntax to disable In-Database Archiving for a table?
ALTER TABLE TABLE_NAME NO ROW ARCHIVAL;
-
What column get added to a table when In-Database Archiving is enabled
ORA_ARCHIVE_STATE
-
How can one see the value of ORA_ARCHIVE_STATE in In-Database Archiving?
By explicitly stating ORA_ARCHIVE_STATE in a SELECT statement
-
What is the default setting for parameter ROW_ARCHIVAL_VISIBILITY
ACTIVE
-
How is In-Database Archiving retrospectively added to a table?
ALTER TABLE [TABLE_NAME] ROW ARCHIVAL;
-
If ORA_ARCHIVE_STATE is 0, is the row visible or not?
Visible
-
How is the ORA_ARCHIVE_STATE altered manually
- UPDATE [TABLE_NAME] SET ORA_ARCHIVE_STATE = DBMS_ILM.ARCHIVESTATENAME(1)
- WHERE......
-
To what values can DBMS_ILM.ARCHIVESTATENAME procedure set ORA_ARCHIVE_STATE?
- Row active 0
- Row archived 1
-
To what values can ORA_ARCHIVE_STATE be set?
Any, but anything > 0 renderers the row as in a archived state.
-
Hybrid Columnar Compression is available for tablespaces residing on what hardware?
- Exadata storage
- ZFS
- Pillar Axiom 600
-
What is a Compression Units (CU)?
Data reorganised by Hybrid Columnar Compression
-
What are the two types of Hybrid Columnar Compression?
-
What is Query type in Hybrid Columnar Compression?
- The less aggressive type of compression
- Available as LOW and HIGH
- For use in data warehousing
-
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
-
How is In Database Archiving enabled for a table?
- CREATE TABLE TABLE_NAME
- ....
- ROW ARCHIVAL;
-
What column does In Database Archiving add to a table?
The hidden column ORA_ARCHIVE_STATE
-
What does alter session set row archival visibility = all; do?
Ignores ORA_ARCHIVE_STATE, all rows are visible
-
Which Oracle feature defines flags rows that are archived?
- In Database Archiving
- ROW ARCHIVAL
-
In Temporal Validity, where is inactive data moved to?
It isn't moved, it remains in the table
-
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
-
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
-
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!!
|
|
