Oracle 10g Database Administration
Which
three out of the following are the physical structures that constitute the
Oracle database?
a. Segment
b. Data
file
c. Log
file
d. Tablespace
e. Control
file
Examine
the following SQL statement:
CREATE
TABLESPACE userdata
DATAFILE
'/oracle/oradata/userdata_0l.dbf' SIZE 100M
LOCALLY
MANAGED UNIFORM SIZE 1M
AUTOMATIC
SEGMENT SPACE MANAGEMENT;
Which
part of the tablespace will be of a uniform size of 1 MB?
a. Extent
b. Segment
c. Oracle
block
d. Operating
system block
What
should be confirmed before changing the CURSOR_SPACE_FOR_TIME parameter in the
initialization file to TRUE?
a. The
TIMED_STATISTICS parameter is set to TRUE
b. The
hit percentage in the buffer cache is at least 95%
c. The
OPEN_CURSOR parameter is set to at least twice the default value
d. The
value in the RELOADS column of V$LIBRARYCACHE is consistently zero
Which
data dictionary view shows the available free space in a certain tablespace?
a. DBA_EXTENTS
b. V$FREESPACE
c. DBA_FREE_SPACE
d. DBA_TABLESPACFS
A
table is stored in a data dictionary managed tablespace. Which two columns are
required from DBA_TABLES to determine the size of the extent when it extends?
a. BLOCKS
b. PCT_FREE
c. NEXT_EXTENT
d. PCT_INCREASE
Which
command would revoke the ROLE_DATA role from all users?
a. REVOKE
role_data FROM ALL;
b. REVOKE
role_data FROM PUBLIC;
c. REVOKE
role_data FROM default;
d. REVOKE
role_data FROM ALL_USERS;
Which
of the following is a new initialization parameter for Oracle Database 10g?
a. UNDO_SUPPRESS_ERRORS
b. PARALLEL_AUTOMATIC_TUNING
c. RESUMABLE_TIMEOUT
d. SHARED_SERVER
When
is SGA created in an Oracle database environment?
a. When
the database is created
b. When
the instance is started
c. When
the database is mounted
d. When
a user process is started
Which
of the following sets of tablespaces is mandatory for any Oracle 10g database?
a. SYSTEM,
SYSAUX, and UNDOTBS
b. SYSTEM
and UNDOTBS
c. SYSAUX
and UNDOTBS
d. SYSTEM
and SYSAUX
For
what purpose is the initialization parameter UNDO_RETENTION used?
a. To
specify the length of time Oracle must retain undo data in the undo tablespace
b. To
specify the length of time Oracle must retain undo data in the flash recovery
area
c. To
specify the length of time Oracle must retain undo data in the data files
d. To
specify the length of time Oracle will retain undo data in the flashback
database logs
Which
of the following conditions should be met before a RENAME DATAFILE with the
alter tablespace command is used?
a. The
datafile must be taken offline before renaming
b. The
database must be open
c. When
only a single datafile is to be renamed
d. When
only a single datafile on the same drive is to be renamed
Which
of the following items are stored in the flash recovery area?
a. Datafiles
b. Undo
segments
c. Datafile
copies
d. Archived
redo log files
What
is the purpose of the flashback technology in Oracle Database 10g?
a. Recovery
of a data file from physical corruption
b. Recovery
of a data file from physical and logical corruption
Ans c. Recovery
of a data file from logical corruption
d. Recovery
from a problem brought on by a damaged disk drive
What
will the command EXECUTE DBMS_SCHEDULER.PURGE_LOG(); do?
a. Purge
only all the window logs
b. Purge
only all the job logs
c. Purge
all window and job logs
d. Purge
only yesterday's job logs
Which
tablespace is used as the temporary tablespace if 'TEMPORARY TABLESPACE' is not
specified for a user?
a. TEMP
b. DATA
c. SYSTEM
d. ROLLBACK
Which
initialization parameter specifies the location of the alert log file?
a. UTL_FILE_DIR
b. USER_DUMP_DEST
c. LOG_ARCHIVE_DEST
Ans d. BACKGROUND_DUMP_DEST
Which
data dictionary view should be used to get a list of object privileges for all
database users?
Ans a. DBA_TAB_PRIVS
b. ALL_TAB_PRIVS
c. USER_TAB_PRIVS
d. ALL_TAB_PRIVS_MADE
Which
of the following are considered types of segments?
a. Only
LOBS
b. Only
nested tables
c. Only
index-organized tables
d. Only
LOBS and index-organized tables
e. Nested
tables, LOBS, index-organized tables, and boot straps
Which
option lists the correct hierarchy of storage structures, from largest to
smallest?
a. Segment,
extent, tablespace, data block
b. Data
block, extent, segment, tablespace
c. Tablespace,
extent, data block, segment
d. Tablespace,
segment, extent, data block
What
statistics will Oracle collect If LIST subpartitioning is used, and the value
of the GRANULARITY argument is specified as AUTO?
a. Global
statistics only
b. Global
and partition statistics only
c. Global,
partition, and subpartition statistics
d. No
partitioned table statistics
What
does the RMAN CATALOG command do?
a. Helps
create image copies of datafiles
b. Helps
create image copies of current control files
c. Helps
catalog any new files that aren't a part of the recovery catalog
d. Helps
catalog only the old files
Which
of the following commands must be used to apply incremental backups?
a. RECOVER
BACKUP OF DATABASE
b. RECOVER
DATAFILE
c. RECOVER
COPY OF DATAFILE
d. RECOVER
COPY OF DATABASE
What
is the default value for the OPTIMIZER_MODE initialization parameter?
a. FIRST_ROWS
b. ALL_ROWS
c. CHOOSE
d. COST
Consider
this SQL statement:
UPDATE
employees SET first_name = 'John'
WHERE
emp_id = 1009;
COMMIT;
What
happens when a user issues the COMMIT in the above SQL statement?
a. The
server process places the commit record in the redo log buffer
b. Log
Writer (LGWR) writes the redo log buffer entries to the redo log files and data
files
c. The
user process notifies the server process that the transaction is complete
d. The
user process notifies the server process that the resource locks can be
released
How
can the SQL Access Advisor be managed?
a. Through
the DBMS_SQLTUNE package
b. Through
the DBMS_ADVISOR package
c. Through
the SQL_TUNE package
d. Through
the DBA_ADVISOR package
How
is the SQL profile used?
a. In
the tuning mode of the optimizer only
b. In
the normal mode of the optimizer
c. In
neither the tuning nor the normal mode of the optimizer
d. Along
with existing statistics, to get better SQL execution plans
Evaluatethe following SQL:
CREATE
USER sam IDENTIFIED BY sam;
GRANT
CREATE
ANY MATERIALIZED VIEW,
CREATE
ANY DIMENSION, DROP ANY DIMENSION, QUERY REWRITE, GLOBAL QUERY REWRITE
TO
dw_manager
WITH
ADMIN OPTION;
GRANT
dw_manager TO sam WITH ADMIN OPTION;
Which
three actions among the following is the user SAM able to perform?
a. Select
from a table
b. Create
and drop a materialized view
c. Alter
a materialized view that was created
d. Grant
and revoke the role to and from other users
e. Enable
the role and exercise any privileges in the role's privilege domain
When
there is a monotonically increasing sequence number as an indexed column, which
of the following is true?
a. Hash-partitioned
global indexes are useful
b. Range-partitioned
global indexes are better
c. Hash-partitioned
local indexes are better
d. Range-partitioned
local indexes are better
An
index on the PASSPORT_RECORDS table has to be created. It contains 10 million
rows of data. The key columns have low cardinality. The queries generated
against this table use a combination of multiple WHERE conditions involving the
OR operator.Which of the following index will be used for this criteria?
a. Bitmap
b. Unique
c. Partitioned
d. Reverse
key
Which
three statements among the following are true about rebuilding indexes?
a. The
ALTER INDEX REBUILD command is used to change the storage characteristics of an
index
b. Using
the ALTER INDEX REBUILD is usually faster than dropping and recreating an index
because it uses the fast full scan feature
c. Oracle8i
allows for the creation of an index or re-creation of an existing index while
allowing concurrent operations on the base table
d. When
building an index, the NOLOGGING and UNRECOVERABLE keywords can be used
concurrently to reduce the time it takes to rebuild itself
Which
graphical DBA administration tool should be used to tune an Oracle database?
a. SQL*Plus
b. Oracle
Enterprise Manager
c. Oracle
Universal Installer
d. Oracle
Database Configuration Assistant
Which
of the following initialization parameters would belong to the basic group of
parameters?
a. SHARED_POOL
b. CURSOR_SPACE_FOR_TIME
c. DB_NAME
d. CURSOR_SHARING
User
Smith created indexes on some tables owned by user John. The following has to
be displayed:
1.Index
names
2.Index
types
Which
data dictionary view(s) should be queried?
a. DBA_INDEXES
only
b. DBA_IND_COLUMNS
only
c. DBA_INDEXES
and DBA_USERS
d. DBA_IND
COLUMNS and DBA_USERS
Where
can the nondefault parameters be found when the instance is started?
a. Alert
log
b. Online
redo log
c. Archiver
redo log
d. SYSTEM
user's trace file
Which
background process performs a checkpoint in the database,by writing modified
blocks from the database buffer cache in the SGA to the data files?
a. LGWR
b. SMON
c. DBWn
d. CKPT
Which
of the following is true if the alter tablespace statement is used and the
TEMPORARY clause is specified?
a. Oracle
no longer performs any checkpoint for the online datafiles in the tablespace
b. Oracle
performs a checkpoint for all online datafiles in the tablespace
c. Oracle
does not ensure that all files are written
d. The
offline files may require media recovery before the tablespace is brought
online
e. The
offline files may require media recovery after the tablespace is brought online
Which
statement is true when connecting to the Oracle instance using the
multithreaded server configuration?
a. The
User Global Area (UGA) may only contain sort areas
b. The
User Global Area (UGA) may be accessible to dedicated servers
c. The
User Global Area (UGA) components may reside in the large pool
d. The
User Global Area (UGA) components may reside in the buffer cache
Which
three among the following dynamic performance views obtain information from the
control file?
a. V$LOG
b. V$SGA
c. V$THREAD
d. V$VERSION
e. V$DATAFILE
What
are the functions of an ASM instance?
a. Managing
disk groups and communicating file metadata to the Oracle database using
ASM
files
b. Managing
database files and communicating file metadata to the ASM instance using
ASM
files
c. Managing
disk groups and communicating file metadata to the RMAN
d. Protecting
disk groups
Which
statement is valid regarding index clusters?
a. Index
clusters can only be used for tables with low cardinality columns
b. Index
clusters are generally well suited for tables that have many full table scans
c. Normal
B-Tree indexes do not store null key values, whereas cluster indexes store null
keys
d. A
cluster index always takes up much more storage space than a normal index for
the same set of key values