It is very useful!

Questions on

Regular Expression Support

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.

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:

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;

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?

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.

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.

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