Oracle DBA Questions And Answers

1⟩ When using Oracle export/import what character set concerns might come up? How do you handle them?

Be sure to set NLS_LANG for example to "AMERCIAN_AMERICA.WE8ISO8859P1". If your source database is US7ASCII, beware of 8-bit characters. Also be wary of multi-byte characters sets as those may require extra attention. Also watch export/import for messages about any "character set conversions" which may occur.

Is this answer helpful? 0 Yes | 0 No

Answer This Question

169 views

2⟩ How do you use automatic PGA memory management with Oracle 9i and above?

Set the WORKAREA_SIZE_POLICY parameter to AUTO and set PGA_AGGREGATE_TARGET

Is this answer helpful? 0 Yes | 0 No

Answer This Question

151 views

3⟩ Explain two easy SQL optimizations.

a. EXISTS can be better than IN under various conditions

b. UNION ALL is faster than UNION (not sorting)

Is this answer helpful? 0 Yes | 0 No

Answer This Question

143 views

4⟩ Name three SQL operations that perform a SORT.

a. CREATE INDEX

b. DISTINCT

c. GROUP BY

d. ORDER BY

f. INTERSECT

g. MINUS

h. UNION

i. UNINDEXED TABLE JOIN

Is this answer helpful? 0 Yes | 0 No

Answer This Question

140 views

5⟩ What is your favorite tool for day-to-day Oracle operation?

Hopefully we hear some use of command line as the answer!

Is this answer helpful? 0 Yes | 0 No

Answer This Question

155 views

6⟩ What is the difference between Truncate and Delete? Why is one faster? Can we ROLLBACK both? How would a full table scan behave after?

Truncate is nearly instantaenous, cannot be rolled back, and is fast because Oracle simply resets the HWM. When a full table scan is performed on a table, such as for a sort operation, Oracle reads to the HWM. So if you delete every single solitary row in 10 million row table so it is now empty, sorting on that table of 0 rows would still be extremely slow.

Is this answer helpful? 0 Yes | 0 No

Answer This Question

166 views

7⟩ What is the difference between a materialized view (snapshot) fast refresh versus complete refresh? When is one better, and when the other?

Fast refresh maintains a change log table, which records change vectors, not unlike how the redo logs work. There is overhead to this, as with a table that has a LOT of indexes on it, and inserts and updates will be slower. However if you are performing refreshes often, like every few minutes, you want to do fast refresh so you don't have to full-table-scan the source table. Complete refresh is good if you're going to refresh once a day. Does a full table scan on the source table, and recreats the snapshot/mview. Also inserts/updates on the source table are NOT impacted on tables where complete refresh snapshots have been created.

Is this answer helpful? 0 Yes | 0 No

Answer This Question

173 views

8⟩ What does the NO LOGGING option do? Why would we use it? Why would we be careful of using it?

It disables the logging of changes to the redologs. It does not disable ALL LOGGING, however as Oracle continues to use a base of changes, for recovery if you pull the plug on the box, for instance. However it will cause problems if you are using standby database. Use it to speed up operations, like an index rebuild, or partition maintenance operations.

Is this answer helpful? 0 Yes | 0 No

Answer This Question

167 views

9⟩ Tell me about standby database? What are some of the configurations of it? What should we watch out for?

Standby databases allow us to create a copy of our production db, for disaster recovery. We merely switch mode on the target db, and bring it up as read/write. Can setup as master->slave or master->master. The latter allows the former prod db to become the standby, once the failure cause is remedied. Watch out for NO LOGGING!! Be sure we're in archivelog mode.

Is this answer helpful? 0 Yes | 0 No

Answer This Question

174 views

10⟩ What is the difference between RMAN and a traditional hotbackup?

RMAN is faster, can do incremental (changes only) backups, and does not place tablespaces into hotbackup mode.

Is this answer helpful? 0 Yes | 0 No

Answer This Question

144 views

11⟩ What are bind variables and why are they important?

With bind variables in SQL, Oracle can cache related queries a single time in the SQL cache (area). This avoids a hard parse each time, which saves on various locking and latching resources we use to check objects existence and so on. BONUS: For rarely run queries, especially BATCH queries, we explicitely DO NOT want to use bind variables, as they hide information from the Cost Based Opitmizer.

BONUS BONUS: For batch queries from 3rd party apps like peoplesoft, if we can't remove bind variables, we can use bind variable peeking!

Is this answer helpful? 0 Yes | 0 No

Answer This Question

151 views

12⟩ In PL/SQL, what is bulk binding, and when/how would it help performance?

Oracle's SQL and PL/SQL engines are separate parts of the kernel which require context switching, like between unix processes. This is slow, and uses up resources. If we loop on an SQL statement, we are implicitely flipping between these two engines. We can minimize this by loading our data into an array, and using PL/SQL bulk binding operation to do it all in one go!

Is this answer helpful? 0 Yes | 0 No

Answer This Question

149 views

13⟩ Why is SQL*Loader direct path so fast?

SQL*Loader with direct path option can load data ABOVE the high water mark of a table, and DIRECTLY into the datafiles, without going through the SQL engine at all. This avoids all the locking, latching, and so on, and doesn't impact the db (except possibly the I/O subsystem) at all.

Is this answer helpful? 0 Yes | 0 No

Answer This Question

137 views

14⟩ What are the tradeoffs between many vs few indexes? When would you want to have many, and when would it be better to have fewer?

Fewer indexes on a table mean faster inserts/updates. More indexes mean faster, more specific WHERE clauses possibly without index merges.

Is this answer helpful? 0 Yes | 0 No

Answer This Question

165 views

15⟩ What is the difference between RAID 5 and RAID 10? Which is better for Oracle?

RAID 5 is striping with an extra disk for parity. If we lose a disk we can reconstruct from that parity disk.

RAID 10 is mirroring pairs of disks, and then striping across those sets.

RAID 5 was created when disks were expensive. Its purpose was to provide RAID on the cheap. If a disk fails, the IO subsystem will perform VERY slowly during the rebuild process. What's more your liklihood of failure increases dramatically during this period, with all the added weight of the rebuild. Even when it is operating normally RAID 5 is slow for everything but reading. Given that and knowing databases (especially Oracle's redo logs) continue to experience write activity all the time, we should avoid RAID5 in all but the rare database that is MOSTLY read activity. Don't put redologs on RAID5.

RAID10 is just all around goodness. If you lose one disk in a set of 10 for example, you could lose any one of eight other disks and have no troubles. What's more rebuilding does not impact performance at all since you're simply making a mirror copy. Lastly RAID10 perform exceedingly well in all types of databases.

Is this answer helpful? 0 Yes | 0 No

Answer This Question

167 views

16⟩ How do I write a cron which will run a SQL query and mail the results to a group?

Use DBMS_JOB for scheduling a cron job and DBMS_MAIL to send the results through email.

Is this answer helpful? 0 Yes | 0 No

Answer This Question

152 views

17⟩ What operator performs pattern matching

LIKE operator

Is this answer helpful? 0 Yes | 0 No

Answer This Question

169 views

18⟩ What is the use of the DROP option in the ALTER TABLE command?

It is used to drop constraints specified on the table.

Is this answer helpful? 0 Yes | 0 No

Answer This Question

152 views

19⟩ what is difference between Co-related sub query and nested sub query?

Co-related sub query is one in which inner query is evaluated only once and from that result outer

query is evaluated.

Nested query is one in which Inner query is evaluated for multiple times for getting one row of that

outer query.

ex. Query used with IN() clause is Co-related query.

Query used with = operator is Nested query

Is this answer helpful? 0 Yes | 0 No

Answer This Question

144 views

20⟩ Which date function is used to find the difference between two dates?

MONTHS_BETWEEN

Is this answer helpful? 0 Yes | 0 No

Answer This Question

162 views

Oracle DBA

Home Oracle Oracle DBA

50 Oracle DBA Questions And Answers

1⟩ When using Oracle export/import what character set concerns might come up? How do you handle them?

2⟩ How do you use automatic PGA memory management with Oracle 9i and above?

3⟩ Explain two easy SQL optimizations.

4⟩ Name three SQL operations that perform a SORT.

5⟩ What is your favorite tool for day-to-day Oracle operation?

6⟩ What is the difference between Truncate and Delete? Why is one faster? Can we ROLLBACK both? How would a full table scan behave after?

7⟩ What is the difference between a materialized view (snapshot) fast refresh versus complete refresh? When is one better, and when the other?

8⟩ What does the NO LOGGING option do? Why would we use it? Why would we be careful of using it?

9⟩ Tell me about standby database? What are some of the configurations of it? What should we watch out for?

10⟩ What is the difference between RMAN and a traditional hotbackup?

11⟩ What are bind variables and why are they important?

12⟩ In PL/SQL, what is bulk binding, and when/how would it help performance?

13⟩ Why is SQL*Loader direct path so fast?

14⟩ What are the tradeoffs between many vs few indexes? When would you want to have many, and when would it be better to have fewer?

15⟩ What is the difference between RAID 5 and RAID 10? Which is better for Oracle?

16⟩ How do I write a cron which will run a SQL query and mail the results to a group?

17⟩ What operator performs pattern matching

18⟩ What is the use of the DROP option in the ALTER TABLE command?

19⟩ what is difference between Co-related sub query and nested sub query?

20⟩ Which date function is used to find the difference between two dates?

Quick Links:

Oracle DBA

Home Oracle Oracle DBA

50 Oracle DBA Questions And Answers

1⟩ When using Oracle export/import what character set concerns might come up? How do you handle them?

2⟩ How do you use automatic PGA memory management with Oracle 9i and above?

3⟩ Explain two easy SQL optimizations.

4⟩ Name three SQL operations that perform a SORT.

5⟩ What is your favorite tool for day-to-day Oracle operation?

6⟩ What is the difference between Truncate and Delete? Why is one faster? Can we ROLLBACK both? How would a full table scan behave after?

7⟩ What is the difference between a materialized view (snapshot) fast refresh versus complete refresh? When is one better, and when the other?

8⟩ What does the NO LOGGING option do? Why would we use it? Why would we be careful of using it?

9⟩ Tell me about standby database? What are some of the configurations of it? What should we watch out for?

10⟩ What is the difference between RMAN and a traditional hotbackup?

11⟩ What are bind variables and why are they important?

12⟩ In PL/SQL, what is bulk binding, and when/how would it help performance?

13⟩ Why is SQL*Loader direct path so fast?

14⟩ What are the tradeoffs between many vs few indexes? When would you want to have many, and when would it be better to have fewer?

15⟩ What is the difference between RAID 5 and RAID 10? Which is better for Oracle?

16⟩ How do I write a cron which will run a SQL query and mail the results to a group?

17⟩ What operator performs pattern matching

18⟩ What is the use of the DROP option in the ALTER TABLE command?

19⟩ what is difference between Co-related sub query and nested sub query?

20⟩ Which date function is used to find the difference between two dates?

BE THE FIRST TO KNOW

Quick Links: