1Z0-030 - Oracle9i New Features for Administrator

Go back to Oracle

Example Questions

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? What does the Metadata Application Programming Interface (API) allow you to do? What is true if you want to switch undo tablespaces from the current one, called UNDO1, to a new one called UNDO2? 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.) Why can setting too low a value for the FAST_START_MTTR_TARGET parameter reduce your overall database performance? 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? What does the Character Set Scanner command line utility do? Which two are true regarding a list-partitioned table? (Choose two.) 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? 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.) You can use Oracle Flashback to look at past activity in your database. What are two other characteristics of Oracle Flashback? (Choose two.) What is the meaning of using "character semantics" to create a table? In the Oracle9i Data Guard architecture, what is the purpose of the Log Transport Services? Which statement correctly describes the function of the Oracle9i Cache Fusion feature? 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 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.) What are the two methods used to add a second, or subsequent, block size to an existing database? (Choose two.) Database Resource Manager uses resource plans to determine resource limits for the set of users. Which statement is true? 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? Consider this syntax: MERGE INTO tl USING t2 ON (join predicate)..... What does the MERGE syntax do? Which three table transformations can be done by using online redefinition of tables? (Choose three.) Which three attributes of the LOG_ARCHIVE_DEST_n initialization parameter control the data availability mode of a Data Guard standby database? (Choose three.) Examine the command: ALTER DATABASE ADD SUPPLEMENTAL LOG DATA (PRIMARY KEY) COLUMNS; What does the command accomplish? 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? 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.) 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.) What are the steps the server process goes through when it needs to access a particular data block into the buffer cache? 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 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.) What is true about version-enabled tables? Oracle Enterprise Manager can generate database reports. Which three statements are true about this feature? (Choose three) When enabling Flashback using a wall-clock time, within what period does the database choose a System Commit Number (SCN)? What are three benefits of performing data definition language (DDL) statements against a partitioned table with the UPDATE GLOBAL INDEXES clause? (Choose three.) 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? When does Oracle9i flag an index as being used in the V$OBJECT_USAGE view? When querying the new v$spparameter view, the column ISSPECIFIED displays the value TRUE for a number of parameters. What does this mean? 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.) Which operation causes an index to be considered "used" while monitoring is turned on for that particular 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.) What is required for the Character Set Scanner to operate? 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? 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.) 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? You can control instance recovery performance by setting parameters to define targets for _______.