Oracle 10g Fundamental Exam Answers
NOTE:
The answers go with their sequences. If a question was not answer, that means
that it a repeating question and the answer was given by the previous questions
or it is not in the scope of this subject.
“Happiness
is when what you think, what you say, and what you do are in harmony.”
Mahatma Gandhi
|
Q:
What is Regular Expression
(REGEXP) in the Oracle 10g Database?
A:
It is a method for simple and complex patterns
for searching and manipulating a text. You can search, extract, format, and
manipulate a text in the database. At the beginning, it appears that the syntax
is not very intuitive but by second look, it may look easy. The technique more
reflects as UNIX style regular expressions.
Q:
What are functions of REGEXP?
A:
Interfaces:
Oracle Regular Expressions are implemented by the following functions available
in SQL and PL/SQL.
-
REGEXP_LIKE
,
-
REGEXP_REPLACE
,
-
REGEXP_INSTR
, and
-
REGEXP_SUBSTR
Q:
What are the Metacharacters in REGEXP?
Metacharacters:
The following is a list of supported Oracle metacharacters use in Oracle Regular
Expressions.
Syntax
|
Description
|
Classification
|
|
Match
any character
|
Dot
|
a?
|
Match
‘a’ zero or one time
|
Quantifier
|
a*
|
Match
‘a’ zero or more time
|
Quantifier
|
a+
|
Match
‘a’ one or more time
|
Quantifier
|
a|b
|
Match
either ‘a’ or ‘b’
|
Alternation
|
a{m}
|
Match
‘a’ exactly m times
|
Quantifier
|
a{m,}
|
Match
‘a’ at least m times
|
Quantifier
|
a{m,n}
|
Match
‘a’ between m and n times
|
Quantifier
|
[abc]
|
Match
either ‘a’ or ‘b’ or ‘c’
|
Bracket
Expression
|
(…)
|
Group
an expression
|
Subexpression
|
\n
|
Match
nth subexpression
|
Backreference
|
[:cc:]
|
Match
character class in bracket expression
|
Character
Class
|
[.ce.]
|
Match
collation element in bracket expression
|
Collation
Element
|
[=ec=]
|
Match
equivalence class in bracket expression
|
Equivalence
Class
|
Q:
What are the Character Classes?
A:
Character
Classes:
They are sensitive to the underlying character set such as the [:lower:]
character class.
The
following is a list of Oracle supports character classes
, based on character class definitions in NLS
classification data:
Character
Class Syntax
|
Meaning
|
[:alnum:]
|
All
alphanumeric characters
|
[:alpha:]
|
All
alphabetic characters
|
[:blank:]
|
All
blank space characters.
|
[:cntrl:]
|
All
control characters (nonprinting)
|
[:digit:]
|
All
numeric digits
|
[:graph:]
|
All
[:punct:], [:upper:], [:lower:], and [:digit:] characters.
|
[:lower:]
|
All
lowercase alphabetic characters
|
[:print:]
|
All
printable characters
|
[:punct:]
|
All
punctuation characters
|
[:space:]
|
All
space characters (nonprinting)
|
[:upper:]
|
All
uppercase alphabetic characters
|
[:xdigit:]
|
All
valid hexadecimal characters
|
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 backreferences by using subexpressions, then replaces the
first one, followed by a space, then followed by the second letter.
A:
SQL>
SELECT
REGEXP_REPLACE
(‘McLean’,
‘([[:lower:]])([[:upper:]])’, ‘\1 \2’) as “City”
FROM dual;
Q:
How to use REGULAR EXPRESSIONS in Oracle
A:
Keep this in your mind that these functions
support CHAR, VARCHAR2, CLOB, NCHAR, NVARCHAR, and NCLOB datatypes.
Q:
What does the REGEXP_LIKE
function?
A:
It returns a Boolean indicating whether the
pattern matched or not.
Q:
Consider to write an expression that could search for common inflections
of the verb ‘try’.
A:
The following regular expression will match
try, trying, tried, and tries.
SQL>
SELECT
REGEXP_LIKE
(‘We are trying to make
the subject easier.’,
‘tr(y(ing)? | (ied) | (ies))’) as REGEXT_SAMPLE
FROM dual;
Q:
What does the REGEXP_SUBSTR
function?
A:
It returns the actual data that matches the
specified pattern.
Q:
Consider to write an expression that could return the ‘trying’
specified pattern.
A:
SQL>
SELECT
REGEXP_SUBSTR
(‘We are trying to make
the subject easier.’,
‘tr(y(ing)? | (ied) | (ies))’) as REGEXT_SAMPLE
FROM dual;
Q:
What does the REGEXP_INSTR
function?
A:
It returns the character position of either
the beginning or end of the match.
Q:
Consider to write an expression that could return the position of
‘trying’ specified pattern.
A:
SQL>
SELECT
REGEXP_INSTR
(‘We are trying to make
the subject easier.’,
‘tr(y(ing)? | (ied) | (ies))’) as REGEXT_SAMPLE
FROM dual;
Q:
What does the REGEXP_REPLACE
function?
A:
It looks for an occurrence of a regular
expression and replaces it with the contents of a supplied text literal.
Q:
Query a list of all employees’ name that hired between 1996 and
1999.
A:
SQL>
SELECT ename FROM emp
WHERE REGEXP_REPLACE
(TO_CHAR(hire_date, ‘YYYY’), ‘^199[6-9]$’);
You
used ‘^’ to indicate that the beginning of the line has to be 199, and [-]
with $ to specify range of valid characters.
Q:
What is occurrence in the REGEXP functions?
A:
All functions take an occurrence that
specifies you require the nth matching expression in REGEXP_SUBSTR
and REGEXP_INSTR
, the default for which is 1.
Q:
Consider extracting the third field being the Oracle system
identification in a column.
A:
SQL>
SELECT
REGEXP_SUBSTR
(‘system/password@myhost:1521:mysid’,
‘[^:]+’, 1, 3) as “SID name”
FROM dual;
Q:
What is the UNDO advisor?
A:
You can size your UNDO tablespace
with the UNDO
Advisor
. The Snapshot Too Old
error will be
noted in the database alert history. Assuming that the UNDO tablespace is
UNDOTBS1, to check the time and problem on the UNDO tablespace do the following.
SQL>
SELECT time_suggested, reason
FROM dba_alert_history
WHERE object_name = ‘UNDOTBS1’
/
You
can use the Database Control home page to utilize the UNDO Advisor
to get
recommendations to correct the problem. From the Database Control home page,
click on the Administration tab and then UNDO Management. On that page change
the Analysis Time Period field to Last One Hour and click the Update Analysis
button. Now, you should see a recommendation from UNDO Advisor. You should be
able also to change the size and apply the changes.
Q:
How do you check the time and problem on the UNDO tablespace
?
A:
SQL>
SELECT time_suggested, reason
FROM dba_alert_history
WHERE object_name = ‘UNDOTBS1’
/
To
correct the problem: You can use the Database Control home page to utilize the
UNDO Advisor
to get
recommendations to correct the problem. From the Database Control home page,
click on the Administration tab and then UNDO Management. On that page change
the Analysis Time Period field to Last One Hour and click the Update Analysis
button. Now, you should see a recommendation from UNDO Advisor. You should be
able also to change the size and apply the changes.
Q:
Why do you use Data Pump
Export and Import?
A:
The expdp
and impdp
tools support all the
original exp and imp functionalities plus many new features. With previous
release, you could only move the transportable tablespace across Oracle
databases that were running on the same architecture and operating system. With
Data Pump
, you are able to transport data files from one
plateform to another. Only you have to make sure that both source and target
databases set their COMPATIBLE initialization parameter to 10.0.0 or greater
Q:
Export the DEPT and EMP records that deptno is 10 or 30 from the ISELF
schema.
A:
#
expdp
FILE=/u02/oradata/ora10g/EXPDAT02.DMP
FILESIZE=2048M
LOG=
/u02/oradata/ora10g/EXPDAT.LOG
TABLES=ISELF.CUSTOMER,ISELF.DEPT,ISELF.EMP
GRANTS=y
INDEXES=y
ROWS=y
CONSTRAINTS=y
CONSISTENT=n
RECORD=n
QUERY='WHERE
deptno IN (10, 30)'
Q:
Export the iself, outln and system schemas.
A:
#
expdp
FILE=/u02/oradata/ora10g/EXPDAT05.DMP
FILESIZE=2048M
LOG=
/u02/oradata/ora10g/EXPDAT.LOG
OWNER=ISELF,OUTLN,SYSTEM
GRANTS=y
INDEXES=y
ROWS=y
CONSTRAINTS=y
CONSISTENT=n
RECORD=n
Q:
How do you import the DEPT and EMP tables with recalculating statistics
and committing after each array insert?
A:
#
impdp
FILE=/u02/oradata/ora10g/EXPDAT.DMP
LOG=
/u02/oradata/ora10g/IMPORT.LOG
FROMUSER=iself
TABLES=emp,dept
GRANTS=y
INDEXES=y
ROWS=y
CONSTRAINTS=y
IGNORE=y
COMMIT=y
RECALCULATE_STATISTICS=y
DATAFILES=n
Q:
Perform a Parallel Full Export on the DIR1, DIR2 directory objects and
make sure that each file be 2 GB in size.
A:
$
expdp
FULL=y
PARALLEL=2
DUMPFILE=DIR1:exp1%U.dmp,
DIR2:exp2%U.dmp
FILESIZE=2G
The
%u implies that multiple files may be generated and start at 01 with a
two-digital number.
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.
A:
$
expdp
SCHEMAS=iself,scott
DIRECTORY=private_exp_space
DUMPFILE=expdat01.dmp
INCLUDE=function
INCLUDE=table
INCLUDE=procedure:”in
(‘proc1’,’proc2’)”
INCLUDE=view:”like
‘EMP%’”
Either
you should use INCLUDE or EXCLUDE.
Q:
Generate a SQL script from an existing export dump file.
A:
$
impdp
DIRECTORY=private_exp_space
DUMPFILE=expdat01.dmp
SQLFILE=MyScript.sql
Q:
Move objects from one tablespace to another by using the REMAP_TABLESPACE
option.
A:
$
impdp
SCHEMAS=iself
REMAP_TABLESPACE=iself_tablespace:urself_tablespace
Q:
How can you read from your exported file directly without importing them
into your database?
A:
SQL>
CREATE TABLE external_emp
(ename, sal, comm)
ORGANIZATION EXTERNAL
(
TYPE ORACLE_DATAPUMP
DEFAULT DIRECTORY private_exp_space
LOCATION ( ‘expdat01.dmp’)
)
PARALLEL AS
SELECT ename, sal, comm.
FROM emp WHERE deptno IN (10, 30);
Q:
What is an endian format?
A:
The endian format or Byte ordering is a format
that will affect the results when data is written and read. For example, the
2-bytes integer value 1 is written as 0x0001 on a big-endian system and as
0x0100 on a little-endian system. To determine the endian format of a platform
do the following query:
SQL>
SELECT p.endian_format
FROM v$transportable_platform p, v$database
d
WHERE p.platform_name = d.platform_name
/
The
v$transportable_platform view contains all supported platforms. In order to
convert form one platform to another platform uses the rman utility. The
following is an example of how to convert from one platform to another.
$
rman TARGET=/
RMAN> CONVERT DATAFILE ‘/local/oradata/school/*’
FROM PLATFORM = ‘Solari [tm] OE (32-bit)’
DB_FILE_NAME_CONVERT
=
‘/local/oradata/school/data’ , ‘/remote/oradata/data’;
Q:
What is the Flash Recovery Area
?
A:
It is a unified storage location for all
recovery-related files and activities in an Oracle Database. It includes Control
File, Archived Log Files, Flashback
Logs
, Control File Autobackups, Data Files, and RMAN
files.
Q:
How do you define a Flash Recovery Area
?
A:
To define a Flash Recovery Area
set the
following Oracle Initialization Parameters.
SQL>
ALTER SYSTEM SET db_recovery_file_dest_size = 100G;
SQL>
ALTER SYSTEM SET db_recovery_file_dest = ‘/u10/oradata/school’;
Q:
How do you use the V$RECOVERY_FILE_DEST
view to display information regarding the flash recovery area?
A:
SQL>
SELECT name, space_limit, space_used,
space_reclaimable, number_of_files
FROM v$recovery_file_dest;
Q:
How can you display warning messages?
A:
SQL>
SELECT object_type, message_type,
message_level, reason, suggested_action
FROM dba_outstanding_alerts
;
Q:
How do you backup the Flash Recovery Area
?
A:
RMAN>
BACKUP RECOVERY
FILES;
The
files on disk that have not previously been backed up will be backed up. They
are full and incremental backup sets, control file auto-backups, archive logs,
and datafile copies.
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?
A:
SQL>
ALTER SYSTEM SET
db_create_file_dest = ‘/u03/oradata/school’;
SQL>
ALTER SYSTEM SET
db_create_online_dest_1 = ‘/u04/oradata/school’;
Q:
How to enable Fast Incremental Backup to backup only those data blocks
that have changed?
A:
SQL>
ALTER DATABASE enable BLOCK CHANGE TRACKING;
Q:
How do you monitor block change tracking?
A:
SQL>
SELECT filename, status, bytes
FROM v$block_change_tracking
;
It
shows where the block change-tracking file is located, the status of it and the
size.
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?
A:
SQL>
SELECT file#, AVG(datafile_blocks), AVG(blocks_read),
AVG (blocks_read/datafile_blocks), AVG(blocks)
FROM v$backup_datafile
WHERE used_change_tracking = ‘YES’ AND incremental_level > 0
GROUP BY file#;
If
the AVG (blocks_read/datafile_blocks) column is high then you may have to
decrease the time between the incremental backups.
Q:
How do you backup the entire database?
A:
RMAN>
BACKUP DATABASE
;
Q:
How do you backup an individual tablespaces?
A:
RMAN>
CONFIGURE DEFAULT DEVICE
TYPE TO
DISK;
RMAN>
BACKUP TABLESPACE system;
Q:
How do you backup datafiles and control files?
A:
RMAN>
BACKUP DATAFILE
3;
RMAN>
BACKUP CURRENT CONTROLFILE;
Q:
Use a fast recovery without restoring all backups from their backup
location to the location specified in the controlfile.
A:
RMAN>
SWITCH DATABASE TO COPY;
RMAN
will adjust the control file so that the data files point to the backup file
location and then starts recovery.
Q:
How can you begin and end backup on the database level?
A:
SQL>
ALTER DATABASE BEGIN BACKUP
;
Copy
all the datafiles…
SQL>
ALTER DATABASE END BACKUP;
Q:
How do you set the flash recovery area?
A:
SQL>
ALTER SYSTEM SET db_recovery_file_dest_size = 100G;
SQL>
ALTER SYSTEM SET db_recovery_file_dest = ‘/u10/oradata/school’;
Q:
How do you gather information regarding the flash recovery area?
A:
SQL>
SELECT name, space_limit, space_used,
space_reclaimable, number_of_files
FROM v$recovery_file_dest;
Q:
What is the flashback?
A:
The flashback gives users the capability to
query past version of schema objects, query historical data, and perform change
analysis.
Q:
How it works?
A:
Every transaction logically generates a new
version of the database. You can navigate through these versions to find an
error and its cause.
Q:
Why do need to use the flashback?
A:
It eliminates restore process and it is faster
than traditional point-in-time recovery.
Q:
What is its architecture?
A:
Now, one more log was added as Flashback
Database
log. The Oracle database server regularly logs before images of data blocks in
the Flashback Database logs from Flashback buffer in the SGA Oracle memory. The
Flashback Database must be enabled. When it is enabled, the new RVWR (Recovery
Version Writer) background process will be started. The RVWR background process
sequentially writes Flashback Database data from the flashback buffer to the
Flashback Database logs which are circularly reused.
Q:
How do you configure Flashback
Database?
A:
Assuming:
The
database is in archive mode.
The
database flash recovery area was configured.
Set
the database flashback retention time target.
SQL>
ALTER SYSTEM SET db_flashback_retention_target
= 2880;
-- Ex: for two days.
Enable
Flashback
Database.
Before altering your database, the database must be in MOUNT EXCLUSIVE mode,
ARCHIVELOG mode, and the Flashback be enabled. To check whether it is enable do
the following SQL statement.
SQL>
SELECT flashback_on FROM v$database
;
SQL>
ALTER DATABASE FLASHBACK ON;
If
you disable the flashback (OFF), all existing Flashback
Database
logs are deleted automatically.
Q:
How do you Flashback
a database?
A:
The FLASHBACK DATABASE
command
force the database back to a past time or SCN. See the following examples:
SQL>
FLASHBACK DATABASE
TO
TIMESTAMP (sysdate-5/24); -- Go
back 5 hours from now.
SQL>
FLASHBACK DATABASE
TO SCN
65473;
Q:
How do you monitor Flashback
Database?
A:
Use the V$FLASHBACK_DATABASE_LOG
view to
display the approximate lowest SCN and time to which you can flash back your
database.
SQL>
SELECT oldest_flashback_scn, oldest_flashback_time
FROM v$flashback_database_log;
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?
A:
SQL>
SELECT estimated_flashback_size, flashback_size
FROM v$flashback_database_log;
Q:
How do you use the V$FLASHBACK_DATABASE_STAT
view to monitor the overhead of logging flashback data?
A: You can use this to adjust the retention time or the
flash recovery area size.
SQL>
SELECT * FROM v$flashback_database_stat;
Q:
How do you exclude a tablespace from flashback database?
A:
If you do not want the USER tablespace to be
included to log Flashback
Database
data, do the following SQL statement.
SQL>
ALTER TABLESPACE users FLASHBACK OFF;
Q:
When are you not able to Flashback
Database?
A:
-
The control file has been
restored or recreated,
-
A tablespace has been dropped,
-
A data file has been shrunk, and
-
A RESETLOSG operation is
required.
Q:
How can you query the content of the recycle bin by using the
DBA_RECYCLEBIN
view?
A:
SQL>
SELECT * FROM dba_recyclebin WHERE can_undrop = ‘YES’;
SQL>
SHOW RECYCLEBIN
Q:
How do you restore from recycle bin?
A:
Use the FLASHBACK TABLE
command
to recover a table and all its possible dependent objects form the recycle bin.
SQL>
DROP TABLE iself.emp;
SQL>
SELECT original_name, object_name, type, ts_name,
dropttime, related, space
FROM dba_recyclebin
WHERE original_name = ‘EMP’;
SQL>
FLASHBACK TABLE
emp TO
BEFORE DROP;
SQL>
FLASHBACK TABLE
emp
TO BEFORE DROP RENAME TO employee;
SQL>
FLASHBACK TABLE
emp
TO TIMESTAMP to_timestamp (’14:45’,’HH24:MI’);
Q:
How do you reclaim the recycle bin?
A:
By using PURG option.
SQL>
PURGE TABLE emp; -- Purges the
specified table.
SQL>
PURGE TABLESPACE scott_ts USER scott; --
All the Scott’s objects.
SQL>
PURGE RECYCLEBIN
;
-- Purges all user objects.
SQL>
PURGE DBA_RECYCLEBIN
; --
Purges all the objects.
Q:
How can you perform queries on the database as of a certain clock time or
SCN?
A:
SQL>
SELECT versions_xid, sal, versions_operation
FROM emp
VERSIONS BETWEEN TIMESTAMP sysdate-10/24 AND sysdate
WHERE empno = 100;
Q:
How can you use the CURRENT_SCN column in the V$DATABASE view to obtain
the current SCN?
A:
SQL>
SELECT current_scn FROM v$database
;
Q:
How can you enforce to guaranteed UNDO retention?
A:
You can do one of the following SQL
statements.
SQL>
CREATE UNDO TABLESPACE my_undotbs1
DATAFILE ‘my_undotbs01.dbf’ SIZE 10G AUTOEXTEND ON
RETENTION GUARANTEE;
SQL>
ALTER TABLESPACE my_undotbs1
RETENTION GUARANTEE;
Q:
How can you check the UNDO retention?
A:
SQL>
SELECT tablespace_name, retention FROM dba_tablespaces;
Q:
How can you recover deleted file?
A:
Connect
as sysdba and flashback the table.
SQL>
CONNECT / AS SYSDBA
Use
the FLASHBACK TABLE
command
to recover a table and all its possible dependent objects form the recycle bin.
Check
what do you have in your recycle bin.
SQL>
SELECT original_name, object_name, type, ts_name,
dropttime, related, space
FROM dba_recyclebin
WHERE original_name = ‘FLASHBACK_TABLE’
/
SQL>
FLASHBACK TABLE
iself.emp
TO BEFORE DROP;
Q:
How do you test that your recovery was successful?
A:
SQL>
SELECT count(*) FROM flashback_table;
Q:
What does the Automatic
Database Diagnostic Monitor
?
A:
The
Automatic Database Diagnostic Monitor
(ADDM
) maintains a self-diagnostic to a database. It will either
perform a treatment or refer it to specialists such as the SQL tuning advisor.
Q:
How does ADDM
work?
A:
The Oracle database automatically
gathers statistics from the SGA every 60 minutes and stores them in the
Automatic Workload Repository
(AWR
) in the form of snapshots. These snapshots are similar to
STATSPACK
snapshots.
The MMON process, it is a process that schedules the ADDM
to run
automatically to detect problems proactively for every two last snapshots. It is
possible also to invoke an ADDM analysis manually.
Q:
Where can I access the latest
ADDM
run?
A:
Go to the Database Control home page, on
the Diagnostic Summary section you will see the number of ADDM
finding from
the previous automatic run. Click on the Performance Findings link. The
Automatic Database Diagnostic Monitor
(ADDM)
page will be display with the details of the latest ADDM run.
Q:
How can I turn the ADDM
process off?
A:
By default the ADDM
process is
enabled since the STATISTICS_LEVEL initialization parameter is TYPICAL. By
setting these parameters to BASIC, it will stop to run automatically.
Q:
How can you check your ADDM
default setting?
A:
Execute
the following SQL statement.
SQL>
SELECT parameter_value, is_default
FROM dba_advisor_def_parameters
WHERE advisor_name = ‘ADDM
’
/
Q:
How can I retrieve ADDM
Reports using SQL?
A:
You should type the following SQL statement to
display the most recent ADDM
report using a
SQL command.
SQL>
SELECT dbms_advisor
.GET_TASK_REPORT(task_name)
FROM dba_advisor_tasks
WHERE task_id = (SELECT max(t.task_id)
FROM dba_advisor_tasks
t,
dba_advisor_log l
WHERE t.task_id = l.task_id AND t.advisor_name = ‘ADDM
’
AND
l.status = ‘COMPLETED’
/
or
SQL>
@$ORACLE_HOME/rdbms/addmrpt
Q:
What is the Automatic Shared Memory Management
(MMAN
)?
A:
It maintains the management of the most
important shared memory structures. For example, if your system runs OLTP
during the day
and large parallel batch jobs at night, you may not need to decrease buffer
cache and increase large pool in order to satisfy the needs of your nightly
jobs. The MMAN
background
process should do that.
Q:
How do you enable or disable Automatic Shared Memory Management
?
A:
Go to your Database Control page. Click on the
Administration tab, select Memory Parameters under the Instance heading, and
click the SGA tab. Now, you are able to enable or disable. When you enable it,
you can enter the total SGA size or the SGA_TARGET
value.
If you set SGA_TARGET to 0, Automatic Shared Memory Management
will be
disabled.
Q:
How do you determine the actual size of the auto-tuned components in the
SGA?
A:
When the SGA_TARGET
value
is set to no-zero, you can determine the actual size of the auto-tuned
components in the SGA by the following SQL statement.
SQL>
SELECT component, current_size/1024/1024
FROM v$sga_dynamic_components
/
Notice
that if the SGA_TARGET
value
is no-zero and no value for an auto-tuned SGA parameter, then the values of the
auto-tuned SGA parameters in the v$parameter
view
is 0. You will see the values if you assigned a value for any of the auto-tuned
parameters.
SQL>
SELECT name, value, isdefault
FROM v$parameter
WHERE name LIKE ‘%size’
/
Q:
How do you change the SGA_TARGET
value?
A:
You can change it by using the ALTER SYSTEM
command dynamically. The value can be increased up to the value of SGA_MAX_SIZE
.
Q:
What is Automatic Checkpoint Tuning?
A:
It will make the best effort to write out
dirty buffers without adverse impact on the database automatically. To enable it
you should set the FAST_START_MTTR_TARGET
value to a
nonzero value and all the checkpoint parameters will be ignored.
Q:
What are the components of Oracle manageability Infrastructure?
A:
Automated tasks
Server-Generated alerts
Advisory Framework
Automatic Workload Repository
Q:
Describe Automatic Routine Administration tasks:
A:
You can use the Scheduler
, to submit a task that needs to be performed for keeping
the database in tune. To add a task, go to the Database Control home page, click
on the Administration tab, click the Jobs link in the Scheduler section, and
then create the task. You may add a task using PL/SQL. See the following
example: Assuming that you have already created the ONLINE_BKUP procedure to
perform online backup. You now want to add that task to the WEEKEND_WINDOW.
SQL>
BEGIN
DBMS_SCHEDULER
.CREATE_JOB
(
Job_name
=> ‘online_bkup’,
Job_type
=>
‘STORED_PROCEDURE’,
Job_action
=>
‘myonline_backup’,
Job_class
=>
‘AUTO_TASKS_JOB_CLASS
’,
Scheduler
_name=>
‘WEEKEND_WINDOW’);
END;
/
Q:
Describe Server-Generated alerts
:
A:
If a problem was detected, the Oracle server
will send an (email) alert message with possible corrective actions. The
difference between Enterprise Manager Alerts and Server-Generated alerts
is mainly that
the metrics threshold validations are performed by MMON, which unlike Enterprise
Manager should access SGA. These alerts appear in DBA_OUTSTANDING_ALERTS and,
when cleared, they go to DBA_ALERT_HISTORY. To set alert thresholds, go to
database home page, click Manage Metrics in the Related links section. Then
click the Edit Thresholds button. You can also use the DBMS_SERVER_ALERT
.SET_THRESHOLD
procedure. For example:
SQL>
BEGIN
DBMS_SERVER_ALERT
.SET_THRESHOLD (
DBMS_SERVER_ALERT
.CPU_TIME_PER_CALL,
DBMS_SERVER_ALERT
.OPERATOR_GE,
‘8000’,
DBMS_SERVER_ALERT
.OPERATOR_GE,
‘10000’, 1, 2, ‘school’,
DBMS_SERVER_ALERT
.OBJECT_TYPE_SERVICE,
‘payroll’);
END;
/
Q:
Describe Advisory Framework
:
A:
They are server components that provide a DBA
with useful feedback about a database resource utilization and performance. The
following are the list of advisors: ADDM
, SQL Tuning Advisor, SQL Access Advisor
, PGA Advisor, SGA Advisor, Segment Advisor
, and UNDO Advisor
. To open the Advisors Central page, go to the Database
Control home and click on the Advisor Central link in the list of Related Links.
The DBMS_ADVISOR package contains all constants and procedure declarations you
need for all advisors. There are Advisor views such as DBA_ADVISOR_{TASKS | LOG
| OBJECTS | RECOMMENDATIONS | ACTIONS}.
Q:
What is Automatic Workload Repository
(AWR
)?
A:
It provides services to Oracle components to
collect, maintain, process, and access performance statistics for problem
detection and self-tuning purposes. The MMON (Manageability Monitor) background
process will transfer the memory version of the statistics every 60 minutes to
disk on a regular basis and MMNL (Manageability Monitor Light) whenever the
buffer is full. The workload repository resides in the SYSAUX tablespace
. A baseline can be identified by executing the
DBMS_WORKLOAD_REPOSITORY.CREATE_BASELINE procedure. To run Automatic Workload
Repository
Reports run the
following SQL script.
SQL>
@$ORACLE_HOME/rdbms/admin/awrrpt
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
A:
Define a binding variable to hold the task
name and another variable to hold task id.
SQL>
VARIABLE tname VARCHAR2 (60)
SQL>
VARIABLE taskid NUMBER
Create
an advisor task of the particular ADDM
type.
SQL>
EXEC dbms_advisor
.create_task(‘ADDM
’, :taskid, :tname);
Set
the required parameters to run this specific type of task.
SQL>
EXEC dbms_advisor
.set_task_parameter(:tname, ‘START_SNAPSHOT’, 60);
SQL>
EXEC dbms_advisor
.set_task_parameter(:tname, ‘END_SNAPSHOT’, 66);
Execute
the task.
SQL>
EXEC dbms_advisor
.execute_task(:tname);
Use
the task name to get the results from the analysis.
SQL>
SELECT dbms_advisor
.get_task_report(:tname)
FROM dba_advisor_tasks
t
WHERE t.task_name = :tname AND
t.owner = SYS_CONTEXT (‘userenv’, ‘session_user’)
/
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.
A:
Logging into SQL*PLUS as sysdba
SQL>
CONNECT / as sysdba
Add
a new subscriber ALERT_MYUSERS to the internal ALERT_QUE queue.
SQL>
EXEC dbms_aqadm
.add_subscriber (
‘SYS.ALERT_QUE’, AQ$_AGENT(‘ALERT_MYUSERS’,’’,0));
Grant
user SYSTEM the right to dequeue from the ALERT_QUE.
SQL>
BEGIN
dbms_aqadm
.enable_db_access(
agent_name=>’ALERT_MYUSERS’,db_username=>’SYSTEM’);
END;
SQL>
BEGIN
dbms_aqadm
.grant_queue_privilege
(
Privilege=>’DEQUEUE’,
queue_name=>’ALERT_QUE’,
Grantee=>’SYSTEM’);
END;
Now,
write a Stored PL/SQL procedure that is used by user SYSTEM to dequeue alert
information from the ALERT_QUE.
SQL>
CREATE OR REPLACE PROCEDURE my_dequeue IS
dequeue_options
dbms_aq.dequeue_options_t;
message_properies
dbms_aq.message_properties_t;
message
ALERT_TYPE;
message_handle
RAW(16);
BEGIN
dequeue_options.consumer_name := ‘ALERT_MYUSERS’;
dequeue_options.wait := dbms_aq.no_wait;
dequeue_options.naviagtion := dbms_aq.first_message;
dequeue_options.dequeue_mode := dbms_aq.remove;
dbms_aq.dequeue (
queue_name
=>
‘SYS.ALERT_QUE’,
dequeue_options
=> dequeue_options,
message_properties
=> message_properties,
payload
=> message,
msgid
=> message_handle);
Dbms_output.put_line(‘This is my alert message dequeue…’);
END;
/
Grant
‘EXECUTE’ object privilege on MY_DEQUEUE to SYSTEM.
SQL>
GRANT EXECUTE ON MY_DEQUEUE TO SYSTEM;
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.
A:
SQL>
BEGIN
DBMS_SERVER_ALERT
.set_threshold (
DBMS_SERVER_ALERT
.user_commits_sec,
DBMS_SERVER_ALERT
.operator_ge, 3,
DBMS_SERVER_ALERT
.operator_ge, 7,
1,2, ‘school’,
DBMS_SERVER_ALERT
.object_type_system,
null);
END:
Check
that the metrics thresholds have been set.
SQL>
COL object_name FORMAT a30
SQL>
COL metrics_name FORMAT a30
SQL>
COL warning_value FORMAT a10
SQL>
COL critical_value FORMAT a10
SQL>
SELECT object_name, metrics_name, warning_value, critical_value
FROM dba_thresholds;
Q:
How do you examine your outstanding alerts and alert history?
A:
SQL>
SELECT reason FROM dba_outstanding_alerts
;
SQL>
SELECT reason FROM dba_alert_history
WHERE upper(reason) like ‘%COMMIT%’
ORDER BY creation_time desc
/
Q:
How to clean up your threshold set up?
A:
Do the following statement as sysdba. Set
threshold values to NULL.
SQL>
BEGIN
DBMS_SERVER_ALERT
.set_threshold (
DBMS_SERVER_ALERT
.user_commits_sec,
NULL,
NULL,
NULL,
NULL,
1, 1, ‘SCHOOL’,
DBMS_SERVER_ALERT
.object_type_system,
NULL);
END;
/
Q:
How do you disable your ALERT_MYUSER and remove subscriber?
A:
SQL>
EXEC dbms_aqadm
.disable_db_access(‘ALERT_MYUSER’,’SYSTEM’);
SQL>
BEGIN
dbms_aqadm
.remove_subscriber(
‘SYS.ALERT_QUE’,AQ$_AGENT(‘ALERT_MYUSER’,’’,0);
END;
/
Q:
What is the OPTIMIZER_DYNAMIC_SAMPING setting default?
A:
To enhance Query Optimization, the
OPTIMIZER_DYNAMIC_SAMPING is set to 2 by default.
Q:
How do you disable the Automatic PGA Memory Management?
A:
In order to disable the Automatic PGA Memory
Management set the parameter to 0.
Q:
How do you gather statistics on dictionary tables in the Oracle 10g
Database?
A:
In Oracle Database 10g, you can gather
statistics on dictionary tables (both fixed and real) to get the best
performance. You use the DBMS_STATS
.GATHER_DATABASE_STATS procedure with GATHER_SYS argument
set to TRUE or DBMS_STATS.GATHER_DICTIONARY_STATS. To use this, you should have
the ANALYZE ANY DICTIONARY system privilege. For example:
SQL>
BEGIN
DBMS_STATS
.GATHER_DATABASE_STATS(options=’GATHER AUTO’);
END;
/ -- Note: you should use GATHER only if you are
using release 8i
Q:
What is the Automatic Tuning Optimizer (ATO)?
A:
It is a SQL automatic tuning optimaizer. When
the optimizer is tuning a SQL statement using ATO, it is called Automatic SQL
Tuning.
Q:
How do you perform automatic SQL tuning?
A:
Create
a binding variable and then move your query into it.
SQL>
VARIABLE my_query VARCHAR2(1000)
SQL>
BEGIN
:my_query := ‘SELECT ename FROM iself.emp WHERE empno = 100;’
END;
/
Q:
How do you use the DBMS_SQLTUNE
package to create a tuning task by calling the
CREATE_TUNING_TASK
function?
A:
We use the DBMS_SQLTUNE
package
to create a tuning task by calling the CREATE_TUNING_TASK
function.
This procedure creates an advisor task and sets its corresponding parameters
according to the user-provided input arguments. To execute this you need one
more binding variable to keep your task name.
SQL>
VARIABLE my_task VARCHAR2(100)
SQL>
BEGIN
:my_task := DBMS_SQLTUNE
.create_tuning_task (
SQL_TEXT =>
:my_query,
BIND_LIST
=>
SQL_BINDS(anydata.ConvertNumber(100)),
USER_NAME =>
‘ISELF’,
SCOPE
=>
‘COMPREHENSIVE’,
TIME_LIMIT
=>
60,
TASK_NAME =>
‘my_tuning_task’,
DESCRIPTION =>
‘Query on EMP table …’);
END;
/
Q:
How do you use the EXECUTE_TUNING_TASK procedure to start the tuning
process?
A:
You need to invoke the EXECUTE_TUNING_TASK
procedure to start the tuning process.
SQL>
BEGIN
DBMS_SQLTUNE
.execute_tuning_task (TASK_NAME=>:my_task);
END;
Q:
How do you call the REPORT_TUNING_TASK function to visualize the tuning
results?
A:
The following is an example of how to call the
REPORT_TUNING_TASK function to visualize the tuning results.
SQL>
SQL> SELECT DBMS_SQLTUNE
.report_tuning_task
(TASK_NAME=>:my_task)
FROM dual;
Q:
How do you store a SQL profile in the data dictionary?
A:
When the SQL Tuning Advisor recommends a SQL
Profile, then create the SQL Profile by calling the ACCEPT_SQL_PROFILE function,
which stores it in the data dictionary. You should have the CREATE ANY SQL
PROFILE privilege.
SQL>
VARIABLE my_profile VARCHAR2(1000)
SQL>
BEGIN
:my_profile := DBMS_SQLTUNE
.accept_sql_profile
(TASK_NAME =>
’my_tuning_task’);
END;
/
SQL>
SELECT :my_profile FROM dual;
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.’
A:
SQL>
BEGIN
DBMS_RESOURCE_MANAGER
.create_plan_directive (
PLAN
=>
‘MY_DAY_PLAN’,
GROUP_OR_SUBPLAN
=> ‘MY_FIRST_GROUP’,
COMMET
=>
‘Limit user idle time’,
MAX_IDLE_TIME
=>
600,
MAX_IDLE_BLOCKER_TIME
=>
300);
END;
/
Q:
What is the DBMS_SCHEDULER
package?
A:
Oracle Database 10g provides scheduling
capabilities through the database Scheduler
. It uses the DBMS_SCHEDULER
package. The
Scheduler offers far more functionality than the DBMS_JOB
package. You
can create a job class a job class always belongs to the SYS schema. Since the
priority of jobs change over a period, now you can also create a window. For
example: you can create a window for the month of October that uses the
END_OF_YEAR plan and is active every day from 8:00 a.m. to 6:00 p.m. Eastern
standard Time (EST).
Q:
How do you monitor a Job using the Database Control page?
A:
Log in to EM Database Control
as the ISELF
user. From the Database Control home page click on the Administration tab. In
the ‘Scheduler
’ region, click the ‘Jobs’ link.
You
should see:
One
schedule, called DAILY_PURGE_SCHEDULE by clicking the Schedulers link,
Two
windows, called WEEKNIGHT_WINDOW and WEEKEND_WINDOW by clicking the Windows
link, and
Two
job classes, called DEFUALT_JOB_CLASS
, AUTO_TASKS_JOB_CLASS
by clicking the
Job Classes link.
Q:
How do you add a Job using the Database Control page?
A:
Click the Create button on the Scheduler
Jobs page; fill
out the applicable fields;
Back
to the Create Job page; enter location of your job script in the Executable Name
field; and then click the Schedule tab.
On
the Schedule page, make sure that the immediate radio button and the Repeat
field are set to Do Not Repeat.
Q:
How to create a schedule named MY_SCHEDULE owned by ISELF that executes
every five seconds.
A:
SQL>
CONNECT iself/schooling
SQL>
BEGIN
DBMS_SCHEDULER
.create_schedule (
SCHEDULE_NAME =>
‘MY_SCHEDULE’,
START_DATE
=>
SYSTIMESTAMP,
REPEAT_INTERVAL
=> ‘FREQ=SECONDLY;INERVAL=5’,
COMMENTS
=>
‘This is my first created schedule.’);
END;
/
Now,
you should be able to see it in your Database Control page.
Q:
How to schedule a job that calls your created online-backup every night
at 10 p.m.?
A:
You should have been granted CREATE JOB to be
able to create a job. Jobs are created as disabled by default. You must enable
them explicitly.
SQL>
BEGIN
DBMS_SCHEDULER
.create_job (
JOB_NAME
=>
‘ISELF.ONLINE_BACKUP’,
JOB_TYPE
=>
‘EXECUTABLE’,
JOB_ACTION
=>
‘/home/my_Nightly_online_backup.sh’,
START_DATE
=>
TRUNC(SYSDATE+1)+22/24,
REPEAT_INTERVAL
=> ‘TRUNC(SYSDATE+1)+22/24’,
COMMENTS
=>
‘My nightly online backup’);
END;
/
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?
A:
SQL>
BEGIN
DBMS_SCHEDULER
.create_program (
PROGRAM_NAME =>
‘DAILY_GATHERING’,
PROGRAM_ACTION
=> ‘ISLEF.DAILY_DATA_GATHERING’,
PROGRAM_TYPE =>
‘STORED_PROCEDURE’,
ENABLED
=>
TRUE);
END;
SQL>
BEGIN
DBMS_SCHEDULER
.create_job (
JOB_NAME
=>
‘ISELF.DAILY_GATHERING_JOB’,
PROGRAM_NAME =>
‘ISLEF.DAILY_GATHERING’,
START_DATE
=>
TRUNC(SYSDATE+1)+22/24,
REPEAT_INTERVAL
=> ‘TRUNC(SYSDATE+1)+22/24’,
COMMENTS
=>
‘Daily Data Gathering Job.’);
END;
/
You
could also use your created schedule:
SQL>
BEGIN
DBMS_SCHEDULER
.create_job (
JOB_NAME
=>
‘ISELF.DAILY_GATHERING_JOB’,
PROGRAM_NAME =>
‘ISLEF.DAILY_GATHERING’,
SCHEDULE_NAME =>
‘MY_SCHEDULE’);
END;
/
Q:
How an alert will raise or clear based on a tablespace size?
A:
In the Oracle Database 10g, tablespace
thresholds are defined in terms of a percentage of the tablespace size. When the
threshold crosses their limits, an appropriate alert will raise or clear.
Q:
When do you need to enable row movement on a segment?
A:
Since a shrink operation may cause ROWIDs to
change in heap-organized segment, before executing a shrink operation you should
enable row movement on a segment.
For
example:
SQL>
ALTER TABLE emp ENABLE ROW MOVEMENT
;
SQL>
ALTER TABLE emp SHRINK SPACE CASCADE
;
Q:
On the USERS tablespace, set a warning threshold of 80% and a critical
threshold of 95%.
A:
SQL>
BEGIN
DBMS_SERVER_ALERT
.set_threshold (
DBMS_SERVER_ALERT
.tablespace_pct_full,
DBMS_SERVER_ALERT
.operator_ge, 80,
DBMS_SERVER_ALERT
.operator_ge, 95,
1, 1, NULL,
DBMS_SERVER_ALERT
.object_type_tablespace,
‘USERS’);
END;
You
can use the NULL value to return to the database-wide default values.
Q:
How do you check the database-wide threshold values for the USERS
tablespace?
A:
SQL>
SELECT warning_value, critical_value
FROM dba_thresholds
WHERE metrics_name = ‘Tablespace Space Usage’ AND
object_name = ‘USERS’
/
Q:
How do you turn off the space-usage tracking for the USER tablespace?
A:
SQL>
BEGIN
DBMS_SERVER_ALERT
.set_threshold (
DBMS_SERVER_ALERT
.tablespace_pct_full,
DBMS_SERVER_ALERT
.operator_do_not_check,
‘0’,
DBMS_SERVER_ALERT
.operator_do_not_check,
‘0’, 1, 1, NULL,
DBMS_SERVER_ALERT
.object_type_tablespace,
‘USERS’);
END;
Q:
How do you reset the database-wide threshold values of the USERS
tablespace to the default database values?
A:
SQL>
BEGIN
DBMS_SERVER_ALERT
.set_threshold (
DBMS_SERVER_ALERT
.tablespace_pct_full,
NULL, NULL, NULL, NULL, 1, 1, NULL,
DBMS_SERVER_ALERT
.object_type_tablespace,
‘USERS’);
END;
Q:
How do you check the status of your threshold?
A:
SQL>
SELECT reason, resolution
FROM dba_alert_history
WHERE object_name = ‘USERS’;
SQL>
SELECT reason, message_level
FROM dba_outstanding_alerts
WHERE object_name = ‘USERS’;
Q:
What is a BIGFILE
tablespace?
A:
It is the Oracle Database 10g feature. A
bigfile tablespace
(BFT
) is a tablespace containing a single file that can have a
very large size and on the other hand a smallfile tablespace can contain many
data files. The size of a bigfile can reach to 128TB depending on the Oracle
block size. An Oracle database can contain both bigfile and smallfile
tablespaces. You can change the default tablespace type to BIGFILE
or SMALLFILE.
Q:
How do you set the default tablespace type to BIGFILE
?
A:
To set the default tablespace type to BIGFILE
, you can use either CREATE DATABASE or ALTER DATABASE.
Q:
how do you display the default tablespace type?
A:
You use the DATABASE_PROPERTIES
dictionary
view to display the default tablespace type for the database:
SQL>
SELECT property_value FROM database_properties
WHERE property_name = ‘DEFAULT_TBS_TYPE’;
Q:
Use the DBA_TABLESPACES
dictionary view to display whether all tablespace is bigfile
(YES) or smallfile (NO).
A:
SQL>
SELECT tablespace_name, bigfile FROM dba_tablespaces;
Q:
Use the V$TABLESPACE
dynamic view to display whether all tablespace is bigfile
(YES) or smallfile (NO).
A:
SQL>
SELECT name, bigfile FROM v$tablespace;
Q:
What are the difference between a BIGFILE
rowid and a small file rowid?
A:
Extended ROWID format
:
For
Smallfile tablespaces is Object# - File# - Block# - Row#
For
Bigfile tablespaces is Object# - Block# - Row#
Q:
Create a temporary tablespace group that it consists of only temporary
tablespaces.
A:
SQL>
CREATE TEMPORARY TABLESPACE mytemp1
TEMPFILE ‘temp_01.dbf’ SIZE 500M
TABLESPACE GROUP mygroup;
The
mygroup group has one more temporary tablespace in its groups. If you do not
want to assign any temporary tablespace to a group do the following:
SQL>
CREATE TEMPORARY TABLESPACE mytemp2
TEMPFILE ‘temp_02.dbf’ SIZE 500M
TABLESPACE GROUP ‘’;
Q:
Use the DBA_TABLESPACE_GROUPS
view to display all tablespace associated to their groups.
A:
SQL>
SELECT tablespace, group_name FROM dba_tablespace_groups;
Q:
Create a tablespace with a BIGFILE
default tablespace type.
A:
SQL>
CREATE BIGFILE
UNDO TABLEPSACE
my_big_tbs
DATAFILE ‘/u01/oradatta/tbs_01.dbf’ SIZE 1G;
Q:
Can you add more datafiles?
A:
If you try to add more datafile to above
tablespace, do the following.
SQL>
ALTER TABLESPACE my_big_tbs
ADD DATAFILE ‘/u02/oradata/tbs_02.dbf’ SIZE 100k;
Notice,
since a bigfile tablespace
can
contain only one data file, your command should fail.
Q:
How do you get a BIGFILE
ROWID?
A:
To get its ROWID, you should use the following
database package (DBMS_ROWID).
SQL>
SELECT distinct DBMS_ROWID.ROWID_RELATIVE_FNO (ROWID,’BIGFILE
’)
FROM test_rowid;
Q:
What is System Auxiliary Tablespace in the Oracle 10g Database?
A:
The Oracle 10g database added one more
tablespace (SYSAUX) to help the SYSTEM tablespace. The SYSAUX tablespace
is an
auxiliary tablespace to the SYSTEM tablespace. It is a mandatory tablespace and
should be created at database creation time. You cannot drop or rename this
tablespace.
Q:
What does the SYSAUX tablespace
hold?
A:
-
Text, Ultra Search
-
Intermedia, Spatial
-
Scheduler
-
OLAP
-
XML DB
-
Workspace Manager
-
Data Mining
-
Server Manageability Components
-
Recovery Catalog
-
EM Repository
-
Analytical Workspace Object table
-
LogMinor, Log Standby, Streems
-
Statspack
Q:
How can you monitor the space usage of each occupant inside the SYSAUX
tablespace
?
A:
Use the following SQL statement.
SQL>
SELECT occupant_name, space_usage_kbytes
FROM v$sysaux_occupants
/
Q:
What are the Mandatory attributes of SYSAUX?
A:
Q:
What are its benefits?
A:
It reduces the number of tablespaces to
manage. You don’t need to create the TOOLS, OEM_REPOSITROY, DRSYS, CWMLITE,
ODM, etc tablespaces.
You
reduce the load on the SYSTEM tablespace.
Q:
Can you rename SYSTEM or SYSAUX tablespaces?
A:
Now you can rename tablespace in the Oracle
10g database. You can not rename the SYSTEM and SYSAUX tablespaces. You can
rename any permanent or temporary tablespaces. All tablespaces must have their
datafiles online. Your database spfile will be updated.
Q:
How do you rename a tablespace?
A:
To rename a tablespace do the following SQL
statement.
SQL>
ALTER TABLESPACE tools RENAME TO my_tools;
Q:
Can you change the default Permanent Tablespace in the Oracle 10g
database?
A:
Now, you can change the default permanent
tablespace for non-system users.
Q:
How can you check what your default permanent tablespace is?
A:
Query the following SQL statement.
SQL>
SELECT property_value
FROM database_properties
WHERE property_name =
‘DEFAULT_PERMANENT_TABLESPACE’
/
Q:
Change your default tablespace to USERS.
A:
Assuming your default tablespace is SYSTEM,
and then you change it to USERS.
SQL>
ALTER DATABASE DEFAULT TABLESPACE users;
Q:
How can you copy files without using system operating system commands?
A:
You can use the COPY_FILE procedure in the
DBMS_FILE_TRANSFER
package
to copy a file from one place to another. You should make sure to create source
and destination directories and you have a read access on the source directory
and a write access on the destination directory.
Assuming
we are going to copy file a1.dbf from location /u01/oradata/school to location
/u02/oradata/school.
SQL>
CREATE DIRECTORY my_source AS ‘/u01/oradata/school’;
SQL>
CREATE DIRECTORY my_destination AS ‘/u02/oradata/school’;
SQL>
BEGIN
DBMS_FILE_TRANSFER
.COPY_FILE (
SOURCE_DIRECTORY_OBJECT => ‘MY_SOURCE’,
SOURCE_FILE_NAME => ‘a1.dbf’,
DESTINATION_DIRECTORY_OBJECT => ‘MY_DESTINATION’,
DESTINATION _FILE_NAME => ‘a1.dbf’);
END;
/
Q:
What does the Redo Logfile size advisor
in the Oracle 10g database?
A:
It determines the optional smallest online
redo log file size based on the current FAST_START_MTTR_TARGET
setting and the
corresponding statistics. To enable the Redo Logfile Size Advisor
, you should set the FAST_START_MTTR_TARGET
parameter. Note that an online redo log file size is considered optimal if it
does not drive incremental check pointing more aggressively than needed by
FAST_START_MTTR_TARGET.
Q:
Make the retention period for one day with an interval off “0” which
switches off snapshot collection.
A:
SQ>
EXEC dbms_workload_repository
.modify_snapshot_settings (1440, 0);
-- 1440 minute or 1 day and “0” switches OFF snapshot collection.
Q:
What does ASM
provide?
A:
ASM
provides
a vertical integration of the file system and the volume manager that is
specifically built for Oracle database files.
Q:
What are its key features and benefits?
A:
-
Stripes files rather than logical
volumes
-
Online disk reconfiguration and
dynamic rebalancing
-
Adjustable rebalancing speed
-
Provides redundancy on a file
basis
-
Supports only Oracle database
files
-
Custer-aware
-
Automatically installed
Q:
Does ASM
have a data dictionary?
A:
ASM
instance
does not have a data dictionary and it is restricted to few SQL commands and
Dynamic Performance view.
Q:
How do you create a disk group by using ASM
?
A:
The following are examples of creating and
deleting disk groups.
Creating
a diskgroup:
SQL>
CREATE DISKGROUP dgora1 NORMAL REDUNDANCY
FAILGROUP controller1 DISK
‘/dev/rdsk/c0t0d0s2’ NAME mydisk SIZE 200G FORCE,
‘/dev/rdsk/c0t1d0s2’,
‘/dev/rdsk/c0t2d0s2’
FAILGROUP controller2 DISK
‘/dev/rdsk/c1t0d0s2’,
‘/dev/rdsk/c1t1d0s2’,
‘/dev/rdsk/c1t2d0s2’;
Q:
How do you delete a disk group by using ASM
?
A: Dropping a diskgroup:
SQL>
DROP DISKGROUP dgora1 INCLUDING CONTENTS;
Q:
How do you add a disk to an existing disk group?
A:
The following are examples of how to add disks
to an existing disk group.
SQL>
ALTER DISKGROUP dgora1
ADD FAILGROUP controller1
‘/dev/rdsk/c0t3d0s2’ NAME a5;
Q:
How do you remove a disk to an existing disk group?
A:
To remove a disk:
SQL>
ALTER DISKGROUP dgora1 DROP DISK a5;
Q:
Can you undo the removed disk? How?
A:
Yes. To undo the removed disk do the following
SQL statement. This only works if the status of drop is pending or the drop
function was not completed yet.
SQL>
ALTER DISKGROUP dgora1 UNDROP DISKS;
Q:
How do you display a list of your diskgroups?
A:
To display a list of diskgroups.
SQL>
SELECT name FROM v$asm_diskgroup;
Q:
How do you display a list of associated ASM
disks?
A:
To display a list of associated ASM
disks.
SQL>
COL name FORMAT a20
SQL>
COL failgroup FORMAT a20
SQL>
SELECT name, failgroup, bytes_read, bytes_written
FROM v$asm_disk
/
Q:
How do you display a list of associated ASM
files?
A:
To display a list of associated ASM
files.
SQL>
SELECT group_number, file_number, bytes, type, striped
FROM v$asm_file
/
Q:
How do you create a tablespace that uses an ASM
disk group?
A:
To create a tablespace that is stored in the
ASM
disk group dgora1.
SQL>
CREATE TABLESPACE mytablespace2
DATAFILE ‘+dgora1’ SIZE 100m
/
Q:
How do you add one addition disk to your system?
A:
Do the following.
SQL>
HOST dd if=/dev/zero of=/u02/oradata/school/diska abs=1024k count=200
SQL>
SELECT name, failgroup, bytes_read, bytes_written
FROM v$asm_disk
/
SQL>
ALTER DISKGROUP dgora1
ADD DISK ‘/u02/oradata/school/diska’
/
Execute
the following query until you get ‘no rows selected.’
SQL>
SELECT operation, est_minutes
FROM v$asm_operation
/
Again,
display a list of associated ASM
disks.
SQL>
SELECT name, failgroup, bytes_read, bytes_written
FROM v$asm_disk
/
Now,
you should see one more disk was added to disk group.
Q:
What is VPD
in the Oracle 10g Database?
A:
In the Oracle 10g database, there is a feature
called Virtual Private Database (VPD
). It enables you to build applications that enforce your security
policy. When a user requests a query, the server dynamically modifies the
user’s SQL statement, which is not transparent to the user. The modification
is based on a WHERE clause returned by a function.
Q:
How do you use the V$FAST_START_TRANSACTIONS
view to monitor (in real-time) normal transaction rollback and
transaction recovery by SMON?
A:
SQL>
SELECT * FROM v$fast_start_ transactions;
Q:
How do you use the V$FAST_START_SERVERS
view to display historical information about transaction
recovery?
SQL>
SELECT * FROM v$fast_start_servers;
Q:
How do you use the DBA_ENABLED_TRACES
view to display enabled and disabled trace?
A:
SQL>
SELECT * FROM dba_enabled_traces;
Q:
What is case-insensitive sorting in the Oracle 10g?
A:
In the Oracle 10g database, you can use the
ALTER SESSION command to change NLS_SORT
for your session to use
case-insensitive binary sorting.
Q:
What is Quote Operator q?
A:
Now, you can eliminate previous additional
quotation string. See the following example:
SQL>
UPDATE customers
SET comments =
q’X In this example, ‘X’ is used as the quotation mark delimiter.
X’;
WHERE ID = 100;
SQL>
COMMIT;
Q:
What does the UTL_MAIL package?
A:
You can send e-mail to a user. In order to use
the package, you should have already run the utlmail.sql and prvtmail.plb
scripts located in the $ORACLE_HOME/rdbms/admin directory.
Q:
What is the Oracle 10g Database Grid?
A:
Yesterday was Internet and today is Grid. It
looks like the technology finds its way to hang on something to grow. Now, there
is a committee called the Global Grid Forum (GGF) that is developing standards
for Grid computing.
Q:
List some of the most important features of the Oracle 10g Grid.
A:
The following are some of the most important
features that enable Grid computing at the Oracle 10g Database level:
-
Automatic Storage Management
-
Portable Clusterware
-
High-Speed InfiniBand Network
Support
-
Real Application Clusters and
automatic workload management
-
Resource Manager
-
Oracle Streams
-
Centralized Management with
Enterprise Manager Grid Control
-
Oracle Database 10g New
self-management feature
-
…
Q:
What is OEM 10g?
A:
It is Oracle Enterprise Manager with features
that are more new. It can automatically alert you to new critical patches.
You can use the patch wizard to select an interim patch and see if any of
your system requires it. It is integrated with a build-in MetaLink connection.
Q:
How do you stop the Oracle Management Server?
A:
#
emctl
stop oms
Q:
How do you start the Oracle Management Server?
A:
#
emctl
start oms
Q:
How do you get the status of the Oracle Management Server?
A:
Do the following command:
#
emctl
status oms
Q:
How do you start EM Database Control
?
A:
#
emctl
start
dbconsole
Q:
How do you stop the EM Database Control
?
A:
#
emctl
stop
dbconsole
Q:
How do you get the status of the EM Database Control
?
A:
#
emctl
status
dbconsole
Q:
What is the default port for your Web Browser?
A:
The default port is 5500 and you can access it
by opening your Web browser and enter the following URL: http://myserver:5500/em
Q:
How do you create an Oracle user by using the EM Database Control
?
A:
Go to the EM Database Control
home page as
sysdba and click on Administration > Users. On the Users page
click on the Create button. You should specify the Name, Enter Password, Confirm
Password, Default Tablespace, and Temporary Tablespace fields. Then click on the
Roles tab and click on the Modify button; select Resource and DBA from the
Available Roles list. Once done, click the Move link and click the OK button.
When you are returned to the Create User page, click the OK button.
Q:
How do you export tables from a schema by using the EM Database Control
?
A: Go to the EM Database
Control
home
page as sysdba. Click on the Administration tab; click on the Tables link in the
Schema region; select your schema in the Schema field in the Search region; and
click the Go button. Select your exported tables from the Results region; Select
Show Dependencies from the Actions drop-down list; and then click on the Go
button in the Results region. On the Export:Review page, click on the Submit Job
button.
Q:
How do you import tables from a schema by using the EM Database Control
?
A: Go to the EM Database
Control
home
page as sysdba. Click on the Maintenance link; click on the Import from Files
link; make sure that the Database Version of Files to Import field is set to 10g
or later; and click the Go button. In the Files region, select your directory
from where the Data Pump
Import
job can retrieve your exported Dump File Set; in the Import Type region, select
the Tables option button and make sure the host credentials are correct; then
click on the Continue button. Click on the Add button and in the Import:Add
Tables page, enter your schema in the Schema field in the Search region; then
click on the Go button. Select your tables; click on the Select button and On
the Import:Re-Mapping page, click the Add Another Row button in the Re-Map
Schema region. Then click the Next button. On the Import:Review page, click on
the Submit Job button; on the Status page, click the View Job button; refresh
your browser page until the Status field reads Succeeded; and then click the
Import link to look at the log file.
Q: How do you retrieve the latest ADDM
and
determine the cause of the problem by using the EM Database Control
?
A: Go to the EM Database
Control
home
page as sysdba and click on the Advisor Central link on the Related Links
section; select ADDM
in the
Advisory Type drop-down list; select Last 24 Hours in the Advisor Runs drop-down
list; and when it is done, click on the Go button. Then select the latest ADDM
task completed by the ADDM user; click the View Result button; it brings you to
the ADDM page and you can see the results for the Performance Analysis; To
investigate further, click on the SQL Statements Consuming Significant Database
Time Were Found link; it will brings you to the Performance Finding Details
page. To tune the statement, click on the Run Advisor Now button; when the
analysis finished, you will be directed to the Recommendations for SQL ID: …
page.
Q: How do you shutdown and startup your databases using an
initialization parameter file by using the EM Database Control
?
A: Go to the EM Database
Control
home
page as sysdba; click the Shutdown button; you should specify host and target
database credential; save them and click on the OK button. On the
Startup/Shutdown: Confirmation page click the Yes button; on your database page,
click the Startup button. Click on the Advanced Options button; make sure that
you select your initialization parameter file with its location and then click
the OK button.
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?
A: Go to the EM Database
Control
home
page as sysdba; click on the Administration tab; click on the Memory Parameters
link in the Instance region; and now you should see the status Automatic Shared
Memory Management
(disable
or enable), Shared Pool size, Buffer Cache, Large Pool, Java Pool, Total SGA,
Maximum SGA size, etc. If the Automatic Shared Memory Management was disabled on
the Memory Parameters page, click on the Enable button to enable it.
Q: How do you look at the corresponding metrics graphic
rate by using the EM Database Control
?
A: Go to the EM Database
Control
home
page as sysdba; click on the All Metrics link; expand the Throughput link; under
this link, click on the User Commits (per second) link; make sure that the View
Data field is set to very short period of time (ex: Real Time: 10 Seconds
Refresh). Now here, depending on your workload, you should be able to see your
graph.
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
?
A: Go to the EM Database
Control
home
page as sysdba.
If
the time corresponding to the problem corresponds with the latest ADDM
, then you should find the link corresponding to the
correct performance analysis directly in the Diagnostic Summary region of the EM
Database Control
home page. If not, then go
to the Advisor Central page and search for the correct ADDM task. To fix your
problem, click the finding with the highest impact on the database time on the
corresponding ADDM page. It should correspond to a SQL Tuning recommendation. On
the Performance Finding Details page, you see the high-load SQL statement; click
on the Run Advisor Now button for the highest high-load SQL statement detected.
Once was done, you get the details of the corresponding recommendations. You can
also click on the Original Explain Plan button to see the original plan. If you
have a proposed SQL command, click on the Implement button after selecting the
appropriate SQL command from the Recommendations table.
Q: How do you determine the most important wait category
from the Sessions: Waiting and Working graph by using the EM Database Control
?
A: Go to the EM Database
Control
home
page as sysdba; click on the Performance tab; if the period for which you want
to observe your database is on the Sessions: Waiting and Working graph use the
current graph. If the period is no there, select the Historical value from the
View Data drop-down list. On the Performance page click on the User I/O category
in the graph’s legend. Select the top SQL statement and you should see the SQL
Details page. Click on the Execution History tab to see what isgoing on to the
statement. Shorten the Seconds Per Execution option button to get a better
understanding of the graph.
Q: How do you use the SQL Access Advisor
to
generate recommendations for a SQL tuning set by using the EM Database Control
?
A: Go to the Database
Control home page as sysdba; click on the Advisor Central link; click on the SQL
Access Advisor
link;
then select the Import Workload from SQL Repository option button; set the SQL
Tuning Set field to your SQL tuning set; and click the Next button. On this
page, click on the Both Indexes and Materialized Views and Comprehensive Mode
buttons; click on the Show Advanced Options link and make sure your options are
set and then click the Next button. On the SQL Access Advisor: Schedule page,
select Standard in the Schedule Type file; on the Review page, click the Submit
button. Refresh the page until you get the COMPLETED status. Now, select your
SQL Access Advisor task and click the View Result button.
Q: How do you create a job and schedule it to run by using
the EM Database Control
?
A: Go to the Database
Control home page as sysdba; click on the Administration tab; in the Scheduler
section,
click on the Jobs link; on this page, click on the Create button; on the page,
enter a job name the Name field; make sure the set the Logging Level (RUNS), Job
Class (DEFAULT_JOB_CLASS), Auto Drop (FALSE), and the Restartable (FALSE). In
the Command section, click the Change Command Type button; on the Select Command
Option, select the In-line Program: Executable radio button; and the click the
OK button. On the Create Job page, enter your shell executable job in the
Executable Name field and click the Schedule tab. On this page, select your
schedule option and click OK.
Q: How do you verify that your schedule was created by
using the EM Database Control
?
A: Go to the Database
Control home page as sysdba; click on the Administration tab; click the
Schedules link on the Scheduler
region.
Q: How do you change the Tablespace Space Usage thresholds
of a tablespace by using the EM Database Control
?
A: Go to the Database
Control home page as sysdba; click on the Administration tab; click on the
Tablespaces link; select your tablespace; select Edit > Thresholds; select
Specify Thresholds, by percent used; now specify the Warning(%) and Critical (%)
fields; and then click the Apply button.
Q: How do you run the Segment Advisor
for a
tablespace by using the EM Database Control
?
A: Go to the Database
Control home page as sysdba; click on the Administration tab; click on the
Tablespace link; select your tablespace; select Run Segment Advisor
from the
Action field; click the Go button; check that the Comprehensive mode is
selected; click the Continue button; check all the options on the Segment
Advisor: Schedule, and Options pages. On the Review page, click the Submit
button and click refresh until you see COMPLETED. Select your task and click the
View Result button to go to the Segment Advisor Recommendations page. Accept all
recommendation and then click the Schedule Implementation button.
Q: How do you use the Undo Advisor to get recommendations
to size your UNDO tablespace
by using
the EM Database Control
?
A: Go to the Database
Control home page as sysdba; click on the Administration tab; click on the UNDO
Management link; on this page change the Analysis Time Period field to Last One
Hour and click the Update Analysis button; on the UNDO Management page, you
should see Oracle 10g recommendation; click the UNDO Advisor
button
to get more info. You can look at the Required Tablespace Size by Undo Retention
Length graph. If you change the New Undo Retention field for example you will
see the impact on your undo tablespace size. On the UNDO Management page, click
the Edit Undo Tablespace button. Change the size or add datafile and click the
Continue button. After you are back to the Edit Tablespace page click the Apply
button.
Q: How do you change the size of flash recovery area by
using the EM Database Control
?
A: Go to the Database
Control home page as sysdba; click on the Maintenance tab; click on the
Configure Recovery Settings link; in the Flash Recovery Area
Size
field, you can enter a value that you want to change and then click on the Apply
button.
Q: How do you backup your whole database to tape by using
the EM Database Control
?
A: Go to the Database
Control home page as sysdba; click on the Maintenance tab; click on the Schedule
backup link; select Customized from the Backup Strategy drop-down list; select
Whole Database; make sure that you enter your host credentials and click the
Next button; on the Options page, click NEXT; on the Setting page, select Tape,
then NEXT; on the Schedule page, click NEXT; on the Review page, click Submit
Job. Now you should be able to view your job by clicking on the View Job button.
Once the job is done, click the Backup link on the Execution page. You see what
you want to see.
Q: How do you use the flashback feature to recover a
dropped table by using the EM Database Control
?
A: Let assume that still
your table is in the Recycle Bin area. Go to the Recycle Bin page; select your
object; and click the Flashback
Drop
button. You will be prompted to the Perform Recovery: Rename page to change the
original name if you wish. Leave the original name, and click NEXT. On the
Review page, click the Submit button and click OK on the Confirmation page. Now
you should see the Recycle Bin page and your object should have been
disappeared.
Q: How do you determine the Redo Log File size using the
Sizing Advice by using the EM Database Control
?
A: Go to the Database
Control home page as sysdba; click on the Administration tab; click on the Redo
Log Groups link; on this page you can see the redo log group size; select Sizing
Advice in the Actions drop-down list and click the Go button. Now, you should
see the recommended optimal redo log file size in the Update Message region of
the Redo Log Groups page.
|