|
Oracle 10g Fundamental Exam Questions
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.
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
?
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?
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?
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?
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?
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?
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?
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.’
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?
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?
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?
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.
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?
Security
VPD
in
the Oracle 10g database
Q:
What is VPD
in the Oracle 10g Database?
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?
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?
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
|