1Z0-020 - Oracle8i: New Features for Administrators
Go back to Oracle
What are autonomous transactions?
PL/SQL blocks that have a transaction scope independent of the transaction scope of the calling PL/SQL block.
What is one of the benefits of system-managed locally managed tablespace, defined with the AUTOALLOCATE option?
Neither the DBA nor the users have to concern themselves with storage parameters to size the extents.
Your standby database has been in read-only mode for the past five hours. During this time ten log switched have taken place on the production database. The DBA now wants to put the database into a sustained recovery mode. What happens to the archive logs?
They are applied to the standby database automatically when the DBA issues the ALTER DATABASE RECOVER MANAGED DATABASE command.
Which three functions can be improved through the use of transportable tablespaces? (Choose two)
Archive historical data.
Distribute relational data on a medium such as CD.
Which two statements are true for an online index rebuild? (Choose two)
No table locking is required.
Changes to the base table are logged in a journal table.
Which tablespace stores the segment of a temporary table?
The temporary tablespace for the current user.
What does the DBMS_OLAP.VALIDATE_DIMENSION procedure do?
Verifies that the columns in a dimension satisfy the one-to-many relationship in the hierarchy.
What must you do to enable partition-wise joins?
Nothing, because this feature is enabled automatically.
A stored outline uses which database feature when creating an execution plan for a SQL statement?
SQL statement hints.
You set the PARALLEL_ADAPTIVE_MULTI_USER initialization parameter to TRUE. What happens?
The requested degree of parallelism, specified on a table or through a hint, is reduced to match the current load.
Which three steps should you complete before attempting a manual upgrade from Oracle8, version 8.0, to Oracle *I, release 1 or 2? (Choose three)
Ensure you do not have a role or user called OUTLN in your database.
Make a backup.
Ensure you have free space in your SYSTEM tablespace.
Which command will open a standby database in read-only mode?
ALTER DATABASE OPEN READ ONLY;
What is found in the destination provided by the STANDBY_ARCHIVE_DEST parameter?
Archive logs copied automatically from the primary site.
Which statement about transportable tablespaces is true?
The name of the schema that owns the objects in the tablespace can be changed during transportation.
Which two statements are true? (Choose two)
Dimensions are based on summaries.
Additional query rewrites at a higher level of aggregation can take place if summaries are created at the lowest level of aggregation.
When you create a stored outline, in which schema is it stored?
Which tool, integrated with Oracle universal installer, can be used by third-party vendors to install their products as art of an Oracle installation?
Oracle Software Packager.
Which statement is true regarding composite partitions?
Partitions use the range method and subpartitions use the hash method.
Which two can be obtained from the summary advisor? (Choose two)
Summary space requirements.
Examine this command: ALTER SESSION SET USE_STORED_OUTLINE=TRUE; Which outline category will be used?
Examine this statement: SELECT deptno, job, SUM(sal) FROM emp Which GROUP BY clause would you use to generate a subtotal of the sum of salaries in each department, and a subtotal of the sum of salaries of all the different job types?
GROUP BY ROLLUP (deptno, job);
What does the optimizer plan stability feature use to maintain different stored outlines for the same SQL statement?
Temporary tables retain data until the ________
Transaction or session completes.
Examine this command: ALTER SYSTEM SET USESTORED_OUTLINE=TRUE; Which outline category will be used?
To avoid having the ampersand (&) treated as the substitution character in SQL*Plus which command(s) must you issue?
SET ESCAPE with the hyphen mark (-)
You want to create a Java stored procedure. You have written the Java program. What are two methods for loading the Java code into the Oracle Virtual Machine? (Choose two)
Use the LOADJAVA utility.
Use the JDEVELOPER LOAD JAVA option.
When does a SHUTDOWN event trigger fire?
Before any instance shutdown.
What can you see when querying V$LOGMNR_CONTENTS?
The old image and the new image of the row that was modified.
Which three statements about fine-grained access control are true? (Choose three)
The application context is required.
Security cannot be bypassed as it built into the database server.
Security can be built in the database server instead of each application.
Which three statements about transportable tablespaces are true? (Choose three)
The source and target databases must have the same block size.
The source and target databases must have the same character set.
The operating systems must be the same for the source and target databases.
A DBA now has the ability to mark certain blocks of a table as being corrupt. This will enable a full table scan to skip over them. Inorderto achieve this, which command should a DBA issue?
Run the DBVERIFY utility on the relevant datafile.
Given: LOG_ARCHIEVE_DEST_2" - "LOCATION = /archive2 OPTIONAL" What effect does OPTIONAL have in the location parameter?
If the destination is unavailable, it allows the online redo log to be overwritten.
What does fine-grained access enable you to do?
Associate policies with tables and view.
Why would you use the PL/SQL procedure DBMS_TTS.TRANSPORT_SET_CHECK?
To verify whether a set of tablespaces is self-contained.
Which parameter would you use to set up archiving to a remote machine?
Following an upgrade from Oracle8, release 8.0, to Oracle8i, release 1 or 2, you should run the script utlrp.sql to accomplish which goal?
Recompile any PL/SQL modules made invalid by the upgrade.
What happens during a complete refresh of a materialized view?
The materialized view is truncated or deleted and repopulated by re-executing the materialized view query.
Which two statements about dimensions are true? (Choose two)
A hierarchy can consist of multiple levels.
A dimension can be based on multiple tables.
Which two initialization parameters are used to create the default value for the SERVICE_NAMES initialization parameter when it is not specified? (Choose two)
Which feature of the Universal installer would help you install software in an environment with multiple databases on a single machine?
Multiple Oracle homes
Which action can be performed on a composite index partition?
Modifying the partition of a global index.
Which two statements about optimizer plan stability is true? (Choose two)
Data dictionary statistics are ignored when using a stored outline.
Categories allow different stored outlines for the same SQL statement.
Examine this execution plan: Execution Plan
<br>0SELECT STATEMENT Optimizer = CHOOSE
<br>32SORT (ORDER BY STOPKEY)
<br>43TABLE ACCESS (FULL) OF ’CLASSES’
<br><br>Which statement generated this execution plan?
SELECT COUNT (*) FROM classes ORDER BY stopkey;
What is the maximum number of archival destinations allowed in Oracle 8i?
What is one benefit of using the ALTER TABLE . . . . . . MOVE command rather than a CREATE TABLE . . . . . . AS SELECT command?
Index definitions are retained.
Which two statements are true for materialized views? (Choose two)
They can reduce the aggregation required for a query.
They can be created in a different tablespace than the base tables.
Which would you use to create a multiple databases with the same configuration?
Database configuration Assistant
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.
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 are two advantages of using locally managed tablespaces? (Choose two)
Extent management operations perform better.
The data dictionary is less prone to contention.