Oracle Blog

Tips for Taking the OCA Exam

2009-05-11T22:37:00.000-07:00

Tips for Taking the OCA Exam

Use the following tips to help you prepare for and pass the exam:

The exam contains about 55–80 questions to be completed in 90 minutes. Answer the questions you know first so that you do not run out of time.

At first glance, the answer choices to many questions look identical. Read the questions carefully. Do not jump to conclusions. Make sure that you clearly understand exactly what each question asks.

Some questions are scenario-based. Some scenarios contain nonessential information and exhibits. You need to be able to identify what’s important and what’s not important.

Do not leave any questions unanswered. There is no negative scoring. After selecting an answer, you can mark a difficult question or one that you’re unsure of and come back to

it later.

When answering questions that you are not sure about, use a process of elimination to get rid of the obviously incorrect answers first. Doing this greatly improves your odds if you need to make an educated guess.

If you’re not sure of your answer, mark it for review, and then look for other questions that might help you eliminate any incorrect answers. At the end of the test, you can go back and review the questions that you marked for review.

Oracle 11g Data Compression Tips for the DBA

2009-03-11T20:58:00.000-07:00

Oracle 11g Data Compression Tips for the DBA

One of the exciting new features of Oracle 11g is their inline data compression utility. While it is true that data storage prices have fallen dramatically over the last decade, and continue to fall rapidly, Oracle data compression has far more appealing benefits than simply saving on disk storage cost. Because indexes and the data itself can be highly compressed, information can be fetched off of the disk devices with less physical IO, which radically improves query performance under certain conditions.

Let's take a closer look at how one would implement Oracle 11g Data Compression in order to achieve the optimal results.

Understanding data compression

Data compression techniques, such as the Huffman algorithm, have been around for nearly a century, but only today are being put to use within main stream information systems processing. Using these techniques, a decompression utility is called immediately upon the data block fetch. Within the Oracle data buffers, the fully uncompressed version of the data remains in the data buffers, even though the information remains compressed on the data blocks themselves. This leads to an anomaly between the size of information on the data blocks and the size of the information within the data buffers. Upon applying Oracle data compression, people will find that far more rows will fit on a data block of a given size, but there is still no impact on the data base management system from the point of view of the SGA (system global area). Because the decompression routine is called upon block fetch, the Oracle data buffers remain largely unchanged while the data blocks themselves tend
to have a lot more data on them.

Tests show that 11g compression results in slower transaction throughput but creates less writes because of higher row density on the data block. Overall, the benchmark slows that I/O writes being reduced while CPU increases, resulting in slowing SQL throughput:

* Slower transaction throughput – As we expect, Oracle transactions run faster without the encryption/decryption processing overhead. This encryption benchmark shows significantly slower throughput when deploying TDE, almost 20% (81 transactions/second with TDE, 121 transactions/second with TDE).

* Less Disk Writes – Since transparent data encryption compresses the data, the benchmark with TDE required less disk writes.

* More CPU required - As we would expert, TDE required CPU cycles for the encrypt/decrypt operations, and in this benchmark test we see User CPU rise from 46 to 80 when using TDE data encryption.

See more details on data compression here: http://oracle-tips.c.topica.com/maamRxVabOkFObMnSnCb/

Top 10 Reasons for poor Oracle performance

2009-02-06T04:56:00.000-08:00

Top 10 Reasons for poor Oracle performance

What are the most common root-causes of poor Oracle performance? Every expert will give you a different opinion, but I’ve prepared my list based on our hundreds of Oracle tuning engagements:

Bad Design – The number one offender to poor performance is over-normalization of Oracle tables, excessive (unused indexes) and 15-way table joins for what should be a simple fetch.

Poor server optimization – Setting the server kernel parameters and I/O configuration (e.g. direct I/O) has a profound impact on Oracle performance

Bad disk I/O configuration – Inappropriate use of RAID5, disk channel bottlenecks and poor disk striping.

Poor Optimizer Statistics – Prior to Oracle 10g (automatic statistics), a common cause of poor SQL performance was missing/stale CBO statistics and missing histograms.

Object contention – Failing to set ASSM, freelists or freelist_groups for DML-active tables and indexes can cause very slow DML performance.

Under-allocated RAM regions – Not allocating enough RAM for shared_pool_size, pga_aggregate_target and db_cache_size can cause an I/O-bound database.

Non-reentrant SQL – All SQL should use host variables/cursor_sharing=force to make SQL reusable within the library cache.

Un-set initialization parameters – Many of the initialization parameters are made to be set by the DBA (db_file_multiblock_read_count, optimizer_index_caching) and failing to set these parameters properly results in poorly optimized execution plans.

Excessive nested loop joins – In 64-bit Oracle systems we have gigabytes available for RAM sorts and hash joins. Failing to set pga_aggregate_target to allow the CBO to choose hash joins can result in very slow SQL performance.

Human Misfeasance – The DBA’s failure to monitor their database (STATSPACK/AWR), set-up exception reporting alerts (OEM) and adjusting their instance to match changing workloads is a major cause of poor performance.

The Oracle Documentation lists these ten reasons for poor performance. The BC list is similar, but our top-10 list is based on what we see with our clients:

Bad Connection Management

The application connects and disconnects for each database interaction. This problem is common with stateless middleware in application servers. It has over two orders of magnitude impact on performance, and it is totally unscalable.

Bad Use of Cursors and the Shared Pool

Not using cursors results in repeated parses. If bind variables are not used, then there is hard parsing of all SQL statements. This has an order of magnitude impact in performance, and it is totally unscalable. Use cursors with bind variables that open the cursor and execute it many times. Be suspicious of applications generating dynamic SQL.

Getting Database I/O Wrong

Many sites lay out their databases poorly over the available disks. Other sites specify the number of disks incorrectly, because they configure disks by disk space and not I/O bandwidth.

Redo Log Setup Problems

Many sites run with too few redo logs that are too small. Small redo logs cause system checkpoints to continuously put a high load on the buffer cache and I/O system. If there are too few redo logs, then the archive cannot keep up, and the database will wait for the archive process to catch up.

Serialization of data blocks in the buffer cache due to lack of free lists, free list groups, transaction slots (INITRANS), or shortage of rollback segments.

This is particularly common on INSERT-heavy applications, in applications that have raised the block size to 8K or 16K, or in applications with large numbers of active users and few rollback segments.

Long Full Table Scans

Long full table scans for high-volume or interactive online operations could indicate poor transaction design, missing indexes, or poor SQL optimization. Long table scans, by nature, are I/O intensive and unscalable.

In Disk Sorting

In disk sorts for online operations could indicate poor transaction design, missing indexes, or poor SQL optimization. Disk sorts, by nature, are I/O-intensive and unscalable.

High Amounts of Recursive (SYS) SQL

Large amounts of recursive SQL executed by SYS could indicate space management activities, such as extent allocations, taking place. This is unscalable and impacts user response time. Recursive SQL executed under another user ID is probably SQL and PL/SQL, and this is not a problem.

Schema Errors and Optimizer Problems

In many cases, an application uses too many resources because the schema owning the tables has not been successfully migrated from the development environment or from an older implementation. Examples of this are missing indexes or incorrect statistics. These errors can lead to sub-optimal execution plans and poor interactive user performance. When migrating applications of known performance, export the schema statistics to maintain plan stability using the DBMS_STATS package.

Likewise, optimizer parameters set in the initialization parameter file can override proven optimal execution plans. For these reasons, schemas, schema statistics, and optimizer settings should be managed together as a group to ensure consistency of performance.

Use of Nonstandard Initialization Parameters

These might have been implemented based on poor advice or incorrect assumptions. In particular, parameters associated with SPIN_COUNT on latches and undocumented optimizer features can cause a great deal of problems that can require considerable investigation.

Top advice from Oracle experts in 2008

2009-01-14T04:31:00.000-08:00

Top advice from Oracle experts in 2008

Looking for tips and expert advice about Oracle jobs, backups, upgrades or errors? Take a look back at the most popular Oracle topics, questions and answers of 2008.

Explore what SearchOracle.com's expert panel had to say regarding the hottest Oracle issues of 2008. Find the answers to all your Oracle questions in this article:

http://oracle-tips.c.topica.com/maamIhJabNbzobMnSnCb/

Discover the best advice from Oracle experts in 2008, including insight on the following:

* What is the ORA-03113 error?
* How to get a history of modified data from Oracle databases
* How DDL and DML commands work in Oracle
* How to do a hot backup when Oracle Database is up
* When should an Oracle database be reorganized?
* Tips for how to size an Oracle server
* And much more!

Application Security in AJAX

2009-01-12T14:51:00.000-08:00

If you have evaluated AJAX (Asynchronous JavaScript and XML) for your next Web application development project, then you probably have read or heard a great deal about AJAX security concerns and the claim that AJAX increases the attack surface for hackers. If you are a skilled security developer, you might wonder whether the AJAX security problem originates in the technologies involved or whether lack of security in AJAX is a misconception. Security threats like SQL injection, cross-site scripting (XSS), message spoofing, and failed input validation existed before in Web applications and have been solved many times since then.

At first glance, it seems that the AJAX security discussion is a retelling of the tale of the emperor's new clothes. At second glance, however, it is obvious that there is a new component in AJAX security - the rich and interactive client. If this smart client really introduces a new security threat to Web applications, then the following questions arise: What can be done today, and what needs to be done in the future, to avoid ÒkillerÓ applications built with AJAX?

The Security Dilemma
Technology alone seldom is the problem. Lack of security in an application arises because of what developers do with the underlying technologies. To build secure Web applications - and this hasn't changed since traditional Web applications - there are two aspects of equal importance to be considered: humans and technology.

The most prevalent philosophy in application security is that security should not be added as an after thought, but should be included by design and default. The latter, however, never seems to happen in this feature-driven Internet technology industry, where new technologies are continuously being born. Two schools of thought exist in security: those who know everything and those who know next to nothing. It appears that because those who know everything are accustomed to handling the shortcomings of a given technology themselves, by devising workarounds or by using third-party security frameworks, it is up to those who know next to nothing to standardize security, making it a reachable goal for everyone.

AJAX is another example of putting security last and features first. AJAX is not a new technology. Instead, it consists of existing technologies such as JavaScript, Cascading Style Sheets (CSS), and Extensible Markup Language (XML) to implement Web 2.0 user interfaces. The technology used for dynamics in AJAX Web-user interfaces is JavaScript. This means, however, that the available Java security features, like the JavaScript sandbox and the same origin policy, are the main security features available in AJAX.

• Same Origin Policy: The same origin policy prevents scripts that are downloaded from a Website to access properties on a page that is downloaded from another Website. The security of the same origin policy, which ensures that malicious scripts do not hijack other loaded documents or spy on user cookies or key inputs, conflicts with another Web 2.0 wanted functionality: mashup. A mashup is an application page that consumes mixed services to build a composite Web-user interface. This type of application may need to interoperate between page fragments, even if it is downloaded from different servers and domains. Within the AJAX community and the World Wide Web Consortium (W3C), a desire exists to loosen the same origin policy limitation for XMLHttpRequest object (XHR) requests, which, from a security perspective, would require trusted clients that do not exist today.
• JavaScript Sandbox: JavaScript is contained in the browser execution environment and is not allowed to access either the client file system or the network, except through Hypertext Transfer Protocol (HTTP) requests. All that JavaScript has access to is the memory representation of the displayed browser document, called the document object model (DOM).

In JavaScript, little can be hidden from would-be hackers because all facets within a page are accessible and modifiable in the DOM tree. Exposing JavaScript source on the client, where it can be read or stolen, is not a security problem. If it were, open source software, which does not hide its implementation from viewers, would pose a huge security threat.

Client-side sources are problematic because everything is accessible in the DOM, which means that nothing can be protected on the client. Any security policy that is downloaded and enforced on the client can be read and manipulated. Obscurity is not a substitute for security. In fact, obfuscated JavaScript only helps to lock out wannabe hackers and is otherwise primarily used to increase JavaScript performance through reduced content lengths.

Security in AJAX

2009-01-12T14:49:00.000-08:00

Unlike traditional Web applications that have a more or less static user interface, AJAX applications have an active client that uses the browser's native XHR to fetch data from a server. There are two major risks to the browser regarding client-side JavaScript: browser bombing and cross-site scripting attacks:
• Browser bombing is the client version of a denial of service (DoS) attack. During this type of attack, the client is kept busy with JavaScript processing, such as running endless loops that fill up an array with content. When the client consumes all computing resources, the desktop hangs.
• Cross-site scripting has two facets. The first one, which was already mentioned, is where a script downloaded from one domain tries to access properties of a page downloaded from another domain. The second facet is where developers fail to validate user input, resulting in a JavaScript that is executed when the user-added input is rendered on a Web page. Imagine a discussion forum that does not encode JavaScript content or check for SQL keywords. A hacker could, for instance, add JavaScript that performs a runtime attachment of an image tag to the page in which the src attribute references a uniform resource locator (URL) on the hacker's server. The script could, for example, append the client's cookies as a request parameter. The application user does not recognize these changes because the HTML does not flag a missing image as an error.

While client-side security protects the end user from the application, application security protects the application from the user. Protection includes enforcement of authentication, authorization, and data privacy.

Though XSS and SQL injection attacks can be handled in AJAX on the client, you should not miss the opportunity to enforce the same policy on the back end in an additional layer of defense. In traditional Web applications, request filters implemented on the HTTP server (or in the application configuration) performed pattern searches using such things as Regular Expressions on the incoming request to detect technology keywords of JavaScript and SQL. In addition, filters were used to replace special characters with their encoded equivalent, such as when replacing <>

Security design patterns are recommendations of best practices to mitigate the risk of an identified threat. Patterns that exist for Web applications include defense in depth, limited view, least privileged access, checkpoint, and roles.

In addition to best security practices, there exists a sensitive balance between usability, performance, and security that needs to be considered when building AJAX applications. It is easy to risk vulnerabilities simply by annoying end users with too many security-related interruptions when they are working in an application. Such users soon turn into hackers on their mission to find a more convenient way to work with an application. AJAX applications are based on client-side JavaScript and only provide a minimum capability to maintain client-state in cookies and page variables. Unless the AJAX application is built on top of a server-side framework that manages the application state, AJAX applications risk losing state upon page reload and navigation, adding a need to re-request user security credentials.

Oracle & PHP - IMPROVING PERFORMANCE WITH STORED PROCEDURES

2009-01-12T14:48:00.000-08:00

PL/SQL is Oracle’s procedural language extension to SQL. It is a server-side, stored procedural language that is easy-to-use. PL/SQL enables you to mix SQL statements with procedural constructs. PHP can call PL/SQL blocks to make use of advanced database functionality, and can use it to efficiently insert and fetch data. Using PL/SQL gives your application access to Oracle’s better date and number handling, for example to make sure your financial data is not affected by PHP’s floating-point semantics.
PL/SQL is stored and executed in the database, so functions and stored procedures can be reused by other applications. This enables you to store the application logic in the database and reduce network round trips. The database does the hard work, and not the application.
PL/SQL enables autonomous transactions, which are effectively sub-transactions. An autonomous transaction can be committed or rolled back without affecting the main transaction. This might be useful for logging data access. An audit record could be created and inserted into an auditing table, even if the user decides to rollback their main change.
To create a simple PL/SQL procedure in PHP to insert a row into a table

$c = oci_connect('hr', 'hrpwd', '//localhost/ORCL');
$plsql = "create or replace procedure "
. "myprocedure(d_p in varchar2, i_p in number) as "
. "begin "
. "insert into mytable (mydata, myid) values (d_p, i_p);"
. "end;"; $s = oci_parse($c, $plsql); $r = oci_execute($s);
if ($r) {
echo 'Procedure created';

}

To call this procedure in PHP
$c = oci_connect('hr', 'hrpwd', '//localhost/ORCL');
$s = oci_parse($c, "call myprocedure('Name', 123)");
oci_execute($s);
Oracle Database 11g delivers dramatic performance improvements with new native compilation of PL/SQL. Native compilation is easily enabled by setting a single database parameter.

Oracle & PHP - FASTER STATEMENT EXECUTION USING BIND VARIABLES

2009-01-12T14:45:00.000-08:00

Oracle bind variables enable an application to repeatedly execute a prepared database statement with different values. Bind variables reduce the need for Oracle to reparse a statement.
$c = oci_connect('hr', 'hrpwd', '//localhost/ORCL');
$s = oci_parse($c, "select last_name from employees where employee_id = :eidbv");
$myeid = 101;
oci_bind_by_name($s, ":EIDBV", $myeid);
oci_execute($s);
oci_fetch_all($s, $res);
echo "Last name is: ". $res['LAST_NAME'][0] ."
\n";
$myeid = 102; oci_execute($s); // No need to re-parse
oci_fetch_all($s, $res);
echo "Last name is: ". $res['LAST_NAME'][0] ."
\n";

Bind variables improve the security of PHP applications as they assist in preventing SQL injection attacks.
Using PL/SQL, together with bind variables can dramatically improve performance of PHP applications by passing the processing to the database, instead of PHP.
The example code shows calling the myfunction PL/SQL function to return a data value using the bind variable :ret
$c = oci_connect('hr', 'hrpwd', '//localhost/ORCL');
$s = oci_parse($c, "begin :ret := myfunction(123); end;");
oci_bind_by_name($s, ':ret', $r, 20);
oci_execute($s);
echo "Name is: ".$r;

Oracle Database Express Edition 10g Release 2 (10.2) - Documentation Library

2009-01-11T22:45:00.001-08:00

Oracle Database 10g Express Edition (Oracle Database XE) is a free version of the world's most capable relational database. Oracle Database XE is easy to install, easy to manage, and easy to develop with.

With Oracle Database XE, you use an intuitive, browser-based interface, to:

Administer the database
Create tables, views, and other database objects
Import, export, and view table data
Run queries and SQL scripts
Generate reports

Installing the Database and Getting Started

Installation Guide for Linux	HTML	PDF
Installation Guide for Microsoft Windows	HTML	PDF
Getting Started Guide	HTML	PDF
Certification Notes for Microsoft Windows Vista	HTML	PDF
Upgrade Guide	HTML	PDF

Administering the Database

2 Day DBA	HTML	PDF
2 Day Developer Guide	HTML	PDF

Developing Applications

2 Day Developer Guide	HTML	PDF
2 Day Plus Application Express Developer's Guide	HTML	PDF
2 Day Plus Java Developer Guide	HTML	PDF
2 Day Plus .NET Developer Guide	HTML	PDF
2 Day Plus Locator Developer Guide	HTML	PDF
2 Day Plus PHP Developer Guide	HTML	PDF
Application Express User's Guide	HTML	PDF

Licensing Information

HTML

PDF

Master Lists

Use the Master Index to see all of the index entries for the library.

Related Information

You can find more information by visiting the Database Express Edition Forums or the Product page.

source : oracle.com

How to Create a Parameterized Report

2009-01-11T22:44:00.001-08:00

How to Create a Parameterized Report

In an Oracle Application Express application, a report is the formatted result of a SQL query. You can generate reports in three ways:

Running a built-in wizard
Defining a report region based on a SQL query
Creating a report region based on a PL/SQL function returning a SQL query

This tutorial illustrates how to create a report in which the results depend on the form input, or a parameterized report. In this exercise, you create a report region based on a SQL query which references the value of a form item within the application.

This section contains the following topics:

Sample Report Utilizing a Form Input

Figure 2-1 is an example of a form in which the report results are based on user input. In this example, this user populates the form by making a selection from the Show list. The easiest way to create this type of report in Application Builder is to define a report region based on a SQL query.

Figure 2-1 Sample Report

Description of "Figure 2-1 Sample Report"

About Sample Application

Application Builder installs with a number of demonstration applications. In this exercise, you create a new page within the demonstration application, Sample Application.

To see if Sample Application is installed:

Log in to the Database Home Page using your database username and password.
Click the down arrow on the right side of the Application Builder icon.
From the menu, select Demonstrations.
The Demonstration Applications page appears, displaying links to demonstration applications.
Locate Sample Application and check the Status column:
1. If the Status column displays Installed, return to the Database Home Page.
2. If the Status column displays Not Installed, select Install in the Action column. Follow the on-screen instructions.

Creating a New Page

First, you create a new blank page and within Sample Application.

To create a new page:

On Database Home Page, click the Application Builder icon.
Select Sample Application.
Click the Create Page button.
For Page, select Blank Page and click Next.
For Page, enter 700 and click Next.
In Name, enter Ordered Products and click Next.
For Tabs, accept the default, No, and click Next.
Review your selections and click Finish.
On the Success Page, click the Edit Page icon.
The Page Definition for page 700 appears.

Creating the Query Region

Next, you need to create a report.

To create a query region to contain the report:

Under Regions, click the Create icon as shown in Figure 2-2.
Figure 2-2 Create Icon

Description of "Figure 2-2 Create Icon"
Select Report and click Next.
For Report Implementation, select SQL Report and click Next.
For Display Region Attributes:
1. For Title, enter Ordered Products.
2. Accept the remaining default values and click Next.

Enter following SQL query:

SELECT p.category,
     p.product_name,
     i.quantity  FROM demo_product_info p,
     demo_order_items i
WHERE p.product_id = i.product_id
AND ( p.category = :P700_SHOW or :P700_SHOW = 'ALL' )

Click Create Region.

Adding an Item

The previous SQL query references an item named P700_SHOW. Next, you need to create this item. An item is part of an HTML form. An item can be a text field, text area, password, select list, check box, and so on.

Adding a Button to Submit the Page

For the report to be driven by the Product Category select list (the form input), you need to submit the page. To accomplish this, you need add a button.

To add a button to submit the page:

Under Buttons, click the Create icon.
For Button Region, select Ordered Products and click Next.
For Button Position, select Create a button displayed among this region's items and click Next.
In Button Name, enter P700_GO.
Accept the remaining defaults and click Create Button.
The Page Definition for page 700 appears.

Run the Page

To run the page:.

Click the Run Page icon in the upper right corner as shown in Figure 2-4.
Figure 2-4 Run Page Icon

Description of "Figure 2-4 Run Page Icon"
If prompted to enter a username and password:
1. For User Name, enter either demo or admin.
2. For Password, enter your database username in lowercase letters.
3. Click Login.
When the Order Products page appears, select Computer from the Show menu and click Go.

As shown in Figure 2-5, notice that making a selection from the Show menu populates the form.

Figure 2-5 Form Results Being Populated from a Select List

Description of "Figure 2-5 Form Results Being Populated from a Select List"

Orecle Database 10g: New Features for Administrators - 1Z0-040 #8

2009-01-11T22:41:00.000-08:00

QUESTION 141:
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, IDS1 INTERVAL DAY TO SECOND
Which three expressions using the new date and time data types are valid?
A. IDS*2
B. TS + IYM
C. TS - TS1
D. IDS -TS
E. IDS +IYM
Answer: A,B,D

QUESTION 142:
Which statement regarding the COMPATIABLE parameter is correct?
A. It is dynamic parameter.
B. It is an advanced parameter
C. It is a new parameter in Oracle 10G
D. It can have any value between 8:0:0 and 10.1.0 in Oracle 10G database
E. After is set to 10.0.0 or greater and the database is opened, it cannot be set back.
Answer: E
The default value is 10.2.0. The minimum value is 9.2.0. If you create an Oracle
Database using the default value, you can immediately use all the new features in this
release, and you can never downgrade the database.
REF.: Oracle(r) 10g Administrator Guide , 2-34

QUESTION 143:
You have a materialized view called emp_mv on the emp table.
You want to stop following query from executing if it does not rewrite:
SELECT deptno,sum (sal) FROM emp GROUP BY deptno;
Which statement should you use?
A. SELECT /*+USE_CONTACT*/deptno,sum(sal) FROM emp GROUP BY deptno;
B. SELECT /*+NO_EXPAND*/deptno,sum(sal) FROM emp GROUP BY deptno;
C. SELECT /*+NO_REWRITE*/deptno,sum(sal) FROM emp GROUP BY deptno;
D. SELECT / *+REWRITE_OR_ERROR */deptno,sum(sal) FROM emp GROUP BY
deptno;
Answer: D
REWRITE_OR_ERROR Hint
There may be situations where you want to stop the query from executing if it did not
rewrite . One such situation can be when you expect the un-rewritten query to take an
unacceptably long time to execute. To support this requirement, Oracle Database 10g
provides a new hint called REWRITE_OR_ERROR. This is a query block-level hint. For
example, if the SELECT statement is not rewritten, the error displayed in the message is
thrown. The REWRITE_OR_ERROR hint allows you to run
DBMS_MVIEW.EXPLAIN_REWRITE() on the query, resolve the problems that caused
rewrite to fail, and run the query again

QUESTION 144:
Which two procedures or functions are part of the DBMS_METADATA package?
A. GET_DDL
B. GET_XML
C. GET_TYPE
D. GET_VIEW
E. GET_TABLE
Answer: A,B
Procedures in DBMS_METADATA that use Object Types
==================================================
DBMS_METADATA.OPEN
DBMS_METADATA.GET_XML
DBMS_METADATA.GET_DDL
DBMS_METADATA.GET_DEPENDENT_XML
DBMS_METADATA.GET_DEPENDENT_DDL
DBMS_METADATA.GET_GRANTED_XML
DBMS_METADATA.GET_GRANTED_DDL
REF.: Metalink Note : 207859.1- Object types for DBMS_METADATA

QUESTION 145:
Which three are true for a version-enabled table? (choose three)
A. must have a primary key
B. can be owned by the SYS user
C. triggers that are not supported for version-enabled tables are deactivated when
versioning is enabled
D. a child table cannot be version-enabled without the parent table being version-enabled
E. can be enabled or disabled by the table owner only
Answer: A,C,D
A - True. Must have a PK. The table that is being version-enabled must have a primary
key defined. The primarykey can be a composite (multicolumn) primary key.
B - False. Tables owned by SYS CAN'T be version-enabled.
C - True . Any triggers that are not supported for version-enabled tables are deactivated
when versioning is enabled, and are activated when versioning is disabled.
D - True - a workspace can be a parent to one or more workspaces (child workspaces).
By default, when a
workspace is created, it is created from the topmost, or LIVE, database workspace.
E - False - The owner of a table or a user with the WM_ADMIN_ROLE role can disable
versioning on the table.
REF.: Oracle(r) Application Developer's Guide - Workspace Manager 10g Release 2
(10.2), 4-40 and 4-44

QUESTION 146:
The database was started up using a text parameter file.
What will be default scope of changes made by using the SET clause of the ALTER
SYSTEM statement?
A. Only MEMORY is updated.
B. Only SPFILE values are updated.
C. Only init.ora parmeters are updated.
D. Both SPFILE and MEMORY memoryvalues are updated.
Answer: A
The ALTER SYSTEM statement lets you set, change, or restore to default the values of
initialization parameter. If you are using a text initialization parameter file, the ALTER
SYSTEM statement changes the value of a parameter only for the current instance,
because there is no mechanism for automatically updating initialization parameters on
disk. You must update them manually to be passed to a future instance. Using a server
parameter file overcomes this limitation.
REF.: Oracle(r) 10g Administrator Guide, 2-38

QUESTION 147:
What should you look at first to computer the number of undo blocks that are
consumed and estimate the size of the undo tablespace needed to handle the
workload on your system?
A. V$UNDOSTAT
B. V$ROLLSTAT
C. V$TRANSACTION
D. DBA_UNDO_EXTENTS
E. DBA_ROLLBACK_SEGS
Answer: A
V$UNDOSTAT Contains statistics for monitoring and tuning undo space. Use this view
to help estimate the amount of undo space required for the current workload. The
database also uses this information to help tune undo usage in the system. This view is
meaningful only in automatic undo management mode.
REF.: Oracle(r) 10g Administrator Guide, 10-10

QUESTION 148:
Character large object (CLOB) data is represented in Oracle database log as
the_____character set.
A. UCS2
B. US7ASCII
C. AL16UTF16
D. WE81SO8859P1
E. D7SIEMENS9780X
F. Same character set as the databes charactr set
Answer: C

QUESTION 149:
You enable Flashback Database with the following command:
ALTER DATABASE FLASHBACK ON;
Which view would you query to determine whether the Flashback Database has
been enabled?
A. V$SGA
B. V$DATABASE
C. V$INSTANCE
D. V$FLASHBACK_DATABASE_LOG
E. V$FLASHBACK_DATABASE_STAT
F. V$FLASHBACK_DATABASE_LOGFILE
Answer: B
SQL>DESC V$DATABASE;
Name Type
----------------------------------------- -------------
FLASHBACK_ON VARCHAR2(18)
SQL>select FLASHBACK_ON from V$DATABASE;
FLASHBACK_ON
------------------
YES

QUESTION 150:
Examine the following parameter settings from an initialization (init.oraa)file:
DB_CREATE_FILE_DEST = '/u01/oradata/'
DB_CREATE_ONLINE_LOGDEST 1 = '/u02/oradata/'
DB_CREATE_ONLINE_LOG_DEST2 = '/u03/oradata/'
If you create an Oracle Managed Files (OMF) database using these settings, what is
the result?
A. The data files, temp files, and control file will be on device/u01; one online redo log
group will be on device /u02; another redo log group will be on device/u03.
B. The data files and temp files will be on device /u02; another copy of the control file and
one online redo log group will be on device /u02; another copy of the control file and a
second redo log group will be on device /u03.
C. The data filesm temp files, online redo log files and control file will be on device/u01;
multiplexed copies of the archive log files will be created; one set on device /u02, and
another set on device /u03.
D. 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.
Answer: D
a - DB_CREATE_FILE_DEST sets the default location for datafile, control file, and
online log creation.
* Datafiles
* Tempfiles
* Redo log files
* Control files
* Block change tracking files
You specify the name of a file system directory that becomes the default location for the
creation of the operating system files for these entities.
b - DB_CREATE_ONLINE_LOG_DEST_n initialization parameter in your initialization
parameter identify the default location for the database server to create:
* Redo log files
* Control files
You specify the name of a file system directory that becomes the default location for the
creation of the operating system files for these entities. You can specify up to five
multiplexed locations.
For the creation of redo log files and control files only , this parameter overrides any
default location specified in the DB_CREATE_FILE_DEST and
DB_RECOVERY_FILE_DEST initialization parameters. If you do not specify a
DB_CREATE_FILE_DEST parameter,but you do specify the
DB_CREATE_ONLINE_LOG_DEST_ n parameter, then only redo log files and control
files can be created as Oracle-managed files.
REF.: Oracle(r) 10g Administrator Guide, 11-5

QUESTION 151:
The Oracle 10G LogMiner needs a data dictionary copy to display the object
names.
From which three locations can LogMiner retrieve the data dictionary information?
A. separate supplemental log file.
B. Information in the control file
C. The current online data dictionary
D. A Data Dictionary copy stored in the redo log file
E. A Data Dictionary copy stored in an operating system flat file
Answer: B, C, D
Explanation:
LogMiner gives you following three options for supplying the dictionary
1.2.2.1 Using the Online Catalog
1.2.2.2 Extracting a LogMiner Dictionary to the Redo Log Files
1.2.2.3 Extracting the LogMiner Dictionary to a Flat File

QUESTION 152:
Examine this startup script called startmydb.sql:
CONNECT myid/mypwd AS SYSDBA
STARTUP
EXIT
In Oracle 8i, you could have executed this script by invoking server manager with
the command:
Svrmgr1 @startmydb.sql
Which alternative command could you use to run the startmydb.sql script with
SQL*Plus to start your Oracle 10g database?
A. sqlplus @startmydb.sql
B. sqlplus -Sstartmydb.sql
C. sqlplus -S @startmydb.sql
D. sqlplus /NOLOG @startmydb.sql
Answer: D

QUESTION 153:
Oracle 10g support block media recovery. This reduces the smallest until of
recovery from a data file to a database block. What are the two main benefits that
block media recovery provides over file-level recovery? (choose two)
A. supports incomplete recovery
B. does not require Recovery Manager
C. lowers the mean time to recover
D. allows increased data availability during media recovery
Answer: C,D
If a small number of blocks within one or more datafiles are corrupt, you can perform
block media recovery instead of restoring the datafiles from backup and performing
complete media recovery of those files. The Recovery Manager BLOCKRECOVER
command can be used to restore and recover specified data blocks while the database is
open and the corrupted datafile is online.Block media recovery may be better than
datafile media recovery if availability is critical. While block media recovery is possible
even if you do not base your backup and recovery strategy on RMAN, RMAN-based
block media recovery can be performed more quickly and with less effort .
REF.: Oracle(r) Backup and Recovery Advanced User's Guide, 2-2 and 2-5

QUESTION 154:
After changing the PCTFREE value of the automatic space management table
EMPLOYEES, which task can you perform in order for your change to take effect
immediately?
A. Execute a full table scan of the EMPLOYEES table
B. Execute thecommand ANALYZE TABLE employees COMPUTE STATISTICS
C. Execute the command DBNS_STATS.GATHER_TABLE_STATISTICS on the
EMPLOYEES table.
D. Execute the DBMS_REPAIR.SEGMENT_FIX_STATUS procedure on the
EMPLOYEES table.
Answer: D

QUESTION 155:
What is true regarding a shared, server-side parameter file for a Real Application
Cluster database?
A. It can contain parameters with distinct values for each instance
B. It can contain only parameters with identical values for each instance
C. It must contain an IFILE parameter for each instance's individual parameters file.
D. It must be located in the default location for the primary instance's parameter file.
Answer: A

QUESTION 156:
What are three benefits provided by the enhanced Unicode support? (choose three)
A. support for the AL24UTFFSS character set
B. support for fixed-width character encoding
C. Unicode support in the database character set
D. Richer support for more character sets languages and territories
Answer: B,C,D
Globalization and Unicode support have been enhanced in Oracle Database 10 g Release
2. Consistent linguistic ordering support has been added for all SQL functions, enhancing
the ability to write internationalized applications without regard to locale. The
Globalization Development Kit (GDK) for PL/SQL provides new locale mapping tables
and Japanese transliteration support. SQL Regular Expression support has been updated
to support key Perl conventions used in the Life Science community. This release also
supports the latest version of Unicode 4.0. Identification of potential problems when
migrating to another database character set has also been enhanced. The Character Set
Scanner (CSSCAN) now offers selective scanning and provides support for new data
types. The Language and Character Set Scanner (LCSSCAN) has improved detection
quality of shorter text strings and now supports HTML files.
REF.: Oracle(r) Database New Features Guide, 1-2

QUESTION 157:
Online table redefinition includes which three features?
A. Tables without primary keys are supported
B. Parallel support can be added or removed
C. Storage parameters for the table can be modified.
D. The redefinition can be done across different schemas.
E. User-defined data types BFILES or LONG columns are supported
F. Organization can be changed from heap-organized to IOT structure or vice versa
Answer: B,C,F
Online table redefinition enables you to:
? Modify the storage parameters of a table or cluster (C)
? Move a table or cluster to a different tablespace in the same schema
? Add, modify, or drop one or more columns in a table or cluster
? Add or drop partitioning support (non-clustered tables only)
? Change partition structure
? Change physical properties of a single table partition, including moving it to a different
tablespace in the same schema
? Change physical properties of a materialized view log or an Oracle Streams Advanced
Queueing queue table
? Add support for parallel queries (B)
? Re-create a table or cluster to reduce fragmentation
? Change the organization of a normal table (heap organized) to an index-organized
table, or do the reverse .(F)
? Convert a relational table into a table with object columns, or do the reverse.
? Convert an object table into a relational table or a table with object columns, or do the
reverse.
REF.: Oracle(r) Database New Features Guide, 15-22

QUESTION 158:
View the Exhibit and examine the Data Pump architecture.

Identify the numbered Components.
A. 1-Oracle Loader, 2-Oracle Data Pump, 3-Direct Path API
B. 1-Oracle Data Pump, 2-Direct Path API 3-Oracle Loader
C. 1-Direct Path API 2-Oracle Loader,3-Oracle data pump
D. 1-Oracle Loader, 2-Direct Path API, 3-Oracle Data Pump
E. 1-Oracle Data Pump, 2-Oracle Loader 3-Direct Path API
Answer: A

QUESTION 159:
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;
Oracle SQL 1999syntax supports the same functionality with which join type?
A. Equijoin
B. Cross join
C. Merge join
D. Natural join
Answer: B

QUESTION 160:
Which data dictionary view would you query to find the name of the default
permanent tablespace of the database?
A. DICTIONARY
B. DBA_TABLESPACS
C. DBA_DIRECTORIES
D. DBA_STORED_SETTINGS
E. DATABASE_PROPERTIES
F. DBA_TABLESPAC_GROUPS
Answer: E
SQL*Plus: Release 10.2.0.1.0 - Production onMon Feb6 21:41:04 2006
Copyright (c) 1982, 2005, Oracle. All rights reserved.
SQL> conn / as sysdba
Connected.
SQL> desc DATABASE_PROPERTIES
Name Null? Type
----------------------------------------- -------- --------------------
PROPERTY_NAME NOT NULL VARCHAR2(30)
PROPERTY_VALUE VARCHAR2(4000)
DESCRIPTION VARCHAR2(4000)
SQL> SELECT PROPERTY_NAME, PROPERTY_VALUE
2 FROM DATABASE_PROPERTIES
3 WHERE PROPERTY_NAME = 'DEFAULT_PERMANENT_TABLESPACE';
PROPERTY_NAME PROPERTY_VALUE
---------- ----------------------------
DEFAULT_PERMANENT_TABLESPACE USERS

Orecle Database 10g: New Features for Administrators - 1Z0-040 #9

2009-01-11T22:36:00.000-08:00

QUESTION 161:
The list below display four step that you need to execute in order to switch from the
primary database role to the standby database role.
Choose the correct order of execution for the steps;
1.Shut down and start up the former primary instance without mounting the
database.
2.Issue the ALTER DATABASE COMMIT TO SWITCHOVER TO PHYSICAL
STANDBY command.
3.Issue the ALTER DATABASE MOUNT STANDBY DATABASE comman.
4.End read or update activity on the former primary and standby database.
A. 1,2,3,4
B. 3,2,4,1
C. 4,2,1,3
D. 4,3,2,1
Answer: C
Stop read or update activity on the primary and standby databases.
You must have exclusive database access before beginning a switchover. Ask users to log
off the primary and standby databases, or query the V$SESSION view to identify users
that are connected to the databases and close all open sessions except the SQL*Plus
session from which you are going to execute the switchover statement.
Switch the primary database to the physical standby role.
On the primary database (in New York), execute the following statement:
SQL> ALTER DATABASE COMMIT TO SWITCHOVER TO PHYSICAL STANDBY
2> WITH SESSION SHUTDOWN;
This statement does the following:
? Closes the primary database, terminating any active sessions
? Transmits any unarchived redo log files and applies them to the standby database
? Adds an end-of-redo marker to the header of the last log file being archived
? Creates a backup of the current control file
? Converts the current control file into a standby control file
Shut down and start up the former primary instance, and mount the database.
Execute the following statement on the former primary database:
SQL> SHUTDOWN NORMAL;
SQL> STARTUP MOUNT;
Switch the original standby database to the primary role.
Issue the following SQL statement:
SQL> ALTER DATABASE COMMIT TO SWITCHOVER TO PRIMARY
DATABASE;
REF.: Oracle(r) Data Guard Concepts and Administration 10g Release 2 (10.2), 12-39

QUESTION 162:
You decided 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________
A. to wait indefinitely for the quota to be increased, regardless of the timeout setting for
other space allocation problem
B. to fail because quota limits do not activate the resumable space allocation feature
C. to wait in a suspended state until the quota increased or until the timeout value is
reached
D. to invoke the default trigger that resets user quota, if there is free space in the related
tablespace
Answer: C

QUESTION 163:
Which statements are true regarding the new Persistent CONFIGURATION
FEATURE IN RMAN? (Choose two)
A. It allows you to set the retention period of backups.
B. It remember the last backup command you used so that you can rescheule it easily.
C. It enables you to store your channel attribute settings in the Recovery catalog with
each script.
D. It enables you to store the settings for channel attributes so that you do not have to
specify them in each backup o restore command
Answer: A,D
When implementing an RMAN-based backup strategy, you can use RMAN more
effectively if you understand the more common options available to you. Many of these
can be set in the RMAN environment on a persistent basis, so that you do not have to
specify the same options every time you issue a command.To simplify ongoing use of
RMAN for backup and recovery, the RMAN lets you set a number of persistent
configuration settings for each target database. These settings control many aspects of
RMAN's behavior when working with that database, such as backup retention policy,
default destinations for backups to tape or disk, default backup device type (tape or disk),
and so on.
REF.: Oracle(r) Backup and Recovery Basics 10g Release 2 (10.2), 3-7 and 3-8

QUESTION 164:
Which four statements regarding the block change track file are correct?
A. The minimum size of this file is 10MB
B. The maintenance of this file is fully automatic.
C. The changed block are tracked in this fils as redo is generated
D. The location of this file must be different from that of the database files.
E. The Oracle database records block change information in this file by default
Answer: A, B,C, E
- True. Should say "start" not "minimum".The size of the change tracking file is
proportional to the size of the database and the number of enabled threads of redo. The
size is not related to the frequency of updates to the database. Typically, the space
required for block change tracking is Overview of Reporting on Backups and the RMAN
Repository approximately 1/30,000 the size of the data blocks to be tracked. Note,
however, the following two factors that may cause the file to be larger than this estimate
suggests:
? To avoid overhead of allocating space as your database grows, the change tracking file
size starts at 10MB, and new space is allocated in 10MB increments;
? For each datafile, a minimum of 320K of space is allocated in the change tracking file,
regardless of the size of the file.
B - True. Change tracking is disabled by default, because it does introduce some
minimal performance overhead on your database during normal operations. Although
RMAN does not support backup and recovery of the change-tracking file itself, if the
whole database or a subset needs to be restored and recovered, then recovery has no
user-visible effect on change tracking. After the restore and recovery, the change tracking
file is cleared, and starts recording block changes again. The next incremental backup
after any recovery is able to use change-tracking data.
C - True. Changed blocks are tracked by the CTWR background process as redo is
generated. Database incremental backups, automatically use the change tracking file;
D - False. By default, the change tracking file is created as an Oracle managed file in
DB_CREATE_FILE_DEST. You can also specify the name of the block change tracking
file, placing it in any location you choose. You can also change the location of the
change tracking file while preserving its contents.
E - True. One change tracking file is created for the whole database. RMAN's change
tracking feature for incremental backups improves incremental backup performance by
recording changed blocks in each datafile in a change tracking file.
REF.: Oracle(r) Backup and Recovery Basics 10g - BASICS , 4-19, 4-20 and 4-21

QUESTION 165:
What must you do to enable Automated SQL Execution Memory Magement?
A. Execute the DBMS_STATS.GATHER_SYSTEM_STATES procedure with
appropriate values
B. Set the instance parameters PGA_AGGREGATE_TARGET and
WORKAREA_SIZE_POLICY to appropriate values.
C. Set the instance parameters SORT_AREA_SIZE,BITMAP_MERGE_AREA_SIZE,
CREATE_BITMAP_AREA-SIZE,HASH_AREA_SIZE,SORT_AREA_SIZE, and
SORT_AREA_RETAINED_SIZE, all to Auto.
D. Unset the instance parameters SORT_AREA_SIZE,
BITMAP_MERGE_AREA_SIZE, CREATE_BITMAP_AREA_SIZE,
HASH_AREA_SIZE,SORT_AREA_SIZE, and SORT_AREA_RETAINED_SIZE.
Answer: B
The automatic SQL execution memory management feature is enabled by setting the
parameter WORKAREA_SIZE_POLICY to AUTO and by specifying a size of
PGA_AGGREGATE_TARGET in the initialization file.
REF.: Metalink Note :262946.1

QUESTION 166:
You added the following policy to enable managers to access information about only
their employees:
EXEC DBMS_RLS.ADD_POLICY (object_schema => 'scott',
object_name => 'emp',
policy_name => 'hr_policy',
fuction_schema => 'scott',
policy_function => 'hrpol',
sec_relevant_cols => 'sal,comm') ;
hr_policy is of the____________type.
A. DBMS_RLS.STATIC
B. DBMS_RLS.DYNAMIC
C. DBMS_RLS.SHARED_STATIC
D. DBMS_RLS.CONTEXT_SENSITIVE
E. DBMS_RLS.SHARED_CONTEXT_SENSITIVE
Answer: B
A dynamic predicate for a table, view, or synonym is generated by a PL/SQL function,
which you write and associate with a security policy through a PL/SQL interface.
Dynamic predicates are acquired at statement parse time, when the base table or view is
referenced in a query using SELECT or a DML statement. The function or package that
implements the security policy you create returns a predicate (a WHERE condition). This
predicate controls access according to the policy
you specify. Rewritten queries are fully optimized and shareable. Whenever the
EMPLOYEES table, under the HR schema, is referenced in a query or subquery
(SELECT), the server calls the EMP_SEC function (under the HR schema).
This function returns a predicate (called P1) defined in the function, which in this
example could be specific to the current user for the HR_POLICY policy.
REF.: Oracle(r) 10g Security Guide 6-4

QUESTION 167:
You want to implement ASM for the existing database instances and define the
following parameters in the parameter file of the ASM instance.
DB_UNIQUE_NAME = +ASM
ASM_POWER_LIMIT = 1
ASM_DISKSTRING = '/dev/dsk/*s2','´/dev/dsk/c1*'
ASM_DISKGROUPS = dgroupA, dgroup B
LARGE_POOL_SIZE = 8MB
While starting the ASM interface, you get the following error message:
ORA-15021: parameter "asm_diskstring" is not valid in RDBMS instance.
And the instance startup fails.
What causes the instance startup to fail?
A. ASM:DISKGROUPS cannot have two values.
B. LARGE_POOL_SIZE should be set to lower value.
C. ASM_POWER_LIMIT should be set to higher value.
D. DB_NAME parameter should have been defined add set to ASM
E. INSTANCE_TYPE parameter should have been defined add set to ASM
F. AMS_DISKSTRING parameter should not contain any wildcard characters.
Answer: E
Some initialization parameters are specifically relevant to an ASM instance. Of those
initialization parameters intended for a database instance, only a few are relevant to an
ASM instance. You can set those parameters at database creation time using Database
Configuration Assistant or later using Enterprise Manager. The INSTANCE_TYPE
parameter must be set to ASM. This is the only required parameter. All other parameters
take suitable defaults for most environments.
REF.: Oracle(r) 10g Administrator Guide, 12-8

QUESTION 168:
What does the DBMS_WM.GETWORKSPACE function return?
A. the name of your current workspace
B. a comma-delimited list of all the workspaces in the database.
C. a comma-delimited list of all the users assigned to the current workspace
D. a comma-delimited list of all the workspaces to which you have been assigned
Answer: A
GetWorkspace
Returns the current workspace for the session.
Format
DBMS_WM.GetWorkspace() RETURN VARCHAR2;
Parameters
None.
Usage Notes
None.
Examples
The following example displays the current workspace for the session.
SELECT DBMS_WM.GetWorkspace FROM DUAL;
GETWORKSPACE
B_focus_2
REF.: Oracle(r) 10g Application Developer's Guide - Workspace Manager, 4-69

QUESTION 169:
What is true a bout version-enabled tables?
A. the unit of versioning is the schema.
B. It is possible to version enable a table pertaining to SYS
C. There are as many segment as versions for the same base table.
D. It is possible to create workspaces if there is no version-enabled table in the database
Answer: D

QUESTION 170:
Which two statements regarding a SQL profile are true? (Choose two)
A. it is built by automatic Tuning Optimizer.
B. It cannot be stored persistently in the data dictionary.
C. It can be used by the query optimizer automatically.
D. It can be created manually by using the CREATE PROFILE command
Answer: A, C
The query optimizer can sometimes produce inaccurate estimates about an attribute of a
statement due to lack of information, leading to poor execution plans. Traditionally, users
have corrected this problem by manually adding hints to the application code to guide the
optimizer into making correct decisions. For packaged applications, changing application
code is not an option and the only alternative available is to log a bu with the application
vendor and wait for a fix. Automatic SQL Tuning deals with this problem with its SQL
Profiling capability. The Automatic Tuning Optimizer creates a profile of the SQL
statement called a SQL Profile, consisting of auxiliary statistics specific to that statement.
The query optimizer under normal mode makes estimates about cardinality, selectivity,
and cost that can smetimes be off by a significant amount resulting in poor execution
plans .
REF.: Oracle(r) Database 10g Performance Guide , 12-2

QUESTION 171:
Oracle provides a database package called dbms_redefinition to perform an online
rebuild of a table.
Which two steps are you recommended to do prior to issuing the
dbms _redefinition.start_redef_table procedure call? (Choose two)
A. grant privileges on the interim table
B. invoke the dbms_redefinition.can_redef_table procedure
C. invoke the dbms_redefinition.sync_interim_table procedure
D. create any triggers, indexes, or constraints on the interim table
E. create an empty interim table with all the desired characteristics
Answer: B, E
To perform online redefinition of a table with the DBMS_REDEFINITION package:
1. Choose the redefinition method: by key or by rowid
2. Verify that the table can be online redefined by invoking the
CAN_REDEF_TABLE procedure.(B)
3. Create an empty interim table (in the same schema as the table to be redefined)
with all of the desired logical and physical attributes. (E)
4. Start the redefinition process by calling START_REDEF_TABLE, providing the
following:
? The schema and table name of the table to be redefined
? The interim table name
? A column mapping string that maps the columns of table to be redefined tothe columns
of the interim table
? The redefinition method.If not specified, the default method of redefinition (using keys)
is assumed.
? Optionally, the columns to be used in ordering rows
? If redefining only a single partition of a partitioned table, the partition name
5. Copy dependent objects (such as triggers, indexes, grants, and constraints) and
statistics from the table being redefined to the interim table,
6. Execute the FINISH_REDEF_TABLE procedure to complete the redefinition of
thetable.
REF.: Oracle(r) 10g Administrator Guide, 15-22

QUESTION 172:
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 Oracle 10g?
A. Rebuild the index using the online option
B. Take the individual data file offline (not the whole tablespace), restore and recover the
data file from backup with RMAN, then set the data file online again
C. Without setting the individual data file offline, use RMAN with Block Media
Recovery to restore and recover only those blocks.
D. Take the individual data file (not the whole tablespace) offline, use RMAN with Block
Media Recovery to restore and recover only those blocks, then set the data file online
again
Answer: C
If a small number of blocks within one or more datafiles are corrupt, you can perform bl
ock media recovery instead of restoring the datafiles from backup and performing
complete media recovery of those files. The Recovery Manager BLOCKRECOVER
command can be used to restore and recover specified data blocks while the database is
open and the corrupted datafile is online.
REF.: Oracle(r) 10g Backup and Recovery Guide, 2-5

QUESTION 173:
When you are performing a backup, the Flash Recovery Area is low on free space.
Under which two circumstances would warnings be issued to you? (Choose two.)
A. When the used space is at 85%, a warning is issued.
B. When the used space is at 90%, a warning is issued.
C. When the used space is at 95%, a warning is issued.
D. When the used space is at 95%, a critical warning is issued.
E. When the used space is at 99%, a critical warning is issued.
F. When the used space is at 97%, a critical warning is issued.
Answer: A, F
By default a server-generated alert is generated for tablespace space usage when the
percentage of space usage exceeds either the 85%warning or 97% critical threshold level.
REF.: Oracle(r) 10g Administrator Guide, 4-19

QUESTION 174:
You want the database to send an e-mail to the senior database administrator
whenever the Warning threshold for the Database CPU time metric is reached.
Which option would you use to accomplish this activity?
A. the DBMS_MAIL package
B. the DBMS_PIPE package
C. the DBMS_HTTP package
D. the DBMS_ALERT package
E. the Database Control page
Answer: E
Use Database Control for Database Management to manage your database locally. If
you choose this option, you can additionally check Enable Email Notifications for Oracle
to e-mail you alerts regarding potential problems, and check Enable Daily Backup.
REF.: Oracle(r) 10g Database 2 Day DBA, 2-8

QUESTION 175:
Identify three key features of Oracle Data Pump (Choose three.)
A. Data Pump can stop a running job and resume it later
B. Data Pump can perform import using database link
C. Data Pump uses absolute path instead of relative path
D. Data Pump uses relative path instead of absolute path
E. Data Pump can perform incremental and cumulative exports
Answer: A, B, D
? The ability to restart Data Pump jobs. The START_JOB command restarts the current
job to which you are attached (the job cannot be currently executing). The job is restarted
with no data loss or corruption after an unexpected failure or after you issued a
STOP_JOB command, provided the dump file set and master table have not been altered
in any way.
? The NETWORK_LINK parameter initiates an export using a database link. This means
that the system to which the expdp client is connected contacts the source database
referenced by the source_database_link, retrieves data from it, and writes the data to a
dump file set back on the connected system.
? When data is moved, Data Pump automatically uses either direct path load (or unload)
or the external tables mechanism, or a combination of both.
REF.: Oracle(r) 10g Database Utilities ,1-2 and 1-3

QUESTION 176:
Which two statements are true regarding the use of UTF-16 encoding? (Choose
two.)
A. enables easier loading of multinational data
B. uses a fixed-width multibyte encoding sequence
C. Asian characters are represented in three characters
D. uses a variable-width multibyte encoding sequence
E. European characters are represented in one or two bytes
Answer: A, B

QUESTION 177:
When WORKAREA_SIZE_POLICY is set to AUTO, the work areas are sized to
accomplish which two goals? (Choose two)
A. To tune memory using the existing *_AREA_SIZE parameters
B. To allocate memory to work areas in order to optimize throughput only
C. To allocate memory to work areas in order to optimize response time only
D. To allocate memory to work areas in order to optimize both throughput and response
time
E. To ensure that the overall size of the PGA will never exceed the value of
PGA_AGGREGATE_TARGET
Answer: D, E

QUESTION 178:
Oracle 10g supports the creation of constraints on views. Which constraint states
are supported?
A. enforced
B. EXCEPTIONS INTO clause
C. [RELY|NORELY] DISABLE NOVALIDATE
D. ON DELETE actions for foreign key constraints
Answer: C

QUESTION 179:
Which three attributes of the LOG_ARCHIVE_DEST_n initialization parameter
control the data availability mode of a Data Guard standby database? (Choose
three.)
A. SYNC or ASYNC to specify that network I/O is to be done synchronously or
asynchronously
B. LGWR or ARCH to specify the process that will perform archival operations
C. AFFIRM or NOAFFIRM to control whether log archiving disk write operations are to
be performed synchronously or asynchronously.
D. VERIFY and NOVERIFY to verify the correctness of the contents of a completed
archived redo log file
Answer: A, B, C

The attributes of the LOG_ARCHIVE_DEST_n initialization parameter. The following
list shows the attributes:
* AFFIRM and NOAFFIRM (C) Controls whether redo transport services use
synchronous or asynchronous I/O towrite redo data to disk
* ALTERNATE
* ARCH and LGWR (B) Specifies whether redo transport services use archiver
processes (ARCn) or the log writer process (LGWR) to collect transaction redo data and
transmit it to standby destinations. If neither the ARCH or LGWR attributes are
specified, the default is ARCH.
* DB_UNIQUE_NAME
* DELAY
* DEPENDENCY
* LOCATION and SERVICE
* MANDATORY and OPTIONAL
* MAX_CONNECTIONS
* MAX_FAILURE
* NET_TIMEOUT
* NOREGISTER
* REOPEN
* SYNC and ASYNC (A) Specifies that network I/O is to be done synchronously
(SYNC) or asynchronously (ASYNC) when archival is performed using the log writer
process (LGWR).
* TEMPLATE
* VALID_FOR
* VERIFY
REF.: Oracle(r) Data Guard Concepts and Administration 10g Release 2 (10.2), 14-1

QUESTION 180:
What does the Metadata Application Programming Interface (API) allow you to do?
A. repair damaged data dictionary entries
B. delete data dictionary information about database objects you n o longer need
C. extract data definition commands from the data dictionary in a variety of formats
D. prepare pseudocode modules for conversion to Java or PL/SQL programs with a
Metadata code generator
Answer: C
When you fetch metadata for an object, you may want to use it to re-create the object in a
different database or schema. You may not be ready to make remapping decisions when
you fetch the metadata. You may want to defer these decisions until later. To accomplish
this, you fetch the metadata as XML and store it in a file or table. Later you can use the
submit interface to re-create the object.
The submit interface is similar in form to the retrieval interface. It has an OPENW
procedure in which you specify the object type of the object to be created. You can
specify transforms, transform parameters, and parse items. You can call the CONVERT
function to convert the XML to DDL, or you can call the PUT function to both convert
XML to DDL and submit the DDL to create the object.
REF.: Oracle(r) Database Utilities, 18-10

unior Oracle DBA: ManTech International Corporation

2009-01-11T22:35:00.001-08:00

Job ID	25136BR
Company Name	ManTech International Corporation
Job Category	Technology
Location	Hanover, MD
Position Type	Full-Time, Employee
Experience	1-2 Years Experience
Desired Education Level	Bachelor of Science
Date Posted	December 31, 2008 (Reposted Jan 8)

Primary Duties/Responsibilities:

Tune and optimize query and load performance.
Create and modify database schema objects in response to requirements
Support existing large Oracle database in accordance with user and system requirements.

Knowledge, Skills and Experience Required:

Minimum 6 months experience in developing applications for and providing life cycle support to databases.
Basic experience with Unix operating system environment
Like to have experience with gathering and analyzing performance metrics.
TS/SCI FS Poly

Why would someone want this job?

Growth opportunity for a junior Oracle database engineer. Customer wants an individual willing to become ‘go-to’ person for system. Opportunity to develop into a leadership position.

ManTech International Corporation is a leading provider of innovative technologies and solutions for mission-critical national security programs for the Intelligence Community; the departments of Defense, State, Homeland Security, and Justice; the Space Community; and other U.S. federal government customers. In 2007, cleared and national security job candidates using IntelligenceCareers.com, rated ManTech one of their top ten favorite employers. In 2006, G.I. Jobs magazine named ManTech a top 10 Military Friendly Employer.
For more information about ManTech, visit us our web site www.mantech.com and click on CAREERS. ManTech is an Equal Opportunity Employer. M/F/D/V are urged to apply.

Sr. Oracle/SQL Server DBA: MIVA

2009-01-11T22:32:00.000-08:00

Company Name	MIVA
Job Category	Internet; Engineering
Location	New York, NY
Position Type	Full-Time, Employee
Experience	5-10 Years Experience
Desired Education Level	Bachelor of Science
Date Posted	December 30, 2008

View MIVA profile and job listings

Apply Now: A Better Job Search Experience. Learn Why

MIVA is the largest independent Internet Performance Marketing Network, dedicated exclusively to helping businesses grow. MIVA delivers qualified leads to advertisers, helps maximize revenue for partners, facilitates commerce for online merchants and provides relevant information to customers. The Company has relationships with more than 100,000 customers, spanning North America, Europe and Asia.

We are seeking a Senior Oracle and SQL Server DBA. The responsibilities and duties of this position include but are not limited to:

Responsible for the planning, maintenance and development of all online Oracle and SQL Server databases
Handle day-to-day database housekeeping and maintenance task.
Automate database monitoring and improve on existing architecture.
Ensure data remains consistent across each database, data is clearly defined, users can consistently access databases concurrently in a form that meets their business needs, and that there are sufficiently robust provisions for data security and disaster recovery
Administration of all databases including assisting application support personnel in design, creation, and maintenance of databases, designing appropriate system backup and restore procedures, and maintaining appropriate data security
Play key role in providing system users with technical assistance regarding database activities
Evaluate database performance and take steps to maintain acceptable response times
Identify commercially available database management utilities to facilitate maintenance and monitoring of databases
Stay current with emerging industry standards and technologies
Maintain collaborative relationship with diverse IT personnel to partner in deployment of new database systems, or troubleshoot problems

The skills, experience, and knowledge required to be considered for this position include:

5-7 years of experience as a professional DBA
High level of expertise with both Oracle and MS-SQL Server databases
Extensive experience in database monitoring, optimization, and replication for disaster recovery and high availability
Expertise in Oracle development suite of tools
Extensive expertise in PL/SQL, TSQL, and SQL programming
Experience in Computer Systems in the online media space is highly desired
Bachelors degree in Computer Science, advanced degree is preferred
MS-SQL Server Certified and/or Oracle Certified Professional are desired
Excellent project management and people management skills
Excellent problem solving skills
Excellent technical troubleshooting skills
Ability to work effectively under pressure
Ability to work independently, with limited supervision
Ability to work on many tasks simultaneously

This position is based in our New York office and offers a superb compensation package including base salary, medical/dental benefits starting on the first day of employment, 401k, casual clothes environment, and several other excellent employment perks.

Oracle Developer - PL/SQL, Oracle Forms, Unix near Renton, WA

2009-01-11T22:31:00.000-08:00

Job ID	JC-orcdev-wa20
Company Name	CyberCoders
Job Category	Technology; Technology
Location	Renton, WA • Seattle, WA
Position Type	Full-Time, Employee
Salary	$70,000 to $80,000 per year
Experience	5-10 Years Experience
Desired Education Level	Bachelor of Science
Date Posted	January 11, 2009

Oracle Developer

Oracle Developer - PL/SQL, Oracle Forms, Unix near Renton, WA

This job is open as of 1/11/2009. Apply Now!

Not a fit for this job? Search other Oracle Developer jobs!

Are you an employer? Visit us for more info!

Location		Renton, WA; Seattle, WA
Salary		$70,000 - $80,000
Education		Bachelor of Science
Category		Information Technology
Experience Required		At least 2 Years
Short Description		Oracle Developer
Required Skills		Oracle Developer, Custom Application Development, System Integration, Software Implementation, PL/SQL, Perl, Unix/Linux, JavaScript
Recruiter		Jimmy Chung
Date Updated		1/11/2009

Skills Required

Oracle Developer, Custom Application Development, System Integration, Software Implementation, PL/SQL, Perl, Unix/Linux, JavaScript

Job Description

Oracle Developer

We are a leader in affordable and easy to use electronics industry and we are looking for a Oracle Developer to join our team!

What will you be doing?

- Provide simple solutions to complicated real-world problems within the framework of company demands and technical feasibility.
- Produce clear, concise and well-documented designs and code for client-server and web-based systems. Database development in Oracle, using PL/SQL, JavaScript, HTML, Perl, and other technologies.
- Communicate technical concepts to non-technical audiences, and gather functional system enhancement and upgrade requirements to be used in the technical design and development phases.
- Work well both independently, when necessary, as well as functioning as an integral part of a dynamic IT team.
- Multi-task as needed and manage time effectively to meet deadlines.

What do you need to qualify?

- 3+ years of technical experience with custom application development, system integration or software implementation work.
- Experience with most of the following: Oracle, PL/SQL, Oracle Forms, UNIX (Linux and/or Solaris), Windows, JavaScript, Perl, C, HTML.
- Knowledge of multiple technologies, including but not limited to programming languages, database systems, and development environments.

"Good to have" skills and experience:

- Any experience with Ajax and/or XML is a plus.
- Ability to provide creative solutions to both functional and technical problems.
- Genuine interest in working with fast pace dynamic Enviornment.
- Attention to detail.
- Exceptional verbal and written communications skills.
- ENERGY.

What's in it for you?

- Exceptional benefits package!
- Competitive base salary + perks!
- Dynamic working environment and very fun!
- Be part of a leader in the wireless industry!

If you are a Oracle Developer with great experience, please apply today!

Must be authorized to work in the United States on a full-time basis for any employer.

Are you a fit for this position?

Please Click Here to Apply!
(your information will be kept strictly confidential!)

Not a fit for this position? Click Here to Search Other CyberCoders Jobs!

Looking forward to receiving your resume through our website and going over the position with you. Clicking apply is the best way to apply, or you may also:

Email your resume in Word to:

Jimmy.Chung@CyberCoders.com
**Please do NOT change the email subject line in any way. You must keep the JobID: CC HotJobAp : JC-orcdev-wa -- in the email subject line for your application to be considered.***

Jimmy Chung - Senior Recruiter - CyberCoders

Junior Oracle DBA: ManTech International Corporation

2009-01-09T15:00:00.000-08:00

Job ID	25136BR
Company Name	ManTech International Corporation
Job Category	Technology
Location	Hanover, MD
Position Type	Full-Time, Employee
Experience	1-2 Years Experience
Desired Education Level	Bachelor of Science
Date Posted	December 31, 2008 (Reposted Jan 8)

Primary Duties/Responsibilities:

Knowledge, Skills and Experience Required:

Why would someone want this job?

Growth opportunity for a junior Oracle database engineer. Customer wants an individual willing to become ‘go-to’ person for system. Opportunity to develop into a leadership position.

Sr. Oracle/SQL Server DBA: MIVA

2009-01-09T14:57:00.000-08:00

Company Name	MIVA
Job Category	Internet; Engineering
Location	New York, NY
Position Type	Full-Time, Employee
Experience	5-10 Years Experience
Desired Education Level	Bachelor of Science
Date Posted	December 30, 2008

View MIVA profile and job listings

Apply Now: A Better Job Search Experience. Learn Why

We are seeking a Senior Oracle and SQL Server DBA. The responsibilities and duties of this position include but are not limited to:

Responsible for the planning, maintenance and development of all online Oracle and SQL Server databases
Handle day-to-day database housekeeping and maintenance task.
Automate database monitoring and improve on existing architecture.
Ensure data remains consistent across each database, data is clearly defined, users can consistently access databases concurrently in a form that meets their business needs, and that there are sufficiently robust provisions for data security and disaster recovery
Administration of all databases including assisting application support personnel in design, creation, and maintenance of databases, designing appropriate system backup and restore procedures, and maintaining appropriate data security
Play key role in providing system users with technical assistance regarding database activities
Evaluate database performance and take steps to maintain acceptable response times
Identify commercially available database management utilities to facilitate maintenance and monitoring of databases
Stay current with emerging industry standards and technologies
Maintain collaborative relationship with diverse IT personnel to partner in deployment of new database systems, or troubleshoot problems

The skills, experience, and knowledge required to be considered for this position include:

5-7 years of experience as a professional DBA
High level of expertise with both Oracle and MS-SQL Server databases
Extensive experience in database monitoring, optimization, and replication for disaster recovery and high availability
Expertise in Oracle development suite of tools
Extensive expertise in PL/SQL, TSQL, and SQL programming
Experience in Computer Systems in the online media space is highly desired
Bachelors degree in Computer Science, advanced degree is preferred
MS-SQL Server Certified and/or Oracle Certified Professional are desired
Excellent project management and people management skills
Excellent problem solving skills
Excellent technical troubleshooting skills
Ability to work effectively under pressure
Ability to work independently, with limited supervision
Ability to work on many tasks simultaneously

Diposkan oleh Aisyah Runi

Orecle Database 10g: New Features for Administrators - 1Z0-040 #7

2009-01-06T18:37:00.000-08:00

QUESTION 121:
You are using disk-based backups and the disk space in the backup destination is
limited. A normal complete backupset will not fit onto the disk. You need to
perform a full database backup in the same disk.
Which two commands in this scenario are correct? (Choose two.)
A. RMAN> BACKUP DATABASE;
B. RMAN> BACKUP AS BACKUPSET DATABASE;
C. RMAN> BACKUP AS COMPRESSED BACKUPSET DATABASE;
D. RMAN> BACKUP DURATION 2:00 PARTIAL MINIMIZE TIME DATABASE;
E. RMAN> BACKUP DURATION 1:00 PARTIAL FILESPERSET 1 DATABASE;
F. RMAN> BACKUP AS COMPRESSED BACKUPSET DATABASE PLUS
ARCHIVELOG;
Answer: C, F
For any use of the BACKUP command that creates backupsets, you can take advantage
of RMAN's support for binary compression of backupsets, by using the AS
COMPRESSED BACKUPSET option to the BACKUP command. The resulting
backupsets are compressed using an algorithm optimized for efficient compression of
Oracle database files. No extra uncompression steps are required during recovery if you
use RMAN's integrated compression.
REF.: Oracle(r) 10g Backup and Recovery Basics, 4-6

QUESTION 122:
Which two statements regarding the Flashback Table feature are correct? (Choose
two.)
A. Flashback Table can be performed on system tables.
B. Flashback Table operation does not shrink the segments.
C. Flashback Table uses log mining to extract SQL_REDO and SQL_UNDO statements.
D. Flashback Table operation acquires exclusive data manipulation language (DML)
locks.
Answer: B, D

QUESTION 123:
You have a materialized view called emp-mv on the emp table.
You want to stop the following query from executing if it does not rewrite:
SELECT deptno, sum(sal) FROM emp GROUP BY deptno;
Which statement wold you use?
A. SELECT /*+ USE_CONCAT */deptno,sum(sal) FROM emp GROUOP BE deptno;
B. SELECT /*+ NO_EXPAND */deptno,sum(sal) FROM emp GROUOP BE deptno;
C. SELECT /*+ NO_REWRITE */deptno,sum(sal) FROM emp GROUOP BE deptno;
D. SELECT /*+ REWRITE_OR_ERROR */deptno,sum(sal) FROM emp GROUP BY
deptno;
E. SELECT /*+ NO_QUERY_TRANSFORMATION */deptno,sum(sal) FROM emp
GROUP BY deptno;
Answer: D
REWRITE_OR_ERROR Hint
There may be situations where you want to stop the query from executing if it did not
rewrite . One such situation can be when you expect the un-rewritten query to take an
unacceptably long time to execute. To support this requirement, Oracle Database 10g
provides a new hint called REWRITE_OR_ERROR. This is a query block-level hint. For
example, if the SELECT statement is not rewritten, the error displayed in the message is
thrown. The REWRITE_OR_ERROR hint allows you to run
DBMS_MVIEW.EXPLAIN_REWRITE() on the query, resolve the problems that caused
rewrite to fail, and run the query again

QUESTION 124:
Which method would you use to undo the changes made by a particular transaction
without affecting the changes made by other transaction?
A. point-in-time recovery
B. manually enter the appropriate data again
C. execute the ROLLBACK command with transaction number
D. flashback the database to before the transaction was committed
E. determine all the necessary undo SQL statements from
FLASHBACK_TRANSACTION_QUERY and use them for recovery.
Answer: E

QUESTION 125:
Which four tablespaces can be renamed? (Choose four.)
A. an undo tablespace
B. an offline tablespace
C. a read-only tablespace
D. a temporary tablespace
E. the SYSTEM tablespace
F. the default permanent tablespace for the non-SYSTEM users
Answer: A, C, D, F
Besides any other, The COMPATIBLE parameter must be set to 10.0 or higher.
A - True. UNDO Tablespace. If the tablespace is an undo tablespace and if the following
conditions are met, then the tablespace name is changed to the new tablespace name in
the server parameter file (SPFILE).
- The server parameter file was used to start up the database.
- The tablespace name is specified as the UNDO_TABLESPACE for any instance.
If a traditional initialization parameter file (PFILE) is being used then a message is
written to the alert log stating that the initialization parameter file must bemanually
changed.
B - False. Offline Tablespaces. If any datafile in the tablespace is offline, or if the
tablespace is offline, then the tablespace is not renamed and an error is raised.
C - True. Read-only Tablespace. If the tablespace is read only, then datafile headers are
not updated. This should
not be regarded as corruption; instead, it causes a message to be written to the alert log
indicating that datafile headers have not been renamed. The data dictionary and control
file are updated.
D - True.Temporary Tablespace.If the tablespace is the default temporary tablespace,
then the corresponding entry in the database properties table is updated and the
DATABASE_PROPERTIES view
shows the new name.
E - False. SYSTEM Tablespace. If the tablespace being renamed is the SYSTEM
tablespace or the SYSAUX
tablespace, then it will not be renamed and an error is raised.
F - True. When you rename a tablespace the database updates all references to the
tablespace name in the data dictionary, control file, and (online) datafile headers. The
database does not change the tablespace ID so if this tablespace were, for example, the
default tablespace for a user, then the renamed tablespace would show as the default
tablespace for the user in the DBA_USERS view.
REF.: Oracle(r) Database Administration Guide,8-19

QUESTION 126:
Exhibit 1-5:

View the exhibit and examine the sparsely populated EMP table segment.

You execute the following SQL command:
ALERT TABLE emp SHRINK SPACE
Identify how the data in the segment will be reorganized as a result of the above
statement.
A. Exhibit 1.
B. Exhibit 2.
C. Exhibit 3
D. Exhibit 4
E. Exhibit 5
Answer: A
Segment shrink reclaims unused space both above and below the high water mark. In
contrast, space deallocation reclaims unused space only above the high water mark. In
shrink operations, by default, the database compacts the segment, adjusts the high water
mark, and releases the reclaimed space.
REF.: Oracle(r) Database Administration Guide,14-28

QUESTION 127:
You want to convert your existing non-ASM files to ASM filesfor the database
PROD. Which method or command would you use to accomplish this task?
A. the CONVERT command of RMAN
B. Data Pump Export and Import
C. Conventional export and import
D. The BACKUP .. RESTORE command of RMAN
E. The BACKUP AS COPY DATABASE .. command of RMAN
Answer: E
If you have enough disk space that you can have both your entire non-ASM database and
your ASM disk group on disk at the same time, you can do the migration directly without
using tapes.
1. Back up your database files as copies to the ASM disk group.
BACKUP AS COPY INCREMENTAL LEVEL 0 DATABASE FORMAT '+DISK' TAG
'ORA_ASM_MIGRATION';
REF.: Oracle(r) 10g Backup and Recovery Advance User's Guide, 16-2

QUESTION 128:
View the Exhibit and examine the recommendation graph provided for a test
database by the Undo Advisor.

The graph recommends that the size of undo tablespace should be set to _______.
A. 1 MB
B. 6 MB
C. 10 MB
D. 15 MB
E. 11 MB
Answer: E

QUESTION 129:
At 6:00 p.m. you notice that the database performance was slow between 2:00 p.m.
and 6:00 p.m. due to a series of interrelated problems. You plan to use the
Automatic Database Diagnostic Monitor (ADDM) findings for the time period 2:00
p.m. to 6:00 p.m. to resolve the performance issue. The Automatic Workload
Repository (AWR) snapshots are made every 30 minutes.
What would you do?
A. Lock at the latest ADDM report.
B. Check the alert log to see if it contains ADDM advice.
C. Modify the time interval used by the DBMS_JOB.INTERVAL procedure
D. Check for the ADDM advice trace file in the BACKGROUND_DUMP_DEST
directory
E. Modify the Automatic Workload Repository (AWR) snapshot retention period to four
hours
F. Create a custom ADDM task over the period defined by the snapshots taken at 2:00
p.m. and 6:00 p.m.
Answer: F

QUESTION 130:
In the Oracle 10g Data Guard architecture, what is the purpose of the Log
Transport Services?
A. to control the automated transfer or redo data from the production database to one or
more archival destinations
B. to apply redo log records sent from the primary database to a stadby database at the
receiving location
C. to synchronize changes to the control files on all standby database with changes on the
primary database when a log switch occurs
D. to batch archived log files on the primary database until a defined number of
checkpoints have been processed and then to distribute the arcives to each standby
database
Answer: A
A - True. Redo Transport Services control the automated transfer of redo data from the
production database to one or more archival destinations.
Redo transport services also perform the following tasks:
*Transmit redo data from the primary system to the standby systems in the configuration;
* Manage the process of resolving any gaps in the archived redo log files due to a
network failure;
*Enforce the database protection modes;
* Automatically detect missing or corrupted archived redo log files on a standby system
and automatically retrieve replacement archived redo log files from the primary database
or another standby database
B - False . Apply redo service do this
C - False . Apply redo service do this
D - False
REF.: Oracle(r) Data Guard Concepts and Administration 10g Release 2 (10.2), 1-3

QUESTION 131:
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)
A. Oracle Managed Files are used for this instance
B. The USER_DATA tablespace is managed using FET$/UET$ tables
C. The database will manage the free space of segments in the tablesapce using a bitmap.
D. Space within segments in the USER_DATA tablespac is managed with freelists
Answer: A,C
A - True. No datafile location had been specified, because OMF. Using Oracle-managed
files simplifies the administration of an Oracle Database. Through initialization
parameters, you specify the file system directory to be used for a particular type of file.
The database then ensures that a unique file, an Oracle-managed file, is created and
deleted when no longer needed. The database internally uses standard file system
interfaces to create and delete files as needed for the following database
structures:
* Tablespaces
* Redo log files
* Control files
* Archived logs
* Block change tracking files
* Flashback logs
* RMAN backups
B - False. See C
C - True. The clause, MANAGEMENT LOCAL, tells us that tocally managed
tablespaces track all extent information in the tablespace itself by using bitmaps,
resulting in the following benefits:
* Fast, concurrent space operations. Space allocations and deallocations modify locally
managed resources (bitmaps stored in header files).
* Enhanced performance
* Readable standby databases are allowed, because locally managed temporary
tablespaces do not generate any undo or redo.
* Space allocation is simplified, because when the AUTOALLOCATE clause is
specified, the database automatically selects the appropriate extent size.
* User reliance on the data dictionary is reduced, because the necessary information is
stored in file headers and bitmap blocks.
* Coalescing free extents is unnecessary for locally managed tablespaces.
D - False. See C
REF.: Oracle(r) Database Administration Guide, 8-3 and 11-1

QUESTION 132:
What is the Character Set Scanner?
A. a separate program that searches the database for invalid strings
B. a package (DBMS_CSS) that searches the database for invalid strings
C. a separate program that searches for strings that require Unicode conversion if you
want to support multiple languages
D. a separate program that identifies character data loss if you change the character sets
of the database
Answer: D
Before altering the character set of a database, check the convertibility of the data before
converting. Character set conversions can cause data loss or data corruption.
The Character Set Scanner utility provides this 2 features:
1) convertibility check of existing data and potential issues.
The Scanner checks all character data in the database including the data dictionary and
tests for the effects and problems of changing the character set encoding (characterset).
At the end of the scan, it generates a summary and exception report of the database scan.
2) from csscan V1.1 onwards it allows you also to do a check if there is no data in the
database that is incorrectly stored.
REF.: Metalink Note : 123670.1 - Use Scanner Utility before Altering the Database Character Set

QUESTION 133:
In an online transaction processing(OLTP) environment you find that the
transaction tables get heavily fragmented during the week. You decide to
defragment the transaction tables on every Friday at 9:30 p,m to gain performance.
Which two new features of the Oracle 10G database could you use to automate this
task?
A. the DBMS_AQ package
B. th DBMS_IJOB package
C. the OS levl job automation tool
D. the DBMS_SCHEDULER package
E. Enterprise Manager job scheduling
Answer: D,E

QUESTION 134:
Standby redo logs are used in Data Guard environments for a number of reasons.
Which three statements are true regarding standby redo logs?
A. They are required to implement a no-data-loss disaster recovery solution.
B. They are used on the primary database to allow the primary and a standby database to
switch roles easily and quickly without additional DBA intervention.
C. They must be archived before being applied to a standby database, requiring the
ARCH process to be running on the standby irrespective of the archive log mode.
D. They must be updated on the standby database server in asynchronous mode to
guarantee data availability.
Answer: A,B,C

QUESTION 135:
When enabling a flashback, you must identify the version of the database contents
you want to see. Which two options can you use to identify this version? (choose
two)
A. a point in time
B. a start and an end time
C. a start and an end SCN
D. a System Change Number (SCN)
E. a transaction ID
Answer: A,D

QUESTION 136:
Which two data types can be converted to LOBs using an ALTERMODIFY
command?
A. raw
B. long
C. varchar
D. long raw
Answer: B,D

QUESTION 137:
You want to perform the database backup when user activity on your system is low,
such as between 12:00 a.m. and 2:00 a.m.
Which command terminates with an error if the backup is not complete at the end
of th specified duration?
A. RMAN> BACKUP DURATION 2:00 PARTIAL LOAD DATABASE;
B. RMAN> BACKUP DURATION 2:00 PARTIAL FILESPERSET 1 DATABASE;
C. RMAN> BACKUP DURATION 2:00 PARTIAL MINIMIZE TIME DATABASE;
D. RMAN> BACKUP AS COPY DURATION 2:00PARTIAL MINIMIZE LOAD
DATABASE;
Answer: A
By default, when a BACKUP... DURATION command runs out of time before the
backup completes, RMAN reports an error. (The effect of this is that if the command is
running in a RUN block, the RUN block terminates.) You can control this behavior by
adding the PARTIAL option to the BACKUP... DURATION command, as in this
example:
BACKUP DURATION 4:00 PARTIAL TABLESPACE users FILESPERSET 1;
When PARTIAL is used, no error is reported when a backup command is interrupted due
to the end of the backup window. Instead, a message showing which files could not be
backed will be displayed. If the BACKUP command is part of a RUN block, then the
remaining commands in the RUN block will continue to execute.
When using DURATION the least recently backed up files are backed up first. Thus, if
you retry a job that was interrupted when the available duration expired, each
successive attempt covers more of the files needing backup.
REF.: Oracle(r) Backup and Recovery Advanced User's Guide, 2-41

QUESTION 138:
What is the result of setting the CURSOR_SHARING = SIMILAR initialization
parameter?
A. turn off all literal replacement for the shared cursor
B. causes the optimizer to share cursors for all statements
C. causes the optimizer to examine the statement of ensure cursor sharing occurs for safe
literals only
D. causes the optimizer to ignore all indexes and static's to establish the appropriate
execution path
Answer: C

QUESTION 139:
In order to speed forign key certain, Oracle 10G will cache the first_____primary
key values only where there are multirow DML statements.
A. 32
B. 128
C. 256
D. 512
Answer: C

QUESTION 140:
Which four are true regarding the workspace Manager in Oracle 10G? (choose
four)
A. automatically versions all tables
B. automatically installed with Oracle 10G.
C. provides concurrency by isolating versions of data until they are explicity merged with
production data or discarded
D. provides mechanism to identify and resolve conflicts
E. allows for version enabling tables by use of a packaged procedure
F. creates a database wide system table that maps row versions to workspaces and is
visible to all users
Answer: A,B,C,E
Workspace Manager lets you version-enable one or more user tables in the database.
When a table is version-enabled, all rows in the table can support multiple versions of the
data.
A workspace is a virtual environment that one or more users can share to make changes
to the data in the database. A workspace logically groups collections of new row versions
from one or more version-enabled tables, and isolates these versions until they are
explicitly merged with production data or discarded, thus providing maximum
concurrency.
Workspace Manager automatically detects conflicts, which are differences in data values
resulting from changes to the same row in a workspace and its parent workspace. You
can use workspace locks to avoid conflicts.
Workspace Manager is installed by default in the seed database and in all databases
created by the Database Configuration Assistant (DBCA).
D - False. The Workspace manager detects conflicts, but YOU must resolve conflicts
before merging changes from a workspace into its parent workspace.
F- False. Will be visible only AFTER making the changes public.
REF.: Oracle(r) Application Developer's Guide - Workspace Manager 10g Release 2 (10.2), 1-2 and 1-3

Orecle Database 10g: New Features for Administrators - 1Z0-040 #6

2009-01-06T18:02:00.000-08:00

QUESTION 100:
The SQL Access Advisor may recommend additional structures to improve the
performance of SQL statements. You can select the type of structure recommended
by the SQL Access Advisor.
Which two structures can you select? (Choose two)
A. Indexes
B. Partitions
C. Synonyms
D. Sequences
E. Database links
F. Materialized views
G. Hash or index clusters
H. Index-Organized Table
Answer: A, F
Materialized views and indexes are essential when tuning a database to achieve optimum
performance for complex, data-intensive queries. The SQL Access Advisor helps you
achieve your performance goals by recommending the proper set of materialized views,
materialized view logs, and indexes for a given workload.
REF.: Oracle 10g Performance Tuning Guide 17-1

QUESTION 101:
Sam is a DBA of an OLTP system that has a large number of users. He receives a
complaint from two users with client ids Richard and Hardy that the Application
response time is poor. To understand the problem faced by Richard and Hardy
better, Sam wants to gather the statistics for all active sessions of these clients for a
particular instance.
Which two of these steps should Sam perform and in which order to view the
relevant information?
A. 1, 3
B. 1, 4
C. 2, 3
D. 2, 4
Answer: A
The question asks for "to gather the statistics for all" and not to enable tracing. Thus it
can be assumed that we need to do DBMS_MONITOR.CLIENT_ID_STAT_ENABLE
(p18-10) and not DBMS_MONITOR.CLIENT_ID_TRACE_ENABLE (p18-13)

QUESTION 102:
You are using the Automatic Shared Memory Management configuration.
Which four initialization parameters will be tuned automatically? (Choose four)
A. LOG_BUFFER
B. DB_CACHE_SIZE
C. JAVA_POOL_SIZE
D. LARGE_POOL_SIZE
E. STREAMS_POOL_SIZE
F. DB_Nk_CACHE_SIZE
G. SHARED_POOL_SIZE
H. DB_KEEP_CACHE_SIZE
I. DB_RECYCLE_CACHE_SIZE
Answer: B, C, D, G
ASMM will tune automatically Db_cache_size, Shared_pool_size, large_pool_size and
Java_pool_size

QUESTION 103:
In which three cases should you collect statistics manually? (Choose three)
A. For large objects (LOBs)
B. For fixed objects
C. For external tables
D. For undo tablespaces
E. For schema statistics
F. For tables that are bulk loaded
Answer: B, C, F
You must gather statistics manually for the following situations:
1. When a table is loaded using bulk operation
2. When using external tables
3. To collect system statistics
To collect statistics on fixed objects.

QUESTION 104:
In an online transaction processing (OLTP) environment, you find that the
transaction tables get heavily fragmented during the week. You decide to
defragment the transaction tables on every Friday at 9:30 p.m. to gain performance.
Which two new features of the Oracle 10g database could you use to automate this
task? (Choose two)
A. The DBMS_AQ package
B. The DBMS_JOB package
C. The OS level job automation tool
D. The DBMS_SCHEDULER package
E. Enterprise Manager job scheduling
Answer: D, E
D: Oracle 10g's new DBMS_SCHEDULER package offers significant improvements over
DBMS_JOB for scheduling jobs and tasks.
Here is an example of scheduling with DBMS_SCHEDULER. Note that the syntax is
now much clearer, and the parameters even now make sense when compared to
DBMS_JOB. And gone at last is that wacky INTERVAL parameter!
BEGIN DBMS_SCHEDULER.CREATE_JOB ( job_name => 'HR_STATS_REFRESH'
,job_type => 'PLSQL_BLOCK' ,job_action => 'BEGIN
DBMS_STATS.GATHER_SCHEMA_STATS("HR");END;',start_date =>'09/01/2004
09:30 PM' ,repeat_interval => 'WEEKLY BYDAY=5' ,enabled => TRUE ,comments =>
Refreshes the HR Schema every Friday at 9.30 PM');END;
Not B: DBMS_JOB is not new with 10g)

QUESTION 105:
You have the following requirements with respect to optimizer statistics:
The statistics should be generated automatically during weekdays (Monday to
Friday) between 10:00 p.m. and 7:00 a.m.
The objects that need updated statistics the most should be processed first.
What should you do?
A. Modify the AUTO_TASKS_JOB_CLASS Scheduler class.
B. Use the DBMS_STATS.GATHER_DATABASE_STATS procedure with the
GATHER AUTO option.
C. Modify the duration of the default WEEKNIGHT_WINDOW scheduler window to
10:00 pm.7:00 a.m.
D. Not make any medications, because by default the WEEKNIGHT_WINDOW
scheduler window automatically generates statistics during weekdays between 10:00 p.m.
and 7:00 a.m.
Answer: C
A Scheduler program GATHER_STATS_PROG and Scheduler job
GATHER_STATS_JOB are predefined on installation of Oracle Database.
GATHER_STATS_PROG collects optimizer statistics for all objects in the database for
which there are no statistics or only stale statistics. GATHER_STATS_JOB is defined on
GATHER_STATS_PROG and is scheduled to run in the
MAINTENANCE_WINDOW_GROUP.
The Oracle Scheduler enables you to create time windows during which jobs are
automatically run. A typical Scheduler window defines a start time, a duration, and
optionally a resource plan to activate. A Scheduler job can then name a window as its
schedule. (When the window "opens," the job begins to run.) In addition, windows can be
combined into window groups, and if a job names a window group as its schedule instead
of naming a window, the job runs whenever any of the windows in the window group
opens.
Two Scheduler windows are predefined upon installation of Oracle Database:
* WEEKNIGHT_WINDOW starts at 10 p.m . and ends at 6 a.m. every Monday through
Friday.
* WEEKEND_WINDOW covers whole days Saturday and Sunday.
Together these windows constitute the MAINTENANCE_WINDOW_GROUP in which
all system maintenance tasks are scheduled. Oracle Database uses the maintenance
windows for automatic statistics collection and for some other internal system
maintenance jobs .
REF.: Oracle 10g Administrator Guide, 23-1

QUESTION 106:
You are working on a database that was created using Oracle Database 10g, the
default tablespace type property was not changed. You execute the following
statement to create a tablespace, mytbs:
CREATE TABLESAPCE mythbs DATAFILE '/u1/data/mytbs.dbf' SIZE
100M;
Which two statements are correct regarding the mytbs tablespace? (Choose two)
A. It is a smallfile tablespace.
B. It is a bigfile tablespace (BFT).
C. It is a default bigfile tablespace.
D. It is a locally managed tablespace.
E. It is a dictionary-managed tablespace.
Answer: A, D
Explanation:
Specify either SET DEFAULT BIGFILE TABLESPACE or SET DEFAULT
SMALLFILE
TABLESPACE. If you omit this clause, the default is a smallfile
tablespace, which is the traditional type of Oracle Database tablespace.
Verify also:
SELECT tablespace_name, extent_management, bigfile
FROM dba_tablespaces
WHERE tablespace_name = 'MYTHBS';
TABLESPACE_NAME EXTENT_MANAGEMENT BIGFILE
MYTHBS LOCAL NO

QUESTION 107:
You executed the following command to back up the control file:
ALTER DATABASE BACKUP CONTROLFILE TO TRACE;
What do you find in the trace file?
A. Image of the control file
B. Location of the control file
C. Contents of the control file in text format
D. SQL command to re-create the database
E. SQL command to re-create the control file
F. Contents of the control file in binary format
Answer: E
Use the ALTER DATABASE BACKUP CONTROLFILE statement to back up your
control files. You have two options:
1. Back up the control file to a binary file (duplicate of existing control file) using
the following statement:
ALTER DATABASE BACKUP CONTROLFILE TO '/oracle/backup/control.bkp';
2. Produce SQL statements that can later be used to re-create your control file:
ALTER DATABASE BACKUP CONTROLFILE TO TRACE;
This command writes a SQL script to the database trace file where it can be captured and
edited to reproduce the control file.
REF.: Oracle 10g Administrator Guide, 5-8

QUESTION 108:
Your boss at Certkiller .com wants you to clarify Oracle 10g. Which two steps are
performed the first time any UPDATE statement is issued after the instance is
started? Choose two
A. Creating the parse tree of the statement
B. Writing the modified data blocks to the data files
C. Writing the modified data to the archived redo log files
D. Updating the control file to indicate the most recent checkpoint
E. Updating the data file header to indicate the most recent checkpoint
F. Reading the blocks to database buffer cache if they are not already there
Answer: A, F

QUESTION 109:
You work as a database administrator for Certkiller .com. You have been asked to
use a centralized administrative tool to administer your database servers and
application servers. In order to achieve the objective, which component would you
configure on each database server?
A. Database Control
B. Management Server
C. Management Repository
D. Application Server Control
E. Oracle Management Agent
Answer: E

QUESTION 110:
The data file belonging to the SYSTEM tablespace is corrupted and no
backup of the file is available.
How do you recover the data file?
A. The data file cannot be recovered.
B. The date file can be restored from the SYSTEM auto backup.
C. Take the tablespace offline, drop, and re-create it.
D. Recovery Manager (RMAN) is used to recover the data file.
E. The ALTER DATABSE CREATE DATAFILE.. command is used to recover the data
file.
Answer: A
The tablespace that contains the Oracle data dictionary for a database, which is the
metadata that describes the complete contnents of the database. The SYSTEM tablespace
is unlike other tablespaces in that all datafiles contained in the tablespace must be online
for Oracle to function. If a media failure affects one of the datafiles in SYSTEM, then
you must mount the database and recover. The question ask to "How to recover?", but
also says that there is NO BACKUP AVAILABLE.
To restore a datafile or control file from backup is to retrieve the file onto disk from a
backup location on tape, disk or other media, and make it available to the database server.
To recover a datafile (also called performing recovery on a datafile), is to take a restored
copy of the datafile and apply to it changes recorded in the database's redo logs. To
recover a whole database is to perform recovery on each of its datafiles.
REF.: Oracle(r) 10g Backup and Recovery Guide 1-5

QUESTION 111:
You work as a database administrator for Certkiller .com. You started the instance
using the init.ora file. You have two control files and three
redo log groups in your database. You decided to protect
the database against failures by adding one more control
file.
Which file is the appropriate way to perform this task?
A. Shut down the instance, copy the control file to the third location and open the
database.
B. Abort the instance, copy the control file to the third location, modify the
CONTROL_FILES parameter in the init.ora file and open the database.
C. Copy the control file to the third location, modify the CONTROL_FILES parameter in
the init.ora file, restart the instance and open the database.
D. Shut down the instance, copy the control file to the third location, modify the
CONTROL_FILES parameter in the init.ora file and open the database.
E. Shut down the instance, startup in the mount state, copy the control file to the third
location, modify the CONTROL_FILES parameter in init.ora and open the database.
Answer: D
You can create an additional control file copy for multiplexing by copying an existing
control file to a new location and adding the file name to the list of control files.
Similarly, you rename an existing control file by copying the file to its new name or
location, and changing the file name in the control file list. In both cases, to guarantee
that control files do not change during the procedure, shut down the database before
copying the control file.
To add a multiplexed copy of the current control file or to rename a control file:
1. Shut down the database
2. Copy an existing control file to a new location, using operating system
commands.
3. Edit the CONTROL_FILES parameter in the database initialization parameter
file to add the new control file name, or to change the existing control filename.
4. Restart the database.
REF.: Oracle 10g Administrator Guide, 5-4

QUESTION 112:
You work as a database administrator for Certkiller .com. You find that the database
performance degrades while you backup the Certkiller database using Recovery
Manager (RMAN). The Certkiller database is running in shared server mode.
The database instance is currently using 60% of total operating system memory.
You suspect the shared pool fragmentation to be the reason.
Which action would you consider to overcome the performance degradation?
A. Configure Java Pool to cache the java objects.
B. Configure Streams Pool to enable parallel processing.
C. Increase Shared Pool size to cache more PL/SQL objects.
D. Increase Database Buffer Cache size to increase cache hits.
E. Configure Large Pool to be used by RMAN and shared server.
F. Increase the total System Global Area (SGA) size to increase memory hits.
Answer: E
When attempting to get shared buffers for I/O slaves, the database does the following:
* If LARGE_POOL_SIZE is set, then the database attempts to get memory from the
large pool. If this value is not large enough, then an error is recorded in the alert log, the
database does not try to get buffers from the shared pool, and asynchronous I/O is not
used.
* If LARGE_POOL_SIZE is not set, then the database attempts to get memory from the
shared pool.
* If the database cannot get enough memory, then it obtains I/O buffer memory from the
PGA and writes a message to the alert.log file indicating that synchronous I/O is used for
this backup.
The memory from the large pool is used for many features, including the shared server
(formerly called multi-threaded server), parallel query, and RMAN I/O slave
buffers.Configuring the large pool prevents RMAN from competing with other
subsystems for the same memory. Se the LARGE_POOL_SIZE initialization parameter
to configure the large pool. To see in which pool (shared pool or large pool) the memory
for an object resides, query $SGASTAT.POOL.
REF.: Oracle(r) 10g Backup and Recovery Guide 11-6 and 11-7

QUESTION 113:
You work as a database administrator for Certkiller .com. You decided to manage
client and server connections using Local Naming method in Certkiller .com. When
you try to connect to the database you get the following error:
ERROR:
ORA-12154: TNS:could not resolve service name
Which network configuration files would you look into to resolve this error? Choose
two.
A. snmp.ora
B. names.ora
C. spfile.ora
D. sqlnet.ora
E. listener.ora
F. tnsnames.ora
Answer: D, F
Error: ORA-12154 / TNS-12154
Text: TNS:could not resolve service name
--------------------------------
Cause: The service name specified is not defined correctly in the TNSNAMES.ORA file.
Action: Make the following checks and correct the error:
- Verify that a TNSNAMES.ORA file exists and is in the proper place and accessible.
See the operating system specific manual for details on the required name and location.
Explanation:
The SQL*Net layer cannot find a definition for the alias supplied
Diagnosis:
1) Check the tnsnames.ora file you are using, verify that it is accessible.
5) Check the default domain name being used, and the path used to locate aliases, in the
SQLNET.ORA file. The default domain is specified in the
NAMES.DEFAULT_DOMAIN parameter - this is appended to the alias specified in the
connect string if there is no domain given.
REF.: Metalink Note:21321.1 ORA 12154 "TNS: could not resolve service name"

QUESTION 114:
You work as a database administrator for Certkiller .com. In your production
database, the size of Database Buffer Cache needs to be increased immediately for
the current as well as future instances of the database. The Oracle instance has been
configured to accommodate any changes in the size of the memory structures.
At this is production database, you want to accomplish this task with no impact on
the user's connections.
Which activity must you have completed before accomplishing this task?
A. You must have started the database instance in restricted mode.
B. You must have started the database instance in NORMAL mode.
C. You must have started the database instance with server parameter file.
D. You must have started the database instance but must not have mounted.
E. You must have started the database instance but must not have opened.
Answer: C

QUESTION 115:
Which three statements regarding the column-level Virtual Database Policy (VPD)
policy are true? (Choose three.)
A. The column-level VPD policy can be applied only to tables.
B. The security policy is applied whenever the column is referenced in a query.
C. The column-level VPD policy can be applied to tables and views, but not to
synonyms.
D. The security policy cannot be applied to data manipulation language (DML)
statements.
E. The security policy is applied and statements are rewritten when they access
security-relevant
Answer: B, C, E
A - False. Virtual private database (VPD) enables you to enforce security, to a fine level
of granularity, directly on tables, views, or synonyms.
B - True. When a user directly or indirectly accesses a table, view, or synonym protected
with aVPD policy, the server dynamically modifies the SQL statement of the user.
Themodification creates a WHERE condition (known as a predicate) returned by a
function implementing the security policy. The statement is modified dynamically,
transparently to the user, using any condition that can be expressed in or returned by
a function. VPD policies can be applied to SELECT, INSERT, UPDATE, INDEX, and
DELETE statements.
C - True. see B
D - False. see B
E - True. see B
REF.: Oracle 10g Security Guide 14-2

QUESTION 116:
Which four policy rule categories are available in the Policy Framework? (Choose
four.)
A. Objects
B. Storage
C. Security
D. Network
E. Configuration
Answer: A, B, C, E

QUESTION 117:
Which three files can be managed using an ASM Instance? (Choose three.)
A. Data files
B. Audit files
C. Trace files
D. Export files
E. Alert log files
F. Archived log files
G. Change tracking files
Answer: A, F, G
File Types Supported by Automatic Storage Management
Control files yes
Datafiles yes
Redo log files yes
Archive log files yes
Trace files no
Temporary files yes
Datafile backup pieces yes
Datafile incremental backup pieces yes
Archive log backup piece yes
Datafile copy yes
Persistent initialization parameter file (SPFILE) yes
Disaster recovery configurations yes
Flashback logs yes
Change tracking file yes
Data Pump dumpset yes
Automatically generated control file backup yes
Cross-platform transportable datafiles yes
Operating system files no
REF.: Oracle(r) 10G Admin Guide 12-33

QUESTION 118:
You are checking every 10 minutes for alerts regarding the violation of tablespace
usage threshold. Some of the tablespaces' fullness was not detected on time. As a
result the transactions were aborted before you had a chance to resolve the space
issue. What would you do to avoid transactional failures?
A. Use bigfile tablespaces (BFTs)
B. Enable resumable space allocation
C. Modify the background process PMON settings
D. Modify the LOG_CHECKPOINT_TIMEOUT parameter
E. Reduce the default tablespace usage threshold values for the database.
Answer: B
Explanation:
That is the purpose of resumable space allocation.

QUESTION 119:
You create a locally managed tablespace by using the following SQL statement:
CREATE TABLESPACE hr_tbs
DATAFILE 'data.dat' SIZE 1M REUSE
AUTOEXTEND ON MAXSIZE 100M;
The warning and critical tablespace usage threshold values are set to 85% and 97%,
respectively. Which two statements regarding the threshold-based alerts generation
for the HR_TBS tablespace are correct? (Choose two.)
A. A critical alert will be generated when 97 MB space is used.
B. A critical alert will be generated when 970 KB space is used.
C. A warning alert will be generated when 85 MB space is used.
D. A warning alert will be generated when 850 KB space is used.
E. A critical alert will be generated when 970 KB is left as free space.
F. A warning alert will be generated when 850 KB is left as free space.
G. No critical alert will be generated because it is a small file tablespace.
H. No warning alert will be generated because the tablespace is autoextensible.
Answer: A, C
By default a server-generated alert is generated for tablespace space usage when the
percentage of space usage exceeds either the 85% warning or 97% critical threshold
level. MAXSIZE 100M , then 97 MB and 85MB, are the correct answers.
REF.: Oracle(r) 10g Administrator Guide , 4-19

QUESTION 120:
While using buffer cache advisory, you find that no statistics have been collected.
Which task would you perform to collect the statistics?
A. Restart the database instance.
B. Set DB_CACHE_ADVICE to ON.
C. Set TIMED_STATISTICS to TRUE.
D. Set TIMED_OS_STATISTICS to 100.
E. Set ACTIVE_INSTANCE_COUNT to 2.
F. Set STATISTICS_LEVEL to TYPICAL.
G. Modify the AUTO_TASK_JOB_CLASS schedule class.
H. Wait for the default automatic optimizer statistics generation.
I. Create a custom Automatic Database Diagnostic Monitor (ADDM) task.
Answer: F
If no statistics have been collected, them the automatic statistics colletion isn't enable.
The Oracle gathering and monitoring features are mainly automatic, managed by an
Oracle background processes. To enable automatic statistics collection and automatic
performance features, the STATISTICS_LEVEL initialization parameter must be set to
TYPICAL or ALL.
REF.: Oracle(r) 10g Performance Guide , 1-4

Security : Encrypted Tablespace Administration

2009-01-05T14:30:00.000-08:00

This article has demonstrated how to use the command line to create encrypted tablespaces, but you can also create encrypted tablespaces via Oracle Enterprise Manager. To create an encrypted tablespace in Oracle Enterprise Manager, from the main Database page, choose the Server tab and then click the Tablespaces link under Storage. On the page, click Create, which brings up a screen similar to the one shown in Figure 3. Check the Encryption check box, and click Encryption Options. That will lead to a new screen where you can specify the type of encryption algorithm.

Figure 3: Encrypted tablespace creation via Oracle Enterprise Manager

Different database views can help you monitor encrypted tablespaces by indicating the tablespace number, the encryption algorithm, and whether a tablespace is encrypted. The ENCRYPTED column in the DBA_TABLESPACES data dictionary view shows whether a tablespace is encrypted (YES or NO). If a tablespace is encrypted, the relevant information is shown in a different view: V$ENCRYPTED_TABLESPACES.

The V$ENCRYPTED_TABLESPACES view includes the following columns:

TS#. The tablespace number
ENCRYPTIONALG. The encryption algorithm, such as AES128
ENCRYPTEDTS. Indicates whether the tablespace is encrypted (value is YES or NO)

Note that when you recover an encrypted tablespace, the wallet must be open, and you can transport an encrypted tablespace to a different database, but the other database must have the same wallet (and therefore the same master key). The wallet password for the other database can be different, but the wallet must be the same. If you transport an encrypted tablespace across platforms, the target platform must have the same endianness for encrypted tablespaces.

Finally, note that you can only create encrypted tablespaces; you cannot modify existing tablespaces to encrypt them. So, when you need existing data in encrypted tablespaces, the best solution is to first create encrypted tablespaces and then move the objects from the unencrypted tablespaces to them.

Security : Comparison of Tablespace and Column-Level Encryption

2009-01-05T14:28:00.001-08:00

Column-level TDE allows you to encrypt data in a specific column only. This is how to encrypt the FIRST_NAME column in the ACCOUNTS_REG_ENC table, created from the ACCOUNTS_REG table:

create table accounts_reg_enc
nologging as
select * from accounts_reg;

alter table accounts_reg_enc
modify first_name encrypt using 'AES128'
no salt;

After this modification, the FIRST_NAME column values will be stored encrypted in the ACCOUNTS_REG_ENC table. All other columns will be in cleartext. However, because the blocks of this table in the buffer cache will be replicas of the table, the FIRST_NAME column will still be encrypted in the buffer cache. So, there will be a severe impact on the performance of the index scans using FIRST_NAME.

Let’s examine the impact by running a small test. We issue a query against the ACCOUNTS_REG_ ENC table (which is in an unencrypted tablespace but with its FIRST_NAME column encrypted). We search for first names starting with D, and the query uses the index. We repeat the query against the ACCOUNTS_ENC table (in the encrypted tablespace). Then we examine the impact of the encrypted column on query performance, by issuing autotrace on explain stat, which shows the optimization plan used as well as statistics such as consistent gets. Listing 3 shows the commands and the output.

Code Listing 3: Query comparison of column-level and tablespace encryption

/* Run a query on the ACCOUNTS_REG_ENC table (in the unencrypted */
/* tablespace, but with the encrypted first_name column): */

SQL> set autotrace on explain stat
SQL> set linesize 120
SQL> select count(1) from accounts_reg_enc
 2    where first_name like 'D%';

--------------------------------------------------------------------------------------------
| Id  | Operation               | Name          | Rows   | Bytes   | Cost (%CPU)| Time     |
--------------------------------------------------------------------------------------------
|   0 | SELECT STATEMENT        |               |      1 |       7 |   686   (5)| 00:00:09 |
|   1 |  SORT AGGREGATE         |               |      1 |       7 |            |          |
|*  2 |     INDEX FAST FULL SCAN| IN_ACC_REG_FN |  50000 |     341K|   686   (5)| 00:00:09 |
--------------------------------------------------------------------------------------------

Predicate Information (identified by operation id):
--------------------------------------------------------

  2 - filter(INTERNAL_FUNCTION("FIRST_NAME") LIKE 'D%')


Statistics
--------------------------------
         0  recursive calls
         0  db block gets
     13963  consistent gets
...

/* Now, run the same query on the ACCOUNTS_ENC table (in the encrypted tablespace): */

SQL> select count(1) from accounts_enc
 2    where first_name like 'D%';

-------------------------------------------------------------------------------------
| Id  | Operation         | Name          | Rows  | Bytes  |  Cost (%CPU)| Time     |
-------------------------------------------------------------------------------------
|   0 | SELECT STATEMENT  |               |     1 |      7 | 513      (1)| 00:00:07 |
|   1 |  SORT AGGREGATE   |               |     1 |      7 |             |          |
|*  2 |   INDEX RANGE SCAN| IN_ACC_ENC_FN |   210K|   1442K| 513      (1)| 00:00:07 |
-------------------------------------------------------------------------------------

Predicate Information (identified by operation id):
----------------------------------------------------

  2 - access("FIRST_NAME" LIKE 'D%')
       filter("FIRST_NAME" LIKE 'D%')


Statistics
------------------------------
         0  recursive calls
         0  db block gets
       120  consistent gets
...

Consider the difference. The access path changed from INDEX FAST FULL SCAN for the table with the encrypted column (ACCOUNTS_REG_ENC) to INDEX RANGE SCAN for the table in the encrypted tablespace (ACCOUNTS_ENC), which resulted in a reduction of consistent gets from 13,963 to a mere 120—less than 1 percent of the original value. This means that the query against the encrypted tablespace table generated just 1 percent of the logical I/O of the query on the table with the encrypted column.

A second major difference is in column restrictions. Certain datatypes can’t be encrypted with TDE column encryption, columns under TDE can’t be used for foreign keys, only B-tree indexes can be created against the columns under TDE—and these are just some of the limitations of TDE column encryption. However, there is no restriction on these objects in an encrypted tablespace.

TDE column encryption has additional space requirements. Typically, encrypted values are larger than unencrypted values, causing a table with column encryption to be larger overall. To check for the exact increase, we can use the show_space procedure to show the space inside the tables—ACCOUNTS_REG (the table in the unencrypted tablespace), ACCOUNTS_ENC (the table in the encrypted tablespace), and ACCOUNTS_REG_ENC (the ACCOUNTS_REG table with only the FIRST_NAME column encrypted). Listing 4 shows the output. Note that the space consumption (of full blocks) is about the same for both forms of the table—in the unencrypted and encrypted tablespaces. However, the table in the unencrypted tablespace with the encrypted column takes about 15K full blocks, against about 10K in the table in the encrypted tablespace—about 50 percent more space. So, not only is the performance better with TDE tablespace encryption, but there is also no discernible additional space consumption, which, in turn, boosts performance by reducing I/O.

Next Steps

LEARN more about tablespace encryption
Oracle Database Advanced Security Administrator’s Guide
Using Transparent Database Encryption in Oracle Database 11g

DOWNLOAD sample code for this article

Code Listing 4: Space consumption on various forms of the table

SQL> set serveroutput on

SQL> exec show_space ('ACCOUNTS_REG','ARUP')
Unformatted Blocks .....................                                 0
FS1 Blocks (0-25)  .....................                                 2
FS2 Blocks (25-50) .....................                                 0
FS3 Blocks (50-75) .....................                                 0
FS4 Blocks (75-100).....................                                 0
Full Blocks        .....................                            10,938
Total Blocks............................                            11,088
Total Bytes.............................                        90,832,896
Total MBytes............................                                86
Unused Blocks...........................                                 1
Unused Bytes............................                             8,192
Last Used Ext FileId....................                                 6
Last Used Ext BlockId...................                            40,969
Last Used Block.........................                               847

SQL> exec show_space ('ACCOUNTS_ENC','ARUP')
Unformatted Blocks .....................                                 0
FS1 Blocks (0-25)  .....................                                 1
FS2 Blocks (25-50) .....................                                 0
FS3 Blocks (50-75) .....................                                 0
FS4 Blocks (75-100).....................                                 1
Full Blocks        .....................                            10,967
Total Blocks............................                            11,120
Total Bytes.............................                        91,095,040
Total MBytes............................                                86
Unused Blocks...........................                                 4
Unused Bytes............................                            32,768
Last Used Ext FileId....................                                 7
Last Used Ext BlockId...................                            10,249
Last Used Block.........................                               876


SQL> exec show_space ('ACCOUNTS_REG_ENC','ARUP')
Unformatted Blocks .....................                                 0
FS1 Blocks (0-25)  .....................                                 2
FS2 Blocks (25-50) .....................                                 0
FS3 Blocks (50-75) .....................                                 0
FS4 Blocks (75-100).....................                                 1
Full Blocks        .....................                            15,680
Total Blocks............................                            15,856
Total Bytes.............................                       129,892,352
Total MBytes............................                               123
Unused Blocks...........................                                 6
Unused Bytes............................                            49,152
Last Used Ext FileId....................                                 6
Last Used Ext BlockId...................                            29,705
Last Used Block.........................                               490

Security : Tablespace Encryption and Performance

2009-01-05T14:27:00.000-08:00

An issue with any encryption method is the negative impact on performance. For example, searches for patterns in the values in an encrypted column may or may not be able to use indexes. That is exactly where TDE tablespace encryption excels—it allows data to be in cleartext in the buffer cache, where all the searching occurs.

To accurately ascertain the performance impact of encryption in the tablespace, we can do a small test. First, let’s create one normal tablespace:

create tablespace normal_ts
datafile '/u01/oracle/database/
normal_ts.dbf'
size 1M autoextend on next 1M;

Next, let’s create two identical tables—ACCOUNTS_REG and ACCOUNTS_ENC—in tablespaces normal_ts and enc128_ts (the encrypted tablespace we created earlier), respectively, and populate the tables in the same way. Finally, let’s create an index on each of the tables on the FIRST_NAME column. The code for creating the tables and populating the data is available in the sample code download for this article.

With the data set up, we run a query against one table, ACCOUNTS_REG, to find all the first names starting with D. This query uses an index on the FIRST_NAME column, as shown in Listing 1. Next, we run the same query but replace ACCOUNTS_REG with ACCOUNTS_ENC, as shown in Listing 2. The execution time is approximately the same for the query in both the unencrypted and encrypted tablespaces, with about the same number of blocks fetched in both cases. This shows that there is no significant performance penalty for querying tables in encrypted tablespaces.

Code Listing 1: Index scan on table in unencrypted tablespace

SQL> set autot on explain stat
SQL> set timing on
SQL> select first_name
 2    from accounts_reg
 3    where first_name like 'D%'
 4    /

... the rows come here ...

50161 rows selected.

Elapsed: 00:05:36.38

Execution Plan
---------------------------
Plan hash value: 966430551

---------------------------------------------------------------------------------
| Id  | Operation        | Name         | Rows  | Bytes | Cost (%CPU)| Time     |
---------------------------------------------------------------------------------
|   0 | SELECT STATEMENT |              |   210K|  1442K|  513    (1)| 00:00:07 |
|*  1 |  INDEX RANGE SCAN|IN_ACC_REG_FN |   210K|  1442K|  513    (1)| 00:00:07 |
---------------------------------------------------------------------------------
Predicate Information (identified by operation id):
---------------------------------------------------

  1 - access("FIRST_NAME" LIKE 'D%')
       filter("FIRST_NAME" LIKE 'D%')


Statistics
--------------------------------
          1  recursive calls
          0  db block gets
       3458  consistent gets
        127  physical reads
...

Code Listing 2: Index scan on table in encrypted tablespace

SQL> set autot on explain stat
SQL> set timing on
SQL> select first_name
 2    from accounts_enc
 3    where first_name like 'D%'
 4    /

... the rows come here ...

Elapsed: 00:05:33.85

Execution Plan
---------------------------
Plan hash value: 399953395

-----------------------------------------------------------------------------------
| Id  | Operation         | Name          | Rows  | Bytes | Cost (%CPU)| Time     |
-----------------------------------------------------------------------------------
|   0 | SELECT STATEMENT  |               |   210K|  1442K|   513   (1)| 00:00:07 |
|*  1 |  INDEX RANGE SCAN | IN_ACC_ENC_FN |   210K|  1442K|   513   (1)| 00:00:07 |
-----------------------------------------------------------------------------------

Predicate Information (identified by operation id):
---------------------------------------------------

  1 - access("FIRST_NAME" LIKE 'D%')
       filter("FIRST_NAME" LIKE 'D%')


Statistics
--------------------------------
          1  recursive calls
          0  db block gets
       3427  consistent gets
        127  physical reads
...

Security : Tablespace Encryption Setup

2009-01-05T14:25:00.000-08:00

Let’s look at how to set up TDE tablespace encryption, using a file-based wallet. Note that the compatibility of the database must be set to 11.1 or higher. First, if you don’t have one, create the wallet:

1. Make sure the ORACLE_BASE variable has been set. If it has not, set it by issuing

$ export ORACLE_BASE=/opt/oracle

2. Change to the ORACLE_BASE directory and then to the admin subdirectory for that instance. In my case, the instance is named prolin1, so I issue

$ cd $ORACLE_BASE/admin/prolin1

3. Create a directory called “wallet” to hold the wallet:

$ mkdir wallet

4. Create the wallet, along with a secure password, preferably containing a mix of alphanumeric characters, such as “T45rustMe54”:

$ sqlplus / as sysdba
SQL> alter system set encryption key
identified by "T45rustMe54";

The password is case-sensitive.

The preceding step will create the wallet as well as open it. You need to create the wallet only once. After the database is opened, the wallet remains open until either the wallet is explicitly closed or the database is shut down, at which time the wallet automatically closes. You can reopen this wallet after the database is restarted, by using

SQL> alter system set wallet open
identified by "T45rustMe54";
System altered.

Now that the wallet is set up, you can create the encrypted tablespace.

1. The following code sets up an encrypted tablespace named enc128_ts:


create tablespace enc128_ts
datafile '/u01/oracle/database/
enc128_ts.dbf'
size 1M autoextend on next 1M
encryption using 'AES128'
default storage (encrypt)
/

Note the special encryption using 'AES128’ clause, which indicates that the AES algorithm is to be used with a 128-bit key. You can also use the values AES192 and AES256 (in place of AES128, the default value) to use 192- and 256-bit keys, respectively.

2. Once the tablespace is created, you can create objects in it. For instance, the following code creates a table called ACCOUNTS_ENC:

create table accounts_enc (
 ACC_NO        NUMBER           NOT NULL,
 FIRST_NAME  VARCHAR2(30)  NOT NULL,
... other columns ...
)
tablespace enc128_ts;

That’s it; no special clause is required. All the columns of the table (or anything else created in this tablespace) will be encrypted.

To confirm encryption, you can insert a record with a value “David” for FIRST_NAME and search the datafile you created for the enc128_ts tablespace for that value:


SQL> insert into accounts_enc values (1, 'David' , ...);
$ strings enc128ts_ts.dbf | grep David

This will not show any output, because the “David” value has been stored in an encrypted tablespace. Searching for a cleartext value found nothing, as expected. (Note that if you do a string search in a file of an unencrypted tablespace, a successful search will return the cleartext from the file.)

Security : Encrypting Tablespaces - Enter TDE Encrypted Tablespaces

2009-01-05T14:16:00.000-08:00

The Oracle Advanced Security option for Oracle Database 11g introduces a nifty new TDE feature—tablespace encryption—that allows a whole tablespace to be encrypted and therefore addresses the range scan and foreign key limitation of column-level encryption. The new tablespace is created as encrypted, and the data, whether tables or indexes, is stored encrypted in that tablespace, as shown in Figure 1. When a user selects that data, the server process moves the data from storage to the buffer cache and subsequently to the user’s session. The data is decrypted before being loaded into the buffer cache, so it’s always in cleartext (unencrypted) inside the buffer cache. Almost all types of data access—including index scans, table joins, and so on—happen in the buffer cache, so the performance of operations involving data from the encrypted tablespace is no different from that involving data from a normal, unencrypted tablespace.

Encryption requires at least two things: an encryption key and an algorithm. TDE uses what is known as a two-tier key architecture: both column and tablespace encryption keys are stored in the database but are encrypted with another key called the master key. The master key is stored outside the database in a special container called an external security module, which can be something as easy to set up as an Oracle wallet or as sophisticated as a hardware security module device. The Oracle wallet is a file formatted according to Public Key Cryptography Standard No. 12 and encrypted with a password. For using the wallet as the external security module, a password must be provided to make the master key accessible to the database. Unless the right password is supplied, the wallet can’t be opened and the encrypted data can’t be retrieved. The wallet is automatically closed when the database instance is shut down and must be reopened by a security officer when the instance starts. So although thieves might be able to restore a database from tapes, without the wallet and the password, they will not be able to view the encrypted data. (In the hardware security module case, the hardware device must be made available to the database in a manner specified by the vendor of the device.)

Figure 1: Loading of data buffers

With tablespace encryption, before data buffers are written back to disk (as a result of the checkpoint process), they are encrypted by DB Writer processes (DBWn), as shown in Figure 2. Operations, such as direct path inserts and reads that manipulate the data directly in the database, perform encryption inline. When the log buffers are written to the redo logs by the log writer process, they are encrypted as well, so the initial and subsequently archived redo logs contain only encrypted data.

Figure 2: Flushing of buffers from cache to disk

Encrypting Tablespaces

2009-01-05T05:57:00.000-08:00

Encrypt tablespaces transparently—without affecting performance.

In an era when data security is critical in almost every type of business, databases are particularly important. This is where the organization’s crown jewels live—credit card numbers, Social Security information, names and addresses—the list is endless. The data security risk increases when business data leaves the security of an organization’s databases in the form of backup tapes. Historically, these offsite tapes have been the source of many data thefts. And because the tapes can be restored on any equivalent machine, attackers can browse the data at their leisure, and there is nothing the organization can do to protect the data then.

How can you prevent this from happening? The simple answer is encryption—encrypt the data in the database and store the encryption key in a different place. Thieves might be able to access the backup tapes and restore the database on a different server, but without the encryption key, they won’t be able to see the data.

Oracle introduced transparent data encryption (TDE) column encryption in Oracle Advanced Security with the release of Oracle Database 10g Release 2. (For more information, see “Transparent Data Encryption” in the September/October 2005 issue of Oracle Magazine.) Although this feature enables you to encrypt columns of a table without writing a single piece of code and helps with several key compliance regulations, it can negatively affect performance in applications when an encrypted column is used in range scans or as a foreign key. Column encryption is performed transparently within the database SQL layer, and indexes on an encrypted column are built on the encrypted values. Because encrypted values look random and disconnected, range scan operations on encrypted columns are not effective. Although column encryption occurs quickly, the range scan limitation is a real challenge in many real-world situations.

Oracle Database 10g: New Features for Administrators - 1Z0-040 #5

2009-01-04T14:27:00.000-08:00

QUESTION 81:
You want to unload data from the ORDERS, ORDER_ITEMS, and PRODUCTS
database tables to four os flat files by using the External
Table Population methods. To achieve this objective, you
execute the following command:
Create TABLE orders_ext
(order_id, order_date, product_id, product_name,quantity)
ORGANIZATION EXTERNAL
(
TYPE ORACLE_DATAPUMP
DEFAULT DIRECTORY ext_dir
LOCATION ('orders1.dmp','orders2.dmp','orders3.dmp','
orders4.dmp')
]
PARALLEL
AS
SELECT
o.order_id,o.order_date,p.product_id,p.product_name,i.quanity
FROM orders o,products p,order_items i
WHERE o.order_id = i.order_id and i.product_id =
p.product_id;
After successful execution of the command, you find that only two files have been
created and not four.
Which option do you need to change to achieve your objective?
A. TYPE
B. LOCATION
C. PARALLEL
D. DEFAULT DIRECTORY
E. ORGANIZATION EXTERNAL
Answer: C

QUESTION 82:
Which two statements are correct regarding renaming a tablespace? (Choose two)
A. You cannot rename a dictionary-managed tablespace.
B. When you rename a tablespace, all the data files are also renamed accordingly.
C. You cannot rename a tablespace that happens to be the default permanent tablespace
of a user.
D. You can rename a tablespace provided that COMPATIBILITY initialization
parameter is set to at least 10.0.0.0.0.
E. When you rename a tablespace, the Oracle 10g database updates all references to the
tablespace name in the data dictionary as well as the control file.
Answer: D, E
The COMPATIBLE parameter must be set to 10.0 or higher. When you rename a
tablespace the database updates all references to the tablespace name in the data
dictionary, control file, and (online) datafile headers. The database does not change the
tablespace ID so if this tablespace were, for example, the default tablespace for a user,
then the renamed tablespace would show as the default tablespace for the user in the
DBA_USERS view.
REF.: Oracle 10g Administrator Guide, 8-19

QUESTION 83:
You want to have a compatible audit trail of everything that has happened to the
jobs in the job class named JOB_CLASSA.
What should the logging level be for JOB_CLASSA?
A. DBMS_SCHEDULER.ENABLE
B. DBMS_SCHEDULER.LOGGING_OFF
C. DBMS_SCHEDULER.LOGGING_RUNS
D. DBMS_SCHEDULER.LOGGING_FULL
E. DBMS_SCHEDULER.LOGGING_COMPLETE
Answer: D

QUESTION 84:
The application tables owned by the user TEST in a test database need
to be exported to the
APPS schema in the production database by using Data Pump.
Which option of Data Pump Import would you use to accomplish this?
A. owner
B. touser
C. attach
D. fromuser
E. remap_schema
Answer: E
The REMAP_SCHEMA parameter changes the ownership of database objects. If you do
not specify REMAP_SCHEMA, all database objects (such as tables and indexes) are
created in the same user schema as in the source database, and those users must already
exist in the target database. If they do not exist, then
the import utility returns an error.
REF.: Oracle(r) 10g Administrator Guide , 8-36

QUESTION 85:
While observing database statistics, you found that the Automatic Workload
Repository (AWR) snapshots are being purged after each day. You want the
snapshots to be purged on a weekly basis.
Which two options would you use to increase the purge interval? (Choose two)
A. Enterprise Manager
B. DBMS_JOB.INTERVAL
C. DBMS_SCHEDULER.CHANGE
D. DBMS_SCHEDULER.INTERVAL
E. DBMS_WORKLOAD_REPOSITORY.MODIFY_SNAPSHOT_SETTINGS
Answer: A, E

QUESTION 86:
Consider the following configuration:
/devices/D1 is a member of disk group dgroupA.
/devices/D2 is a member of disk group dgroupA.
/devices/D3 is a member of disk group dgroupA.
You plan to add a new disk, /devices/D4, to the disk group dgroupA
and execute the following command:
SQL> ALTER DISKGROUP dgroupA ADD DISK '/devices/D4' ;
Which task would be accomplished by the command?
A. The command adds the new disk, D4 to the disk group.
B. The command would result in an error because there is no disk by the name
"/devices/D*".
C. The command would result in an error because no wildcard characters can be used in
the disk name.
D. The command will be ignored because disks starting with "D" are already members of
the disk group.
E. The command first detaches all the member disks starting with "D" and then
reattached all of them including the new disk.
Answer: A

QUESTION 87:
Which task would you perform to convert little-endian CLOB data to the big endian
format while transporting from Oracle9i database to Oracle 10g database?
A. Use the CHANGE command of the RMAN utility.
B. Use the CONVERT command of the RMAN utility.
C. Transport the data after changing the character set of the target database to big endian.
D. Transport the data after changing the character set to be an endian-independent format
in the target database.
E. No action is required because Oracle RDBMS implicitly does the conversion in Oracle
10g database while accessing the data.
Answer: B
If you are transporting the tablespace set to a platform with different endianness from the
source platform, you must convert the tablespace set to the endianness of the target
platform.Tablespaces can be transported from one platform to another. The RMAN
CONVERT command is used to do the conversion. The tablespaces being transported
can be either dictionary managed or locally managed. Starting with Oracle9 i, the
transported tablespaces are not required to be of the same block size as the target
database standard block size.
SQL> SELECT * FROM V$TRANSPORTABLE_PLATFORM;
PLATFORM_ID PLATFORM_NAME ENDIAN_FORMAT
----------- -------------------------------- --------------
1 Solaris[tm] OE (32-bit) Big
2 Solaris[tm] OE (64-bit) Big
7 Microsoft Windows IA (32-bit) Little
10 Linux IA (32-bit) Little
6 AIX-Based Systems (64-bit) Big
3 HP-UX (64-bit) Big
5 HP Tru64 UNIX Little
4 HP-UX IA (64-bit) Big
11 Linux IA (64-bit) Little
15 HP Open VMS Little
8 Microsoft Windows IA (64-bit) Little
9 IBM zSeries Based Linux Big
13 Linux 64-bit for AMD Little
16 Apple Mac OS Big
12 Microsoft Windows 64-bit for AMD Little
17 Solaris Operating System (x86) Little
REF.: Oracle(r) 10g Administrator Guide , 8-27

QUESTION 88:
The system was running a normal workload during the last 24 hours. You need to
ensure that with the same workload you should be able to flashbash tables by three
hours.
What would you do to find the system recommendations for the undo retention
period and the undo tablespace size?
A. Set DB_FLASHBACK_RETENTION_TARGET=10800
B. Create a new scheduler window for the time period of 24 hours and use the optimizer
statistics.
C. Specify Flashback Retention Time as three hours in the MTTR Advisor page and use
its recommendations.
D. Modify the Automatic Workload Repository (AWR) snapshot time interval to three
hours and use ADDM findings.
E. Specify New Undo Retention as three hours and Analysis Time period as Last One
Day in the Undo Advisor page and use its recommendations.
Answer: E

QUESTION 89:
To which three elements is the size of the block change tracking file proportional?
(Choose three)
A. Number of redo log groups.
B. Number of DBWR process.
C. Size of the database in bytes.
D. Number of archiving destinations.
E. Number of old backups maintained by the block change tracking file.
F. Number of enabled threads in Real Application Cluster (RAC) environment.
Answer: C, E, F
The size of the block change tracking file is proportional to:
* The database size in bytes: the block change tracking file contains data representing
every data file block in the database. The data is approximately 1/250000 of the total size
of the database.
* The number of enabled threads: In a Real Application Cluster (RAC) environment, the
instances update different areas of the tracking file without any locking or inter-node
block swapping. You enable block change tracking for the entire database and not for
individual instances.
* The number of old backups: The block change tracking file keeps a record of all
changes between previous backups, in addition to the modification since the last backup.
It retains the change history for a maximum of eight backups.
REF.: RMAN (
http://searchoracle.techtarget.com/searchOracle/downloads/10g_rman.doc)

QUESTION 90:
You executed the following SQL statement to shrink the EMPLOYEES table
segment stored in the EXAMPLE tablespace:
ALTER TABLE employee SHRINK SPACE CASCADE;
Which statement is correct in this scenario?
A. The data in the segment will be compacted but the high water mark will not be
adjusted.
B. The EMPLOYEES tablespace will be changed to read-only mode during the shrink
operation.
C. The indexes created on the EMPLOYEES table will need to be rebuilt after the shrink
operation is over.
D. The shrink behavior will be cascaded to all dependent segments of the table that
support a shrink operation.
E. Data manipulation language (DML) operations will not be possible on the
EMPLOYEE table during the COMPACTION phase of the shrink operation.
Answer: D
The CASCADE clause extends the segment shrink operation to all dependent segments
of the object. For example, if you specify CASCADE when shrinking a table segment, all
indexes of the table will also be shrunk.
REF.: Oracle 10g Administrator Guide 14-29

QUESTION 91:
Exhibit:

View the Exhibit and examine the Data Pump architecture.
Identify the numbered components.
A. 1 - Oracle Loader, 2 - Oracle Data Pump, 3 - Direct Path API
B. 1 - Oracle Data Pump, 2 - Direct Path API, 3 - Oracle Loader
C. 1 - Direct Path API, 2 - Oracle Loader, 3 - Oracle Data Pump
D. 1 - Oracle Loader, 2 - Direct Path API, 3 - Oracle Data Pump
E. 1 - Oracle Data Pump, 2 - Oracle Loader, 3 - Direct Path API
Answer: A

QUESTION 92:
How frequently does the Automatic Database Diagnostic Monitor (ADDM) analysis
run by default?
A. Every 30 minutes
B. Every 45 minutes
C. Every 15 minutes
D. Every 60 minutes
E. Each time a statspack snapshot is taken
Answer: D
ADDM is invoked automatically every time that a new AWR snapshot is generated (by
default every 60 minutes) and the results of the analysis are stored in the AWR repository
tagged with the snapshot id. Therefore, the DBA can find historical executions of the
advisor and its recommendations.
REF.:Note:276103.1 PERFORMANCE TUNING USING 10g ADVISORS AND
MANAGEABILITY FEATURES

QUESTION 93:
Which three statements regarding resumable statements are correct? (Choose
three)
A. Resumable statements cannot contain a distributed transaction.
B. Resumable statements can only be enabled at the system level.
C. Resumable statements can be enabled at the system level as well as the session level.
D. RESUMABLE_TIMEOUT=0 disabled resumable space allocation
for all sessions.
E. The ALTER SESSION ENABLE RESUMABLE command is obsolete in Oracle
Database 10g.
F. Only the local instance is affected when changing the resumable timeout parameter in
a distribution transaction.
Answer: C, D, F
C : At the system level:
ALTER SYSTEM SET RESUMABLE_TIMEOUT = 72000;
At the session level:
ALTER SESSION SET RESUMABLE_TIMEOUT = 72000;
E. ALTER SESSION ENABLE RESUMABLE ; is still supported in Oracle 10g

QUESTION 94:
Which two sources may be used for a running session by SQL Tuning Advisor
(STA)? (Choose two)
A. SQL Profiles
B. Recovery Manager (RMAN) Repository
C. Automatic Workload Repository (AWR)
D. Automatic Database Diagnostic Monitor (ADDM)
Answer: C, D
Explanation:
Not A: A SQL profile is a result of running the tuning advisor.

QUESTION 95:
Immediately after adding a new disk to or removing an existing disk from an ASM
instance, you find that the performance of the database goes down initially until the
time the addition or removal process is completed, and then gradually becomes
normal.
Which two activities would you perform to maintain a consistent performance of the
database while adding or removing disks? (Choose two)
A. Increase the number of checkpoint processes.
B. Define the POWER option while adding or removing the disks.
C. Increase the number of ARB processes by setting up a higher value for
ASM_POWER_LIMIT.
D. Increase the number of DBWR processes by setting up a higher value for
DB_WRITER_PROCSSES.
E. Increase the number of slave database writer processes by setting up a higher value for
DBWR_IO_SLAVES.
Answer: B, C
If the POWER clause is not specified in an ALTER DISKGROUP command, or when
rebalance is implicitly invoked by adding or dropping a disk, the rebalance power
defaults to the value of the ASM_POWER_LIMIT initialization parameter. You can
adjust this parameter dynamically. The higher the limit, the faster a rebalance operation
may complete. Lower values cause rebalancing to take longer, but consume fewer
processing and I/O resources. This leaves these resources available for other applications,
such as the database. The default value of 1 minimizes disruption to other applications.
The appropriate value is dependent upon your hardware configuration as well as
performance and availability requirements.
REF.: Oracle 10g Administrator Guide 12-9

QUESTION 96:
Which four statements regarding the block change track file are correct? (Choose
four)
A. The minimum size of this file is 10 MB.
B. The maintenance of this file is fully automatic.
C. The changed blocks are tracked in this file as redo is generated.
D. The location of this file must be different from that of the database files.
E. The Oracle database records block change information in this file by default.
F. The V$BLOCK_CHANGE_TRACKING view contains the name and location of this
file.
Answer: A, B,C, F
A - True. Should say "start" not "minimum". The size of the change tracking file is
proportional to the size of the database and the number of enabled threads of redo. The
size is not related to the frequency of updates to the database. Typically, the space
required for block change tracking is Overview of Reporting on Backups and the RMAN
Repository approximately 1/30,000 the size of the data blocks to be tracked.
B - True. Based on the book from the Oracle class Oracle Database 10g New Features for
Administrators - page 13-18
C - True. Changed blocks are tracked by the CTWR background process as redo is
generated. Database incremental backups, automatically use the change tracking file;
D - False. You can also create the change tracking file in a location you choose yourself,
using the following SQL statement:
SQL> ALTER DATABASE ENABLE BLOCK CHANGE TRACKING
USING FILE '/mydir/rman_change_track.f' REUSE;
E - False. Because this is not the default behavior of Oracle..
F - True. From SQL*Plus, you can query V$BLOCK_CHANGE_TRACKING.STATUS
to determine whether change tracking is enabled, and if it is, query
V$BLOCK_CHANGE_TRACKING.FILENAME to display the filename.
REF.: Oracle(r) Backup and Recovery Basics, 4-19, 4-20 and 4-21

QUESTION 97:
You define the Warning threshold for the tablespace usage metric for the USERS
tablespace to be 35%. Because of this, several alerts are
being raised. After observing this, you decide to increase
the Warning threshold and clear all old alerts related to
tablespace usage metric.
Where would you fund the information about cleared alerts?
A. The alert.log file
B. The DBA_ALERT_HISTORY data dictionary table
C. The DBA_ALERT_ARGUMENTS data dictionary table
D. The DBA_TAB_STATS_HISTORY data dictionary table
E. The DBA_OUTSTANDING_ALERTS data dictionary table
F. The V$ACTIVE_SESSION_HISTORY data dictionary table
Answer: B
The following dictionary views provide information about server alerts:
* DBA_THRESHOLDS lists the threshold settings defined for the instance.
* DBA_OUTSTANDING_ALERTS describes the outstanding alerts in the database.
*DBA_ALERT_HISTORY lists a history of alerts that have been cleared. (B)
* V$ALERT_TYPES provides information such as group and type for each alert.
* V$METRICNAME contains the names, identifiers, and other information about the
system metrics.
* V$METRIC and V$METRIC_HISTORY views contain system-level metric values in
memory.
REF.: Oracle 10g Administrator Guide, 4-21
REF.: Oracle 10g Administrator Guide, 4-21

QUESTION 98:
Which data dictionary view would you query to find the name of the default
permanent tablespace of the database?
A. DICTIONARY
B. DBA_TABLESPACES
C. DBA_DIRECTORIES
D. DBA_STORED_SETTINGS
E. DATABASE_PROPERTIES
F. DBA_TABLESPACE_GROUPS
Answer: E
SQL*Plus: Release 10.2.0.1.0 - Production on Sat Feb 4 20:31:26 2006
Copyright (c) 1982, 2005, Oracle. All rights reserved.
SQL> conn / as sysdba
Connected.
SQL> desc DATABASE_PROPERTIES
Name Null? Type
----------------------------------------- -------- --------------------
PROPERTY_NAME NOT NULL VARCHAR2(30)
PROPERTY_VALUE VARCHAR2(4000)
DESCRIPTION VARCHAR2(4000)
SQL> SELECT PROPERTY_NAME, PROPERTY_VALUE
2 FROM DATABASE_PROPERTIES
3WHERE PROPERTY_NAME='DEFAULT_PERMANENT_TABLESPACE;
PROPERTY_NAME PROPERTY_VALUE
---------- ----------------------------
DEFAULT_PERMANENT_TABLESPACE USERS

QUESTION 99:
Which three statements regarding the properties of a temporary tablespace group
are correct? (Choose three)
A. It is created explicitly.
B. It has the same namespace as tablespaces.
C. It contains at least one temporary tablespace.
D. It can contain a maximum of 1024 tablespaces in it.
E. It has to be removed manually after the last temporary tablespace is removed.
F. It is created automatically when the first temporary tablespace is assigned to it.
Answer: B, C, F
Using a tablespace group, rather than a single temporary tablespace, can alleviate
problems caused where one tablespace is inadequate to hold the results of a sort,
particularly on a table that has many partitions. A tablespace group enables parallel
execution servers in a single parallel operation to use multiple temporary tablespaces.
A - see F
B - It shares the namespace of tablespaces, so its name cannot be the same as any
tablespace.
C - It contains at least one tablespace. There is no explicit limit on the maximum number
of tablespaces that are contained in a group.
D - see C
E - The group is deleted when the last temporary tablespace it contains is removed from
it.
F - You do not explicitly create a tablespace group. Rather, it is created implicitly when
you assign the first temporary tablespace to the group.
REF.: Oracle 10g Administrator Guide 8-11

QUESTION 100:
The SQL Access Advisor may recommend additional structures to improve the
performance of SQL statements. You can select the type of structure recommended
by the SQL Access Advisor.
Which two structures can you select? (Choose two)
A. Indexes
B. Partitions
C. Synonyms
D. Sequences
E. Database links
F. Materialized views
G. Hash or index clusters
H. Index-Organized Table
Answer: A, F
Materialized views and indexes are essential when tuning a database to achieve optimum
performance for complex, data-intensive queries. The SQL Access Advisor helps you
achieve your performance goals by recommending the proper set of materialized views,
materialized view logs, and indexes for a given workload.
REF.: Oracle 10g Performance Tuning Guide 17-1

Data Warehousing and OLAP : Other New Features Covered Elsewhere

2009-01-03T23:11:00.001-08:00

There are several other new features in Oracle Database 11g that help in efficient design and operation of a data warehouse. These have already been covered in other installments of this series. Instead of repeating, I will give a very concise overview and direct you to those installments for details:

Partitioning. Oracle Database 11g allows a new type of range partitioning called Interval Partitioning, which allows you to merely define a value as an interval—for example, Oracle automatically creates partitions as needed every month based on the record getting inserted. Another type, Reference Partitioning, allows you to create partitions on the child tables based on a column that is present in parent table only, not in child tables. For instance, you can partition the EMP table based on department location, which is on the parent table (DEPT), but not in the EMP table. In addition, you can now create LIST-LIST, LIST-RANGE, and RANGE-RANGE composite partitioning schemes.

Virtual Columns

Advanced Compression. In addition to the compression functionality available in earlier releases, Oracle Database 11g now offers a new Advanced Compression option that performs compression differently. Instead of compressing the row as it comes, it compresses the entire block when a certain threshold is reached in the block. This makes the process extremely efficient and offers significant space advantages.
Pivot (and, of course, Unpivot). Crosstab reports are fairly common in any reporting environment, especially since the report format is the easiest to read and data to be presented. In earlier releases the crosstab reports could be done by joining the table to itself as many times as there are columns. This results in less performant queries, particularly in data warehouses where the tables are typically large. In Oracle Database 11g, a new operator called PIVOT transforms the output to be presented in a crosstab format. UNPIVOT allows the data already in the format to be reset back to a relational format.
Partition Advisor. One of the key points to consider in partitioning is which column to partition on. Many people, even seasoned professionals, stumble here. Oracle Database 11g makes this process easier by providing a new tool called Partition Advisor, which analyzes the workload pattern and offers performance-based suggestions on the partition scheme. While it may not be 100-percent perfect, it's definitely a boon for those who are completely unfamiliar with the process. And it's at least a starting point for those who are proficient.
Data Caching. When data does not change often, why keep refreshing it from disk? In Oracle Database 11g, you can cache the result of a query (not a table) in a special memory pool called the Result Cache. When the table data changes, the cache is automatically updated, sort of like a self-refreshed materialized view in memory. It's also possible to cache the results of a PL/SQL function in this result cache. Since the results come from memory instead of from disk, the response times are infinitesimally fast. In large databases such as data warehouses, where tables under most of the lookup queries do not change much, you can cache the queries and the function calls to get a boost in performance.
Invisible Indexes. Indexes can be made invisible, which makes them not used in access paths unless a special session parameter is set. However, the index is maintained as a normal index, affecting the performance of DMLs. This is a perfect way to examine the impact of an index without committing it. If performance is not desired, the index can be dropped.

Benefits of Oracle Certification

2008-12-14T22:48:00.000-08:00

The demand for professionals in the information technology (IT) industry is high, and the competition for jobs is intense. Individuals, experienced or new to the profession, need to know what skills make them attractive to employers. Employers look for ways to select prospective employees who have the solid foundation of skills needed for effective performance.

The Oracle Certification Program helps the IT industry establish a standard of competence in key entry-level and professional job roles.

An Oracle Certification is a valuable, industry-recognized credential that signifies a proven level of knowledge and ability. Each higher level of Oracle certification brings a higher standard of benchmarked skill and ability, which leads to greater opportunities and higher pay.

"Technical certifications have evolved from a hiring tool to a screening tool: If you don’t have them, you aren’t viewed as a serious candidate." ¹

Benefits to the Technical Professional

An Oracle Certified Associate (OCA) demonstrates a solid understanding of the foundation skills of a given job role, which can be applied at an apprentice or entry level.

By earning an OCA certification, you can have increased entry-level job opportunities. The OCA is the stepping-stone to starting a successful career as an Oracle professional.

Beyond OCA, by becoming an Oracle Certified Professional (OCP) you demonstrate your understanding of the full range of skills required by Oracle professionals in your chosen job role. An OCP is in high demand in today's marketplace, and the level of demand is expected to grow with each new installation of Oracle technologies around the world. An Oracle Certification helps raise your visibility and increases your access to the industry's most challenging opportunities.

"Oracle’s certification strength in a declining economy is due in part to the fact tha more complex, high-level t certifications appear to be less vulnerable" ²

An Oracle Certified Master (OCM) certification is the highest credential that an Oracle professional can earn in the Oracle Certification Program. An Oracle DBA Certified Master is a top-level Oracle expert who has in-depth technical knowledge of the Oracle data server and industry experience in managing mission-critical Oracle database systems and applications. OCMs are looked upon as experts within their organizations, among their peers, and across the industry.

The true value of earning an Oracle Certification credential is increased opportunity. With more opportunity come career growth and higher pay.

"Given the key importance of data as an organizational asset, it should come to no surprise that DBAs remain in high demand and that related training certification programs are popular even in this time of economic trouble... By the numbers, Oracle leads the DBMS/DBA certification area.” ³

Benefits to the IT Employer

The Oracle Certification Program is also valuable to hiring managers who want to distinguish among candidates for critical IT positions. For companies that send employees for annual IT training, certification helps ensure a return on the training investment by validating the knowledge and understanding gained during training sessions. Companies can also combine certification with an employee development program to enhance employee loyalty and performance on the job. Hiring certified professionals can have a direct impact on a company's success.

1 Source: Employers Raise the Bar on Certification, Information Week, 2002

2 Source: IDC Certification Report and Forecast 2002–2006

3 Source: Certified Expert: Working as a Database Administrator, CertMag, January 2003