iSelfSchooling - The Oracle 10g Database Answers

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.

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.

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.

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.

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.

SQL> SELECT

REGEXP_SUBSTR(‘system/password@myhost:1521:mysid’,

‘[^:]+’, 1, 3) as “SID name”

FROM dual;

Questions on

UNDO Advisor in the Oracle 10g database

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?

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.

Questions on

The Oracle Data Pump Export and Import utilities in the Oracle 10g database

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.

# 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.

# 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?

# 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.

$ 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.

$ 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.

$ 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.

$ 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?

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’;

Questions on

Backup and Recovery Enhancements in the Oracle 10g database

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?

SQL> SELECT name, space_limit, space_used,

space_reclaimable, number_of_files

FROM v$recovery_file_dest;

Q: How can you display warning messages?

SQL> SELECT object_type, message_type,

message_level, reason, suggested_action

FROM dba_outstanding_alerts;

Q: How do you backup the Flash Recovery Area?

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?

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?

SQL> ALTER DATABASE enable BLOCK CHANGE TRACKING;

Q: How do you monitor block change tracking?

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?

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?

RMAN> BACKUP DATABASE;

Q: How do you backup an individual tablespaces?

RMAN> CONFIGURE DEFAULT DEVICE TYPE TO DISK;

RMAN> BACKUP TABLESPACE system;

Q: How do you backup datafiles and control files?

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.

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?

SQL> ALTER DATABASE BEGIN BACKUP;

Copy all the datafiles…

SQL> ALTER DATABASE END BACKUP;

Q: How do you set the flash recovery area?

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?

SQL> SELECT name, space_limit, space_used,

space_reclaimable, number_of_files

FROM v$recovery_file_dest;

Questions on

The Flashback feature in the Oracle 10g database

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?

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?

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?

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?

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?

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?

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?

SQL> SELECT tablespace_name, retention FROM dba_tablespaces;

Q: How can you recover deleted file?

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?

SQL> SELECT count(*) FROM flashback_table;

Questions on

Automatic Database Diagnostic Monitor in the Oracle 10g database

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 sto