The flashcards below were created by user
Tralala
on FreezingBlue Flashcards.
-
What is an Invisible column
One that is only visible when explicitly specified
-
What happens when a SELECT * is issued on a table with invisible columns?
They are not returned
-
TO INSERT into a visible column, what must the syntax be?
INSERT INTO tab1 (col1, col2, inviscol)...
-
Where can't invisible columns be defined?
- External Tables
- Cluster Tables
- Temporary Tables
-
Where are invisible columns found in data_dictionary
- HIDDEN_COLUMN and USER_GENERATED columns in
- ADU_TAB_COLUMNS
-
How is an invisible column defined?
- CREATE TABLE tab1 (id number, myinvcol1 VARCHAR2(20) INVISIBLE);
- ALTER TABLE tab1 ADD (myinvcol1 VARCHAR2(20) INVISIBLE);
-
How do Invisible Columns appear in DESCRIBE?
- They don't, unless SET COLINVISIBLE ON is declared before the DESCRIBE is run
- Or must reference data dictionary
-
What does SET COLINVISIBLE do?
A switch declaring whether DESCRIBE will show Invisible COlumns
-
What does CREATE TABLE tab1 (myinvcol1 VARCHAR2(20) INVISIBLE); fail?
ORA-54039: table must have at least one column that is not invisible
-
In 12c. if an index is defined on columns that already have an index, what happens
Generally, the index is created, Multiple Indexes are permitted on the same set of columns
-
How does the Optimizer deal with deal with multiple index columns?
- It can't only pick the "best" index
- Only one index may be visible at any one time
-
What is the major constraint in multiple index columns?
Only one index may be visible at any time
-
To create a second or third index on the same columns, what must happen?
Make the other indexes invisible
-
When multiple index columns are used, which index(s) is maintained?
All of them
-
What must be ensured to create multiple index columns?
- At least one of the following is met
- 1. Indexes are of different types (ie B-Tree/Bitmap not B-Tree/B-Tree)
- 2. Different types of partitioning (ie B-Tree Global/B-Tree Local is OK)
- 3. Indexes had different uniqueness (ie B-Tree unique/B-Tree non-unique is OK)
-
What happens if ALTER INDEX invind_ix1 VISIBLE; is run on a table with a visible index?
- ORA-14147: There is an existing VISIBLE index defined on the same set of
- columns.
-
What does DROP INDEX test1.index ONLINE; allow?
The index can be dropped while DML operations are occurring on the underlying table
-
What does ALTER TABLE cust DROP CONSTRAINT cust_email ONLINE; allow?
To drop the integrity constraint cust_email while DML operations are occurring on the underlying table
-
To what types of constraint are DROP CONSTRAINT ONLINE operation permitted?
Integrity Constraint
-
What happens if ALTER TABLE cust DROP CONSTRAINT ref_orders CASCADE ONLINE; is run?
- An error is thrown.
- Can't drop a referential constraint online
- Can't drop a constraint cascade online
-
What does ALTER INDEX temp1 UNUSABLE ONLINE; allow?
The index can be made unusable while DML operations are occurring on the underlying table
-
Can an invisible column be used as the partitioning key on a partitioned table?
Yes it can
-
What does ALTER TABLE emps SET UNUSED (aka) ONLINE; allow?
The column aka will be be made inaccessible while DML operations are occurring on the table
-
What does COPY_VPD_OPT parameter in DBMS_REDEFINITION.START_REDEF_TABLE allow?
TO handle any VPD policies on the table during online redefinition
-
In Online Redefinition with VPD, what does setting COPY_VPD_OPT to DBMS_REDEFINITION: NONE achieve?
- This is default
- This is for tables with no VPD policies
- If VPD policies exist, an error is generated
-
In Online Redefinition with VPD, what does setting COPY_VPD_OPT to DBMS_REDEFINITION.CONS_VPD_AUTO achieve?
Copies the current VPD policies on the table to the new table
-
In Online Redefinition with VPD, what does setting COPY_VPD_OPT to DBMS_REDEFINITION.CONS_VPD_MANUAL achieve?
The DBA will manually copy the VPD policies during the process
-
In 12c, what is now possible with VPD when redefining a table?
Online Redefinition
-
When should manually copying the VPD policies during Online Redefinition with VPD be done?
- When there are column mapping between the current table and the interim table.
- You want to modify or add VPD policies during the online redefinition.
-
In Online Redefinition with VPD, that does the DML_LOCK_TIMEOUT parameter of FINISH_REDEF_TABLE allow?
- The original table will be locked briefly at the end on redefinition
- Procedure will wait for all pending DML to commit
- DML_LOCK_TIMEOUT is how long to wait (secs) for all pending DML to commit
-
What are min and max for DML_LOCK_TIMEOUT?
- NULL - no automatic time out of the FINISH_REDEF_TABLE procedure
- 0 - Not waiting
- 1,000,000 - Wait indefinitely
|
|