1Z0-030 - Oracle9i New Features for Administrator
Go back to Oracle
Examine this statement, which creates a Cartesian product of the COUNTRIES and REGION tables: SQL> SELECT c.country_name, 2> r.region name 3> FROM countries c, region r; Oracle9i SQL: 1999 syntax supports the same functionality with which join type?
The direct handoff connection method to an Oracle9i Shared Server involves fewer messages than in previous releases of the multithreaded architecture. Which type of message has been eliminated?
A redirect message from the listener to the client during client connections
What does the Metadata Application Programming Interface (API) allow you to do?
Extract data definition commands from the data dictionary in a variety of formats
What is true if you want to switch undo tablespaces from the current one, called UNDO1, to a new one called UNDO2?
It is possible to switch to UNDO2; only current active transactions will continue to execute inside UNDO1
Examine the code. CREATE ROLE readonly IDENTIFIED USING app.chk_readwrite ; CREATE ROLE readwrite IDENTIFIED USING app.chk_readwrite ; CREATE OR REPLACE PROCEDURE app.chk_readwrite AUTHID CURRENT_USER IS ipchk STRING(30); BEGIN IF says context ('USERENV', 'ISDBA'='TRUE' THEN DBMS SESSION.SET_ROLE ('READWRITE') ; ELSE DMBS_SESSION_SET_ROLE )'READONLY') ; END IF ; END; / Which three statements correctly describe the Secure Application role definition? (Choose three.) (The SYS_CONTEXT calls returns whether or not the session is a true DBA session.)
No user or application has to remember or hide a password.
It prevents everyone except a true DBA session from acquiring the READWRITE role.
app.chk_readwrite is called by users or applications when they want to enable the READONLY or READWRITE role.
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.
You want to run the UTLLOCKT script to detect lock contention. Which script must be run prior to running UTLLOCKT?
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.
What does the Character Set Scanner command line utility do?
Checks for any character definitions that will fail conversion to the new character set
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 listpartitioned table.
The keyword NULL can be specified as a partition literal value for one partition of a list-partitioned table.
What should you look at first to compute the number of undo blocks written per second to disk?
Before adding a nonstandard block size to your database, which preparatory task must you complete?
Define a new buffer pool by setting the DB_nK_CACHE_SIZE parameter where n is the new block size.
You word for a manufacturing company that frequently runs its production lines 24 hours a day. The accounting department is working with the development staff on plans to create an application to track manufacturing costs at the shop floor level. As a senior DBA, you have been assigned the task of ensuring that the new application will run with sub-second response time as information is entered into the application via terminals installed in the manufacturing area. Daily, weekly and monthly reports will also be run overnight detailing the previous period's activity. The reports must run during a time of high user interaction with the database. Choose two tasks that you should perform to make sure that the new application meets its sub-second response time requirement. (Choose two.)
Use performance-monitoring tools to gather information about the current workload on the system and again later while testing the application to identify potential bottlenecks within the database.
Help with the design of the tables used in the application to guarantee that every table is normalized to the third normal form for good relational design, and then de-normalize the tables as necessary to achieve the best performance of the application.
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 cursor opened while using an Oracle Flashback image to perform DML once you deactivate Oracle Flashback in your session.
What is the meaning of using "character semantics" to create a table?
You specify the width of a column in characters, not in bytes.
In the Oracle9i Data Guard architecture, what is the purpose of the Log Transport Services?
To transfer redo log information to one or more destinations
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.
Which two data types can be converted to LOBs using an ALTER TABLE... MODIFY command? (Choose two.)
Consider the following statement: SQL> EXECUTE DBMS _STATS.GATHER_SCHEMA_STATS (- 2> ownname => 'OE', - 3> estimate_percent => DBMS_STATS.AUTO_SAMPLE_SIZE, - 4> method opt => 'for all columns size AUTO'); What is the effect of 'for all columns size AUTO' of the METHOD_OPT option?
The Oracle server creates a histogram based on data distribution and application usage of the column/s for all table, column, and index statistics for the OE schema.
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 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
What are the two methods used to add a second, or subsequent, block size to an existing database? (Choose two.)
Re-create the control file to specify a new block size for specific data files
Take a tablespace offline, and then put it back online with a new block size specification.
Database Resource Manager uses resource plans to determine resource limits for the set of users. Which statement is true?
The database can have many resource plans, but only one can be active at any one time.
At a minimum, from what point should you leave the RESOURCE_MANAGER_PLAN initialization parameter set to an existing plan, in order to be able to issue the ALTER SYSTEM QUIESCE RESTRICTED command?
Since instance startup.
Consider this syntax: MERGE INTO tl USING t2 ON (join predicate)..... What does the MERGE syntax do?
For each row from T2, it updates the row if it exists within table T1, otherwise it inserts the row into T1.
Which three table transformations can be done by using online redefinition of tables? (Choose three.)
Rename a column
Change a heap table to an index-organized table
Change the data type of a column from DATE to CHAR
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 the command: ALTER DATABASE ADD SUPPLEMENTAL LOG DATA (PRIMARY KEY) COLUMNS; What does the command accomplish?
Stores the primary key column values of each row involved in a DML operation in the online redo log files
Examine the extract from a SQL*Plus session. SQL> SELECT partition_name, high_value, tablespace_name 2 FROM user_tab_partitions 3 WHERE table_name = 'LOCATIONS'; PARTITION_NAME HIGH_VALUE TABLES ----------- ------ ----- REGION_EAST 'MA', 'NY', 'CT', 'NH', NULL, 'MD', 'VA', 'PA', 'NJ' tbs1 REGION_WEST 'CA', 'AZ', 'NM', 'OR', 'WA', 'UT', 'NV', 'CO', tbs2 REGION_SOUTH 'TX', 'KY', 'TN', 'LA', 'MS', 'AR', 'AL', 'GA' tbs3 REGION_CENTRAL 'OH', 'ND', 'SD', 'MO', 'IL', 'MI', NULL, 'IA' tbs4 SQL> ALTER TABLE locations 2 SPLIT PARTITION region_east 3 VALUES('CT','MA','MD') 4 INTO 5 (PARTITION region_north_east TABLESPACE tbs2 6 ,PARTITION region_south_east STORAGE (NEXT 2M)); Assuming that there is at least one row for each value shown in the HIGH VALUE column, what is the result of the ALTER TABLE statement?
Rows with partitioning keys in the current REGION_EAST partition not included in the VALUES clause will be stored in the REGION_SOUTH_EAST partition.
Examine the statement: SQL> CREATE TABLESPACE user_ data 2> EXTENT MANAGEMENT LOCAL 3> SEGMENT SPACE MANAGEMENT AUTO; Which two assumptions must be true for this statement to execute successfully? (Choose two.)
Oracle Managed Files are used for this instance.
The COMPATIBLE initialization parameter must be 9.0.0 or higher.
You and several other DBAs are discussing the different areas in the SGA and debating the importance of tuning each area. The four statements below are made during your conversation. Which two are true? (Choose two.)
A cache miss in the data dictionary cache or the library cache is more expensive than a miss on the database buffer cache, so tuning the shared pool is a priority.
When using shared servers, you should either allocate additional space to the shared pool or set up the large pool to accommodate the User Global Area for the shared server users.
What are the steps the server process goes through when it needs to access a particular data block into the buffer cache?
The server process first checks to see if the block is already in the buffer cache using a hash function. If it is already in memory, the server process uses it. If the block is not in memory, the server process reads the data block into the buffer cache. Once it is there, the server process can access the data block.
The EMPLOYEES table is stored in the SAMPLE tablespace. The corresponding IPK_EMP index for the EMPLOYEES table's primary key is stored in the INDX tablespace. Out of 12 partitions, only partition P1 of the SALES table is stored in the SAMPLE tablespace. Which objects will be exported by this command? exp system/manager tablespaces=SAMPLE
The EMPLOYEES table, IPK_EMP index, and the SALES table
Which two procedures or functions are part of the DBMS_METADATA package? (Choose two.)
The buffer cache advisory is used for which two purposes? (Choose two.)
Provides a physical read factor for the keep buffer pool if it is in use in the SGA.
Advises DBAs on the optimal size of the default buffer cache for the current workload.
What is true about version-enabled tables?
It is possible to create workspaces if there is no version-enabled table in the database.
Oracle Enterprise Manager can generate database reports. Which three statements are true about this feature? (Choose three)
It generates HTML reports of database objects.
You can extend the reports with your own SQL selects.
It can be run stand-alone, without a repository or agent.
When enabling Flashback using a wall-clock time, within what period does the database choose a System Commit Number (SCN)?
Five minutes of the time specified
What are three benefits of performing data definition language (DDL) statements against a partitioned table with the UPDATE GLOBAL INDEXES clause? (Choose three.)
Global indexes are rebuilt automatically at the end of the DDL operation thereby avoiding problems with the UNUSABLE status.
You do not have to search for invalid global indexes after the DDL command completes and rebuild them individually
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.
You are attempting to create an Oracle-Managed Files (OMF) tablespace in a production database with the following statement and receive the following error message: CREATE TABLESPACE tbsl; ORA-02199: missing DATAFILE/TEMPFILE clause oerr ora 2199 02199, 00000, "missing DATAFILE/TEMPFILE clause" *Cause: A CREATE TABLESPACE statement has no DATAFILE/TEMPFTLE clause. *Action: Specify DATAFILE/TEMPFILE clause. What is the corrective action to create the OMF based tablespace?
Set the db_create_file_dest parameter using the ALTER SESSION command and re-issue the statement.
When does Oracle9i flag an index as being used in the V$OBJECT_USAGE view?
During a query's parse time only
When querying the new v$spparameter view, the column ISSPECIFIED displays the value TRUE for a number of parameters. What does this mean?
The parameters are specified in the SPFILE parameter file.
You are using Oracle Shared Server. If the large pool is not configured, which two are stored in the shared pool instead of in private user memory? (Choose two.)
user session information
cursor state information
Which operation causes an index to be considered "used" while monitoring is turned on for that particular index?
When a statement is parsed and the resulting execution plan contains a reference to the index
A switchover to a standby database differs from a failover to the standby in a number of ways. What are two of the key differences that characterize a switchover? (Choose two.)
The operation is planned and causes no data loss.
The old primary database can take on the standby role and be available for a future switchover.
What is required for the Character Set Scanner to operate?
A GUI capable workstation, because it uses a Java interface
You want to gather statistics for all objects in a schema, but you don't want to discard the current statistics. What is the most efficient method of completing this task with minimal impact on database downtime and performance?
Use the DBMS_STATS package to gather statistics while saving the current statistics, then restore the original statistics.
Examine the list of variables and their data types: Name Data Type TS, TS1 TIMESTAMP TSZ TIMESTAMP WITH TIME ZONE TLZ TIMESTAMP WITH LOCAL TIME ZONE IYM INTERVAL YEAR TO MONTH IDS INTERVAL DAY TO SECOND Which three expressions using the new date and time data types are valid? (Choose three.)
TS + IYM
IDS + IYM
During scheduled maintenance for a database that runs 24 hours a day, 7 days a week, the DBA wants to bring online a larger undo tablespace, smu_tbs2, while application users are performing DML. The current ONLINE undo tablespace is smu_tbsl . The DBA issues the following command: SQL>ALTER SYSTEM SET undo_tablespace = smu_tbs2; What happens?
All undo segments in the smu_tbsl tablespace will have v$rollstat.status = 'PENDING OFFLINE' until all existing transactions have been committed or rolled back. All new transactions will use smu_tbs2.
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