1Z0-035 - Oracle9i DBA New Features for Oracle7.3 and Oracle8 OCPs
Go back to Oracle
Which statement is true when using the DBVERIFY utility to detect corruptions in an Oracle data block?
The utility can be used with the database shut down.
Examine the statement: DROP TABLESPACE IND2 INCLUDING CONTENTS AND DATAFILES; What is the result of the statement?
The contents and data files belonging to the IND2 tablespace are dropped.
Examine the following parameter settings from an initialization (init.ora) file: DB_CREATE_FILE_DEST = '/u01/oradata/' DB_CREATE_ONLINE_LOG_DEST_1 = '/u02/oradata/' DB_CREATE_ONLINE_LOG_DEST_2 = '/u03/oradata/' If you create an Oracle Managed Files (OMF) database using these settings, what is the result?
The data files and temp files will be on device /u01; one copy of the control file and the first member in each online redo log group will be on device /u02; another copy of the control file and a second member of each redo log group will be on device /u03.
You issue these queries to obtain information about the REGIONS table: SQL> SELECT segment_name, tablespace_name 2> FROM user_segments 3> WHERE segment_name = 'REGIONS'; SEGMENT_NAME TABLESPACE_NAME ----------------------- ------------------------------ REGIONS SAMPLE SQL> SELECT constraint_name, constraint_type 2> FROM user_constraints 3> WHERE table_name = 'REGIONS'; CONSTRAINT_NAME C ------------------------------ ---- REGION_ID_NN C REG_ID_PK P SQL> SELECT index_name 2> FROM user_indexes 3> WHERE table_name = 'REGIONS'; INDEX_NAME ------------------------------ REG_ID_PK You then issue this command to move the REGIONS table: ALTER TABLE regions MOVE TABLESPACE user_data; What else must you do to complete the move of the REGIONS table?
You must rebuild the REG_ID_PK index.
You can control instance recovery performance by setting parameters to define targets for _____.
the number of blocks to be read during instance recovery, or the time needed to complete instance recovery
Examine this startup script called startmydb.sql: CONNECT myid/mypwd AS SYSDBA STARTUP EXIT In Oracle8i, you could have executed this script by invoking Server Manager with the command: svrmgrl @startmydb.sql Which alternative command could you use to run the startmydb.sql script with SQL*Plus to start your Oracle9i database?
sqlplus /NOLOG @startmydb.sql
The Dynamic SGA feature allows the SGA to grow and shrink dynamically according to an ALTER SYSTEM command. This avoids the previous need of shutting down the instance in order to modify the components of the SGA, namely the buffer cache and shared pool components. Which three statements are true for the Dynamic SGA feature? (Choose three.)
The minimum SGA configuration is three granules.
SGA memory is based on granules by SGA components.
The size of the SGA components is limited by the setting of SGA_MAX_SIZE parameter.
Which three statements about Recovery Manager backups are true? (Choose three.)
Incremental backups are noncumulative by default.
Each file in a backup set must have the same block size.
In a full backup, Recovery Manager will back up all files and all used blocks only.
Which two are true regarding a list-partitioned table? (Choose two.)
It is NOT possible to have multiple columns as the partition key of a list-partitioned table.
The keyword NULL can be specified as a partition literal value for one partition of a list-partitioned table.
What is characteristic of autonomous transactions?
Committed changes in an autonomous transaction are visible to other transactions (except possibly the calling transaction) regardless of whether the calling transaction commits or rolls back.
You decide to activate the resumable space allocation feature for all your database users by enabling the feature in a logon trigger. This causes a statement that requires space beyond a user's quota _____.
to wait in a suspended state until the quota is increased or until the timeout value is reached
Consider this RMAN command: RMAN> CONFIGURE RETENTION POLICY CLEAR; What is the effect of this command?
It sets the retention policy to the default of REDUNDANCY 1.
What are three benefits of performing data definition language (DDL) statements against a partitioned table with the UPDATE GLOBAL INDEXES clause? (Choose three.)
You do not have to search for invalid global indexes after the DDL command completes and rebuild them individually.
Global indexes are maintained during the operation of the DDL command and therefore can be used by any concurrent query.
Global indexes remain intact and available for use by data manipulation language (DML) statements even for sessions that have not enabled the skipping of unusable indexes.
What criterion does Oracle9i use to determine whether a database file is an Oracle Managed File?
the filename format
Which type of index should be created to spread the distribution of index updates across the index tree?
Which two statements are true? (Choose two.)
Composite partitioning is ideal for both historical data and data placement.
Composite partitioning provides high performance and manageability, like range partitioning.
What is required for the Character Set Scanner to operate?
a special schema installed with csminst.sql
Consider the following scenario using incremental backups. A level 1 backup is taken on the first of the month. A level 2 backup is taken every Sunday, and a level 3 backup is taken daily. If the database gets corrupted on the second Wednesday of the month and is lost, which scenario would restore the database completely?
a base level backup (level 0) is missing and nothing can be recovered
What does Block Media Recovery (BMR) do?
does a restore and a recovery of the specified blocks
What does the Character Set Scanner command line utility do?
checks for any character definitions that will fail conversion to the new character set
You use the TABLESPACES option of the Export command to export, not transport, the contents of a set of tablespaces. What are the only contents of the dump file?
1. non-partitioned tables in the tablespace set 2. partitioned tables that have at least one partition in the tablespace set 3. indexes on all the exported tables
Users complain that SQL statements using a particular index fail. Using DBVERIFY, you find that two separate blocks in the index have become corrupt. The database data files for user data and index are very large. What is the least disruptive recovery strategy available in Oracle9i?
Without setting the individual data file offline, use RMAN with Block Media Recovery to restore and recover only those blocks.
Which statement correctly describes the function of the Oracle9i Cache Fusion feature?
It enables you to execute scalable applications on a clustered database without having to partition the users or the database tables.
Oracle Enterprise Manage (OEM) can generate database reports. Which statement about this feature is true?
The reports are in HTML format for viewing in a browser.
What is true regarding a shared, server-side parameter file for a Real Application Clusters database?
It can contain parameters with distinct values for each instance.
Which feature requires the extended ROWID format?
segments that can be in different tablespaces
What is the meaning of using "character semantics" to create a table?
You specify the width of a column in characters, not in bytes.
Which two are true regarding automatic space management segments? (Choose two.)
You can create an automatic space management segment only in a locally managed tablespace.
You CANNOT successfully invoke the DBMS_SPACE.FREE_BLOCKS procedure on an automatic space management segment.
Your database has tablespaces with different block sizes. What must you do to back these up with RMAN?
You must allow for the fact that RMAN will generate several backupsets, one for each block size, when you issue the RMAN BACKUP command.
To assist you in migrating your existing LONG columns to LOB data types, the ALTER TABLE syntax has been enhanced to support LONG to CLOB migration and LONG RAW to BLOB migration. During this migration, if redo logging is enabled for the table or for the LOB being created, the ALTER TABLE ?MODIFY command temporarily _____ the space requirements.
Examine this fragment from a SQL*Plus session: SQL> SELECT name, value FROM v$sysstat 2> WHERE name LIKE 'work area executions%'; NAME VALUE -------------------------------- -------------- work area executions - optimal 1544 work area executions - onepass 11 work area executions - multipass 1038 What can you conclude about the setting of the PGA_AGGREGATE_TARGET initialization parameter?
It may be set too low because there are many more multipass work area executions than one pass executions.
What is true about version-enabled tables?
It is possible to create workspaces if there is no version-enabled table in the database.
Which three attributes of the LOG_ARCHIVE_DEST_n initialization parameter control the data availability mode of a Data Guard standby database? (Choose three.)
SYNC or ASYNC to identify the network transmission mode
LGWR or ARCH to identify the primary database process responsible for sending redo information to the standby
AFFIRM or NOAFFIRM to control whether log archiving disk write operations are to be performed synchronously or asynchronously
Examine this statement: SELECT * FROM ( SELECT class_id, crs_id FROM classes ORDER BY start_date DESC) WHERE ROWNUM < 11; What will happen when this executes?
The ten rows with the latest start date will be returned.
What does fine-grained access enable you to do?
associate policies with tables and views
You migrated the PROD database to Oracle9i and are considering using the partition feature. The first step is to identify which tables should be partitioned. Which two tables would you consider candidates for partitioning? (Choose two.)
tables greater than 10 GB in size
tables containing a multi-column primary key
Why can setting too low a value for the FAST_START_MTTR_TARGET parameter reduce your overall database performance?
Data blocks have to be written more frequently by DBWR.
Which command can you use to alter the active size of the SGA dynamically, where the entered value differs from the current one?
ALTER SYSTEM SET SHARED_POOL_SIZE=40M;
Which three things can the ALTER INDEX REBUILD command accomplish? (Choose three.)
move the index to a different tablespace
change the storage parameters for the index
rebuild a reverse index from an existing B-tree index
Automatic Consumer Group Switching is an important new feature of the Database Resource Manager. Which three plan directive parameters are used to control this feature? (Choose three.)
Which three table transformations can be done using online redefinition of tables? (Choose three.)
drop a column
change a heap table to an index-organized table
change a range-partitioned table to a list-partitioned table
You need to add a column to a table that holds updateable image data. Which data type should you use for this column?
The TYPE clause used in the creation of an external table defines which access driver is used to convert the data when needed by SQL statements inside the database. Which type of access driver is supported for external tables?
Which CREATE TABLE parameter should you be concerned with if the length of rows in an index-organized table vary over a wide range of values?
Which two procedures or functions are part of the DBMS_METADATA package? (Choose two.)
You can use Oracle Flashback to look at past activity in your database. What are two other characteristics of Oracle Flashback? (Choose two.)
Oracle Flashback uses undo information to construct consistent data.
You can use a PL/SQL cursor opened while using an Oracle Flashback image to perform DML once you deactivate Oracle Flashback in your session.
In which tablespace are temporary LOBs stored?
in the user's temporary tablespace
Online index rebuild functionality has been extended to include which four index structures? (Choose four.)
reverse key indexes
key-compressed indexes on regular tables
key-compressed indexes on IOT (including secondary indexes)
What are three advantages of using Oracle Advanced Queuing? (Choose three.)
allows you to use RAW or user-defined data types for messages
allows you to prioritize or order messages
allows you to put time constraints on messages so that they will be removed from the queue after a specified time
What do you need to do in order for the Database Resource Manager to estimate, in advance, the execution time of a statement?
Gather optimizer statistics on related objects.