|
Oracle 10g Fundamental Exam Questions
|
“Honest
differences are often a healthy sign of progress.”
Mahatma
Gandhi
|
Q:
What is Regular Expression
(REGEXP) in the Oracle 10g Database?
Q:
What are functions of REGEXP?
Q:
What are the Meta-characters in REGEXP?
Q:
What are the Character Classes?
Q:
Consider a simple query to convert the ‘McLean’ city name to a more
readable format (Mc Lean). You should look for any instance for a lower case
letter immediately followed by an upper case letter. Your query should record
these two letters in back-references by using sub-expressions, then replaces the
first one, followed by a space, then followed by the second letter.
Q:
How to use REGULAR EXPRESSIONS in Oracle
Q:
What does the REGEXP_LIKE
function?
Q:
Consider to write an expression that could search for common inflections
of the verb ‘try’.
Q:
What does the REGEXP_SUBSTR
function?
Q:
Consider to write an expression that could return the ‘trying’
specified pattern.
Q:
What does the REGEXP_INSTR
function?
Q:
Consider to write an expression that could return the position of
‘trying’ specified pattern.
Q:
What does the REGEXP_REPLACE
function?
Q:
Query a list of all employees’ name that hired between 1996 and 1999.
Q:
What is occurrence in the REGEXP functions?
Q:
Consider extracting the third field being the Oracle system
identification in a column.
|
‘I
believe in the fundamental truth of all great religions of the world.
I believe that they are all God-given and I believe that they were
necessary for the people to whom these religions were revealed. And I
believe that if only we could all of us read the scriptures of the
different faiths from the standpoints of the followers of these faiths, we
should find that they were at bottom all one and were all helpful to one
another. ‘
Mahatma
Gandhi
|
UNDO Advisor
in
the Oracle 10g database
Q:
What is the UNDO advisor?
Q:
How do you check the time and problem on the UNDO tablespace
?
|
‘Hatred
ever kills, love never dies
such is the vast difference between the two. What is obtained by love is
retained for all time. What is obtained by hatred proves a burden in
reality for it increases hatred. ‘
Mahatma
Gandhi
|
The Oracle
Data Pump
Export and Import utilities
in the Oracle 10g database
Q:
Why do you use Data Pump
Export and Import?
Q:
Export the DEPT and EMP records that deptno is 10 or 30 from the ISELF
schema.
Q:
Export the iself, outln and system schemas.
Q:
How do you import the DEPT and EMP tables with recalculating statistics
and committing after each array insert?
Q:
Perform a Parallel Full Export on the DIR1, DIR2 directory objects and
make sure that each file be 2 GB in size.
Q:
Export only all functions, tables, procedures (proc1 and proc2 only), and
all views that starts with the ‘EMP’ characters from the iself and SCOTT
schemas.
Q:
Generate a SQL script from an existing export dump file.
Q:
Move objects from one tablespace to another by using the REMAP_TABLESPACE
option.
Q:
How can you read from your exported file directly without importing them
into your database?
Q:
What is an endian format?
|
‘There
are times when you have to obey a call which is the highest of all,
i.e. the voice of conscience even though such obedience may cost many a
bitter tear, and even more, separation from friends, from family, from the
state to which you may belong, from all that you have held as dear as life
itself. For this obedience is the law of our being.’
Mahatma
Gandhi
|
Q:
What is the Flash Recovery Area
?
Q:
How do you define a Flash Recovery Area
?
Q:
How do you use the V$RECOVERY_FILE_DEST
view to display information regarding the flash recovery area?
Q:
How can you display warning messages?
Q:
How do you backup the Flash Recovery Area
?
Q:
How to use the best practice to use Oracle Managed File (OMF) to let
Oracle database to create and manage the underlying operating system files of a
database?
Q:
How to enable Fast Incremental Backup to backup only those data blocks
that have changed?
Q:
How do you monitor block change tracking?
Q:
How do you use the V$BACKUP_DATAFILE
view to display how effective the block change tracking is in
minimizing the incremental backup I/O?
Q:
How do you backup the entire database?
Q:
How do you backup an individual tablespaces?
Q:
How do you backup datafiles and control files?
Q:
Use a fast recovery without restoring all backups from their backup
location to the location specified in the controlfile.
Q:
How can you begin and end backup on the database level?
Q:
How do you set the flash recovery area?
Q:
How do you gather information regarding the flash recovery area?
|
‘As
soon as we lose the moral basis, we
cease to be religious. There is no such thing as religion over-riding
morality. Man, for instance, cannot be untruthful, cruel or incontinent
and claim to have God on his side.’
Mahatma
Gandhi
|
The Flashback
feature in the Oracle 10g database
Q:
What is the flashback?
Q:
How it works?
Q:
Why do need to use the flashback?
Q:
What is its architecture?
Q:
How do you configure Flashback
Database?
Q:
How do you Flashback
a database?
Q:
How do you monitor Flashback
Database?
Q:
How do you use the V$FLASHBACK_DATABASE_LOG
view to determine how much disk space is needed to meet the
current flashback retention target?
Q:
How do you use the V$FLASHBACK_DATABASE_STAT
view to monitor the overhead of logging flashback data?
Q:
How do you exclude a tablespace from flashback database?
Q:
When are you not able to Flashback
Database?
Q:
How can you query the content of the recycle bin by using the
DBA_RECYCLEBIN
view?
Q:
How do you restore from recycle bin?
Q:
How do you reclaim the recycle bin?
Q:
How can you perform queries on the database as of a certain clock time or
SCN?
Q:
How can you use the CURRENT_SCN column in the V$DATABASE view to obtain
the current SCN?
Q:
How can you enforce to guaranteed UNDO retention?
Q:
How can you check the UNDO retention?
Q:
How can you recover deleted file?
Q:
How do you test that your recovery was successful?
|
‘Live
as if you were to die tomorrow. Learn as if you were to live forever.’
Mahatma
Gandhi
|
Q:
What does the Automatic Database Diagnostic Monitor
?
Q:
How does ADDM
work?
Q:
Where can I access the latest ADDM
run?
Q:
How can I turn the ADDM
process off?
Q:
How can you check your ADDM
default setting?
Q:
How can I retrieve ADDM
Reports using SQL?
Q:
What is the Automatic Shared Memory Management
(MMAN
)?
Q:
How do you enable or disable Automatic Shared Memory Management
?
Q:
How do you determine the actual size of the auto-tuned components in the
SGA?
Q:
How do you change the SGA_TARGET
value?
Q:
What is Automatic Checkpoint Tuning?
|
‘Fear
is not a disease of the body; fear kills the soul.’
Mahatma
Gandhi
|
Q:
What are the components of Oracle manageability Infrastructure?
Q:
Describe Automatic Routine Administration tasks:
Q:
Describe Server-Generated alerts
:
Q:
Describe Advisory Framework
:
Q:
What is Automatic Workload Repository
(AWR
)?
Q:
Manually invoke the ADDM
advisor to analyze the database between snapshots 60 and 66.
Then, use the task name to get the results from the analysis
Q:
As sysdba, create a special procedure to de-queue alert information from
the ALERT_QUE. Then give an object privilege to SYSTEM to use it.
Q:
Set the USER Commits Per Sec metric with a warning threshold set 3, and a
critical threshold set to 7. Your observation period should be for one minute,
and the number of consecutive occurrences should be set to two.
Q:
How do you examine your outstanding alerts and alert history?
Q:
How do you disable your ALERT_MYUSER and remove subscriber?
|
‘The
difference between what we do and what we are capable of doing would
suffice to solve most of the world's problems.’
Mahatma
Gandhi
|
Q:
What is the OPTIMIZER_DYNAMIC_SAMPING setting default?
Q:
How do you disable the Automatic PGA Memory Management?
Q:
How do you gather statistics on dictionary tables in the Oracle 10g
Database?
Q:
What is the Automatic Tuning Optimizer (ATO)?
Q:
How do you perform automatic SQL tuning?
Q:
How do you use the DBMS_SQLTUNE
package to create a tuning task by calling the
CREATE_TUNING_TASK
function?
Q:
How do you use the EXECUTE_TUNING_TASK procedure to start the tuning
process?
Q:
How do you call the REPORT_TUNING_TASK function to visualize the tuning
results?
Q:
How do you store a SQL profile in the data dictionary?
|
‘An
eye for eye only ends up making the whole world blind.’
Mahatma
Gandhi
|
Q:
How to do you use the DBMS_RESOURCE_MANAGER
package to tell the PMON process to kill sessions that are
idle for longer than 600 seconds and kill sessions that are idle for more than
300 seconds and are blocking other sessions. We assumed that you have already
defined your plan as ‘MY_DAY_PLAN’ and your group as ‘MY_FIRST_GROUP.’
|
‘The
Seven Deadly Sins.
Wealth without work, pleasure without conscience,
knowledge without character, commerce without morality, science without
humanity, worship without sacrifice, and politics without principle.’
Mahatma
Gandhi
|
Scheduler
in
the Oracle 10g database
Q:
What is the DBMS_SCHEDULER
package?
Q:
How do you monitor a Job using the Database Control page?
Q:
How do you add a Job using the Database Control page?
Q:
How to create a schedule named MY_SCHEDULE owned by ISELF that executes
every five seconds.
Q:
How to schedule a job that calls your created online-backup every night
at 10 p.m.?
Q:
Assuming that you have a procedure that collects information daily called
DAILY_DATA_GATHERING. Now, you should create a problem to call this procedure
and create a job to run it daily. How you do that?
|
‘When
I despair, I remember that all through history the ways of truth and
love have always won. There have been tyrants, and murderers, and for a
time they can seem invincible, but in the end they always fall. Think of
it - always.’
Mahatma
Gandhi
|
Q:
How an alert will raise or clear based on a tablespace size?
Q:
When do you need to enable row movement on a segment?
Q:
On the USERS tablespace, set a warning threshold of 80% and a critical
threshold of 95%.
Q:
How do you check the database-wide threshold values for the USERS
tablespace?
Q:
How do you turn off the space-usage tracking for the USER tablespace?
Q:
How do you reset the database-wide threshold values of the USERS
tablespace to the default database values?
Q:
How do you check the status of your threshold?
|
‘Woman
is the companion of man, gifted with equal mental capacities. She has
the right to participate in the minutest details in the activities of man,
and she has an equal right of freedom and liberty with him.’
Mahatma
Gandhi
|
Q:
What is a BIGFILE
tablespace?
Q:
How do you set the default tablespace type to BIGFILE
?
Q:
how do you display the default tablespace type?
Q:
Use the DBA_TABLESPACES
dictionary view to display whether all tablespace is bigfile
(YES) or smallfile (NO).
Q:
Use the V$TABLESPACE
dynamic view to display whether all tablespace is bigfile
(YES) or smallfile (NO).
Q:
What are the difference between a BIGFILE
rowid and a small file rowid?
Q:
Create a temporary tablespace group that it consists of only temporary
tablespaces.
Q:
Use the DBA_TABLESPACE_GROUPS
view to display all tablespace associated to their groups.
Q:
Create a tablespace with a BIGFILE
default tablespace type.
Q:
Can you add more datafiles?
Q:
How do you get a BIGFILE
ROWID?
|
‘The
only virtue I want to claim is truth and non-violence. I lay no claim
to superhuman powers. I want none. I wear the same corruptible flesh that
the weakest of my fellow beings wears, and am therefore as liable to err
as any. My services have many limitations, but God has up to now blessed
them in spite of the imperfections.’
Mahatma
Gandhi
|
General
Storage
Enhancements in
the Oracle 10g database
Q:
What is System Auxiliary Tablespace in the Oracle 10g Database?
Q:
What does the SYSAUX tablespace
hold?
Q:
How can you monitor the space usage of each occupant inside the SYSAUX
tablespace
?
Q:
What are the Mandatory attributes of SYSAUX?
Q:
What are its benefits?
Q:
Can you rename SYSTEM or SYSAUX tablespaces?
Q:
How do you rename a tablespace?
Q:
Can you change the default Permanent Tablespace in the Oracle 10g
database?
Q:
How can you check what your default permanent tablespace is?
Q:
Change your default tablespace to USERS.
Q:
How can you copy files without using system operating system commands?
Q:
What does the Redo Logfile size advisor
in the Oracle 10g database?
Q:
Make the retention period for one day with an interval off “0” which
switches off snapshot collection.
|
‘Non-violence
is the greatest force
at the disposal of mankind. It is mightier than the mightiest weapon of
destruction devised by the ingenuity of man.’
Mahatma
Gandhi
|
Q:
What does ASM
provide?
Q:
What are its key features and benefits?
Q:
Does ASM
have a data dictionary?
Q:
How do you create a disk group by using ASM
?
Q:
How do you delete a disk group by using ASM
?
Q:
How do you add a disk to an existing disk group?
Q:
How do you remove a disk to an existing disk group?
Q:
Can you undo the removed disk? How?
Q:
How do you display a list of your diskgroups?
Q:
How do you display a list of associated ASM
disks?
Q:
How do you display a list of associated ASM
files?
Q:
How do you create a tablespace that uses an ASM
disk group?
Q:
How do you add one addition disk to your system?
|
‘It
is easy enough to be friendly to one's friends. But to be friend the one
who regards himself as your enemy is the quintessence of true religion.
The other is mere business.’
Mahatma
Gandhi
|
Security
VPD
in
the Oracle 10g database
Q:
What is VPD
in the Oracle 10g Database?
|
‘I object
to violence because when it appears to do good, the good is only
temporary; the evil it does is permanent.’
Mahatma
Gandhi
|
Q:
How do you use the V$FAST_START_TRANSACTIONS
view to monitor (in real-time) normal transaction rollback and
transaction recovery by SMON?
Q:
How do you use the V$FAST_START_SERVERS
view to display historical information about transaction
recovery?
Q:
How do you use the DBA_ENABLED_TRACES
view to display enabled and disabled trace?
Q:
What is case-insensitive sorting in the Oracle 10g?
Q:
What is Quote Operator q?
Q:
What does the UTL_MAIL package?
|
‘Whenever
I see an erring man, I say to myself I have also erred; when I see a
lustful man I say to myself, so was I once; and in this way I feel kinship
with everyone in the world and feel that I cannot be happy without the
humblest of us being happy.’
Mahatma
Gandhi
|
Q:
What is the Oracle 10g Database Grid?
Q:
List some of the most important features of the Oracle 10g Grid.
Q:
What is OEM 10g?
Q:
How do you stop the Oracle Management Server?
Q:
How do you start the Oracle Management Server?
Q:
How do you get the status of the Oracle Management Server?
Q:
How do you start EM Database Control
?
Q:
How do you stop the EM Database Control
?
Q:
How do you get the status of the EM Database Control
?
Q:
What is the default port for your Web Browser?
|
‘Always
aim at complete harmony of thought and word and deed. Always aim at
purifying your thoughts and everything will be well.’
Mahatma
Gandhi
|
Navigate
the Oracle Enterprise Manager Grid Control in the Oracle 10g database
Q:
How do you create an Oracle user by using the EM Database Control
?
Q:
How do you export tables from a schema by using the EM Database Control
?
Q:
How do you import tables from a schema by using the EM Database Control
?
Q: How do you retrieve the latest ADDM
and
determine the cause of the problem by using the EM Database Control
?
Q: How do you shutdown and startup your databases using an
initialization parameter file by using the EM Database Control
?
Q: How do you check or resize the size of the various SGA
buffers using the EM Database Control
and how
to enable the Automatic Shared Memory Management
by using
the EM Database Control?
Q: How do you look at the corresponding metrics graphic
rate by using the EM Database Control
?
Q: How do you find a high load SQL statement and fix it by
looking at an ADDM
report
analysis during the problem by using the EM Database Control
?
Q: How do you determine the most important wait category
from the Sessions: Waiting and Working graph by using the EM Database Control
?
Q: How do you use the SQL Access Advisor
to
generate recommendations for a SQL tuning set by using the EM Database Control
?
Q: How do you create a job and schedule it to run by using
the EM Database Control
?
Q: How do you verify that your schedule was created by
using the EM Database Control
?
Q: How do you change the Tablespace Space Usage thresholds
of a tablespace by using the EM Database Control
?
Q: How do you run the Segment Advisor
for a
tablespace by using the EM Database Control
?
Q: How do you use the Undo Advisor to get recommendations
to size your UNDO tablespace
by using
the EM Database Control
?
Q: How do you change the size of flash recovery area by
using the EM Database Control
?
Q: How do you backup your whole database to tape by using
the EM Database Control
?
Q: How do you use the flashback feature to recover a
dropped table by using the EM Database Control
?
Answers
|
