Review Questions
Securing the Oracle Listener, Diagnostic Sources, and Memory
1. Your database instance has the following initialization parameter set:
MAX_DUMP_FILE_SIZE = 1000
What effect will this setting have on user session trace files?
A. No effect. MAX_DUMP_FILE_SIZE affects only background process trace files.
B. Trace files will be limited to 1000 bytes.
C. Trace files will be limited to 1000 kilobytes.
D. Trace files will be limited to 1000 megabytes.
E. Trace files will be limited to 1000 blocks.
---
Ans: E.
The default unit type for the MAX_DUMP_FILE_SIZE parameter is blocks.
2. The following initialization parameters reside in your initialization file:
SGA_TARGET=8G
STREAMS_POOL_SIZE=500M
JAVA_POOL_SIZE=1G
SHARED_POOL_SIZE=3G
Which of the following statements will be true when your instance is started?
(Choose all that apply.)
A. The automatically sized components will have 6.5GB allocated among them.
B. The total SGA size will be 9GB.
C. The total SGA size will be 8GB.
D. The JAVA_POOL_SIZE will never shrink below 1GB.
E. The STREAMS_POOL_SIZE will always stay at 500MB.
----
Ans: C, D, E.
The SGA_TARGET parameter specifies total memory available for sizing the
SGA. This includes both automatically sized and manually sized components. The
Java pool is an automatically sized component, so its size is unknown, but it will
never shrink below the size specified in the JAVA_POOL_SIZE parameter. The streams
pool is a manually sized component, so it receives a static allocation of 500MB.
3. ASMM cannot be started on your instance. What might be the reason?
A. The shared pool is sized larger than the database buffer cache size.
B. The DB_KEEP_CACHE_SIZE parameter is not expressed as a power of 2.
C. The STATISTICS_LEVEL parameter is set to BASIC.
D. An invalid sizing parameter is specified.
E. The ASMM option is not installed.
---
Ans: C.
ASMM is dependent on Oracle statistics and requires a STATISTICS_LEVEL of
either TYPICAL or ALL. If the STATISTICS_LEVEL parameter is set to BASIC, ASMM
will not function.
4. You wish to limit database access to a select group of hosts. The following
lines have been added to your sqlnet.ora file:
tcp.validnode_checking = yes
tcp.invited_nodes = (peabody, 159.162.210.24, 159.162.211.*, 159.163.*.*)
From which of the following hosts will database requests be allowed?
A. peabody
B. 159.162.210.22
C. 159.162.211.44
D. 159.163.212.6
E. None. Valid node checking is defined in the tnsnames.ora file.
---
Ans: A.
Only option A is correct. Wildcards are not allowed in the tcp.invited_nodes
parameter, so only exact matches will be allowed.
5. Your external procedure agent is governed by the following listener setting:
ENVS="EXTPROC_DLLS=/lib/javalib.so:/lib/tlib.so"
Which of the following statements is true regarding external procedure
requests handled by this listener?
A. They can access procedures only in the /lib/javalib.so and /lib/tlib.so shared library files.
B. They can access procedures in the /lib/javalib.so and /lib/tlib.so shared libraries, as well as in any external libraries
referenced by these procedures.
C. They can access procedures in the /lib/javalib.so or /lib/tlib.so shared libraries, as well as in any libraries in the $ORACLE_HOME/lib directory.
D. They can access procedures in the /lib/javalib.so or /lib/tlib.so shared libraries, as well as in any libraries in the $ORACLE_HOME/lib directory. They can also access any external libraries referenced by
these procedures.
---
Ans: C.
The EXTPROC_DLLS environment variable limits the available shared libraries that
the agent can access. Since the ONLY option is not included in the example, the
specified libraries are available in addition to the default libraries (those in the
$ORACLE_HOME/lib directory).
6. You are working with Oracle Support to resolve a database issue. The support
technician asks you to forward any diagnostic files that might help them
resolve the issue. Which two of the following choices represent the best files to
send?
A. Alert log
B. Trace files from the USER_DUMP_DEST directory
C. Trace files from the BACKGROUND_DUMP_DEST directory
D. Files from both the USER_DUMP_DEST directory and the BACKGROUND_DUMP_DEST directory
E. External procedure files
---
Ans: A, C.
The alert log can be used to find error messages and other significant events,
along with a timestamp. Trace files from background sessions may also be helpful to
determine the cause of the problem. User trace files can be helpful to resolve errant
SQL statements, but those are not classified as database problems. External
procedure files do not exist.
7. You notice that a lot of disk space is being used up on one of your mount
points. Upon investigating, you find that SQL tracing is turned on for the
instance. You issue an ALTER SYSTEM statement to turn it off. In an SPFILE
environment, how can you determine if it was turned on at startup or if it was
turned on using an ALTER SYSTEM statement? Select the best answer.
A. User process trace file
B. Alert log
C. System log
D. Background process trace file
E. init.ora file
---
Ans: B.
The alert log details all startup events, including listing all of the non-default
initialization parameters that were set.
8. Your instance does not have a value set for the PGA_AGGREGATE_TARGET
initialization parameter. The server has 8GB of memory and the SGA is using
4GB. Which of the following statements is true regarding APMM for this
instance?
A. It will not be enabled.
B. It will use the default size of 10MB.
C. It will always use the *_AREA_SIZE parameters.
D. It will use the default size of 800MB.
E. It will dynamically allocate unused SGA memory.
-----
Ans: D.
If the PGA_AGGREGATE_TARGET parameter is not explicitly set, it will default to the
greater of 10MB or 20 percent of the SGA size (800MB). Therefore, it will default to
800MB.
9. Your junior DBA accidentally deleted the alert log file. How do you ensure
that Oracle will continue to function? Choose the best answer.
A. Restore the most recent backup.
B. Shut down the instance. A new file will be created on startup.
C. Create a new alert log file using EM Database Control.
D. Create a new alert log file using the ALTER SYSTEM statement.
E. Do nothing.
---
Ans: E.
A new alert log will be created automatically the next time Oracle needs to write
an alert log entry, so no action is required. You may indeed wish to restore the last
backup of the alert log if you want it for historical purposes, but that has no effect on
the Oracle instance itself.
10. You execute a Java stored procedure consisting of a single SQL statement
using a large hash-join operation and a large sort operation. Which memory
structure will have the most impact on your performance?
A. Shared pool
B. Java pool
C. SQL work area
D. Private SQL area
E. Shared SQL area
---
Ans: C.
Though Java stored procedures utilize Java pool memory, the usage is minimal. A
large sort operation, on the other hand, is greatly impacted by the size of the SQL
work area available to it.
11. The following initialization parameters are set on your database instance:
DB_NAME=LNX1
SQL_TRACE=FALSE
BACKGROUND_DUMP_DEST=/data1/admin/lnx1/bdump
USER_DUMP_DEST=/data1/admin/lnx1/udump
TRACEFILE_IDENTIFIER='prod'
An archiver (ARCH) process encounters an error situation. Its process ID is
32123. Which of the following is true?
A. The trace file /data1/admin/lnx1/bdump/lnx1_arch_32123.trc will be created.
B. The trace file /data1/admin/lnx1/bdump/arch_lnx1_32123_prod.trc will be created.
C. The trace file /data1/admin/lnx1/udump/arch_lnx1_32123.trc will be created.
D. The trace file /data1/admin/lnx1/bdump/lnx1_arch_32123_prod.trc will be created.
E. No trace file will be created.
----
Ans: B. Because the archiver is a background process, it will follow the naming
conventions and storage locations used by all background processes. The
TRACEFILE_IDENTIFIER parameter has no effect on background processes, so it is
ignored.
12. Which of the following could be found in the alert log? (Choose all that apply.)
A. Evidence of a brute force attack against the listener
B. External procedure executions
C. Tablespace creation events
D. Evidence of recovery operations
E. Initialization parameter values
---
Ans: C, D, E.
All of the information specified in C, D, and E is logged to the alert log.
Listener attacks would be logged in the listener log, not the alert log. External
procedure executions are not logged.
13. You need to secure your listener. Which of the following is not a
recommended security measure? (Choose the best answer.)
A. Enable Listener logging.
B. Remove unnecessary external procedure services.
C. Configure listener on a non-standard port.
D. Apply patches.
E. Block SQL*NET traffic on firewalls.
---
Ans: C.
Configuring the listener on a non-standard port may offer a slight security
increase, but port-scanning programs could still easily locate the listener. Therefore,
it is not a recommended security measure.
14. You want to set a password on the listener. Why might you want to use
lsnrctl rather than manually editing the listener.ora file? (Select the best
two answers.)
A. It is easier.
B. It will encrypt the password in the listener.ora file.
C. You don't have access to the operating system.
D. lsnrctl will log the event to the alert log.
E. The listener.ora file is stored in binary format.
----
Ans: B, C.
Manually editing the listener.ora file will result in a password that is stored
in plaintext, whereas lsnrctl will store the password in an encrypted format. Also,
you cannot manually edit the listener.ora file if you don't have access to the
operating system (for example, if you need to perform the change remotely, but don't
have an operating system account). In this situation, lsnrctl could be used from a
remote location.
15. Which of the following are not automatically sized components of ASMM?
A. Streams pool
B. Keep buffer cache
C. SQL work area
D. Recycle buffer cache
E. All of the above
---
Ans: E.
None of these components are automatically sized by ASMM. They are all
manually sized SGA components, except for the SQL work area. The SQL work area
isn't a part of the SGA at all. It is managed by APMM.
16. Which of the following operations is not impacted by the size of the SQL work
area? (Select the best answer.)
A. Index lookups
B. Sort operations
C. Bitmap merges
D. Bitmap creation
E. Bulk load operations
----
Ans: A.
Index lookups are not memory intensive, whereas sort operations, bitmap merges,
bitmap creation, and bulk load operations tend to be very memory intensive.
Therefore, the SQL work area size has a major impact on their performance.
17. You want to change the password for your listener. The listener.ora file
contains the following entries:
PASSWORDS_LISTENER=64F4692D9E6443F
LOGGING_LISTENER=ON
ADMIN_RESTRICTIONS=ON
Which of the following methods can you use to change the password?
A. lsnrctl
B. EM Database Control
C. Manually editing the listener.ora file
D. netmgr
E. netca
---
Ans: C. When the ADMIN_RESTRICTIONS option is enabled, all changes to the listener are
disabled, except through manually editing the listener.ora file. This protects from
changes being made remotely by unauthorized individuals.
18. Dirty blocks in the database buffer cache are tracked by which component?
A. Dirty block list
B. Block list
C. LRU list
D. Write list
E. Dirty list
---
Ans: D.
Dirty blocks in the database buffer are tracked in the write list. The write list is
accessed by the DBWR process to write dirty blocks back to disk.
19. While performing routine management tasks on your database, you discover a
high concentration of ORA-01169 messages in the listener log. What might be
the cause?
A. Invalid setting in the sqlnet.ora file.
B. Mismatched parentheses in the listener.ora file.
C. Valid node checking has denied connection requests based on their host addresses.
D. A brute force password attack has been attempted.
E. The database is down.
----
Ans: D.
The ORA-01169 message indicates a failed password attempt. Repeated
occurrences of the message likely indicate that someone is trying to hack the
password through a brute force attack.
20. The following parameters are set in your instance:
SGA_TARGET=3G
HASH_AREA_SIZE=100M
SORT_AREA_SIZE=100M
WORKAREA_SIZE_POLICY=AUTO
PGA_AGGREGATE_TARGET=600M
A user session executes a SQL statement that performs a large sort operation.
What can be determined from these settings? (Select the best two answers.)
A. The operation will be allocated 100MB.
B. The operation will be allocated a minimum of 100MB.
C. The *_AREA_SIZE parameters will be ignored.
D. The *_AREA_SIZE parameters will not be ignored.
E. The amount of memory allocated is not known.
---
Ans: C, E.
When the WORKAREA_SIZE_POLICY parameter is set to AUTO (the default
setting), APMM will dynamically size the SQL work area. Therefore, the amount of
memory actually allocated is unknown. The *_AREA_SIZE parameters have no
meaning unless the WORKAREA_SIZE_ POLICY is set to MANUAL
Sunday, September 30, 2007
Saturday, September 29, 2007
OCP Oracle 10g Admin II - Ch10 Understanding Globalization Support
Chapter 10 - Understanding Globalization Support
Review Questions
1. Globalization support is implemented through the text- and character processing functions provided by which Oracle feature?
A. RSTLNE
B. NLSRTL
C. LISTENER
D. NLSSORT
E. Linguistic sorts
----
Ans: B.
The NLS Runtime Library (NLSRTL) provides the language-independent text and
character processing functionality for Oracle.
2. What three elements of globalization can be explicitly defined using the NLS_LANG environment variable?
A. NLS_LANGUAGE
B. NLS_SORT
C. NLS_CALENDAR
D. NLS_CHARACTERSET
E. NLS_TERRITORY
---
Ans: A, D, E.
The client-side NLS_LANG parameter can define language, territory, and
character set all at once. Though the value for NLS_SORT is derived from the
NLS_LANGUAGE parameter setting, it is not explicitly set by NLS_LANG. NLS_CALENDAR
is not affected by the setting of NLS_LANG.
3. Given two different character sets (A and B), which of the following must be
true for A to be considered a strict superset of B? (Choose all that apply.)
A. A must contain all of the characters defined in B.
B. A must be Unicode.
C. The encoded values in A must match the encoded values in B for all characters defined in B.
D. A must be a multi-byte character set.
E. The encoded values in A must match the encoded values in B for all numeric and alphabetic characters in B.
---
Ans: A, C.
A strict superset must contain all characters found in the other character set and
have matching encoded values for those characters.
4. The NLS_SORT parameter sets the default sort method for which of the following operations? (Choose all that apply.)
A. WHERE clause
B. ORDER BY clause
C. BETWEEN clause
D. NLSSORT function
E. NLS_SORT function
---
Ans: A, D.
The NLS_SORT parameter defines the default sort method (binary or linguistic)
for both SQL WHERE clause operations and NLSSORT function operations. The default
sort method for ORDER_BY and BETWEEN (and all other SQL operations that support
linguistic sorts) is defined by the NLS_COMP parameter. NLS_SORT is an invalid
function name.
5. Which view shows all valid values for the NLS_LANGUAGE, NLS_SORT, NLS_TERRITORY, and NLS_CHARACTERSET parameters?
A. V$VALID_NLS_VALUES
B. NLS_VALID_VALUES
C. NLS_VALUE_OPTIONS
D. V$NLS_VALUE_OPTIONS
E. V$NLS_VALID_VALUES
---
Ans: E.
The V$NLS_VALID_VALUES view shows the names of all language, territory, sort,
and character set definitions that are available in the database.
6. Which of the following datatypes store time zone information in the database?
A. TIMESTAMP
B. DATE
C. TIMESTAMP WITH TIME ZONE
D. TIMESTAMP WITH LOCAL TIME ZONE
E. DATETIME
---
Ans: C.
Only TIMESTAMP WITH TIME ZONE datatype actually stores time zone information
in the database. The TIMESTAMP WITH LOCAL TIME ZONE datatype converts the
timestamp to local time and drops the time zone information before storing it in the
database. DATE and TIMESTAMP datatypes do not deal with time zone information at
all. DATETIME is not a valid datatype.
7. Which of the following are valid settings for the NLS_COMP parameter?
(Choose all that apply.)
A. ASCII
B. ANSI
C. BINARY
D. MONOLINGUAL
E. MULTILINGUAL
---
Ans: B, C.
The NLS_COMP parameter can be set to BINARY or ANSI. This parameter
determines the default sort type for certain SQL functions. (A setting of ANSI
specifies that linguistic sorts should be used.)
8. NLS parameters can be set using the five methods listed below. Put the methods in order from highest to lowest according to Oracle's order of precedence:
1. Default setting
2. Client environment variable
3. Explicit ALTER SESSION statement
4. Inside SQL function
5. Server initialization parameter
A. 2, 4, 5, 1, 3
B. 5, 1, 2, 3, 4
C. 4, 3, 2, 5, 1
D. 1, 2, 4, 3, 5
E. 4, 3, 2, 1, 5
----
Ans: C.
NLS settings embedded in a SQL function have the highest precedence, followed
by explicit ALTER SESSION statements, client environment variables (which execute
an implicit ALTER SESSION statement), server initialization parameters, and finally
default settings.
9. What can you determine about the following linguistic sorts based only on their names?
1. GERMAN
2. FRENCH_M
Select all the true statements:
A. 1 is a monolingual sort.
B. 2 is a monolingual sort.
C. 1 is case-insensitive.
D. Both 1 and 2 are case-insensitive.
E. Case-sensitivity is unknown.
---
Ans: A.
A is the only true statement. The _M appended to the end of a sort name denotes a
multilingual sort. Its absence denotes a monolingual sort. Case-sensitive and accent-insensitive
sorts have _CI or _AI appended to the name. Its absence denotes case- and accent-sensitivity.
10. In a database with the database character set of US7ASCII and a national
character set of UTF-8, which datatypes would be capable of storing Unicode
data by default?
A. VARCHAR2
B. CHAR
C. NVARCHAR2
D. CLOB
E. LONG
---
Ans: C.
NLS datatypes (NCHAR, NVARCHAR, and NCLOB) store data using the character set
defined as the national character set by default. Because the national character set is
UTF-8 (a Unicode character set), data stored in these datatypes will be Unicode data
by default. All other datatypes use the character set defined as the database character
set. Because US7ASCII is not a Unicode character set, it does not store Unicode data
by default.
11. Automatic data conversion will occur if
A. The client and server have different NLS_LANGUAGE settings.
B. The client and server character sets are not the same, and the database character set is not a strict superset of the client character set.
C. The client and server are in different time zones.
D. The client requests automatic data conversion.
E. The AUTO_CONVERT initialization parameter is set to TRUE.
--
Ans: B.
Automatic data conversion occurs when data is moved between character sets.
However, if the server character set is a strict superset of the client character set, no
conversion is necessary.
12. Which of the following NLS_SORT parameter values would result in case-insensitive
and accent-insensitive binary sorts?
A. NLS_SORT = BINARY
B. NLS_SORT = BINARY_AI
C. NLS_SORT = BINARY_CI
D. NLS_SORT = BINARY_AI_CI
E. Binary sorts are case and accent-insensitive by default.
---
Ans: B.
The _AI suffix implies that an accent-insensitive sort will be performed. Accent-insensitive
sorts are also case-insensitive by default. The _CI suffix implies that a
case-insensitive sort will be performed, but it will not be accent-insensitive.
Specifying both suffixes (_AI_CI) is illegal.
13. Which NLS parameter can be used to change the default Oracle sort method
from binary to linguistic for the SQL SELECT statement?
A. NLS_LANG
B. NLS_SORT
C. NLS_COMP
D. NLS_SORT
E. None of the above
--
Ans: E.
The SQL SELECT statement does not invoke a sort.
14. Which of the following would be affected by setting NLS_LENGTH_SEMANTICS=CHAR?
A. All objects in the database
B. Tables owned by SYS and SYSTEM
C. Data dictionary tables
D. NCHAR columns
E. CHAR columns
----
Ans: E.
Only option E is correct. Tables owned by the SYS and SYSTEM users are not
affected by default-length semantics. Data dictionary tables always use byte
semantics, and NCHAR columns always use character semantics. Therefore, neither is
affected by the setting of the NLS_LENGTH_ SEMANTICS parameter.
15. Which is not a valid locale definition file type?
A. Language
B. Linguistic sort
C. Calendar
D. Territory
E. Character set
---
Ans: C.
Calendar definitions are not stored as locale definition files. Only languages,
linguistic sorts, territories, and character set definitions are stored as locale definition
files.
16. How many different calendars does Oracle 10g support?
A. 22
B. 7
C. 6
D. 15
E. 2
----
Ans: B.
Oracle supports 7 distinct calendars: Gregorian, Japanese Imperial, ROC Official,
Persian, Thai Buddha, Arabic Hijrah, and English Hijrah.
17. Which NLS parameter directly governs linguistic searches?
A. NLS_SEARCH_L
B. NLS_SORT
C. NLS_SEARCH
D. NLS_SORT_L
E. None of the above
---
Ans: B.
Linguistic searches are closely related to linguistic sorts and are governed by the
NLS_SORT parameter.
18. Case-insensitive sorts are always accent-insensitive by default.
A. True
B. False
---
Ans: B.
Accent-insensitive sorts are always case-insensitive, not the other way around.
19. What is the name of the file that identifies the set of available locale definitions?
A. locale.def
B. lxdef.ora
C. lx1boot.nlb
D. lx1boot.ora
E. lang.def
--
Ans: C.
The lx1boot.nlb file identifies the available locale definitions to the NLSRTL.
20. Which of the following is not a valid linguistic sort element?
A. Accent expansion
B. Canonical equivalence
C. Reverse secondary sorting
D. Ignorable characters
E. Character rearrangement
---
Ans: A.
Linguistic sort elements define the rules for linguistic sorting. There is no
linguistic sort element named "accent expansion." The other choices are all valid
rules.
Review Questions
1. Globalization support is implemented through the text- and character processing functions provided by which Oracle feature?
A. RSTLNE
B. NLSRTL
C. LISTENER
D. NLSSORT
E. Linguistic sorts
----
Ans: B.
The NLS Runtime Library (NLSRTL) provides the language-independent text and
character processing functionality for Oracle.
2. What three elements of globalization can be explicitly defined using the NLS_LANG environment variable?
A. NLS_LANGUAGE
B. NLS_SORT
C. NLS_CALENDAR
D. NLS_CHARACTERSET
E. NLS_TERRITORY
---
Ans: A, D, E.
The client-side NLS_LANG parameter can define language, territory, and
character set all at once. Though the value for NLS_SORT is derived from the
NLS_LANGUAGE parameter setting, it is not explicitly set by NLS_LANG. NLS_CALENDAR
is not affected by the setting of NLS_LANG.
3. Given two different character sets (A and B), which of the following must be
true for A to be considered a strict superset of B? (Choose all that apply.)
A. A must contain all of the characters defined in B.
B. A must be Unicode.
C. The encoded values in A must match the encoded values in B for all characters defined in B.
D. A must be a multi-byte character set.
E. The encoded values in A must match the encoded values in B for all numeric and alphabetic characters in B.
---
Ans: A, C.
A strict superset must contain all characters found in the other character set and
have matching encoded values for those characters.
4. The NLS_SORT parameter sets the default sort method for which of the following operations? (Choose all that apply.)
A. WHERE clause
B. ORDER BY clause
C. BETWEEN clause
D. NLSSORT function
E. NLS_SORT function
---
Ans: A, D.
The NLS_SORT parameter defines the default sort method (binary or linguistic)
for both SQL WHERE clause operations and NLSSORT function operations. The default
sort method for ORDER_BY and BETWEEN (and all other SQL operations that support
linguistic sorts) is defined by the NLS_COMP parameter. NLS_SORT is an invalid
function name.
5. Which view shows all valid values for the NLS_LANGUAGE, NLS_SORT, NLS_TERRITORY, and NLS_CHARACTERSET parameters?
A. V$VALID_NLS_VALUES
B. NLS_VALID_VALUES
C. NLS_VALUE_OPTIONS
D. V$NLS_VALUE_OPTIONS
E. V$NLS_VALID_VALUES
---
Ans: E.
The V$NLS_VALID_VALUES view shows the names of all language, territory, sort,
and character set definitions that are available in the database.
6. Which of the following datatypes store time zone information in the database?
A. TIMESTAMP
B. DATE
C. TIMESTAMP WITH TIME ZONE
D. TIMESTAMP WITH LOCAL TIME ZONE
E. DATETIME
---
Ans: C.
Only TIMESTAMP WITH TIME ZONE datatype actually stores time zone information
in the database. The TIMESTAMP WITH LOCAL TIME ZONE datatype converts the
timestamp to local time and drops the time zone information before storing it in the
database. DATE and TIMESTAMP datatypes do not deal with time zone information at
all. DATETIME is not a valid datatype.
7. Which of the following are valid settings for the NLS_COMP parameter?
(Choose all that apply.)
A. ASCII
B. ANSI
C. BINARY
D. MONOLINGUAL
E. MULTILINGUAL
---
Ans: B, C.
The NLS_COMP parameter can be set to BINARY or ANSI. This parameter
determines the default sort type for certain SQL functions. (A setting of ANSI
specifies that linguistic sorts should be used.)
8. NLS parameters can be set using the five methods listed below. Put the methods in order from highest to lowest according to Oracle's order of precedence:
1. Default setting
2. Client environment variable
3. Explicit ALTER SESSION statement
4. Inside SQL function
5. Server initialization parameter
A. 2, 4, 5, 1, 3
B. 5, 1, 2, 3, 4
C. 4, 3, 2, 5, 1
D. 1, 2, 4, 3, 5
E. 4, 3, 2, 1, 5
----
Ans: C.
NLS settings embedded in a SQL function have the highest precedence, followed
by explicit ALTER SESSION statements, client environment variables (which execute
an implicit ALTER SESSION statement), server initialization parameters, and finally
default settings.
9. What can you determine about the following linguistic sorts based only on their names?
1. GERMAN
2. FRENCH_M
Select all the true statements:
A. 1 is a monolingual sort.
B. 2 is a monolingual sort.
C. 1 is case-insensitive.
D. Both 1 and 2 are case-insensitive.
E. Case-sensitivity is unknown.
---
Ans: A.
A is the only true statement. The _M appended to the end of a sort name denotes a
multilingual sort. Its absence denotes a monolingual sort. Case-sensitive and accent-insensitive
sorts have _CI or _AI appended to the name. Its absence denotes case- and accent-sensitivity.
10. In a database with the database character set of US7ASCII and a national
character set of UTF-8, which datatypes would be capable of storing Unicode
data by default?
A. VARCHAR2
B. CHAR
C. NVARCHAR2
D. CLOB
E. LONG
---
Ans: C.
NLS datatypes (NCHAR, NVARCHAR, and NCLOB) store data using the character set
defined as the national character set by default. Because the national character set is
UTF-8 (a Unicode character set), data stored in these datatypes will be Unicode data
by default. All other datatypes use the character set defined as the database character
set. Because US7ASCII is not a Unicode character set, it does not store Unicode data
by default.
11. Automatic data conversion will occur if
A. The client and server have different NLS_LANGUAGE settings.
B. The client and server character sets are not the same, and the database character set is not a strict superset of the client character set.
C. The client and server are in different time zones.
D. The client requests automatic data conversion.
E. The AUTO_CONVERT initialization parameter is set to TRUE.
--
Ans: B.
Automatic data conversion occurs when data is moved between character sets.
However, if the server character set is a strict superset of the client character set, no
conversion is necessary.
12. Which of the following NLS_SORT parameter values would result in case-insensitive
and accent-insensitive binary sorts?
A. NLS_SORT = BINARY
B. NLS_SORT = BINARY_AI
C. NLS_SORT = BINARY_CI
D. NLS_SORT = BINARY_AI_CI
E. Binary sorts are case and accent-insensitive by default.
---
Ans: B.
The _AI suffix implies that an accent-insensitive sort will be performed. Accent-insensitive
sorts are also case-insensitive by default. The _CI suffix implies that a
case-insensitive sort will be performed, but it will not be accent-insensitive.
Specifying both suffixes (_AI_CI) is illegal.
13. Which NLS parameter can be used to change the default Oracle sort method
from binary to linguistic for the SQL SELECT statement?
A. NLS_LANG
B. NLS_SORT
C. NLS_COMP
D. NLS_SORT
E. None of the above
--
Ans: E.
The SQL SELECT statement does not invoke a sort.
14. Which of the following would be affected by setting NLS_LENGTH_SEMANTICS=CHAR?
A. All objects in the database
B. Tables owned by SYS and SYSTEM
C. Data dictionary tables
D. NCHAR columns
E. CHAR columns
----
Ans: E.
Only option E is correct. Tables owned by the SYS and SYSTEM users are not
affected by default-length semantics. Data dictionary tables always use byte
semantics, and NCHAR columns always use character semantics. Therefore, neither is
affected by the setting of the NLS_LENGTH_ SEMANTICS parameter.
15. Which is not a valid locale definition file type?
A. Language
B. Linguistic sort
C. Calendar
D. Territory
E. Character set
---
Ans: C.
Calendar definitions are not stored as locale definition files. Only languages,
linguistic sorts, territories, and character set definitions are stored as locale definition
files.
16. How many different calendars does Oracle 10g support?
A. 22
B. 7
C. 6
D. 15
E. 2
----
Ans: B.
Oracle supports 7 distinct calendars: Gregorian, Japanese Imperial, ROC Official,
Persian, Thai Buddha, Arabic Hijrah, and English Hijrah.
17. Which NLS parameter directly governs linguistic searches?
A. NLS_SEARCH_L
B. NLS_SORT
C. NLS_SEARCH
D. NLS_SORT_L
E. None of the above
---
Ans: B.
Linguistic searches are closely related to linguistic sorts and are governed by the
NLS_SORT parameter.
18. Case-insensitive sorts are always accent-insensitive by default.
A. True
B. False
---
Ans: B.
Accent-insensitive sorts are always case-insensitive, not the other way around.
19. What is the name of the file that identifies the set of available locale definitions?
A. locale.def
B. lxdef.ora
C. lx1boot.nlb
D. lx1boot.ora
E. lang.def
--
Ans: C.
The lx1boot.nlb file identifies the available locale definitions to the NLSRTL.
20. Which of the following is not a valid linguistic sort element?
A. Accent expansion
B. Canonical equivalence
C. Reverse secondary sorting
D. Ignorable characters
E. Character rearrangement
---
Ans: A.
Linguistic sort elements define the rules for linguistic sorting. There is no
linguistic sort element named "accent expansion." The other choices are all valid
rules.
Friday, September 28, 2007
OCP Oracle 10g Admin II - Ch11 Managing Resources
Managing Resources
Review Questions
1. Which system privileges allow you to administer the Database Resource
Manager?
A. DBA
B. ADMINISTER_ANY_RESOURCE
C. ADMINISTER_RESOURCE_MANAGER
D. RESOURCE_MANAGER_ADMIN
E. All of the above
---
Ans: C.
While the DBA role will grant the ADMINISTER_RESOURCE_MANAGER privilege,
DBA is not a valid system privilege name.
2. When creating a plan directive, which of the following resource allocation
methods are valid when the target of the directive is a sub-plan?
A. Execution time limit
B. Undo pool
C. Automatic consumer group switching
D. Canceling SQL and terminating sessions
E. CPU method
---
Ans: E.
A plan directive can only allocate CPU resources to a sub-plan. A, B, C, and D are
all valid allocation methods, but they can only be defined for a resource consumer
group, not for a sub-plan.
E.
3. Which of the following is not a valid consumer group mapping attribute?
A. CLIENT_OS_USER
B. MODULE_NAME
C. CLIENT_PROGRAM
D. MODULE_ACTION
E. SERVICE_MODULE_ACTION
---
Ans: D.
MODULE_ACTION is not a valid attribute by itself. The DBMS_APPLICATION_INFO
package allows applications to define an application name and, secondarily, the
action that they are currently performing. In general, the action without a
corresponding name isn't useful. Therefore, MODULE_NAME and MODULE_NAME_ACTION
are both useful and valid mapping attributes, but not MODULE_ACTION.
4. Which of the following methods of CPU allocation can be defined for a single level resource plan?
A. RATIO
B. EMPHASIS
C. BASIC
D. A, B, and C
E. Both A and B
---
Ans: E.
A single-level resource plan can allocate CPU resources to consumer groups using
either the RATIO or the EMPHASIS (percentage) method. A multi-level resource plan is
limited to using the EMPHASIS method exclusively. BASIC is not a valid CPU
allocation method.
5. What is the effect of the following statement?
ALTER SYSTEM SET RESOURCE_MANAGER_PLAN = 'FORCE:NIGHT_PLAN';
A. DRM will be enabled with NIGHT_PLAN as the top-level plan. Changes to the top-level plan will be restricted.
B. NIGHT_PLAN will be moved into the data dictionary, and DRM will be
enabled with NIGHT_PLAN as the top-level plan. Changes to the toplevel
plan will be restricted.
C. The current DRM top-level plan will be changed to NIGHT_PLAN, in spite of restrictions.
D. NIGHT_PLAN will be moved into the data dictionary, and the current DRM top-level plan will be changed to NIGHT_PLAN, in spite of restrictions.
E. NIGHT_PLAN will be made active.
---
Ans: A.
The ALTER SYSTEM statement sets NIGHT_PLAN as the enabled plan. The FORCE:
prefix restricts the setting from being changed.
6. A simple resource plan can define how many CPU allocation levels?
A. 1
B. 2
C. 4
D. 8
E. 16
---
Ans: D.
Simple resource plans can define up to eight levels of CPU allocation.
7. A session assigned to the MARKETING group is switched to the LOW_GROUP
because it exceeded the execution time set in the SWITCH_TIME_IN_CALL
parameter of the plan directive. When the long-running operation completes,
which group will the session be assigned to?
A. MARKETING
B. LOW_GROUP
C. DEFAULT_GROUP
D. None, the session would have been killed.
----
Ans: A.
The SWITCH_TIME_IN_CALL parameter will cause the offending session to be
switched for the duration of the current operation. It will then revert to its original
group.
8. Where are active DRM objects are located?
A. V$ tables
B. Pending area
C. Data dictionary
D. DRM_OBJECTS table
E. None of the above
----
Ans: C.
When objects in the pending area are submitted, they become active and are stored
in the data dictionary.
9. Consumer groups can be automatically assigned to sessions based on the
mappings created through which DBMS_RESOURCE_MANAGER procedure?
A. CREATE_CONSUMER_GROUP_MAPPING
B. CREATE_GROUP_MAPPING
C. SET_CONSUMER_GROUP_MAPPING
D. MAP_CONSUMER_GROUPS
E. CREATE_CONSUMER_RSRC_GROUP_MAPPING
---
Ans: C.
The SET_CONSUMER_GROUP_MAPPING procedure is used to map session attributes to
consumer groups.
10. Every resource plan must contain an allocation to which consumer group?
A. LOW_GROUP
B. SYS_GROUP
C. DEFAULT_GROUP
D. BASE_GROUP
E. OTHER_GROUPS
---
Ans: E.
The OTHER_GROUPS consumer group is assigned to sessions whose assigned group
is not contained in the enabled plan. Therefore, Oracle requires that an allocation be
made so that no sessions will be completely deprived of resources.
11. Which DBMS_RESOURCE_MANAGER procedure prioritizes consumer group mappings?
A. CREATE_MAPPING_PRIORITY
B. SET_MAPPING_PRIORITY
C. SET_MAPPING_ORDER
D. PRIORITIZE_MAPPING_ORDER
E. This functionality is not available through the DBMS_RESOURCE_MANAGER package.
---
Ans: B. The SET_MAPPING_PRIORITY procedure allows for prioritization based on the
session attribute type.
12. Which of the following are valid methods of switching consumer groups for one or more sessions? (Choose all that apply.)
A. SWITCH_CONSUMER_GROUP_FOR_SESS procedure of DBMS_RESOURCE_MANAGER
B. SWITCH_CONSUMER_GROUPS procedure of DBMS_RESOURCE_MANAGER
C. SWITCH_CONSUMER_GROUP_FOR_USER procedure of DBMS_RESOURCE_MANAGER
D. ALTER SESSION SWITCH CONSUMER GROUP command
E. All of the above
---
Ans: A, C. The SWITCH_CONSUMER_GROUP_FOR_SESS procedure will switch a single session. The procedure SWITCH_CONSUMER_GROUP_FOR_USER will switch all sessions owned by a user.
13. If a session is assigned to a consumer group that is not included in the enabled plan, which group will it be considered part of?
A. None
B. BASE_GROUP
C. DEFAULT_GROUP
D. OTHER_GROUPS
E. SWITCH_GROUP
---
Ans: D.
If a session belongs to a consumer resource group that receives no allocation of
resources from the enabled plan, their session could not function. Therefore, Oracle
switches them to the OTHER_GROUPS group. Because all resource plans are required
to allocate resources to the OTHER_GROUPS group, the session will receive at least
some level of resources.
14. Within a resource plan definition, what differentiates a top-level plan from a
sub-plan?
A. A sub-plan has the PLAN_SUB parameter value set to SUB.
B. A top-level plan has the GROUP_OR_PLAN parameter set to the name of the sub-plan in the resource plan definition.
C. There is no difference in the resource plan definition.
D. A sub-plan always has the CPU_MTH parameter value set to RATIO.
E. The string TOP_LEVEL is appended to the name of top-level resource plans.
---
Ans: C.
There is no concept of sub-plan in the resource plan definition. Only in a resource
plan directive can a sub-plan be identified.
15. Which DBMS_RESOURCE_MANAGER procedure deletes a plan along with all of its subordinate objects?
A. DELETE_PLAN_CASCADE
B. DELETE_RESOURCE_PLAN_CASCADE
C. DROP_PLAN
D. DROP_PLAN_CASCADE
E. Subordinate objects must be deleted separately.
---
Ans: A.
The DELETE_PLAN_CASCADE procedure removes a plan along with any subordinate
objects that it may reference. Using the DELETE_PLAN procedure, on the other hand,
removes the plan but leaves the subordinate objects in place.
16. Which DRM object ties a plan to a resource group and also defines the
allocation method to be utilized?
A. Resource plan
B. Resource plan directive
C. Resource plan rule
D. Consumer group directive
E. None of the above
----
Ans: B.
A resource plan directive identifies a plan, a consumer group (or sub-plan), and
defines the allocation method for a resource.
17. Which view displays the resource consumer groups currently assigned to sessions?
A. V$SESS_GROUP
B. DBA_RSRC_GROUP
C. DBA_RSRC_PLANS
D. V$RSRC_CONSUMER_GROUPS
E. V$SESSION
---
Ans: E.
The V$SESSION view contains a CONSUMER_GROUP column that displays the value of the consumer group currently assigned to the session.
18. The switch privilege can be granted to a user or role by using which
method(s)? (Choose all that apply.)
A. GRANT statement
B. DBMS_RESOURCE_MANAGER procedure
C. DBMS_RESOURCE_MANAGER_PRIVS procedure
D. DBMS_SESSION procedure
E. All of the above
---
Ans: C.
The GRANT_SWITCH_CONSUMER_GROUP procedure in the DBMS_RESOURCE_MANAGER_PRIVS package is the only way to grant the switch privilege to users or roles.
19. Which of the following statements are true regarding users who have been granted the switch privilege? (Choose all that apply.)
A. They can switch only to a consumer group with equal or lower priority.
B. They can switch the group for any session as long as they have been granted the privilege ADMIN option.
C. They can switch to any consumer group for which they have been granted the switch privilege.
D. They can grant the switch privilege to another user if they have been granted the privilege with the ADMIN option.
E. All of the above are true.
---
Ans: C, D.
The switch privilege allows the user to switch their own session to the specified
consumer group. They can grant this privilege to another user if they have ADMIN
rights. They cannot, however, switch consumer groups for sessions other than their
own. Also, they are not limited to switching only to a lower priority group.
20. If the switch privilege has been granted to PUBLIC for a specific consumer group, which of the following statements is true?
A. Any user assigned to that group can switch to another group.
B. Any user can switch to the specified group.
C. Any user with a SWITCH_PUBLIC grant can switch to the specified group.
D. Both A and B are true.
E. A, B, and C are true.
---
Ans: B. Only option B is true. The switch privilege granted to PUBLIC allows any user the
privilege of switching to the group.
Review Questions
1. Which system privileges allow you to administer the Database Resource
Manager?
A. DBA
B. ADMINISTER_ANY_RESOURCE
C. ADMINISTER_RESOURCE_MANAGER
D. RESOURCE_MANAGER_ADMIN
E. All of the above
---
Ans: C.
While the DBA role will grant the ADMINISTER_RESOURCE_MANAGER privilege,
DBA is not a valid system privilege name.
2. When creating a plan directive, which of the following resource allocation
methods are valid when the target of the directive is a sub-plan?
A. Execution time limit
B. Undo pool
C. Automatic consumer group switching
D. Canceling SQL and terminating sessions
E. CPU method
---
Ans: E.
A plan directive can only allocate CPU resources to a sub-plan. A, B, C, and D are
all valid allocation methods, but they can only be defined for a resource consumer
group, not for a sub-plan.
E.
3. Which of the following is not a valid consumer group mapping attribute?
A. CLIENT_OS_USER
B. MODULE_NAME
C. CLIENT_PROGRAM
D. MODULE_ACTION
E. SERVICE_MODULE_ACTION
---
Ans: D.
MODULE_ACTION is not a valid attribute by itself. The DBMS_APPLICATION_INFO
package allows applications to define an application name and, secondarily, the
action that they are currently performing. In general, the action without a
corresponding name isn't useful. Therefore, MODULE_NAME and MODULE_NAME_ACTION
are both useful and valid mapping attributes, but not MODULE_ACTION.
4. Which of the following methods of CPU allocation can be defined for a single level resource plan?
A. RATIO
B. EMPHASIS
C. BASIC
D. A, B, and C
E. Both A and B
---
Ans: E.
A single-level resource plan can allocate CPU resources to consumer groups using
either the RATIO or the EMPHASIS (percentage) method. A multi-level resource plan is
limited to using the EMPHASIS method exclusively. BASIC is not a valid CPU
allocation method.
5. What is the effect of the following statement?
ALTER SYSTEM SET RESOURCE_MANAGER_PLAN = 'FORCE:NIGHT_PLAN';
A. DRM will be enabled with NIGHT_PLAN as the top-level plan. Changes to the top-level plan will be restricted.
B. NIGHT_PLAN will be moved into the data dictionary, and DRM will be
enabled with NIGHT_PLAN as the top-level plan. Changes to the toplevel
plan will be restricted.
C. The current DRM top-level plan will be changed to NIGHT_PLAN, in spite of restrictions.
D. NIGHT_PLAN will be moved into the data dictionary, and the current DRM top-level plan will be changed to NIGHT_PLAN, in spite of restrictions.
E. NIGHT_PLAN will be made active.
---
Ans: A.
The ALTER SYSTEM statement sets NIGHT_PLAN as the enabled plan. The FORCE:
prefix restricts the setting from being changed.
6. A simple resource plan can define how many CPU allocation levels?
A. 1
B. 2
C. 4
D. 8
E. 16
---
Ans: D.
Simple resource plans can define up to eight levels of CPU allocation.
7. A session assigned to the MARKETING group is switched to the LOW_GROUP
because it exceeded the execution time set in the SWITCH_TIME_IN_CALL
parameter of the plan directive. When the long-running operation completes,
which group will the session be assigned to?
A. MARKETING
B. LOW_GROUP
C. DEFAULT_GROUP
D. None, the session would have been killed.
----
Ans: A.
The SWITCH_TIME_IN_CALL parameter will cause the offending session to be
switched for the duration of the current operation. It will then revert to its original
group.
8. Where are active DRM objects are located?
A. V$ tables
B. Pending area
C. Data dictionary
D. DRM_OBJECTS table
E. None of the above
----
Ans: C.
When objects in the pending area are submitted, they become active and are stored
in the data dictionary.
9. Consumer groups can be automatically assigned to sessions based on the
mappings created through which DBMS_RESOURCE_MANAGER procedure?
A. CREATE_CONSUMER_GROUP_MAPPING
B. CREATE_GROUP_MAPPING
C. SET_CONSUMER_GROUP_MAPPING
D. MAP_CONSUMER_GROUPS
E. CREATE_CONSUMER_RSRC_GROUP_MAPPING
---
Ans: C.
The SET_CONSUMER_GROUP_MAPPING procedure is used to map session attributes to
consumer groups.
10. Every resource plan must contain an allocation to which consumer group?
A. LOW_GROUP
B. SYS_GROUP
C. DEFAULT_GROUP
D. BASE_GROUP
E. OTHER_GROUPS
---
Ans: E.
The OTHER_GROUPS consumer group is assigned to sessions whose assigned group
is not contained in the enabled plan. Therefore, Oracle requires that an allocation be
made so that no sessions will be completely deprived of resources.
11. Which DBMS_RESOURCE_MANAGER procedure prioritizes consumer group mappings?
A. CREATE_MAPPING_PRIORITY
B. SET_MAPPING_PRIORITY
C. SET_MAPPING_ORDER
D. PRIORITIZE_MAPPING_ORDER
E. This functionality is not available through the DBMS_RESOURCE_MANAGER package.
---
Ans: B. The SET_MAPPING_PRIORITY procedure allows for prioritization based on the
session attribute type.
12. Which of the following are valid methods of switching consumer groups for one or more sessions? (Choose all that apply.)
A. SWITCH_CONSUMER_GROUP_FOR_SESS procedure of DBMS_RESOURCE_MANAGER
B. SWITCH_CONSUMER_GROUPS procedure of DBMS_RESOURCE_MANAGER
C. SWITCH_CONSUMER_GROUP_FOR_USER procedure of DBMS_RESOURCE_MANAGER
D. ALTER SESSION SWITCH CONSUMER GROUP command
E. All of the above
---
Ans: A, C. The SWITCH_CONSUMER_GROUP_FOR_SESS procedure will switch a single session. The procedure SWITCH_CONSUMER_GROUP_FOR_USER will switch all sessions owned by a user.
13. If a session is assigned to a consumer group that is not included in the enabled plan, which group will it be considered part of?
A. None
B. BASE_GROUP
C. DEFAULT_GROUP
D. OTHER_GROUPS
E. SWITCH_GROUP
---
Ans: D.
If a session belongs to a consumer resource group that receives no allocation of
resources from the enabled plan, their session could not function. Therefore, Oracle
switches them to the OTHER_GROUPS group. Because all resource plans are required
to allocate resources to the OTHER_GROUPS group, the session will receive at least
some level of resources.
14. Within a resource plan definition, what differentiates a top-level plan from a
sub-plan?
A. A sub-plan has the PLAN_SUB parameter value set to SUB.
B. A top-level plan has the GROUP_OR_PLAN parameter set to the name of the sub-plan in the resource plan definition.
C. There is no difference in the resource plan definition.
D. A sub-plan always has the CPU_MTH parameter value set to RATIO.
E. The string TOP_LEVEL is appended to the name of top-level resource plans.
---
Ans: C.
There is no concept of sub-plan in the resource plan definition. Only in a resource
plan directive can a sub-plan be identified.
15. Which DBMS_RESOURCE_MANAGER procedure deletes a plan along with all of its subordinate objects?
A. DELETE_PLAN_CASCADE
B. DELETE_RESOURCE_PLAN_CASCADE
C. DROP_PLAN
D. DROP_PLAN_CASCADE
E. Subordinate objects must be deleted separately.
---
Ans: A.
The DELETE_PLAN_CASCADE procedure removes a plan along with any subordinate
objects that it may reference. Using the DELETE_PLAN procedure, on the other hand,
removes the plan but leaves the subordinate objects in place.
16. Which DRM object ties a plan to a resource group and also defines the
allocation method to be utilized?
A. Resource plan
B. Resource plan directive
C. Resource plan rule
D. Consumer group directive
E. None of the above
----
Ans: B.
A resource plan directive identifies a plan, a consumer group (or sub-plan), and
defines the allocation method for a resource.
17. Which view displays the resource consumer groups currently assigned to sessions?
A. V$SESS_GROUP
B. DBA_RSRC_GROUP
C. DBA_RSRC_PLANS
D. V$RSRC_CONSUMER_GROUPS
E. V$SESSION
---
Ans: E.
The V$SESSION view contains a CONSUMER_GROUP column that displays the value of the consumer group currently assigned to the session.
18. The switch privilege can be granted to a user or role by using which
method(s)? (Choose all that apply.)
A. GRANT statement
B. DBMS_RESOURCE_MANAGER procedure
C. DBMS_RESOURCE_MANAGER_PRIVS procedure
D. DBMS_SESSION procedure
E. All of the above
---
Ans: C.
The GRANT_SWITCH_CONSUMER_GROUP procedure in the DBMS_RESOURCE_MANAGER_PRIVS package is the only way to grant the switch privilege to users or roles.
19. Which of the following statements are true regarding users who have been granted the switch privilege? (Choose all that apply.)
A. They can switch only to a consumer group with equal or lower priority.
B. They can switch the group for any session as long as they have been granted the privilege ADMIN option.
C. They can switch to any consumer group for which they have been granted the switch privilege.
D. They can grant the switch privilege to another user if they have been granted the privilege with the ADMIN option.
E. All of the above are true.
---
Ans: C, D.
The switch privilege allows the user to switch their own session to the specified
consumer group. They can grant this privilege to another user if they have ADMIN
rights. They cannot, however, switch consumer groups for sessions other than their
own. Also, they are not limited to switching only to a lower priority group.
20. If the switch privilege has been granted to PUBLIC for a specific consumer group, which of the following statements is true?
A. Any user assigned to that group can switch to another group.
B. Any user can switch to the specified group.
C. Any user with a SWITCH_PUBLIC grant can switch to the specified group.
D. Both A and B are true.
E. A, B, and C are true.
---
Ans: B. Only option B is true. The switch privilege granted to PUBLIC allows any user the
privilege of switching to the group.
Oracle 10g - Implement a Resource Managment Plan
Step 1 - Creating the Pending Area
-----------------------------------
exec dbms_resource_manager.create_pending_area();
Step 2 - Creating the Consumer Resource Groups
-----------------------------------------------
begin
dbms_resource_manager.create_consumer_group(
'OLTP_GROUP','Incoming orders');
end;
/
begin
dbms_resource_manager.create_consumer_group(
'DAY_REPORTS_GROUP','DAYTIME REPORTS');
end;
/
begin
dbms_resource_manager.create_consumer_group(
'NIGHTLY_PROCESSING_GROUP','BULK LOADS, ETL, ETC.');
end;
/
begin
dbms_resource_manager.create_consumer_group(
'OLAP_REPORTS_GROUP','OFF HOURS REPORTS');
end;
/
Step 3 - Creating the Resource Plans
------------------------------------
begin
dbms_resource_manager.create_plan(
PLAN => 'DAY_PLAN',
COMMENT => 'GOVERNS NORMAL WORKING HOURS ');
end;
/
begin
dbms_resource_manager.create_plan(
PLAN => 'OLTP_PLAN',
COMMENT => 'ORDER ENTRY SUB-PLAN');
end;
/
begin
dbms_resource_manager.create_plan(
PLAN => 'OFF_HOURS_PLAN',
COMMENT => 'GOVERNS NON-WORKING HOURS',
CPU_MTH => 'RATIO');
end;
/
Step 4- Creating the Resource Plan Directives
4.1 Creating the OFF_HOURS_PLAN Plan Directives
Fig. OFF_HOURS_PLAN high-level design
begin
dbms_resource_manager.create_plan_directive(
PLAN => 'OFF_HOURS_PLAN',
GROUP_OR_SUBPLAN => 'SYS_GROUP',
COMMENT => 'CPU ALLOCATION FOR SYS_GROUP',
CPU_P1 => 10);
end;
/
begin
dbms_resource_manager.create_plan_directive(
PLAN => 'OFF_HOURS_PLAN',
GROUP_OR_SUBPLAN => 'NIGHTLY_PROCESSING_GROUP',
COMMENT => 'CPU ALLOCATION FOR NIGHTLY JOBS',
CPU_P1 => 5);
end;
/
begin
dbms_resource_manager.create_plan_directive(
PLAN => 'OFF_HOURS_PLAN',
GROUP_OR_SUBPLAN => 'OLAP_REPORTS_GROUP',
COMMENT => 'CPU ALLOCATION FOR NIGHTLY REPORTS',
CPU_P1 => 2);
end;
/
begin
dbms_resource_manager.create_plan_directive(
PLAN => 'OFF_HOURS_PLAN',
GROUP_OR_SUBPLAN => 'OTHER_GROUPS',
COMMENT => 'CPU ALLOCATION FOR OTHER_GROUPS',
CPU_P1 => 1);
end;
/
The CPU allocation ratio for the OFF_HOURS_PLAN plan will be 10:5:2:1.
4.2 Creating the OLTP_PLAN Plan Directives
Fig. DAY_PLAN high-level design
begin
dbms_resource_manager.create_plan_directive(
PLAN => 'OLTP_PLAN',
GROUP_OR_SUBPLAN => 'OLTP_GROUP',
COMMENT => 'CPU ALLOCATION FOR OLTP USERS',
CPU_P1 => 90);
end;
/
begin
dbms_resource_manager.create_plan_directive(
PLAN => 'OLTP_PLAN',
GROUP_OR_SUBPLAN => 'DAY_REPORTS_GROUP',
COMMENT => 'CPU ALLOCATION FOR DAYTIME REPORTING',
CPU_P1 => 10);
end;
/
begin
dbms_resource_manager.create_plan_directive(
PLAN => 'OLTP_PLAN',
GROUP_OR_SUBPLAN => 'OTHER_GROUPS',
COMMENT => 'CPU ALLOCATION FOR OTHER_GROUPS',
CPU_P2 => 100);
end;
/
4.3 Creating the DAY_PLAN Plan Directives
begin
dbms_resource_manager.create_plan_directive(
PLAN => 'DAY_PLAN',
GROUP_OR_SUBPLAN => 'SYS_GROUP',
COMMENT => 'CPU ALLOCATION FOR SYS_GROUP',
CPU_P1 => 100);
end;
/
begin
dbms_resource_manager.create_plan_directive(
PLAN => 'DAY_PLAN',
GROUP_OR_SUBPLAN => 'OLTP_PLAN',
COMMENT => 'CPU ALLOCATION FOR OLTP_PLAN SUB-PLAN',
CPU_P2 => 100);
end;
/
begin
dbms_resource_manager.create_plan_directive(
PLAN => 'DAY_PLAN',
GROUP_OR_SUBPLAN => 'OTHER_GROUPS',
COMMENT => 'CPU ALLOCATION FOR OTHER_GROUPS',
CPU_P3 => 100);
end;
/
Step 5 - Validating the Pending Area
--------------------------------------
sys@ORCL> exec dbms_resource_manager.validate_pending_area;
PL/SQL procedure successfully completed.
Step 6 - Submitting the Pending Area
------------------------------------
sys@ORCL> exec dbms_resource_manager.submit_pending_area;
PL/SQL procedure successfully completed.
Step 7 - Enabling the Resource Plans
-----------------------------------
ALTER SYSTEM SET RESOURCE_MANAGER_PLAN = 'DAY_PLAN' SCOPE = BOTH;
Step 8 - Switching the Enabled Resource Plan
--------------------------------------------
SQL> ALTER SYSTEM SET RESOURCE_MANAGER_PLAN = 'FORCE:OFF_HOURS_PLAN';
By adding the prefix FORCE: to the name of the plan, Oracle will restrict the active plan
from being changed by the scheduler. The scheduler will still attempt to make the change,
but it will fail.
SQL> ALTER SYSTEM SET RESOURCE_MANAGER_PLAN = 'OFF_HOURS_PLAN';
-----------------------------------
exec dbms_resource_manager.create_pending_area();
Step 2 - Creating the Consumer Resource Groups
-----------------------------------------------
begin
dbms_resource_manager.create_consumer_group(
'OLTP_GROUP','Incoming orders');
end;
/
begin
dbms_resource_manager.create_consumer_group(
'DAY_REPORTS_GROUP','DAYTIME REPORTS');
end;
/
begin
dbms_resource_manager.create_consumer_group(
'NIGHTLY_PROCESSING_GROUP','BULK LOADS, ETL, ETC.');
end;
/
begin
dbms_resource_manager.create_consumer_group(
'OLAP_REPORTS_GROUP','OFF HOURS REPORTS');
end;
/
Step 3 - Creating the Resource Plans
------------------------------------
begin
dbms_resource_manager.create_plan(
PLAN => 'DAY_PLAN',
COMMENT => 'GOVERNS NORMAL WORKING HOURS ');
end;
/
begin
dbms_resource_manager.create_plan(
PLAN => 'OLTP_PLAN',
COMMENT => 'ORDER ENTRY SUB-PLAN');
end;
/
begin
dbms_resource_manager.create_plan(
PLAN => 'OFF_HOURS_PLAN',
COMMENT => 'GOVERNS NON-WORKING HOURS',
CPU_MTH => 'RATIO');
end;
/
Step 4- Creating the Resource Plan Directives
4.1 Creating the OFF_HOURS_PLAN Plan Directives
-------------------
OFF_HOURS_PLAN
Ratio 10:5:2:1
-------------------
/ /\ \
/ / \ \
/ / \ \
------------- / \ ----------------
SYS_GROUP / \ OTHER_GROUPS
10 / \ 1
-------------- / \ ----------------
/ \
---------------------------- ----------------------
NIGHTLY_PROCESSING_GROUP OLAP_REPORTS_GROUP
5 2
------------------ --------- ----------------------
Fig. OFF_HOURS_PLAN high-level design
begin
dbms_resource_manager.create_plan_directive(
PLAN => 'OFF_HOURS_PLAN',
GROUP_OR_SUBPLAN => 'SYS_GROUP',
COMMENT => 'CPU ALLOCATION FOR SYS_GROUP',
CPU_P1 => 10);
end;
/
begin
dbms_resource_manager.create_plan_directive(
PLAN => 'OFF_HOURS_PLAN',
GROUP_OR_SUBPLAN => 'NIGHTLY_PROCESSING_GROUP',
COMMENT => 'CPU ALLOCATION FOR NIGHTLY JOBS',
CPU_P1 => 5);
end;
/
begin
dbms_resource_manager.create_plan_directive(
PLAN => 'OFF_HOURS_PLAN',
GROUP_OR_SUBPLAN => 'OLAP_REPORTS_GROUP',
COMMENT => 'CPU ALLOCATION FOR NIGHTLY REPORTS',
CPU_P1 => 2);
end;
/
begin
dbms_resource_manager.create_plan_directive(
PLAN => 'OFF_HOURS_PLAN',
GROUP_OR_SUBPLAN => 'OTHER_GROUPS',
COMMENT => 'CPU ALLOCATION FOR OTHER_GROUPS',
CPU_P1 => 1);
end;
/
The CPU allocation ratio for the OFF_HOURS_PLAN plan will be 10:5:2:1.
4.2 Creating the OLTP_PLAN Plan Directives
-------------
DAY_PLAN
------- ------------- ----------------------
-------------
level 1 SYS_GROUP
100%
-------------
-------------
level 2 OLTP_PLAN
100%
-------------
/ \
Sub-plan ------------ -------------------
level 1 OLTP_GROUP DAY_REPORT_GROUP
90% 10%
------------- ------------------
Sub-plan ---------------
level 2 OTHER_GROUP
100%
---------------
level 3 ---------------
OTHER_GROUP
100%
---------------
Fig. DAY_PLAN high-level design
begin
dbms_resource_manager.create_plan_directive(
PLAN => 'OLTP_PLAN',
GROUP_OR_SUBPLAN => 'OLTP_GROUP',
COMMENT => 'CPU ALLOCATION FOR OLTP USERS',
CPU_P1 => 90);
end;
/
begin
dbms_resource_manager.create_plan_directive(
PLAN => 'OLTP_PLAN',
GROUP_OR_SUBPLAN => 'DAY_REPORTS_GROUP',
COMMENT => 'CPU ALLOCATION FOR DAYTIME REPORTING',
CPU_P1 => 10);
end;
/
begin
dbms_resource_manager.create_plan_directive(
PLAN => 'OLTP_PLAN',
GROUP_OR_SUBPLAN => 'OTHER_GROUPS',
COMMENT => 'CPU ALLOCATION FOR OTHER_GROUPS',
CPU_P2 => 100);
end;
/
4.3 Creating the DAY_PLAN Plan Directives
begin
dbms_resource_manager.create_plan_directive(
PLAN => 'DAY_PLAN',
GROUP_OR_SUBPLAN => 'SYS_GROUP',
COMMENT => 'CPU ALLOCATION FOR SYS_GROUP',
CPU_P1 => 100);
end;
/
begin
dbms_resource_manager.create_plan_directive(
PLAN => 'DAY_PLAN',
GROUP_OR_SUBPLAN => 'OLTP_PLAN',
COMMENT => 'CPU ALLOCATION FOR OLTP_PLAN SUB-PLAN',
CPU_P2 => 100);
end;
/
begin
dbms_resource_manager.create_plan_directive(
PLAN => 'DAY_PLAN',
GROUP_OR_SUBPLAN => 'OTHER_GROUPS',
COMMENT => 'CPU ALLOCATION FOR OTHER_GROUPS',
CPU_P3 => 100);
end;
/
Step 5 - Validating the Pending Area
--------------------------------------
sys@ORCL> exec dbms_resource_manager.validate_pending_area;
PL/SQL procedure successfully completed.
Step 6 - Submitting the Pending Area
------------------------------------
sys@ORCL> exec dbms_resource_manager.submit_pending_area;
PL/SQL procedure successfully completed.
Step 7 - Enabling the Resource Plans
-----------------------------------
ALTER SYSTEM SET RESOURCE_MANAGER_PLAN = 'DAY_PLAN' SCOPE = BOTH;
Step 8 - Switching the Enabled Resource Plan
--------------------------------------------
SQL> ALTER SYSTEM SET RESOURCE_MANAGER_PLAN = 'FORCE:OFF_HOURS_PLAN';
By adding the prefix FORCE: to the name of the plan, Oracle will restrict the active plan
from being changed by the scheduler. The scheduler will still attempt to make the change,
but it will fail.
SQL> ALTER SYSTEM SET RESOURCE_MANAGER_PLAN = 'OFF_HOURS_PLAN';
Thursday, September 27, 2007
OCP Oracle 10g Admin II - Ch12: Using the Scheduler to Automate Tasks
Chapter 12: Using the Scheduler to Automate Tasks
Review Questions
1. When setting arguments for a job, which procedure do you use for types that
cannot be implicitly converted to and from a VARCHAR2 datatype?
A. SET_JOB_ARGUMENT_VALUE
B. SET_JOB_VALUE_ANYDATA
C. SET_JOB_ANYDATA_VALUE
D. SET_SPECIAL_JOB_VALUE
E. SET_JOB_ANYTYPE_VALUE
-----
Ans: C.
The SET_JOB_ANYDATA_VALUE procedure allows you to set job arguments that
don't easily convert to and from a string (VARCHAR2) datatype.
2. Which DBMS_SCHEDULER procedures can be used to enable a program? (Choose
all that apply.)
A. ENABLE
B. ENABLE_PROGRAM
C. VALIDATE_PROGRAM
D. SET_ATTRIBUTE
E. SET_ENABLED
----
Ans: A, D.
Programs (as well as jobs) can be enabled in two ways: by using the ENABLE
procedure or by using the SET_ATTRIBUTE procedure to set the ENABLED attribute to
TRUE.
3. Which of the following is not a valid calendaring syntax element?
A. FREQ
B. BYHOUR
C. RUNDATE
D. INTERVAL
E. BYMINUTE
---
Ans: C.
The calendaring syntax does not support an element named RUNDATE. It does not
support the concept of specifying a single run date at all. The purpose of the
calendaring syntax is to define repeat intervals that will be used to calculate run
dates.
4. Which Scheduler view(s) can be queried to see which jobs are currently
executing? (Choose all that apply.)
A. DBA_SCHEDULER_JOB_RUN_DETAILS
B. DBA_SCHEDULER_RUNNING_JOBS
C. DBA_SCHEDULER_CURRENT_JOBS
D. DBA_SCHEDULER_JOBS
E. DBA_SCHEDULER_EXECUTING_JOBS
---
Ans: B, D.
The DBA_SCHEDULER_RUNNING_JOBS view shows detailed information about all
jobs currently executing. The DBA_SCHEDULER_JOBS view contains the STATE
column, which shows a value of RUNNING for an executing job.
5. A schedule defined entirely within the confines of a Scheduler job object is
known as a(n) ____ _______________.
A. Fixed schedule
B. Inline schedule
C. Stored schedule
D. Hard-coded schedule
E. None of the above
---
Ans: B.
A schedule defined within a job object is known as an inline schedule, whereas an
independent schedule object is referred to as a stored schedule. Inline schedules
cannot be referenced by other objects.
6. Which DBMS_SCHEDULER procedure(s) can be used to alter an existing job?
(Choose all that apply.)
A. SET_ATTRIBUTE_NULL
B. ALTER_JOB
C. ALTER_JOB_PARAMETERS
D. ALTER
E. SET_ATTRIBUTE
----
Ans: A, E.
A job can be altered only by changing the value of one or more of its attributes.
This is accomplished by using the SET_ATTRIBUTE and SET_ATTRIBUTE_NULL
procedures.
7. What is the default value for the ENABLED attribute of a job or program when it
is created?
A. TRUE
B. FALSE
C. There is no default. It must be defined at creation time.
D. PENDING
E. NULL
---
Ans: B.
Jobs and programs are created in a disabled state by default. They must be enabled
by setting the ENABLE parameter to TRUE in their respective CREATE statements, or by
altering the object after creation.
8. To set the history retention period for either window logging or job logging
individually, which two parameters of the SET_SCHEDULER_ATTRIBUTE
procedure need to be used?
A. LOG_HISTORY
B. JOB_LOG_RETENTION
C. WINDOW_LOG_RETENTION
D. WHICH_LOG
E. LOG_NAME
---
Ans: A, D.
The LOG_HISTORY parameter defines the retention period for both job logging
and window logging by default. However, the WHICH_LOG parameter can be used to
specify either JOB_LOG or WINDOW_LOG.
9. Consider the following code snippet:
begin
dbms_scheduler.run_job('COLA_JOB',TRUE);
end;
If this code were executed, which of the following statements would be true?
(Choose all that apply.)
A. The COLA_JOB job would be executed asynchronously.
B. The COLA_JOB job would be executed synchronously.
C. The COLA_JOB job would run in the user's current session.
D. The COLA_JOB job would run with the FORCE option.
E. The user could continue to issue SQL statements in the session while
the COLA_JOB job was executing.
----
Ans: B, C.
The RUN_JOB procedure accepts two parameters: JOB_NAME and
USE_CURRENT_SESSION. In this example, the USE_CURRENT_SESSION parameter is set
to a value of TRUE. This causes the job to be executed synchronously in the user's
current session. Because the job runs synchronously, control would not return to the
user until the job was completed. Therefore, the user could not continue to execute
SQL statements while the job was running.
10. Which of the following calendaring syntax expressions would evaluate to the
last day of every month?
A. FREQ = MONTHLY; BYMONTHDAY = 31
B. FREQ = MONTHLY; BYMONTHDAY = -1
C. FREQ = DAILY; BYDAY = -1
D. FREQ = MONTHLY; BYDAY = 31
E. FREQ = DAILY; BYMONTHDAY = LAST_DAY
---
Ans: B.
The BYMONTHDAY element accepts negative values that represent a specific count of
days from the end of the month. Also, the FREQ parameter must be set to MONTHLY,
because it will execute every month.
11. Which of the following tasks is not performed by the job coordinator?
A. Update job log when a job completes
B. Spawn and remove job slaves
C. Write/read job info to/from memory cache
D. Query job table
E. Pass job information to job slaves
---
Ans: A. The job coordinator does not update the job log when a job completes. That
function is performed by the job slave that has been assigned to the job.
12. Which of the following objects can be directly referenced by a window object?
(Choose all that apply.)
A. Schedule object
B. Program object
C. Job object
D. Resource plan
E. Resource Consumer Group
---
Ans: A, D.
A window does not execute programs or jobs. It specifies a resource plan that
will be enabled based on a schedule. Therefore, it can reference both a schedule
object and a resource plan object. And while the resource plan may reference one or
more resource consumer groups, the window object does not directly reference them.
13. Which DBMS_SCHEDULER procedure is available for testing repeat intervals
produced by calendaring syntax expressions?
A. EVALUATE_REPEAT_INTERVAL
B. VALIDATE_CALENDAR_EXPRESSION
C. EVALUATE_CALENDAR_STRING
D. VALIDATE_CALENDAR_STRING
E. EVALUATE_INTERVAL_EXPRESSION
---
Ans: C.
The EVALUATE_CALENDAR_STRING procedure will generate execution dates from a
calendaring syntax expression.
14. Which of the following is not a valid setting for the PROGRAM_TYPE parameter
in a program object or the JOB_TYPE parameter in a job object?
A. PLSQL_BLOCK
B. JAVA_STORED_PROCEDURE
C. STORED_PROCEDURE
D. EXECUTABLE
E. None of the above are invalid settings.
---
Ans: B.
Java stored procedures cannot be executed by the job Scheduler unless they are
called from within a PL/SQL procedure wrapper. This can be done in a stored
procedure using PL/SQL's external procedure feature. Therefore, the job or program
type setting would be STORED_PROCEDURE.
15. Which of the following Scheduler elements encourage object reuse? (Choose
all that apply.)
A. Schedule objects
B. Program arguments
C. Job classes
D. Job arguments
E. All of the above
---
Ans: A, B, D.
Schedule objects do not specify any action to be performed; they simply
generate execution dates that any job can use. Program and job arguments allow the
jobs and programs to be reused by simply changing the arguments that are passed in.
Job classes simplify the management of jobs, but they do not specifically encourage
job reuse.
16. What is the danger associated with stopping a running job by using the
STOP_JOB procedure?
A. The job will need to be re-enabled before it will execute again.
B. The job may hold locks on objects referenced within the job.
C. All jobs within the job group will also be stopped.
D. The job may leave data in an inconsistent state.
E. There is no danger in using the STOP_JOB procedure.
---
Ans: D.
The Scheduler will attempt to wrap the job within a transaction and will execute a
rollback if a job is stopped. However, if the job has performed commits, the rollback
will only roll back any uncommitted changes. This could result in inconsistent data.
17. If a job references a schedule that has been disabled, what will be the result?
A. The job will be automatically disabled.
B. The job will never execute.
C. The job will attempt to execute, but will fail.
D. The job will inherit the DEFAULT_SCHEDULE schedule.
E. A schedule object cannot be disabled.
---
Ans: E.
A schedule object does not possess the ENABLED attribute. It is therefore enabled
upon creation and can never be disabled.
18. When a job exceeds the date specified in its END_DATE attribute, which of the
following will happen? (Choose all that apply.)
A. The job will be dropped automatically if the value of the AUTO_DROP
attribute is TRUE.
B. The job will be disabled if the value of the AUTO_DROP attribute is
FALSE.
C. The STATE attribute of the job will be set to COMPLETED if the value of
the AUTO_DROP attribute is FALSE.
D. All objects referenced by the job will be dropped if the value of the
AUTO_DROP attribute is TRUE and the value of the CASCADE attribute is
TRUE.
E. The STATE column of the job table will be set to COMPLETED for the job.
---
Ans: A, B, E.
When a job exceeds its end date, it will be dropped only if the AUTO_DROP
attribute is set to TRUE. Otherwise, it will be disabled. In either case, the STATE
column will be set to COMPLETED in the job table. A job object does not possess a
CASCADE attribute or a STATE attribute.
19. Which view can you query to see information on Scheduler windows?
A. DBA_WINDOWS
B. DBA_SCHEDULER_WINDOWS
C. DBA_WINDOW_OBJECTS
D. DBA_SCHEDULER_WINDOW_OBJECTS
E. DBA_ALL_SCHEDULER_WINDOWS
----
Ans: B. The DBA_SCHEDULER_WINDOWS view shows information on window objects
defined in the Scheduler.
20. If two windows overlap, which window attribute will determine if one should
be chosen over the other?
A. WINDOW_PRIORITY
B. PRIORITY
C. PRIORITY_LEVEL
D. WINDOW_PRIORITY_LEVEL
E. OVERLAP_RULE
---
Ans: A.
The WINDOW_PRIORITY attribute can be set to either HIGH or LOW for a window. If
two windows overlap and only one of the windows has a priority of HIGH, it will be
chosen.
Review Questions
1. When setting arguments for a job, which procedure do you use for types that
cannot be implicitly converted to and from a VARCHAR2 datatype?
A. SET_JOB_ARGUMENT_VALUE
B. SET_JOB_VALUE_ANYDATA
C. SET_JOB_ANYDATA_VALUE
D. SET_SPECIAL_JOB_VALUE
E. SET_JOB_ANYTYPE_VALUE
-----
Ans: C.
The SET_JOB_ANYDATA_VALUE procedure allows you to set job arguments that
don't easily convert to and from a string (VARCHAR2) datatype.
2. Which DBMS_SCHEDULER procedures can be used to enable a program? (Choose
all that apply.)
A. ENABLE
B. ENABLE_PROGRAM
C. VALIDATE_PROGRAM
D. SET_ATTRIBUTE
E. SET_ENABLED
----
Ans: A, D.
Programs (as well as jobs) can be enabled in two ways: by using the ENABLE
procedure or by using the SET_ATTRIBUTE procedure to set the ENABLED attribute to
TRUE.
3. Which of the following is not a valid calendaring syntax element?
A. FREQ
B. BYHOUR
C. RUNDATE
D. INTERVAL
E. BYMINUTE
---
Ans: C.
The calendaring syntax does not support an element named RUNDATE. It does not
support the concept of specifying a single run date at all. The purpose of the
calendaring syntax is to define repeat intervals that will be used to calculate run
dates.
4. Which Scheduler view(s) can be queried to see which jobs are currently
executing? (Choose all that apply.)
A. DBA_SCHEDULER_JOB_RUN_DETAILS
B. DBA_SCHEDULER_RUNNING_JOBS
C. DBA_SCHEDULER_CURRENT_JOBS
D. DBA_SCHEDULER_JOBS
E. DBA_SCHEDULER_EXECUTING_JOBS
---
Ans: B, D.
The DBA_SCHEDULER_RUNNING_JOBS view shows detailed information about all
jobs currently executing. The DBA_SCHEDULER_JOBS view contains the STATE
column, which shows a value of RUNNING for an executing job.
5. A schedule defined entirely within the confines of a Scheduler job object is
known as a(n) ____ _______________.
A. Fixed schedule
B. Inline schedule
C. Stored schedule
D. Hard-coded schedule
E. None of the above
---
Ans: B.
A schedule defined within a job object is known as an inline schedule, whereas an
independent schedule object is referred to as a stored schedule. Inline schedules
cannot be referenced by other objects.
6. Which DBMS_SCHEDULER procedure(s) can be used to alter an existing job?
(Choose all that apply.)
A. SET_ATTRIBUTE_NULL
B. ALTER_JOB
C. ALTER_JOB_PARAMETERS
D. ALTER
E. SET_ATTRIBUTE
----
Ans: A, E.
A job can be altered only by changing the value of one or more of its attributes.
This is accomplished by using the SET_ATTRIBUTE and SET_ATTRIBUTE_NULL
procedures.
7. What is the default value for the ENABLED attribute of a job or program when it
is created?
A. TRUE
B. FALSE
C. There is no default. It must be defined at creation time.
D. PENDING
E. NULL
---
Ans: B.
Jobs and programs are created in a disabled state by default. They must be enabled
by setting the ENABLE parameter to TRUE in their respective CREATE statements, or by
altering the object after creation.
8. To set the history retention period for either window logging or job logging
individually, which two parameters of the SET_SCHEDULER_ATTRIBUTE
procedure need to be used?
A. LOG_HISTORY
B. JOB_LOG_RETENTION
C. WINDOW_LOG_RETENTION
D. WHICH_LOG
E. LOG_NAME
---
Ans: A, D.
The LOG_HISTORY parameter defines the retention period for both job logging
and window logging by default. However, the WHICH_LOG parameter can be used to
specify either JOB_LOG or WINDOW_LOG.
9. Consider the following code snippet:
begin
dbms_scheduler.run_job('COLA_JOB',TRUE);
end;
If this code were executed, which of the following statements would be true?
(Choose all that apply.)
A. The COLA_JOB job would be executed asynchronously.
B. The COLA_JOB job would be executed synchronously.
C. The COLA_JOB job would run in the user's current session.
D. The COLA_JOB job would run with the FORCE option.
E. The user could continue to issue SQL statements in the session while
the COLA_JOB job was executing.
----
Ans: B, C.
The RUN_JOB procedure accepts two parameters: JOB_NAME and
USE_CURRENT_SESSION. In this example, the USE_CURRENT_SESSION parameter is set
to a value of TRUE. This causes the job to be executed synchronously in the user's
current session. Because the job runs synchronously, control would not return to the
user until the job was completed. Therefore, the user could not continue to execute
SQL statements while the job was running.
10. Which of the following calendaring syntax expressions would evaluate to the
last day of every month?
A. FREQ = MONTHLY; BYMONTHDAY = 31
B. FREQ = MONTHLY; BYMONTHDAY = -1
C. FREQ = DAILY; BYDAY = -1
D. FREQ = MONTHLY; BYDAY = 31
E. FREQ = DAILY; BYMONTHDAY = LAST_DAY
---
Ans: B.
The BYMONTHDAY element accepts negative values that represent a specific count of
days from the end of the month. Also, the FREQ parameter must be set to MONTHLY,
because it will execute every month.
11. Which of the following tasks is not performed by the job coordinator?
A. Update job log when a job completes
B. Spawn and remove job slaves
C. Write/read job info to/from memory cache
D. Query job table
E. Pass job information to job slaves
---
Ans: A. The job coordinator does not update the job log when a job completes. That
function is performed by the job slave that has been assigned to the job.
12. Which of the following objects can be directly referenced by a window object?
(Choose all that apply.)
A. Schedule object
B. Program object
C. Job object
D. Resource plan
E. Resource Consumer Group
---
Ans: A, D.
A window does not execute programs or jobs. It specifies a resource plan that
will be enabled based on a schedule. Therefore, it can reference both a schedule
object and a resource plan object. And while the resource plan may reference one or
more resource consumer groups, the window object does not directly reference them.
13. Which DBMS_SCHEDULER procedure is available for testing repeat intervals
produced by calendaring syntax expressions?
A. EVALUATE_REPEAT_INTERVAL
B. VALIDATE_CALENDAR_EXPRESSION
C. EVALUATE_CALENDAR_STRING
D. VALIDATE_CALENDAR_STRING
E. EVALUATE_INTERVAL_EXPRESSION
---
Ans: C.
The EVALUATE_CALENDAR_STRING procedure will generate execution dates from a
calendaring syntax expression.
14. Which of the following is not a valid setting for the PROGRAM_TYPE parameter
in a program object or the JOB_TYPE parameter in a job object?
A. PLSQL_BLOCK
B. JAVA_STORED_PROCEDURE
C. STORED_PROCEDURE
D. EXECUTABLE
E. None of the above are invalid settings.
---
Ans: B.
Java stored procedures cannot be executed by the job Scheduler unless they are
called from within a PL/SQL procedure wrapper. This can be done in a stored
procedure using PL/SQL's external procedure feature. Therefore, the job or program
type setting would be STORED_PROCEDURE.
15. Which of the following Scheduler elements encourage object reuse? (Choose
all that apply.)
A. Schedule objects
B. Program arguments
C. Job classes
D. Job arguments
E. All of the above
---
Ans: A, B, D.
Schedule objects do not specify any action to be performed; they simply
generate execution dates that any job can use. Program and job arguments allow the
jobs and programs to be reused by simply changing the arguments that are passed in.
Job classes simplify the management of jobs, but they do not specifically encourage
job reuse.
16. What is the danger associated with stopping a running job by using the
STOP_JOB procedure?
A. The job will need to be re-enabled before it will execute again.
B. The job may hold locks on objects referenced within the job.
C. All jobs within the job group will also be stopped.
D. The job may leave data in an inconsistent state.
E. There is no danger in using the STOP_JOB procedure.
---
Ans: D.
The Scheduler will attempt to wrap the job within a transaction and will execute a
rollback if a job is stopped. However, if the job has performed commits, the rollback
will only roll back any uncommitted changes. This could result in inconsistent data.
17. If a job references a schedule that has been disabled, what will be the result?
A. The job will be automatically disabled.
B. The job will never execute.
C. The job will attempt to execute, but will fail.
D. The job will inherit the DEFAULT_SCHEDULE schedule.
E. A schedule object cannot be disabled.
---
Ans: E.
A schedule object does not possess the ENABLED attribute. It is therefore enabled
upon creation and can never be disabled.
18. When a job exceeds the date specified in its END_DATE attribute, which of the
following will happen? (Choose all that apply.)
A. The job will be dropped automatically if the value of the AUTO_DROP
attribute is TRUE.
B. The job will be disabled if the value of the AUTO_DROP attribute is
FALSE.
C. The STATE attribute of the job will be set to COMPLETED if the value of
the AUTO_DROP attribute is FALSE.
D. All objects referenced by the job will be dropped if the value of the
AUTO_DROP attribute is TRUE and the value of the CASCADE attribute is
TRUE.
E. The STATE column of the job table will be set to COMPLETED for the job.
---
Ans: A, B, E.
When a job exceeds its end date, it will be dropped only if the AUTO_DROP
attribute is set to TRUE. Otherwise, it will be disabled. In either case, the STATE
column will be set to COMPLETED in the job table. A job object does not possess a
CASCADE attribute or a STATE attribute.
19. Which view can you query to see information on Scheduler windows?
A. DBA_WINDOWS
B. DBA_SCHEDULER_WINDOWS
C. DBA_WINDOW_OBJECTS
D. DBA_SCHEDULER_WINDOW_OBJECTS
E. DBA_ALL_SCHEDULER_WINDOWS
----
Ans: B. The DBA_SCHEDULER_WINDOWS view shows information on window objects
defined in the Scheduler.
20. If two windows overlap, which window attribute will determine if one should
be chosen over the other?
A. WINDOW_PRIORITY
B. PRIORITY
C. PRIORITY_LEVEL
D. WINDOW_PRIORITY_LEVEL
E. OVERLAP_RULE
---
Ans: A.
The WINDOW_PRIORITY attribute can be set to either HIGH or LOW for a window. If
two windows overlap and only one of the windows has a priority of HIGH, it will be
chosen.
Wednesday, September 26, 2007
Oracle 10g New Features - Ch 3 Automating Management
Review Questions
1. To enable the Automatic Workload Repository performance statistic collection at a minimal scale, the STATISTICS_LEVEL parameter must be set to what?
A. TYPICAL
B. NONE
C. ALL
D. BASIC
----
Ans: A.
The valid values for STATISTICS_LEVEL parameter are BASIC, TYPICAL, and ALL. BASIC disables the AWR and other statistics collection, TYPICAL collects statistics needed for day-to-day monitoring, and ALL collects statistics for manual diagnosis.
2. Which process is responsible for analyzing the AWR information for the ADDM?
A. PMON
B. MMON
C. ADDM
D. SNPn
---
Ans: B.
The new process MMON is responsible for writing the ASH information to the Automatic Workload Repository and also analyzing the statistics each time an AWR snapshot is taken.
3. Which data dictionary view would you query to find out the stateless (event-based or nonthreshold) server alerts?
A. DBA_ALERT_LOG
B. DBA_OUTSTANDING_ALERTS
C. DBA_ALERT_HISTORY
D. DBA_ADVISOR_FINDINGS
---
Ans: C.
The stateless alerts are always written to DBA_ALERT_HISTORY. DBA_OUTSTANDING_ALERTS will have the alerts for threshold or stateful alerts. When the status of such alerts is CLEARED, they will be moved to DBA_ALERT_HISTORY.
4. Which of the following is not an out-of-the-box server-generated alert?
A. "Tablespace space usage"
B. "Recovery area low on free space"
C. "Resumable session suspended"
D. "Tables missing optimizer statistics"
E. "Snapshot too old"
---
Ans: D.
In Oracle 10g, the optimizer statistics for tables, including the dictionary, are collected automatically unless the STATISTICS_LEVEL parameter is set to BASIC. The tables have the MONITORING feature enabled by default. So, no need exists for such an alert to look for missing optimizer statistics.
5. Which parameters enable the Automatic Shared Memory Management feature? (Choose two.)
A. SGA_SIZE
B. SGA_TARGET
C. AUTO_SGA
D. STATISTICS_LEVEL
---
Ans: B, D.
Setting the SGA_TARGET and leaving the STATISTICS_LEVEL to its default (TYPICAL) enables ASMM. SGA_TARGET specifies the total size of the SGA, including the manually configured areas. SGA_TARGET cannot be higher than SGA_MAX_SIZE.
6. Which component of the SGA is not automatically configured when Automatic Shared Memory Management is enabled?
A. Java pool
B. Buffer cache
C. Log buffer
D. Large pool
---
ANs: C.
The log buffer should be configured using the LOG_BUFFER parameter. Setting SGA_TARGET automatically manages SHARED_POOL_SIZE, JAVA_POOL_SIZE, LARGE_POOL_SIZE, and BUFFER_CACHE.
C.
7. Which process is responsible for allocating the various components of the SGA when Automatic Shared Memory Management is used?
A. PMON
B. SMON
C. MMON
D. MMAN
---
Ans: D.
The memory manager process (MMAN) is responsible for managing the components of SGA when the Automatic Shared Memory Management feature is used. It serves as a memory broker, coordinates the sizing of the memory components, and keeps track of the component's sizes.
8. How is automatic undo retention tuning enabled?
A. Set the UNDO_RETENTION parameter to zero.
B. Set the UNDO_MANAGEMENT parameter to AUTO.
C. Configure it using the DBMS_ADVISOR package.
D. Set the UNDO_RETENTION parameter to nonzero value.
---
Ans: B. By keeping UNDO_MANAGEMENT set to AUTO (this is the default when creating a new database), the Automatic Undo Retention Tuning feature is enabled in Oracle 10g. If you set a value for the UNDO_RETENTION parameter, Oracle 10g uses that value as the minimum. If no value or zero is set for UNDO_RETENTION, Oracle 10g uses 900 seconds as the default minimum.
9. Identify the statement that is not true regarding the Automatic Optimizer Statistics Collection feature in Oracle 10g.
A. After creating an Oracle 10g database, the DBA does not have to perform any special activity to keep the optimizer statistics current.
B. After upgrading an Oracle database to Oracle 10g, the DBA does not have to perform any special activity to keep the optimizer statistics current.
C. The statistics are kept current by periodically (automatic) executing the DBMS_STATS.GATHER_DATABASE_STATS procedure with the GATHER AUTO option.
D. For the automatic statistic collection to work properly, the STATISTICS_LEVEL must be set to TYPICAL or ALL.
---
Ans: C. The DBMS_STATS.GATHER_DATABASE_STATS_JOB_PROC internal procedure collects the missing statistics and update statistics in the database. This procedure prioritizes the database objects that require statistics so that the objects that need the statistics most are processed first, before the maintenance window closes.
10. Identify two statements that are true regarding the procedures of DBMS_STATS.
A. Every time you collect statistics on a table, schema, or database using DBMS_STATS, the previous version of statistics is stored in the AWR.
B. The retention period of optimizer statistics stored in the AWR is determined by the DBMS_WORKLOAD_REPOSITORY.MODIFY_SNAPSHOT_SETTINGS (RETENTION=> nn) procedure.
C. When you lock statistics on a table, the schema level statistics collection skips the table.
D. Restoring statistics from the AWR is based on the time stamp when the statistics were collected.
---
Ans: A, C.
The retention period of the statistics history kept in the AWR is 31 days by default, which can be modified by using the DBMS_STATS.ALTER_STATS_HISTORY_RETENTION procedure. The statistics from the AWR are restored using the time stamp as an argument to restore statistics as of that time stamp (not when the statistics are collected).
11. The following are a few of the steps involved in getting the tuning advice from AWR snapshots using the DBMS_ADVISOR PL/SQL API.
1. Use the DBMS_ADVISOR.SET_TASK_PARAMETERS procedure.
2. Use the DBMS_ADVISOR.CREATE_TASK procedure.
3. Use the DBMS_ADVISOR.GET_TASK_REPORT procedure.
4. Use the DBMS_ADVISOR.EXECUTE_TASK procedure.
In which order should be these steps executed?
A. 1, 3, 4, 2
B. 1, 2, 3, 4
C. 2, 1, 4, 3
D. 2, 1, 3, 4
---
Ans: C.
To get tuning advice using the advisory framework DBMS_ADVISOR package, you should create the task first, set appropriate parameters for the task (specify the start snapshot and end snapshot), perform analysis, and optionally get a report or query from the DBA_ADVISOR_FINDINGS view.
12. Choose the most appropriate statement regarding collecting optimizer statistics:
A. The DBMS_STATS package and the ANALYZE statement have same behavior.
B. When statistics are collected using the ANALYZE statement, previous version of statistics is saved in the Workload Repository.
C. When statistics are collected using the DBMS_STATISTICS package, the previous version of statistics is saved in the Workload Repository.
D. When gathering statistics using the DBMS_STATS package, you must specify the SAVE_VERSION parameter to save the old statistics to the Workload Repository.
--
Ans: C.
Oracle10g saves the statistics to the workload repository before setting new statistics values using the DBMS_STATS package. Statistics are not preserved when using the ANALYZE statement.
13. Which parameter of the DBMS_RESOURCE_MANAGER.CREATE_PLAN_DIRECTIVE procedure ensures that at the end of the switched resource group call, the session is returned back to its original resource group?
A. SWITCH_TIME_IN_CALL
B. SWITCH_TIME
C. SWITCH_GROUP
D. SWITCH_BACK_AT_CALL_END
---
Ans: A.
The SWITCH_TIME and SWITCH_TIME_IN_CALL parameters are mutually exclusive, and they specify the time in seconds that a session can execute before an action is taken. The default for both parameters is NULL. The SWITCH_TIME_IN_CALL parameter specifies that the session should be returned to the original consumer group at the end of the call, where as when using the SWITCH_TIME, the session remains in the switched consumer group. Whether you use SWITCH_TIME or SWITCH_TIME_IN_CALL, the SWITCH_GROUP parameter specifies the resource group to which the session will be switched if the switch criteria are met.
14. In the DBMS_RESOURCE_MANAGER.CREATE_PLAN_DIRECTIVE procedure, the MAX_IDLE_TIME parameter is used to define what?
A. The maximum amount of time a session is inactive and is blocking another session. The session will be switched to switch group defined when the amount of time is met.
B. The total amount of time a session is inactive or idle cumulative since the session startup. The session will be terminated when the criteria is met.
C. The maximum amount of time a session is idle. The session will be switched to switch group defined when the amount of time is met.
D. The maximum amount of time a session is idle. The session will be terminated when the criteria is met.
---
Ans: D.
The MAX_IDLE_TIME parameter defines the maximum amount of time in seconds a session can be inactive or idle. When the session exceeds the limit, the PMON process will terminate the session and clean up its state. The MAX_IDLE_BLOCKER_TIME parameter defines the maximum amount of time in seconds a session can be idle and block the acquisition of resources for another user.
15. Identify two statements that are not true regarding AWR.
A. The snapshot data is not purged from the AWR for the snapshots that are part of a baseline.
B. The automatic capturing of AWR snapshots is disabled by dropping or disabling the corresponding job using DBMS_SCHEDULER.
C. To get AWR report in HTML format, you must use the EM Database Control.
D. No data migration from STATSPACK to AWR is supported.
-----
Ans: B, C.
You can disable the automatic capturing of AWR snapshots by setting the INTERVAL parameter to zero in the DBMS_WORKLOAD_REPOSITORY.MODIFY_SNAPSHOT_SETTINGS procedure. You can produce AWR reports in HTML format using SQL*Plus by specifying the report type as HTML or text. The snapshots that are older than the retention period defined are not purged when they are part of a baseline. Though STATSPACK is still available in Oracle 10g, no integration exists between the data collected by STATSPACK and AWR.
16. The alerts generated by the database server are delivered to the DBA using which method?
A. Writing the alert to the database alert log file
B. Sending e-mail to the DBA using the e-mail address specified when creating the database
C. Using a trigger on the DBA_OUTSTANDING_ALERTS view
D. Using DBMS_AQ procedures
---
Ans: D.
The alerts generated by the server are queued to the AQ mechanism of the database, a predefined persistent queue ALERT_QUE. You may write your own subscribing programs. You can use the EM Database Control to enable paging and e-mail to the DBA. You may also query DBA_OUTSTANDING_ALERTS to view the alerts that are not resolved. A message about the alert is written to the alert log file only if the alert cannot be written to the ALERT_QUE.
17. When is the ADDM analysis performed?
A. Every time an AWR snapshot is taken
B. Whenever an AWR snapshot is taken automatically by the MMON process
C. Whenever an AWR snapshot is taken using the CREATE_SNAPSHOT procedure
D. Every hour or at specified interval irrespective on AWR snapshots
---
Ans: A.
ADDM is run by the MMON process every time an AWR snapshot is performed—automatic or manual. Each time a snapshot is taken, ADDM is triggered to do an analysis of the period corresponding to the last two snapshots. You may run the addmrpti.sql script to analyze the data between two different snapshot IDs.
D.
18. Identify a true statement from the following regarding Automatic Shared Memory Management.
A. ASMM is enabled by default.
B. ASMM is disabled by setting SGA_TARGET to zero.
C. SGA_TARGET cannot be altered dynamically using ALTER SYSTEM.
D. When SGA_TARGET is specified as a nonzero value, all the SGA parameters related to the autotuned components must be set to zero.
E. The MMON process is responsible for coordinating the sizing of the memory components.
---
Ans: B.
The default value for SGA_TARGET is zero, which means ASMM is not enabled by default. SGA_TARGET can be increased or decreased dynamically within the limits of the SGA_MAX_SIZE parameter. When ASMM is used and a value for the automatically tuned component is also specified, the value would be considered as the minimum required size for the component. The memory manager process (MMAN) is the SGA memory broker and is responsible for the sizing of the SGA components.
19. The FORCE=>TRUE option of the DBMS_STATS.DELETE_TABLE_STATS procedure is used for what?
A. To delete statistics from the table, even if the table is read only.
B. To delete statistics from the table and to clear out all the SQL statements referring to the table from the shared pool.
C. To delete statistics from the table, even if the statistics on the table are locked.
D. The value of FORCE must be TRUE always, as this parameter is reserved for a future enhancement.
----
Ans: C.
You can use the FORCE=>TRUE option with the DELETE_*_STATS, IMPORT_*_STATS, RESTORE_*_STATS, and SET_*_STATS procedures of the DBMS_STATS package to overwrite the statistics even if they are locked.
C.
20. Which of the following is true about Automatic Checkpoint Tuning?
A. You do not need to specify the LOG_CHECKPOINT_INTERVAL or LOG_CHECKPOINT_TIMEOUT parameter.
B. You do not need to specify the FAST_START_MTTR_TARGET parameter.
C. You do not need to specify the LOG_BUFFER parameter.
D. All of the above are true.
--
Ans: A.
Automatic Checkpoint Tuning is enabled if you specify a value for the FAST_START_MTTR_TARGET or if you do not specify any value for this parameter. Automatic Checkpoint Tuning is disabled when FAST_START_MTTR_TARGET is set to zero explicitly. You do not need to specify any of the checkpoint-related parameters when the FAST_START_MTTR_TARGET parameter is set.
1. To enable the Automatic Workload Repository performance statistic collection at a minimal scale, the STATISTICS_LEVEL parameter must be set to what?
A. TYPICAL
B. NONE
C. ALL
D. BASIC
----
Ans: A.
The valid values for STATISTICS_LEVEL parameter are BASIC, TYPICAL, and ALL. BASIC disables the AWR and other statistics collection, TYPICAL collects statistics needed for day-to-day monitoring, and ALL collects statistics for manual diagnosis.
2. Which process is responsible for analyzing the AWR information for the ADDM?
A. PMON
B. MMON
C. ADDM
D. SNPn
---
Ans: B.
The new process MMON is responsible for writing the ASH information to the Automatic Workload Repository and also analyzing the statistics each time an AWR snapshot is taken.
3. Which data dictionary view would you query to find out the stateless (event-based or nonthreshold) server alerts?
A. DBA_ALERT_LOG
B. DBA_OUTSTANDING_ALERTS
C. DBA_ALERT_HISTORY
D. DBA_ADVISOR_FINDINGS
---
Ans: C.
The stateless alerts are always written to DBA_ALERT_HISTORY. DBA_OUTSTANDING_ALERTS will have the alerts for threshold or stateful alerts. When the status of such alerts is CLEARED, they will be moved to DBA_ALERT_HISTORY.
4. Which of the following is not an out-of-the-box server-generated alert?
A. "Tablespace space usage"
B. "Recovery area low on free space"
C. "Resumable session suspended"
D. "Tables missing optimizer statistics"
E. "Snapshot too old"
---
Ans: D.
In Oracle 10g, the optimizer statistics for tables, including the dictionary, are collected automatically unless the STATISTICS_LEVEL parameter is set to BASIC. The tables have the MONITORING feature enabled by default. So, no need exists for such an alert to look for missing optimizer statistics.
5. Which parameters enable the Automatic Shared Memory Management feature? (Choose two.)
A. SGA_SIZE
B. SGA_TARGET
C. AUTO_SGA
D. STATISTICS_LEVEL
---
Ans: B, D.
Setting the SGA_TARGET and leaving the STATISTICS_LEVEL to its default (TYPICAL) enables ASMM. SGA_TARGET specifies the total size of the SGA, including the manually configured areas. SGA_TARGET cannot be higher than SGA_MAX_SIZE.
6. Which component of the SGA is not automatically configured when Automatic Shared Memory Management is enabled?
A. Java pool
B. Buffer cache
C. Log buffer
D. Large pool
---
ANs: C.
The log buffer should be configured using the LOG_BUFFER parameter. Setting SGA_TARGET automatically manages SHARED_POOL_SIZE, JAVA_POOL_SIZE, LARGE_POOL_SIZE, and BUFFER_CACHE.
C.
7. Which process is responsible for allocating the various components of the SGA when Automatic Shared Memory Management is used?
A. PMON
B. SMON
C. MMON
D. MMAN
---
Ans: D.
The memory manager process (MMAN) is responsible for managing the components of SGA when the Automatic Shared Memory Management feature is used. It serves as a memory broker, coordinates the sizing of the memory components, and keeps track of the component's sizes.
8. How is automatic undo retention tuning enabled?
A. Set the UNDO_RETENTION parameter to zero.
B. Set the UNDO_MANAGEMENT parameter to AUTO.
C. Configure it using the DBMS_ADVISOR package.
D. Set the UNDO_RETENTION parameter to nonzero value.
---
Ans: B. By keeping UNDO_MANAGEMENT set to AUTO (this is the default when creating a new database), the Automatic Undo Retention Tuning feature is enabled in Oracle 10g. If you set a value for the UNDO_RETENTION parameter, Oracle 10g uses that value as the minimum. If no value or zero is set for UNDO_RETENTION, Oracle 10g uses 900 seconds as the default minimum.
9. Identify the statement that is not true regarding the Automatic Optimizer Statistics Collection feature in Oracle 10g.
A. After creating an Oracle 10g database, the DBA does not have to perform any special activity to keep the optimizer statistics current.
B. After upgrading an Oracle database to Oracle 10g, the DBA does not have to perform any special activity to keep the optimizer statistics current.
C. The statistics are kept current by periodically (automatic) executing the DBMS_STATS.GATHER_DATABASE_STATS procedure with the GATHER AUTO option.
D. For the automatic statistic collection to work properly, the STATISTICS_LEVEL must be set to TYPICAL or ALL.
---
Ans: C. The DBMS_STATS.GATHER_DATABASE_STATS_JOB_PROC internal procedure collects the missing statistics and update statistics in the database. This procedure prioritizes the database objects that require statistics so that the objects that need the statistics most are processed first, before the maintenance window closes.
10. Identify two statements that are true regarding the procedures of DBMS_STATS.
A. Every time you collect statistics on a table, schema, or database using DBMS_STATS, the previous version of statistics is stored in the AWR.
B. The retention period of optimizer statistics stored in the AWR is determined by the DBMS_WORKLOAD_REPOSITORY.MODIFY_SNAPSHOT_SETTINGS (RETENTION=> nn) procedure.
C. When you lock statistics on a table, the schema level statistics collection skips the table.
D. Restoring statistics from the AWR is based on the time stamp when the statistics were collected.
---
Ans: A, C.
The retention period of the statistics history kept in the AWR is 31 days by default, which can be modified by using the DBMS_STATS.ALTER_STATS_HISTORY_RETENTION procedure. The statistics from the AWR are restored using the time stamp as an argument to restore statistics as of that time stamp (not when the statistics are collected).
11. The following are a few of the steps involved in getting the tuning advice from AWR snapshots using the DBMS_ADVISOR PL/SQL API.
1. Use the DBMS_ADVISOR.SET_TASK_PARAMETERS procedure.
2. Use the DBMS_ADVISOR.CREATE_TASK procedure.
3. Use the DBMS_ADVISOR.GET_TASK_REPORT procedure.
4. Use the DBMS_ADVISOR.EXECUTE_TASK procedure.
In which order should be these steps executed?
A. 1, 3, 4, 2
B. 1, 2, 3, 4
C. 2, 1, 4, 3
D. 2, 1, 3, 4
---
Ans: C.
To get tuning advice using the advisory framework DBMS_ADVISOR package, you should create the task first, set appropriate parameters for the task (specify the start snapshot and end snapshot), perform analysis, and optionally get a report or query from the DBA_ADVISOR_FINDINGS view.
12. Choose the most appropriate statement regarding collecting optimizer statistics:
A. The DBMS_STATS package and the ANALYZE statement have same behavior.
B. When statistics are collected using the ANALYZE statement, previous version of statistics is saved in the Workload Repository.
C. When statistics are collected using the DBMS_STATISTICS package, the previous version of statistics is saved in the Workload Repository.
D. When gathering statistics using the DBMS_STATS package, you must specify the SAVE_VERSION parameter to save the old statistics to the Workload Repository.
--
Ans: C.
Oracle10g saves the statistics to the workload repository before setting new statistics values using the DBMS_STATS package. Statistics are not preserved when using the ANALYZE statement.
13. Which parameter of the DBMS_RESOURCE_MANAGER.CREATE_PLAN_DIRECTIVE procedure ensures that at the end of the switched resource group call, the session is returned back to its original resource group?
A. SWITCH_TIME_IN_CALL
B. SWITCH_TIME
C. SWITCH_GROUP
D. SWITCH_BACK_AT_CALL_END
---
Ans: A.
The SWITCH_TIME and SWITCH_TIME_IN_CALL parameters are mutually exclusive, and they specify the time in seconds that a session can execute before an action is taken. The default for both parameters is NULL. The SWITCH_TIME_IN_CALL parameter specifies that the session should be returned to the original consumer group at the end of the call, where as when using the SWITCH_TIME, the session remains in the switched consumer group. Whether you use SWITCH_TIME or SWITCH_TIME_IN_CALL, the SWITCH_GROUP parameter specifies the resource group to which the session will be switched if the switch criteria are met.
14. In the DBMS_RESOURCE_MANAGER.CREATE_PLAN_DIRECTIVE procedure, the MAX_IDLE_TIME parameter is used to define what?
A. The maximum amount of time a session is inactive and is blocking another session. The session will be switched to switch group defined when the amount of time is met.
B. The total amount of time a session is inactive or idle cumulative since the session startup. The session will be terminated when the criteria is met.
C. The maximum amount of time a session is idle. The session will be switched to switch group defined when the amount of time is met.
D. The maximum amount of time a session is idle. The session will be terminated when the criteria is met.
---
Ans: D.
The MAX_IDLE_TIME parameter defines the maximum amount of time in seconds a session can be inactive or idle. When the session exceeds the limit, the PMON process will terminate the session and clean up its state. The MAX_IDLE_BLOCKER_TIME parameter defines the maximum amount of time in seconds a session can be idle and block the acquisition of resources for another user.
15. Identify two statements that are not true regarding AWR.
A. The snapshot data is not purged from the AWR for the snapshots that are part of a baseline.
B. The automatic capturing of AWR snapshots is disabled by dropping or disabling the corresponding job using DBMS_SCHEDULER.
C. To get AWR report in HTML format, you must use the EM Database Control.
D. No data migration from STATSPACK to AWR is supported.
-----
Ans: B, C.
You can disable the automatic capturing of AWR snapshots by setting the INTERVAL parameter to zero in the DBMS_WORKLOAD_REPOSITORY.MODIFY_SNAPSHOT_SETTINGS procedure. You can produce AWR reports in HTML format using SQL*Plus by specifying the report type as HTML or text. The snapshots that are older than the retention period defined are not purged when they are part of a baseline. Though STATSPACK is still available in Oracle 10g, no integration exists between the data collected by STATSPACK and AWR.
16. The alerts generated by the database server are delivered to the DBA using which method?
A. Writing the alert to the database alert log file
B. Sending e-mail to the DBA using the e-mail address specified when creating the database
C. Using a trigger on the DBA_OUTSTANDING_ALERTS view
D. Using DBMS_AQ procedures
---
Ans: D.
The alerts generated by the server are queued to the AQ mechanism of the database, a predefined persistent queue ALERT_QUE. You may write your own subscribing programs. You can use the EM Database Control to enable paging and e-mail to the DBA. You may also query DBA_OUTSTANDING_ALERTS to view the alerts that are not resolved. A message about the alert is written to the alert log file only if the alert cannot be written to the ALERT_QUE.
17. When is the ADDM analysis performed?
A. Every time an AWR snapshot is taken
B. Whenever an AWR snapshot is taken automatically by the MMON process
C. Whenever an AWR snapshot is taken using the CREATE_SNAPSHOT procedure
D. Every hour or at specified interval irrespective on AWR snapshots
---
Ans: A.
ADDM is run by the MMON process every time an AWR snapshot is performed—automatic or manual. Each time a snapshot is taken, ADDM is triggered to do an analysis of the period corresponding to the last two snapshots. You may run the addmrpti.sql script to analyze the data between two different snapshot IDs.
D.
18. Identify a true statement from the following regarding Automatic Shared Memory Management.
A. ASMM is enabled by default.
B. ASMM is disabled by setting SGA_TARGET to zero.
C. SGA_TARGET cannot be altered dynamically using ALTER SYSTEM.
D. When SGA_TARGET is specified as a nonzero value, all the SGA parameters related to the autotuned components must be set to zero.
E. The MMON process is responsible for coordinating the sizing of the memory components.
---
Ans: B.
The default value for SGA_TARGET is zero, which means ASMM is not enabled by default. SGA_TARGET can be increased or decreased dynamically within the limits of the SGA_MAX_SIZE parameter. When ASMM is used and a value for the automatically tuned component is also specified, the value would be considered as the minimum required size for the component. The memory manager process (MMAN) is the SGA memory broker and is responsible for the sizing of the SGA components.
19. The FORCE=>TRUE option of the DBMS_STATS.DELETE_TABLE_STATS procedure is used for what?
A. To delete statistics from the table, even if the table is read only.
B. To delete statistics from the table and to clear out all the SQL statements referring to the table from the shared pool.
C. To delete statistics from the table, even if the statistics on the table are locked.
D. The value of FORCE must be TRUE always, as this parameter is reserved for a future enhancement.
----
Ans: C.
You can use the FORCE=>TRUE option with the DELETE_*_STATS, IMPORT_*_STATS, RESTORE_*_STATS, and SET_*_STATS procedures of the DBMS_STATS package to overwrite the statistics even if they are locked.
C.
20. Which of the following is true about Automatic Checkpoint Tuning?
A. You do not need to specify the LOG_CHECKPOINT_INTERVAL or LOG_CHECKPOINT_TIMEOUT parameter.
B. You do not need to specify the FAST_START_MTTR_TARGET parameter.
C. You do not need to specify the LOG_BUFFER parameter.
D. All of the above are true.
--
Ans: A.
Automatic Checkpoint Tuning is enabled if you specify a value for the FAST_START_MTTR_TARGET or if you do not specify any value for this parameter. Automatic Checkpoint Tuning is disabled when FAST_START_MTTR_TARGET is set to zero explicitly. You do not need to specify any of the checkpoint-related parameters when the FAST_START_MTTR_TARGET parameter is set.
Tuesday, September 25, 2007
Oracle 10g New Features - Ch4 General Storage Management
Chapter 4 - General Storage Management
Review Questions
1. Which of the following ALTER commands is supported for hash-partitioned indexes?
A. ALTER INDEX REBUILD
B. ALTER INDEX MODIFY PARTITION
C. ALTER TABLE SPLIT INDEX PARTITION
D. ALTER TABLE MERGE INDEX PARTITIONS
E. None of the above
---
Ans: B.
None of the choices contain an ALTER command that is allowed for hash-partitioned indexes, except for the ALTER INDEX MODIFY PARTITION command with the UNUSABLE option—in other words, marking a partition of a hash-partitioned index as unusable.
2. Which of the following recommended practices should a DBA implement to take advantage of bitmap index storage enhancements? (Choose all that apply.)
A. Rebuilding all bitmap indexes manually after adjusting the COMPATIBLE parameter
B. Raising the COMPATIBLE parameter to at least 10.0.0.0
C. Considering rebuilding bitmap indexes when large volumes of single-row DML operations occur on a table
D. Rebuilding bitmap indexes that exhibit a slowdown after adjusting the COMPATIBLE parameter
E. Raising the COMPATIBLE parameter to at least 9.2.0.0
---
Ans: B, D.
Not all bitmap indexes need to be rebuilt after the COMPATIBLE parameter is adjusted, unless they still exhibit a slowdown or get worse. The COMPATIBLE parameter should be set to 10.0.0.0 to take advantage of all enhancements.
3. What is the maximum number of bytes that can be stored in a bigfile tablespace with a database block size of 16KB?
A. 8 exabytes
B. 8,000,000 terabytes
C. 8 petabytes
D. 64 terabytes
----
Ans: D.
Bigfile tablespaces increase the maximum size of a tablespace to 128TB with a block size of 32KB; therefore, with a block size of 16KB, a bigfile tablespace can be 64TB.
4. Which of the following commands creates a temporary tablespace group TMPGRP1 and adds a temporary tablespace named TMPMEMB1? (Choose two.)
A
CREATE TEMPORARY TABLESPACE GROUP TMPGRP1 MEMBERS (TMPMEMB1);
B
ALTER TEMPORARY TABLESPACE GROUP TMPGRP1
ADD TEMPORARY TABLESPACE TMPMEMB1
TEMPFILE 'tmpmem1.dbf ' SIZE 100M;
C
ALTER TABLESPACE TMPMEMB1 TABLESPACE GROUP TMPGRP1;
D
CREATE TEMPORARY TABLESPACE TMPMEMB1
TEMPFILE 'tmpmem1.dbf' SIZE 100M
TABLESPACE GROUP TMPGRP1;
E
None of the above
-----
Ans: C, D.
A temporary tablespace group is created when the first temporary tablespace member is added and is deleted when the last member is removed from the group. If a temporary tablespace already exists, it can be added to an existing group with the ALTER TABLESPACE command.
5. With a non-SYSTEM default permanent tablespace, which users still have SYSTEM as their default permanent tablespace? (Choose all that apply.)
A. SYSMAN
B. SYS
C. OUTLN
D. SYSTEM
E. DBSNMP
F. SCOTT
---
Ans: B, C, D.
Only the system users SYS, SYSTEM, and OUTLN still use the SYSTEM tablespace as their default permanent tablespace.
6. Which of the following statement(s) are not true about default permanent tablespaces?
A. The default permanent tablespace cannot be dropped until another tablespace is defined as the default permanent tablespace.
B. EM Database Control can be used to change the default permanent tablespace.
C. The Database Configuration Assistant defines the USERS tablespace as the default permanent tablespace.
D. In the CREATE DATABASE command, you use the DEFAULT PERMANENT TABLESPACE to assign the default permanent tablespace for users that are not otherwise assigned a default tablespace.
E. The data dictionary view DATABASE_PROPERTIES can be used to retrieve the name of the default permanent tablespace.
---
Ans: D.
In the CREATE DATABASE command, you can only specify DEFAULT TABLESPACE; the PERMANENT keyword is not required nor allowed in the command.
7. Which of the following applications can be moved out of the SYSAUX tablespace?
A. Automatic Workload Repository
B. Oracle Streams
C. StatsPack
D. Job Scheduler
E. LogMiner
----
Ans: E.
Of the available answers, only LogMiner can be relocated out of the SYSAUX tablespace.
8. Which data dictionary or dynamic performance view(s) indicates whether a tablespace is a big-file or smallfile tablespace? (Choose all that apply.)
A. V$TABLESPACE
B. V$DATABASE
C. V$DATAFILE
D. DBA_TABLESPACES
E. DATABASE_PROPERTIES
---
Ans: A, D.
Both V$TABLESPACE and DBA_TABLESPACES contain a new column called BIGFILE to indicate if the tablespace is a bigfile tablespace. V$DATABASE has no tablespace-specific information; V$DATAFILE contains only information relevant to the datafiles of the tablespace; and DATABASE_PROPERTIES has a row indicating the default tablespace type for the database.
9. Which of the following is not true about the initialization parameter SKIP_UNUSABLE_INDEXES?
A. SKIP_UNUSABLE_INDEXES is a dynamic parameter.
B. Even if set to TRUE, a user may still get ORA-01502 messages if UPDATE INDEXES was not specified in partition maintenance.
C. The default value is TRUE at the session and system level.
D. Even if set to TRUE, the optimizer may choose a suboptimal execution plan.
E. The data dictionary view DBA_IND_PARTITIONS can be monitored to see if a local index partition has become invalid.
----
Ans: B.
By setting SKIP_UNUSABLE_INDEXES to TRUE either at the system level or the session level, the optimizer may choose a suboptimal execution plan, but the query will not return an ORA-01502 error message.
10. Identify the new partitioning method available for global indexes.
A. Range partitioned
B. Range-hash partitioned
C. Hash partitioned
D. List-hash partitioned
---
Ans: C.
Oracle 10g now supports hash-partitioned global indexes; each partition contains values derived from an internal hash function based on the partitioning key or keys and the number of partitions defined for the global index. Range partitioned global indexes are not new to Oracle 10g. There is no such partitioning method known as global list-hash partitioning.
11. Identify the main differences between the procedures COPY_FILE and PUT_FILE in the DBMS_FILE_TRANSFER package. (Choose all that apply.)
A. COPY_FILE copies a file to a destination on the same server, and PUT_FILE copies a file to a remote server.
B. PUT_FILE copies a file to a destination on the same server, and COPY_FILE copies a file to a remote server.
C. PUT_FILE can copy only binary files; COPY_FILE can copy binary and Unicode files.
D. The PUT_FILE procedure requires a destination server name.
---
Ans: A, D.
COPY_FILE copies files on the same server; PUT_FILE copies files to a remote server. Both procedures can copy only binary files. Since PUT_FILE copies to a remote server, it requires a destination server name, unlike COPY_FILE, which copies to a destination on the same server.
12. Which of the following operations is not supported for hash-partitioned global indexes?
A. DROP INDEX IX_ORD;
B. ALTER INDEX IX_ORD REBUILD;
C. ALTER INDEX IX_ORD UNUSABLE;
D. ALTER INDEX IX_ORD MODIFY PARTITION IX_ORD_P1 UNUSABLE;
E. ALTER INDEX IX_ORD REBUILD PARTITION IX_ORD_P2;
---
Ans: B.
For hash-partitioned indexes, each individual index must be rebuilt individually. Other operations not supported for hash-partitioned indexes are ALTER TABLE SPLIT INDEX PARTITION and ALTER INDEX MODIFY PARTITION.
13. Given the commands
CREATE TEMPORARY TABLESPACE PRDTTS1
TEMPFILE 'prdtts1.dbf' SIZE 100M
TABLESPACE GROUP PRDTMP;
CREATE TEMPORARY TABLESPACE PRDTTS2
TEMPFILE 'prdtts2.dbf' SIZE 100M
TABLESPACE GROUP PRDTMP;
which command does not assign the temporary tablespace group PRDTMP to a user?
A. CREATE USER KELLYM IDENTIFIED BY TJPO
DEFAULT TABLESPACE USERS
TEMPORARY TABLESPACE PRDTMP;
B. ALTER USER KELLYM TEMPORARY TABLESPACE GROUP PRDTMP;
C. ALTER DATABASE DEFAULT TEMPORARY TABLESPACE PRDTMP;
D. CREATE USER KELLYM IDENTIFIED BY TJPO
TEMPORARY TABLESPACE PRDTMP;I.
---
Ans: B. Logically, a temporary tablespace group is equivalent to an individual temporary tablespace. If a user is not assigned a default temporary tablespace, they are assigned the database's default temporary tablespace.
14. Given a hash-partitioned global index IX_ORD on the table ORD, with four partitions, and the following SELECT statement
SELECT /*+ PARALLEL_INDEX(ORD,IX_ORD,12) */
ORDER_ID, ORDER_DATE FROM ORD
WHERE ORDER_ID BETWEEN 110000 AND 190000;
which of the following is true about the number of processes used to execute the query?
A. Only one process is spawned since the index is hash-partitioned, and the WHERE clause uses a range.
B. After pruning the partitions down to those having the range of order IDs in the WHERE clause, the 12 processes are divided equally among the remaining partitions.
C. If the number of remaining partitions after pruning is fewer than 12, then not all 12 query processes are spawned; a maximum of one query process per partition is allowed for hash-partitioned global indexes.
D. The number of parallel query processes can only be a power of two, therefore, as many as 16 processes may be spawned.
---
Ans: B.
With range-partitioned indexes, partition pruning occurs, but only one parallel query process is spawned per partition whereas multiple query processes may be spawned for each pruned partition in a hash-partitioned global index.
15. Which of the following are true about renaming tablespaces? (Choose all that apply.)
A. Tablespaces that are READ ONLY cannot be renamed and must be changed to READ WRITE before renaming.
B. When a tablespace is renamed, all references to the tablespace name in the data dictionary, control file, online datafile headers, and initialization parameter files are updated.
C. You cannot rename the SYSTEM or SYSAUX tablespaces.
D. The tablespace must be online to be renamed.
E. Temporary tablespaces, undo tablespaces, and permanent tablespaces can be renamed.
---
Ans: C, D, E.
Tablespaces that are READ ONLY can be renamed, but the datafile header is not changed. References to the tablespace are updated in an SPFILE if necessary, but a text-based initialization parameter file is not changed.
16. Which of the following is a benefit of hash-partitioned global indexes?
A. Contention for the same index leaf blocks is reduced in an OLTP environment.
B. Indexes are smaller in a DSS environment because hash partitioning compresses duplicate entries for dimension keys in a star schema.
C. Hash-partitioned global indexes do not become invalid when partition maintenance occurs on the table partitions.
D. The application developer no longer needs to use a reverse-key index to optimize the updates to the index.
---
Ans: A. Hash partitioning spreads the activity to more leaf blocks and therefore reduces the contention for a given leaf block in an OLTP environment.
A
17. Identify the way(s) a DBA can find out if a suboptimal execution plan is being used for a query because a local partitioned index has become invalid and the SKIP_UNUSABLE_INDEXES parameter is set to TRUE. (Choose all that apply.)
A. Monitoring the data dictionary view DBA_IND_PARTITIONS for invalid indexes
B. Monitoring the alert log
C. Using EXPLAIN PLAN to preview the execution plan used for all queries
D. Monitoring user trace files
E. All of the above
---
Ans: A, B.
The DBA can monitor the view DBA_IND_PARTITIONS to see if index partitions become invalid; in addition, the alert log will contain messages when an index has been marked unusable. While EXPLAIN PLAN may alert a user that the index is not being chosen to run the query, it is impractical for the DBA to run the EXPLAIN PLAN command for all user queries. User trace files will not contain messages regarding invalid indexes.
E.
18. Under which of the following conditions is the tablespace SYSAUX created? (Choose all that apply.)
A. When the database is created
B. When you need to use features such as Ultra Search or the EM Repository
C. When the SYSTEM tablespace can no longer autoextend
D. When the database is upgraded from a previous version of Oracle
E. You do not need the SYSAUX tablespace; it is optional
---
Ans: A, D.
The SYSAUX tablespace is required for all new Oracle 10g database installations, as well as upgrading a previous version of Oracle to Oracle 10g. The SYSAUX table must exist, even if the applications that use the SYSAUX table are not installed.
19. Which of the following operations are allowed on the SYSAUX tablespace?
A. Transporting the SYSAUX tablespace to another database
B. Renaming the SYSAUX tablespace
C. Adding a datafile to the SYSAUX tablespace
D. Dropping the SYSAUX tablespace
E. Changing the SYSAUX tablespace from SEGMENT SPACE AUTO to SEGMENT SPACE MANUAL
---
Ans: C.
A datafile can be added to the SYSAUX tablespace, just as any other tablespace, as long as it is a smallfile tablespace. All the other operations listed are not allowed on the SYSAUX tablespace.
20. Which of the following methods can be used to verify the bigfile tablespace bfile.dbf with the DBVERIFY utility and enable parallel processing?
A
$ dbv FILE=bfile.dbf START=1 END=25000 &
$ dbv FILE=bfile.dbf START=25000 END=50000 &
$ dbv FILE=bfile.dbf START=50001 &
B $ dbv FILE=bfile.dbf PARALLEL=3
C Parallel processing is automatically enabled for DBVERIFY depending on the value of PARALLEL_MAX_SERVERS.
D Since a bigfile tablespace has only one datafile, parallel processing cannot be enabled.
E Parallel processing is automatically enabled for offline datafiles only.
F
$ dbverify FILE=bfile.dbf START=1 END=25000 &
$ dbverify FILE=bfile.dbf START=25000
END=50000 &
$ dbverify FILE=bfile.dbf START=50001 &
----
Ans: A.
The DBVERIFY utility, invoked as dbv on every platform, can be spawned multiple times, with each instance of DBVERIFY accessing a different portion of the datafile. No PARALLEL clause exists for DBVERIFY. Since the DBVERIFY utility is an external utility, it does not use database initialization parameters such as PARALLEL_MAX_SERVERS.
Review Questions
1. Which of the following ALTER commands is supported for hash-partitioned indexes?
A. ALTER INDEX REBUILD
B. ALTER INDEX MODIFY PARTITION
C. ALTER TABLE SPLIT INDEX PARTITION
D. ALTER TABLE MERGE INDEX PARTITIONS
E. None of the above
---
Ans: B.
None of the choices contain an ALTER command that is allowed for hash-partitioned indexes, except for the ALTER INDEX MODIFY PARTITION command with the UNUSABLE option—in other words, marking a partition of a hash-partitioned index as unusable.
2. Which of the following recommended practices should a DBA implement to take advantage of bitmap index storage enhancements? (Choose all that apply.)
A. Rebuilding all bitmap indexes manually after adjusting the COMPATIBLE parameter
B. Raising the COMPATIBLE parameter to at least 10.0.0.0
C. Considering rebuilding bitmap indexes when large volumes of single-row DML operations occur on a table
D. Rebuilding bitmap indexes that exhibit a slowdown after adjusting the COMPATIBLE parameter
E. Raising the COMPATIBLE parameter to at least 9.2.0.0
---
Ans: B, D.
Not all bitmap indexes need to be rebuilt after the COMPATIBLE parameter is adjusted, unless they still exhibit a slowdown or get worse. The COMPATIBLE parameter should be set to 10.0.0.0 to take advantage of all enhancements.
3. What is the maximum number of bytes that can be stored in a bigfile tablespace with a database block size of 16KB?
A. 8 exabytes
B. 8,000,000 terabytes
C. 8 petabytes
D. 64 terabytes
----
Ans: D.
Bigfile tablespaces increase the maximum size of a tablespace to 128TB with a block size of 32KB; therefore, with a block size of 16KB, a bigfile tablespace can be 64TB.
4. Which of the following commands creates a temporary tablespace group TMPGRP1 and adds a temporary tablespace named TMPMEMB1? (Choose two.)
A
CREATE TEMPORARY TABLESPACE GROUP TMPGRP1 MEMBERS (TMPMEMB1);
B
ALTER TEMPORARY TABLESPACE GROUP TMPGRP1
ADD TEMPORARY TABLESPACE TMPMEMB1
TEMPFILE 'tmpmem1.dbf ' SIZE 100M;
C
ALTER TABLESPACE TMPMEMB1 TABLESPACE GROUP TMPGRP1;
D
CREATE TEMPORARY TABLESPACE TMPMEMB1
TEMPFILE 'tmpmem1.dbf' SIZE 100M
TABLESPACE GROUP TMPGRP1;
E
None of the above
-----
Ans: C, D.
A temporary tablespace group is created when the first temporary tablespace member is added and is deleted when the last member is removed from the group. If a temporary tablespace already exists, it can be added to an existing group with the ALTER TABLESPACE command.
5. With a non-SYSTEM default permanent tablespace, which users still have SYSTEM as their default permanent tablespace? (Choose all that apply.)
A. SYSMAN
B. SYS
C. OUTLN
D. SYSTEM
E. DBSNMP
F. SCOTT
---
Ans: B, C, D.
Only the system users SYS, SYSTEM, and OUTLN still use the SYSTEM tablespace as their default permanent tablespace.
6. Which of the following statement(s) are not true about default permanent tablespaces?
A. The default permanent tablespace cannot be dropped until another tablespace is defined as the default permanent tablespace.
B. EM Database Control can be used to change the default permanent tablespace.
C. The Database Configuration Assistant defines the USERS tablespace as the default permanent tablespace.
D. In the CREATE DATABASE command, you use the DEFAULT PERMANENT TABLESPACE to assign the default permanent tablespace for users that are not otherwise assigned a default tablespace.
E. The data dictionary view DATABASE_PROPERTIES can be used to retrieve the name of the default permanent tablespace.
---
Ans: D.
In the CREATE DATABASE command, you can only specify DEFAULT TABLESPACE; the PERMANENT keyword is not required nor allowed in the command.
7. Which of the following applications can be moved out of the SYSAUX tablespace?
A. Automatic Workload Repository
B. Oracle Streams
C. StatsPack
D. Job Scheduler
E. LogMiner
----
Ans: E.
Of the available answers, only LogMiner can be relocated out of the SYSAUX tablespace.
8. Which data dictionary or dynamic performance view(s) indicates whether a tablespace is a big-file or smallfile tablespace? (Choose all that apply.)
A. V$TABLESPACE
B. V$DATABASE
C. V$DATAFILE
D. DBA_TABLESPACES
E. DATABASE_PROPERTIES
---
Ans: A, D.
Both V$TABLESPACE and DBA_TABLESPACES contain a new column called BIGFILE to indicate if the tablespace is a bigfile tablespace. V$DATABASE has no tablespace-specific information; V$DATAFILE contains only information relevant to the datafiles of the tablespace; and DATABASE_PROPERTIES has a row indicating the default tablespace type for the database.
9. Which of the following is not true about the initialization parameter SKIP_UNUSABLE_INDEXES?
A. SKIP_UNUSABLE_INDEXES is a dynamic parameter.
B. Even if set to TRUE, a user may still get ORA-01502 messages if UPDATE INDEXES was not specified in partition maintenance.
C. The default value is TRUE at the session and system level.
D. Even if set to TRUE, the optimizer may choose a suboptimal execution plan.
E. The data dictionary view DBA_IND_PARTITIONS can be monitored to see if a local index partition has become invalid.
----
Ans: B.
By setting SKIP_UNUSABLE_INDEXES to TRUE either at the system level or the session level, the optimizer may choose a suboptimal execution plan, but the query will not return an ORA-01502 error message.
10. Identify the new partitioning method available for global indexes.
A. Range partitioned
B. Range-hash partitioned
C. Hash partitioned
D. List-hash partitioned
---
Ans: C.
Oracle 10g now supports hash-partitioned global indexes; each partition contains values derived from an internal hash function based on the partitioning key or keys and the number of partitions defined for the global index. Range partitioned global indexes are not new to Oracle 10g. There is no such partitioning method known as global list-hash partitioning.
11. Identify the main differences between the procedures COPY_FILE and PUT_FILE in the DBMS_FILE_TRANSFER package. (Choose all that apply.)
A. COPY_FILE copies a file to a destination on the same server, and PUT_FILE copies a file to a remote server.
B. PUT_FILE copies a file to a destination on the same server, and COPY_FILE copies a file to a remote server.
C. PUT_FILE can copy only binary files; COPY_FILE can copy binary and Unicode files.
D. The PUT_FILE procedure requires a destination server name.
---
Ans: A, D.
COPY_FILE copies files on the same server; PUT_FILE copies files to a remote server. Both procedures can copy only binary files. Since PUT_FILE copies to a remote server, it requires a destination server name, unlike COPY_FILE, which copies to a destination on the same server.
12. Which of the following operations is not supported for hash-partitioned global indexes?
A. DROP INDEX IX_ORD;
B. ALTER INDEX IX_ORD REBUILD;
C. ALTER INDEX IX_ORD UNUSABLE;
D. ALTER INDEX IX_ORD MODIFY PARTITION IX_ORD_P1 UNUSABLE;
E. ALTER INDEX IX_ORD REBUILD PARTITION IX_ORD_P2;
---
Ans: B.
For hash-partitioned indexes, each individual index must be rebuilt individually. Other operations not supported for hash-partitioned indexes are ALTER TABLE SPLIT INDEX PARTITION and ALTER INDEX MODIFY PARTITION.
13. Given the commands
CREATE TEMPORARY TABLESPACE PRDTTS1
TEMPFILE 'prdtts1.dbf' SIZE 100M
TABLESPACE GROUP PRDTMP;
CREATE TEMPORARY TABLESPACE PRDTTS2
TEMPFILE 'prdtts2.dbf' SIZE 100M
TABLESPACE GROUP PRDTMP;
which command does not assign the temporary tablespace group PRDTMP to a user?
A. CREATE USER KELLYM IDENTIFIED BY TJPO
DEFAULT TABLESPACE USERS
TEMPORARY TABLESPACE PRDTMP;
B. ALTER USER KELLYM TEMPORARY TABLESPACE GROUP PRDTMP;
C. ALTER DATABASE DEFAULT TEMPORARY TABLESPACE PRDTMP;
D. CREATE USER KELLYM IDENTIFIED BY TJPO
TEMPORARY TABLESPACE PRDTMP;I.
---
Ans: B. Logically, a temporary tablespace group is equivalent to an individual temporary tablespace. If a user is not assigned a default temporary tablespace, they are assigned the database's default temporary tablespace.
14. Given a hash-partitioned global index IX_ORD on the table ORD, with four partitions, and the following SELECT statement
SELECT /*+ PARALLEL_INDEX(ORD,IX_ORD,12) */
ORDER_ID, ORDER_DATE FROM ORD
WHERE ORDER_ID BETWEEN 110000 AND 190000;
which of the following is true about the number of processes used to execute the query?
A. Only one process is spawned since the index is hash-partitioned, and the WHERE clause uses a range.
B. After pruning the partitions down to those having the range of order IDs in the WHERE clause, the 12 processes are divided equally among the remaining partitions.
C. If the number of remaining partitions after pruning is fewer than 12, then not all 12 query processes are spawned; a maximum of one query process per partition is allowed for hash-partitioned global indexes.
D. The number of parallel query processes can only be a power of two, therefore, as many as 16 processes may be spawned.
---
Ans: B.
With range-partitioned indexes, partition pruning occurs, but only one parallel query process is spawned per partition whereas multiple query processes may be spawned for each pruned partition in a hash-partitioned global index.
15. Which of the following are true about renaming tablespaces? (Choose all that apply.)
A. Tablespaces that are READ ONLY cannot be renamed and must be changed to READ WRITE before renaming.
B. When a tablespace is renamed, all references to the tablespace name in the data dictionary, control file, online datafile headers, and initialization parameter files are updated.
C. You cannot rename the SYSTEM or SYSAUX tablespaces.
D. The tablespace must be online to be renamed.
E. Temporary tablespaces, undo tablespaces, and permanent tablespaces can be renamed.
---
Ans: C, D, E.
Tablespaces that are READ ONLY can be renamed, but the datafile header is not changed. References to the tablespace are updated in an SPFILE if necessary, but a text-based initialization parameter file is not changed.
16. Which of the following is a benefit of hash-partitioned global indexes?
A. Contention for the same index leaf blocks is reduced in an OLTP environment.
B. Indexes are smaller in a DSS environment because hash partitioning compresses duplicate entries for dimension keys in a star schema.
C. Hash-partitioned global indexes do not become invalid when partition maintenance occurs on the table partitions.
D. The application developer no longer needs to use a reverse-key index to optimize the updates to the index.
---
Ans: A. Hash partitioning spreads the activity to more leaf blocks and therefore reduces the contention for a given leaf block in an OLTP environment.
A
17. Identify the way(s) a DBA can find out if a suboptimal execution plan is being used for a query because a local partitioned index has become invalid and the SKIP_UNUSABLE_INDEXES parameter is set to TRUE. (Choose all that apply.)
A. Monitoring the data dictionary view DBA_IND_PARTITIONS for invalid indexes
B. Monitoring the alert log
C. Using EXPLAIN PLAN to preview the execution plan used for all queries
D. Monitoring user trace files
E. All of the above
---
Ans: A, B.
The DBA can monitor the view DBA_IND_PARTITIONS to see if index partitions become invalid; in addition, the alert log will contain messages when an index has been marked unusable. While EXPLAIN PLAN may alert a user that the index is not being chosen to run the query, it is impractical for the DBA to run the EXPLAIN PLAN command for all user queries. User trace files will not contain messages regarding invalid indexes.
E.
18. Under which of the following conditions is the tablespace SYSAUX created? (Choose all that apply.)
A. When the database is created
B. When you need to use features such as Ultra Search or the EM Repository
C. When the SYSTEM tablespace can no longer autoextend
D. When the database is upgraded from a previous version of Oracle
E. You do not need the SYSAUX tablespace; it is optional
---
Ans: A, D.
The SYSAUX tablespace is required for all new Oracle 10g database installations, as well as upgrading a previous version of Oracle to Oracle 10g. The SYSAUX table must exist, even if the applications that use the SYSAUX table are not installed.
19. Which of the following operations are allowed on the SYSAUX tablespace?
A. Transporting the SYSAUX tablespace to another database
B. Renaming the SYSAUX tablespace
C. Adding a datafile to the SYSAUX tablespace
D. Dropping the SYSAUX tablespace
E. Changing the SYSAUX tablespace from SEGMENT SPACE AUTO to SEGMENT SPACE MANUAL
---
Ans: C.
A datafile can be added to the SYSAUX tablespace, just as any other tablespace, as long as it is a smallfile tablespace. All the other operations listed are not allowed on the SYSAUX tablespace.
20. Which of the following methods can be used to verify the bigfile tablespace bfile.dbf with the DBVERIFY utility and enable parallel processing?
A
$ dbv FILE=bfile.dbf START=1 END=25000 &
$ dbv FILE=bfile.dbf START=25000 END=50000 &
$ dbv FILE=bfile.dbf START=50001 &
B $ dbv FILE=bfile.dbf PARALLEL=3
C Parallel processing is automatically enabled for DBVERIFY depending on the value of PARALLEL_MAX_SERVERS.
D Since a bigfile tablespace has only one datafile, parallel processing cannot be enabled.
E Parallel processing is automatically enabled for offline datafiles only.
F
$ dbverify FILE=bfile.dbf START=1 END=25000 &
$ dbverify FILE=bfile.dbf START=25000
END=50000 &
$ dbverify FILE=bfile.dbf START=50001 &
----
Ans: A.
The DBVERIFY utility, invoked as dbv on every platform, can be spawned multiple times, with each instance of DBVERIFY accessing a different portion of the datafile. No PARALLEL clause exists for DBVERIFY. Since the DBVERIFY utility is an external utility, it does not use database initialization parameters such as PARALLEL_MAX_SERVERS.
Oracle 10g New Features -- Ch 7 - Backup, Recovery, and High Availability
Chapter 7 - Backup, Recovery, and High Availability
Review Questions
1. Oracle 10g supports fast incremental backups. Which of the following is not true about the incremental backup's change-tracking file?
A. The size if the tracking file is proportional to the size of the database.
B. If a block tracking file exists, it is no longer necessary for each datafile to be read in its entirety during an incremental backup.
C. A change-tracking file is created by default when the database is created.
D. The block tracking file must be at least 10MB in size.
E. RMAN uses the block change-tracking file to determine which blocks to back up during an incremental backup.
----
Ans: C.
Change tracking is not enabled by default. When enabled, however, it incurs a slight amount of overhead whenever a block in any datafile is updated. This is offset by the time saved during incremental backup operations.
e.g.
ALTER DATABASE ENABLE
BLOCK CHANGE TRACKING USING FILE
'/oracle/admin/SMILEY/bct.dbf';
2. Which dynamic performance view or data dictionary view has a column that indicates whether the database is configured for Flashback Database?
A. V$DATABASE
B. V$INSTANCE
C. V$FLASHBACK_DATABASE_STAT
D. DATABASE_PROPERTIES
---
Ans: A.
The dynamic performance view V$DATABASE contains a new column FLASHBACK_ON that indicates whether Flashback Database is enabled.
3. Which two initialization parameters define the flash recovery area?
A. DB_RECOVERY_FILE_DEST and DB_RECOVERY_FILE_SIZE
B. DB_RECOVERY_FILE_DEST_SIZE and DB_RECOVERY_FILE_DEST
C. LOG_ARCHIVE_DEST_10 and FLASH_RECOVERY_DEST
D. DB_RECOVERY_FILE_DIR_SIZE and DB_RECOVERY_FILE_DIR
---
Ans: B.
DB_RECOVERY_FILE_DEST_SIZE specifies the maximum size of the flash recovery area, and DB_RECOVERY_FILE_DEST specifies the location of the flash recovery area. DB_RECOVERY_FILE_SIZE, FLASH_RECOVERY_DEST, DB_RECOVERY_FILE_DIR_SIZE, and DB_RECOVERY_FILE_DIR are not valid initialization parameters. LOG_ARCHIVE_DEST_10 points to the flash recovery area by default but does not define the flash recovery area.
4. Which of the following is true about space associated with a dropped table?
A. The space from the dropped table is reflected in the DBA_FREE_SPACE table, but the space is still counted against the table owner's quota.
B. The space from the dropped table is reflected in the DBA_FREE_SPACE table, and the table owner's quota is reduced accordingly.
C. The space from the dropped table is not reflected in the DBA_FREE_SPACE view until the PURGE TABLE command is issued.
D. The space from the dropped table is not reflected in the DBA_FREE_SPACE view until the PURGE USER_RECYCLEBIN command is issued.
---
Ans: A.
While the space from a dropped table shows up as additional free space in DBA_FREE_SPACE, the space is still counted against the user's quota until the PURGE USER_RECYCLEBIN or PURGE TABLE command is issued.
5. You have just run the following RMAN commands:
RMAN> configure controlfile autobackup off;
RMAN> backup datafile 1;
What are the results?
A. The first datafile of the SYSTEM tablespace is backed up as an image copy without the control file.
B. The SYSTEM tablespace is backed up as an image copy along with the control file.
C. The tablespace containing datafile #1 is backed up without the control file.
D. The first datafile of the SYSTEM tablespace is backed up along with the control file.
E. None of the above.
---
Ans: D.
The command CONFIGURE CONTROLFILE AUTOBACKUP OFF disables the automatic backup of the control file unless the backup includes any datafiles of the SYSTEM tablespace.
6. Which of the following methods does not show the contents of the recycle bin for a user with DBA privileges?
A. Query the view USER_RECYCLEBIN.
B. Use the command SHOW RECYCLEBIN.
C. Query the view DBA_RECYCLEBIN.
D. Query the view RECYCLEBIN.
E. You can use all of the above methods to query the contents of the recycle bin.
---
Ans: E.
In addition to the previous methods, the EM Database Control can display the current contents of the recycle bin.
7. Which of the following statements is not true about space reclamation and a tablespace's recycle bin?
A. Free space outside of the recycle bin is used first for new space requests.
B. If there are objects in the recycle bin, the datafile is autoextended before the contents of the recycle bin are reused.
C. Recycle bin objects are purged from the recycle bin in a FIFO method when free space outside of the recycle bin is not available.
D. More free space is available in the tablespace when a PURGE command is issued; however, those objects can no longer be recovered using Flashback Drop.
----
Ans: B.
The objects in the recycle bin are dropped to satisfy space requests before any of the tablespace's datafiles are autoextended.
8. As of Oracle 10g, the RMAN COPY command has been deprecated. Which RMAN command should you use instead to back up all the datafiles in the database?
A. BACKUP AS BACKUPSET DATABASE;
B. BACKUP AS COPY (TABLESPACE SYSTEM, SYSAUX, USERS, UNDOTBS);
C. BACKUP AS COPY ALL;
D. BACKUP AS COPY DATABASE;
---
Ans: D.
The BACKUP AS COPY DATABASE command will copy all datafiles in one command. BACKUP AS BACKUPSET DATABASE will back up the entire database but not in image copy format. BACKUP AS COPY ALL is not a valid RMAN command. BACKUP AS COPY using individual tablespaces will back up only the datafiles for the specific tablespaces in the database and does not include the archived logs, SPFILE, or control files.
9. The user SCOTT drops and re-creates the EMPLOYEES table four times. How many times must SCOTT issue the PURGE command to free up the space occupied by the dropped copies of the EMPLOYEES table?
A. Once, if SCOTT specifies the table's original name.
B. Four.
C. SCOTT can use only PURGE RECYCLEBIN to remove the dropped tables from the recycle bin.
D. Once, after all the dependent objects in the recycle bin are dropped first.
---
Ans: B.
The PURGE command can be issued four times to remove the four copies of the EMPLOYEES table. Alternatively, SCOTT can issue the PURGE RECYCLEBIN command to remove all dropped tables from the recycle bin.
10. Which of the following backup file types are not backed up when the RMAN BACKUP RECOVERY AREA command is issued?
A. Full backup sets
B. Flashback logs
C. Incremental backup sets
D. Datafile copies
E. Archive logs
----
Ans: B.
The RMAN BACKUP RECOVERY AREA command backs up all flash recovery files created in the flash recovery area that have not yet been backed up to tape, which includes full and incremental backup sets, control file autobackups, archive logs, and datafile copies; flashback logs, incremental bitmaps, current control file, and online redo log files are not backed up.
??
11. Choose the following statement that is true regarding Flashback Versions Query.
A. All rows that existed between the two SCNs or time stamps specified in the VERSIONS clause are returned, including rows that have been deleted and reinserted and rows that have not yet been committed.
B. All rows that existed between the two SCNs or time stamps specified in the VERSIONS clause are returned, not including rows that have been deleted and reinserted or uncommitted.
C. All rows that existed between the two SCNs or time stamps specified in the VERSIONS clause are returned, including rows that have been deleted and reinserted, but not uncommitted rows.
D. All rows that existed between the two SCNs or time stamps specified in the VERSIONS clause are returned, including both rows that have been deleted and reinserted and uncommitted rows in a current transaction.
---
Ans C.
Flashback Versions Query returns only rows that have been committed between two SCNs or time stamps. In addition, rows that have been deleted, reinserted, and committed are also returned.
12. You recently performed an RMAN image copy backup of the USERS tablespace consisting of datafiles #4 and #7. Next, you run the following command in RMAN:
RMAN> recover copy of datafile 7;
What are the results of this command?
A. Only the latest image copy of datafile #7 is updated with the contents of all incremental backup files created since the image copy was created.
B. If the most recent copy of datafile #7 is damaged or missing, it is re-created from an earlier image copy and the subsequent incremental backups; otherwise this command has no effect.
C. All image copies of datafile #7 are updated with the contents of all incremental backup files created since the image copy was created.
D. Both datafile #4 and datafile #7 are merged into a single image copy and updated with recent incremental backups.
----
Ans: A.
The RECOVER COPY OF DATAFILE command applies incremental RMAN backups to an image copy of the datafile and potentially reduces the amount of time needed for media recovery of the datafile because fewer archive log files are necessary to bring the datafile up to the latest SCN in the case of a media failure.
A.
13. Which of the following columns is not in the FLASHBACK_TRANSACTION_QUERY view?
A. REDO_SQL
B. START_SCN
C. UNDO_SQL
D. TABLE_OWNER
E. COMMIT_TIMESTAMP
---
Ans: A.
There is no such column REDO_SQL in FLASHBACK_TRANSACTION_QUERY.
14. What happens when you execute the following RMAN command? RMAN> backup copy of database;
A. It creates a backup of all datafiles to the flash recovery area as image copies by default.
B. A copy of all datafiles, control files, archived log files, and SPFILE are copied to the flash recovery area as image copies by default.
C. A copy of all datafiles, control files, archived log files, and SPFILE are copied to the flash recovery area as backup sets by default.
D. It creates a backup of previous image copies of all datafiles and control files in the database— in other words, a backup of a previous backup.
---
Ans: D. The BACKUP COPY OF DATABASE is usually used to create a copy of a backup already in the RMAN backup destination to another device type, such as tape. The copy can either be another image copy or be a backup set.
15. Identify the two columns of V$RECOVERY_FILE_DEST that are not accessible via the EM database Control.
A. SPACE_RECLAIMABLE, SPACE_LIMIT
B. SPACE_RECLAIMABLE, NUMBER_OF_FILES
C. SPACE_LIMIT, SPACE_USED
D. SPACE_USED, NUMBER_OF_FILES
--
Ans: B.
To view the values for SPACE_RECLAIMABLE and NUMBER_OF_FILES, you must use the dynamic performance view V$RECOVERY_FILE_DEST.
16. To ensure that undo data is retained in an undo tablespace for flashback features even if operations that need to generate undo may fail, what clause should be specified in the CREATE UNDO TABLESPACE or ALTER UNDO TABLESPACE?
A. FLASHBACK OFF
B. GUARANTEE RETENTION
C. RETENTION GUARANTEE
D. UNDO_RETENTION
---
Ans: C.
Turning off flashback generation for a tablespace has no effect on undo retention. GUARANTEE RETENTION is syntactically incorrect. UNDO_RETENTION is an initialization parameter, not an undo tablespace property.
17. Each row in the dynamic performance view V$FLASHBACK_DATABASE_STAT represents what time interval?
A. 24 hours
B. One hour
C. One minute
D. 30 minutes
---
Ans: B.
The dynamic performance view V$FLASHBACK_DATABASE_STAT contains statistics that monitor the overhead of logging flashback data in the Flashback Database logs at 1-hour intervals for a total of 24 hours.
18. Which of the following is not true about a table recovered from the recycle bin using the FLASHBACK TABLE…TO BEFORE DROP command?
A. All recovered indexes, triggers, and constraints associated with the table are no longer valid and must be re-created before they can be used.
B. If you recover a table that has been dropped several times, only the most recent version of the dropped table is restored unless you specify the name of the table in the recycle bin.
C. If the recovered table has the same name as an existing table, you must use the RENAME TO clause or recover the table to another schema.
D. Assuming that a new table with the same name has not yet been created, recovering a table from the recycle bin using either the original name or the recycle bin name achieves the same result.
---
Ans: A.
The indexes, triggers, and constraints keep their recycle bin names when the related table is restored, but they are still valid and usable. It is highly recommended, however, that the related structures be re-created or renamed with the original names.
19. You place all database files in online backup mode with the following command: ALTER DATABASE BEGIN BACKUP; Which of the following is not a requirement when you use this command?
A. You must use RMAN to perform the backup.
B. The database must be mounted and open.
C. The database must be in ARCHIVELOG mode.
D. A tablespace cannot be placed into read-only mode when this command is issued.
--
Ans: A.
The ALTER DATABASE BEGIN BACKUP command is only used when you are not using RMAN to ensure a consistent backup.
20. Choose the statement that is not true about space management in the flash recovery area.
A. A warning is issued when the flash recovery area is 85 percent full, and a critical warning is issued when the flash recovery area is 97 percent full.
B. When the flash recovery reaches 100 percent capacity, only files backed up to tape or another disk are considered for deletion to free up space for new backup files.
C. When files are written to the flash recovery area, a message is written to the alert log.
D. Obsolete files are automatically removed from the flash recovery area when the flash recovery area reaches 100 percent capacity.
E. When files are deleted from the flash recovery area, a message is written to the alert log.
----
Ans: B. Obsolete files are also considered for deletion when the flash recovery area reaches 100 percent capacity, even if they have not yet been backed up to tape or another disk device.
Review Questions
1. Oracle 10g supports fast incremental backups. Which of the following is not true about the incremental backup's change-tracking file?
A. The size if the tracking file is proportional to the size of the database.
B. If a block tracking file exists, it is no longer necessary for each datafile to be read in its entirety during an incremental backup.
C. A change-tracking file is created by default when the database is created.
D. The block tracking file must be at least 10MB in size.
E. RMAN uses the block change-tracking file to determine which blocks to back up during an incremental backup.
----
Ans: C.
Change tracking is not enabled by default. When enabled, however, it incurs a slight amount of overhead whenever a block in any datafile is updated. This is offset by the time saved during incremental backup operations.
e.g.
ALTER DATABASE ENABLE
BLOCK CHANGE TRACKING USING FILE
'/oracle/admin/SMILEY/bct.dbf';
2. Which dynamic performance view or data dictionary view has a column that indicates whether the database is configured for Flashback Database?
A. V$DATABASE
B. V$INSTANCE
C. V$FLASHBACK_DATABASE_STAT
D. DATABASE_PROPERTIES
---
Ans: A.
The dynamic performance view V$DATABASE contains a new column FLASHBACK_ON that indicates whether Flashback Database is enabled.
3. Which two initialization parameters define the flash recovery area?
A. DB_RECOVERY_FILE_DEST and DB_RECOVERY_FILE_SIZE
B. DB_RECOVERY_FILE_DEST_SIZE and DB_RECOVERY_FILE_DEST
C. LOG_ARCHIVE_DEST_10 and FLASH_RECOVERY_DEST
D. DB_RECOVERY_FILE_DIR_SIZE and DB_RECOVERY_FILE_DIR
---
Ans: B.
DB_RECOVERY_FILE_DEST_SIZE specifies the maximum size of the flash recovery area, and DB_RECOVERY_FILE_DEST specifies the location of the flash recovery area. DB_RECOVERY_FILE_SIZE, FLASH_RECOVERY_DEST, DB_RECOVERY_FILE_DIR_SIZE, and DB_RECOVERY_FILE_DIR are not valid initialization parameters. LOG_ARCHIVE_DEST_10 points to the flash recovery area by default but does not define the flash recovery area.
4. Which of the following is true about space associated with a dropped table?
A. The space from the dropped table is reflected in the DBA_FREE_SPACE table, but the space is still counted against the table owner's quota.
B. The space from the dropped table is reflected in the DBA_FREE_SPACE table, and the table owner's quota is reduced accordingly.
C. The space from the dropped table is not reflected in the DBA_FREE_SPACE view until the PURGE TABLE command is issued.
D. The space from the dropped table is not reflected in the DBA_FREE_SPACE view until the PURGE USER_RECYCLEBIN command is issued.
---
Ans: A.
While the space from a dropped table shows up as additional free space in DBA_FREE_SPACE, the space is still counted against the user's quota until the PURGE USER_RECYCLEBIN or PURGE TABLE command is issued.
5. You have just run the following RMAN commands:
RMAN> configure controlfile autobackup off;
RMAN> backup datafile 1;
What are the results?
A. The first datafile of the SYSTEM tablespace is backed up as an image copy without the control file.
B. The SYSTEM tablespace is backed up as an image copy along with the control file.
C. The tablespace containing datafile #1 is backed up without the control file.
D. The first datafile of the SYSTEM tablespace is backed up along with the control file.
E. None of the above.
---
Ans: D.
The command CONFIGURE CONTROLFILE AUTOBACKUP OFF disables the automatic backup of the control file unless the backup includes any datafiles of the SYSTEM tablespace.
6. Which of the following methods does not show the contents of the recycle bin for a user with DBA privileges?
A. Query the view USER_RECYCLEBIN.
B. Use the command SHOW RECYCLEBIN.
C. Query the view DBA_RECYCLEBIN.
D. Query the view RECYCLEBIN.
E. You can use all of the above methods to query the contents of the recycle bin.
---
Ans: E.
In addition to the previous methods, the EM Database Control can display the current contents of the recycle bin.
7. Which of the following statements is not true about space reclamation and a tablespace's recycle bin?
A. Free space outside of the recycle bin is used first for new space requests.
B. If there are objects in the recycle bin, the datafile is autoextended before the contents of the recycle bin are reused.
C. Recycle bin objects are purged from the recycle bin in a FIFO method when free space outside of the recycle bin is not available.
D. More free space is available in the tablespace when a PURGE command is issued; however, those objects can no longer be recovered using Flashback Drop.
----
Ans: B.
The objects in the recycle bin are dropped to satisfy space requests before any of the tablespace's datafiles are autoextended.
8. As of Oracle 10g, the RMAN COPY command has been deprecated. Which RMAN command should you use instead to back up all the datafiles in the database?
A. BACKUP AS BACKUPSET DATABASE;
B. BACKUP AS COPY (TABLESPACE SYSTEM, SYSAUX, USERS, UNDOTBS);
C. BACKUP AS COPY ALL;
D. BACKUP AS COPY DATABASE;
---
Ans: D.
The BACKUP AS COPY DATABASE command will copy all datafiles in one command. BACKUP AS BACKUPSET DATABASE will back up the entire database but not in image copy format. BACKUP AS COPY ALL is not a valid RMAN command. BACKUP AS COPY using individual tablespaces will back up only the datafiles for the specific tablespaces in the database and does not include the archived logs, SPFILE, or control files.
9. The user SCOTT drops and re-creates the EMPLOYEES table four times. How many times must SCOTT issue the PURGE command to free up the space occupied by the dropped copies of the EMPLOYEES table?
A. Once, if SCOTT specifies the table's original name.
B. Four.
C. SCOTT can use only PURGE RECYCLEBIN to remove the dropped tables from the recycle bin.
D. Once, after all the dependent objects in the recycle bin are dropped first.
---
Ans: B.
The PURGE command can be issued four times to remove the four copies of the EMPLOYEES table. Alternatively, SCOTT can issue the PURGE RECYCLEBIN command to remove all dropped tables from the recycle bin.
10. Which of the following backup file types are not backed up when the RMAN BACKUP RECOVERY AREA command is issued?
A. Full backup sets
B. Flashback logs
C. Incremental backup sets
D. Datafile copies
E. Archive logs
----
Ans: B.
The RMAN BACKUP RECOVERY AREA command backs up all flash recovery files created in the flash recovery area that have not yet been backed up to tape, which includes full and incremental backup sets, control file autobackups, archive logs, and datafile copies; flashback logs, incremental bitmaps, current control file, and online redo log files are not backed up.
??
11. Choose the following statement that is true regarding Flashback Versions Query.
A. All rows that existed between the two SCNs or time stamps specified in the VERSIONS clause are returned, including rows that have been deleted and reinserted and rows that have not yet been committed.
B. All rows that existed between the two SCNs or time stamps specified in the VERSIONS clause are returned, not including rows that have been deleted and reinserted or uncommitted.
C. All rows that existed between the two SCNs or time stamps specified in the VERSIONS clause are returned, including rows that have been deleted and reinserted, but not uncommitted rows.
D. All rows that existed between the two SCNs or time stamps specified in the VERSIONS clause are returned, including both rows that have been deleted and reinserted and uncommitted rows in a current transaction.
---
Ans C.
Flashback Versions Query returns only rows that have been committed between two SCNs or time stamps. In addition, rows that have been deleted, reinserted, and committed are also returned.
12. You recently performed an RMAN image copy backup of the USERS tablespace consisting of datafiles #4 and #7. Next, you run the following command in RMAN:
RMAN> recover copy of datafile 7;
What are the results of this command?
A. Only the latest image copy of datafile #7 is updated with the contents of all incremental backup files created since the image copy was created.
B. If the most recent copy of datafile #7 is damaged or missing, it is re-created from an earlier image copy and the subsequent incremental backups; otherwise this command has no effect.
C. All image copies of datafile #7 are updated with the contents of all incremental backup files created since the image copy was created.
D. Both datafile #4 and datafile #7 are merged into a single image copy and updated with recent incremental backups.
----
Ans: A.
The RECOVER COPY OF DATAFILE command applies incremental RMAN backups to an image copy of the datafile and potentially reduces the amount of time needed for media recovery of the datafile because fewer archive log files are necessary to bring the datafile up to the latest SCN in the case of a media failure.
A.
13. Which of the following columns is not in the FLASHBACK_TRANSACTION_QUERY view?
A. REDO_SQL
B. START_SCN
C. UNDO_SQL
D. TABLE_OWNER
E. COMMIT_TIMESTAMP
---
Ans: A.
There is no such column REDO_SQL in FLASHBACK_TRANSACTION_QUERY.
14. What happens when you execute the following RMAN command? RMAN> backup copy of database;
A. It creates a backup of all datafiles to the flash recovery area as image copies by default.
B. A copy of all datafiles, control files, archived log files, and SPFILE are copied to the flash recovery area as image copies by default.
C. A copy of all datafiles, control files, archived log files, and SPFILE are copied to the flash recovery area as backup sets by default.
D. It creates a backup of previous image copies of all datafiles and control files in the database— in other words, a backup of a previous backup.
---
Ans: D. The BACKUP COPY OF DATABASE is usually used to create a copy of a backup already in the RMAN backup destination to another device type, such as tape. The copy can either be another image copy or be a backup set.
15. Identify the two columns of V$RECOVERY_FILE_DEST that are not accessible via the EM database Control.
A. SPACE_RECLAIMABLE, SPACE_LIMIT
B. SPACE_RECLAIMABLE, NUMBER_OF_FILES
C. SPACE_LIMIT, SPACE_USED
D. SPACE_USED, NUMBER_OF_FILES
--
Ans: B.
To view the values for SPACE_RECLAIMABLE and NUMBER_OF_FILES, you must use the dynamic performance view V$RECOVERY_FILE_DEST.
16. To ensure that undo data is retained in an undo tablespace for flashback features even if operations that need to generate undo may fail, what clause should be specified in the CREATE UNDO TABLESPACE or ALTER UNDO TABLESPACE?
A. FLASHBACK OFF
B. GUARANTEE RETENTION
C. RETENTION GUARANTEE
D. UNDO_RETENTION
---
Ans: C.
Turning off flashback generation for a tablespace has no effect on undo retention. GUARANTEE RETENTION is syntactically incorrect. UNDO_RETENTION is an initialization parameter, not an undo tablespace property.
17. Each row in the dynamic performance view V$FLASHBACK_DATABASE_STAT represents what time interval?
A. 24 hours
B. One hour
C. One minute
D. 30 minutes
---
Ans: B.
The dynamic performance view V$FLASHBACK_DATABASE_STAT contains statistics that monitor the overhead of logging flashback data in the Flashback Database logs at 1-hour intervals for a total of 24 hours.
18. Which of the following is not true about a table recovered from the recycle bin using the FLASHBACK TABLE…TO BEFORE DROP command?
A. All recovered indexes, triggers, and constraints associated with the table are no longer valid and must be re-created before they can be used.
B. If you recover a table that has been dropped several times, only the most recent version of the dropped table is restored unless you specify the name of the table in the recycle bin.
C. If the recovered table has the same name as an existing table, you must use the RENAME TO clause or recover the table to another schema.
D. Assuming that a new table with the same name has not yet been created, recovering a table from the recycle bin using either the original name or the recycle bin name achieves the same result.
---
Ans: A.
The indexes, triggers, and constraints keep their recycle bin names when the related table is restored, but they are still valid and usable. It is highly recommended, however, that the related structures be re-created or renamed with the original names.
19. You place all database files in online backup mode with the following command: ALTER DATABASE BEGIN BACKUP; Which of the following is not a requirement when you use this command?
A. You must use RMAN to perform the backup.
B. The database must be mounted and open.
C. The database must be in ARCHIVELOG mode.
D. A tablespace cannot be placed into read-only mode when this command is issued.
--
Ans: A.
The ALTER DATABASE BEGIN BACKUP command is only used when you are not using RMAN to ensure a consistent backup.
20. Choose the statement that is not true about space management in the flash recovery area.
A. A warning is issued when the flash recovery area is 85 percent full, and a critical warning is issued when the flash recovery area is 97 percent full.
B. When the flash recovery reaches 100 percent capacity, only files backed up to tape or another disk are considered for deletion to free up space for new backup files.
C. When files are written to the flash recovery area, a message is written to the alert log.
D. Obsolete files are automatically removed from the flash recovery area when the flash recovery area reaches 100 percent capacity.
E. When files are deleted from the flash recovery area, a message is written to the alert log.
----
Ans: B. Obsolete files are also considered for deletion when the flash recovery area reaches 100 percent capacity, even if they have not yet been backed up to tape or another disk device.
Subscribe to:
Posts (Atom)
