-
What feature allows a table to be redefined while online?
Enhanced online table redefinition
-
Is a table locked during Enhanced online table redefinition?
Yes, but only for a very short time, depending on the table complexity.
-
Can a table be redefined if it has logs or MVs?
Yes, since 11g
-
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
-
What feature allows for the creation of policies that define specific conditions that trigger an audit record?
Fine Grained Auditing
-
What is required to create a fine-grained auditing policy?
Execute privilege on DBMS_FGA package
-
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
-
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
-
What is required to capture SQL in fine grained audit?
DBMS_FGA.ADD_POLICY to DB+EXTENDED or XML+EXTENDED
-
What Initialization parameter are required to enable fine grained auditing?
None
-
What relation does Fine Auditing Policy have with Audit_Trail?
None
-
What formats can the audit_trail be specified for fine-grained auditing?
- XML
- XML+EXTENDED
- DB
- DB+EXTENDED
-
Where are DB and DB+EXTENDED audit records written?
SYS.FGA_LOG$
-
Where are XML and XML+EXTENDED audit records written?
OS Level in XML format
-
How do you modify a fine grained audit policy after it's created?
You can't, need to drop and recreate it.
-
In which table are Fine Grained Auditing Records stored?
SYS.FGA_LOG$
-
Which view shows information about audit policies currently in effect?
DBA_FGA_AUDIT_TRAIL
-
Which view combines both the standard and fine-grained audits?
DBA_COMMON_AUDIT_TRAIL
-
Which view contains fine-grained audit records written in XML format?
V$XML_AUDIT_TRAIL
-
What is an invisible index?
One which the optimizer will ignore
-
How does one make invisible indexes visible to the optimizer?
Use the OPTIMIZER_USE_INVISIBLE_INDEXES Parameter
-
What’s a good method for checking the impact of dropping an index?
Make the index invisible.
-
What is the syntax for making an index visible/invisible?
ALTER INDEX.....(IN)VISIBLE
-
How can one find the if an index is visible or not?
Check the VISIBILTY column of USER|ALL|DBA_INDEXES
-
Which cache stores the results from SQL queries and PL/SQL Functions?
The Results Cache
-
When are the results in the Result Cache invalidated?
When dependent database objects are validated
-
Which initialization parameter controls the behaviour of the Result Cache?
RESULT_CACHE_MODE
-
Which part of the Result Cache stores the results of SQL Queries?
SQL Query Result Cache
-
Which part of the Result Cache stores the results of PL/SQL Functions?
PL/SQL Function Result Cache
-
What happens to Queries results that are bigger than the space available in the Result Cache?
They are not cached
-
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
-
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 */......
-
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 */......
-
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
-
Which parameter allow DBA to change the memory allocated to the Result Cache?
RESULT_CACHE_MAX_SIZE
-
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%
-
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
-
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
-
Which of the Result Cache parts SQL and PL/SQL Function is affected by DBMS_RESULT_CACHE?
Both simultaneously
-
Which DBMS_RESULT_CACHE element stops/starts existing Result Cache results being used and new ones being added?
DBMS_RESULT_CACHE.BYPASS
-
Which DBMS_RESULT_CACHE element cleans out the Result Cache
DBMS_RESULT_CACHE.FLUSH
-
Which DBMS_RESULT_CACHE element invalidates all the result-set objects dependent on a specified object
DBMS_RESULT_CACHE.INVALIDATE
-
How is a memory report of the memory cache produced?
DBMS_RESULT_CACHE.MEMORY_REPORT
-
Which cache allows results to be stored in the memory of a client
Client Result Cache
-
Which init parameter set the size of the Client Result Cache?
CLIENT_RESULT_CACHE_SIZE
-
What is the default CLIENT_RESULT_CACHE_SIZE?
0 - Disabled
-
What init parameter sets a lag time for the client result cache?
CLIENT_RESULT_CACHE_LAG
-
Which view list Result Cache setting and memory usage stats?
V$RESULT_CACHE STATISTICS
-
Which view lists memory blocks and corresponding stats?
V$RESULT_CACHE MEMORY
-
Which view lists all the cached objects along with their attributes?
V$RESULT_CACHE_OBJECTS
-
Which view lists the dependence details between the cached results and dependencies?
V$RESULT_CACHE_DEPENDENCY
-
How is a tablespace group defined?
When creating or altering a tablespace and assigning it to a group
-
When a tablespace is the last to be deleted or moved out a group, what happens to the group?
It is implicitly dropped
-
How is a tablespace removed from a tablespace group?
- Specify a group name as '' (ie empty string)
- ALTER TABLESPACE tbsname TABLESPACE GROUP '';
-
What is the syntax to assign a tablespace to a tablespace group?
- CREATE TABLESPACE TBSNAME .....TABLESPACE GROUP TBSGRP1
- ALTER TABLESPACE TBSNAME .....TABLESPACE GROUP TBSGRP1
-
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
-
How is database assigned to a default temporary tablespace group?
- Substituting the group name for the filename
- ALTER DATABASE.....DEFAULT TEMPORARY TABLESPACE TBSGRP1
-
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
-
How does DBA shrink a temporary tablespace?
ALTER TABLESPACE temp SHRINK SPACE [KEEP 30M]
-
Which view shows information about temporary tablespace usage?
DBA_TEMP_FREE_SPACE
-
What does DDL_LOCK_TIMEOUT do?
Specifies the wait time for a DDL is an object has a DML lock on it.
-
Which take precidence OCI_RESULT_CACHE_MAX_SIZE or CLIENT_RESULT_CACHE_SIZE?
OCI_RESULT_CACHE_MAX_SIZE
-
Which file holds parameter OCI_RESULT_CACHE_MAX_SIZE?
sqlnet.ora on the client
-
Can the results of flashback queries be cached?
Yes
-
When the audit trail is set to DB+Extended where is the data written to? What is written?
-
In Fine Grained Auditing, can the values before and after an update be tracked?
No
-
If a table redefinition logically affect dependent PL/SQL, is recompilation needed?
Yes
-
When a table is redefined, how are triggers affected?
They are invalidated, but revalidated on the next DML on the table.
-
If a column is added to a table, what is the resulting state of the dependent objects?
They remain valid
-
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
-
If RESULT_CACHE_MODE=FORCE, are results from a no_result_cache hint cached?
No
-
Desc DBA_TEMP_FREE_SPACE
- TABLESPACE_NAME
- TABLESPACE_SIZE
- ALLOCATED_SPACE
- FREE_SPACE
-
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
|
|
