1.Your database is running under automatic undo management and the UNDO_RETENTION parameter is set to 900 sec.
You executed the following command to enable retention guarantee:
ALTER TABLESPACE undotbs1 RETENTION GUARANTEE
What efeect would this statement have on the database?
A - The extents in undo tablespace retain data until the next full database backup.
B - The extents containing committed data in the undo tablespace are never overwritten.
C - The extents which no longer contain uncommitted data in the undo tablespace
are not overwritten for at least 15 minutes.
D - The extents containing committed data in the undo tablespace are not overwritten
until the instance is shut down.

Comment:
After a transaction is commited,undo data is no longer needed for rollback or transaction
recovery pursoses.However,for consistent read purposes,long-running queries may queries may
require this old undo information for producing older iamges of data blocks.Furthermore,the
success of serveral Oracle Flashback feathures can also depend upon the availability of older
undo information.For these reasons,it is desirable to retain the old undo information for
as long as possible.

When automatic undo management is enabled,there is always a current undo retention period,
which is the minimum amount of time that Oracle Database attempts to retain old undo information
before overwriting it.Old(committed) undo information that is older than the current undo retention
period is said to be expired.Old undo information with an age that is less than the current
undo retention period is said to be unexpired.

Oracle Database automatically tunes the undo retention period based on undo tablespace size
and system activity. You can specify a minimum undo retention period (in seconds) by setting
the UNDO_RETENTION initialization parameter. The database makes its best effort to honor the
specified minimum undo retention period, provided that the undo tablespace has space available for new transactions.
When available space for new transactions becomes short, the database begins to overwrite expired undo.
If the undo tablespace has no space for new transactions after all expired undo is overwritten,
the database may begin overwriting unexpired undo information. If any of this overwritten undo
information is required for consistent read in a current long-running query, the query could fail
with the snapshot too old error message.

The following points explain the exact impact of the UNDO_RETENTION parameter on undo retention:
.The UNDO_RETENTION paramter is ignored for a fixed size undo tablespace.The tablespace may overwrite
unexpired undo information when tablespace space becomes low.
.For an undo tablespace with the AUTOEXTEND option enabled, the database attempts to honor the minimum
retention period specified by UNDO_RETENTION. When space is low, instead of overwriting unexpired undo
information, the tablespace auto-extends. If the MAXSIZE clause is specified for an auto-extending undo
tablespace, when the maximum size is reached, the database may begin to overwrite unexpired undo information.

Retention Guarantee
To guarantee the success of long-running queries or Oracle Flashback operations, you can enable retention
guarantee. If retention guarantee is enabled, the specified minimum undo retention is guaranteed; the database
never overwrites unexpired undo data even if it means that transactions fail due to lack of space in the undo tablespace.
If retention guarantee is not enabled, the database can overwrite unexpired undo when space is low, thus lowering
the undo retention for the system. This option is disabled by default.

WARNING:
Enabling retention guarantee can cause multiple DML operations to fail. Use with caution.
You enable retention guarantee by specifying the RETENTION GUARANTEE clause for the undo tablespace when you create
it with either the CREATE DATABASE or CREATE UNDO TABLESPACE statement. Or, you can later specify this clause in an
ALTER TABLESPACE statement. You disable retention guarantee with the RETENTION NOGUARANTEE clause.

You can use the DBA_TABLESPACES view to determine the retention guarantee setting for the undo tablespace.
A column named RETENTION contains a value of GUARANTEE, NOGUARANTEE, or NOT APPLY (used for tablespaces other than
the undo tablespace).
(Answer:C)

2.After being hired as a database administrator,you find that there is only one database
that is functional and that is being accessed by the applications.You want to create
a replicat of the database,to be used for testing purposes.What is the best method to
Create the replica?
A - create a database by using CREATE DATABASE command and manually copy the data
B - use DBCA to create a remplate from the existing database to contain the database structure.
C - use DBCA to create a template from the existing database to contain the database structure
and then manually copy the data using Oracle Data Pump
D - use DBCA to create a template from the existing database to contain the database structure
with data files and then use the same template to create the database in the new location
(Answer:D)

3.Your organization plans to dissolve the department with department ID 30.You excute the following
command to delete rows from the DEPARTMENTS table:

delete from DEPARTMENTS
where DEPT_ID = 30;

The command fails and displays the following error:

ERROR at line 1:
ORA-02292:integrity constraint(HR.SYS_C005374)violated - child record found
Which two actions would you take to overcome this error?(choose two)
A - after the foregn key constraint to include the cascade option
B - after the foreign key constraint to include the on delete cascade option
C - first,drop the EMPLOYEES table and then delete the rows from the DEPARTMENTS table
D - first,drop the DEPARTMENTS table and then delete the rows from the EMPLOYEES table
E - first,delete all of the rows from DMPLOYEES table and then delete the rows from
the DEPARTMENTS table for department id 30
F - first,delete rows from the EMPLOYEES table for department id 30 and then delete the
rows from DEPARTMENTS table for department id 30

(Answer:B,F)

4.In the PROD database you have granted the RESUMABLE system privilege to CONNECT role.
Resumable space operation has been enabled for all user session.You want users NOT to
be aware of any kind of space-related problems while performing transactions.Instead,
you want the problem to be resolved by a database trigger automatically.Which combination
of triggering time and event would you use to achieve this objective?
A - AFTER CREATE
B - AFTER SUSPEND
C - BEFORE CREATE
D - AFTER TRUNCATE
E - BEFORE SUSPEND
F - AFTER INSERT OR DELETE OR UPDATE
G - BEFORE INSERT OR DELETE OR UPDATE

(Answer:B)

5.User A executes the following command to update the TRANS table:
UPDATE B.trans SET tr_amt=tr_amt+500 WHERE c_code=’C005′;
Before user A issues a COMMIT or ROLLBACK command,user B executes
the following command on the TRANS table:

ALTER TABLE trans MODIFY(tr_type VARCHAR2(3));
What would happen in the scenario?
A - The transaction for user A is rolled back
B - The ALTER TABLE command modifies the column successfully.
C - The ALTER TABLE command fails due to the resource being busy.
D - The ALTER TABLE command waits until user A ends the transaction.
(Answer:C)

6.Your Oracle 10g database has 6 tablespaces in which:
-TEMP is the default temporary tablespace
-UNDO TBS1 is the default undo tablespace
-USERS is the default permanent tablespace

In this database,which three tablespaces can be made offline?(choose three)
A - TEMP
B - PROD
C - USERS
D - SYSAUX
E - SYSTEM
F - UNDOTBS1

(Answer:B,C,C)

7.You are using flat files as the data source for one of your data warehousing application.
You plan to move the data from the flat file structures to an Oracle database to optimize
the application performance.In your database you have clustered tables.While migrating
the data.you want to have minimal impact on the database performance and optimize the data
load operation.Which method would you use to load data into Oracle database?
A - use the external table population
B - use the Oracle Data Pump export and import utility
C - use the conventional path data load of SQL*Loader utility
D - use the direct path data load of Oracle export and import utility
(Answer:C)

8.The application development team has developed PL/SQL procdures and functions for different
purposes and calls them as and when required.The loading of individual procedures or function
into memroy degrades performance with every call.Also, it causes a security problem for
individual subprograms and loss of program units when the whole system is transported into a
new location.Which method would you recommend to the application develoers to solve this problem?
A - avoiding the use of cursors in the subprograms
B - using anonymous PL/SQL blocks instead of subprograms
C - referring to views instead of tables inside the subprograms
D - creating PL/SQL packages to include interrelated subprograms
(Answer:D)

9.Your test database is running in NOARCHIVELOG mode,What are the implications of this?
A - You can perform open database backups
B - You can perform closed database backups
C - You cannot perform schema-level logical backups
D - You can perform the backup of only the SYSTEM tablespace when the database is open
(Answer:B)

10.You have a text file that maintains information on thousands of items.The end_user
application requires the transfer of that information into a table in the database.
What would you use toachive this task?(choose two)
A - Oracle Text
B - Data Pump
C - SQL * Loader
D - Oracle Import
E - External table
(Answer:C,F)

Popularity: 38% [?]