LPAD

Add characters to the left of a string until a fixed number is reached. Example: lpad('abc',8,'x') = 'xxxxxabc'. If the last parameter is not specified, spaces are added to the left.

LENGTH

Oracle string functions

The LENGTH functions returns the length of char. LENGTH calculates length using characters as defined by the input character set.

Example: length('abcdefghij') = 10

LENGTHB Returns the length of a string, expressed in bytes.

INITCAP

Oracle string functions

Transform String to init cap

Example: INITCAP('UPPERCASE') = 'Uppercase'

DUMP

Oracle string functions

The DUMP function returns a VARCHAR2 value containing the datatype code, length in bytes, and internal representation of expr. The returned result is always in the database character set.

CONVERT

Oracle string functions

The CONVERT function converts a string from one characterset to another. The datatype of the returned value is VARCHAR2.

Example: CONVERT('This is an example','UTF-8','WE8ISO8859P1' )

SELECT CONVERT('Ä Ê Í Ó Ø A B C D E ', 'US7ASCII', 'WE8ISO8859P1') FROM DUAL;
-------------
A E I ? ? A B C D E ?

CONCAT

Oracle string functions

The CONCAT function returns the concatenation of 2 strings. You can also use the || command for this.

Example: CONCAT('abc','def') = 'abcdef'

COALESCE

Oracle string functions

The COALESCE function returns the first non-null expr in the expression list. At least one expr must not be the literal NULL. If all occurrences of expr evaluate to null, then the function returns null. Example: select COALESCE(col1, col2, col3) FROM emp;

ASCII

The ASCII function returns the decimal representation in the database character set of the first character of char. Example: ASCII('b') =98

TRIM - function

Oracle pl/sql trim function

The trim function removed characters from beginning and/or end of a string in Oracle. Oracle has 3 functions for this:

TRIM
The TRIM function trims specified characters from the left and/or right.If no characters are specified, the left and right spaces are left out.
Example: trim(' will it trim ') = 'will it trim'.

Another option is:
trim(trailing 'a' from 'aaaabcbaaaa') which results in 'aaaabb' or
trim(leading 'a' from 'aaaabcbaaaa') which results in 'bbaaaa' or
trim(both 'a' from 'aaaabcbaaaa') which results in 'bb'.

LTRIM
LTRIM removes characters from the left of a string if they are equal to the specified string. Example: ltrim('aaaaaabc','a') = 'bc' If the last parameter is not specified, spaces are removed from the left side.

RTRIM
RTRIM removes characters from the right of a string if they are equal to the specified string. Example: rtrim('bcaaaaaa','a') = 'bc' If the last parameter is not specified, spaces are removed from the right side.

CHR - function

CHR: The CHR function returns the character having the binary equivalent to n as a VARCHAR2 value in either the database character set.

Example: CHR(10)CHR(13) = carriage return plus line feed.

In Oracle/PLSQL, the CHR function is the opposite of the ascii function. It returns the character based on the NUMBER code.

The syntax for the chr function is: CHR( number_code [, USING NCHAR_CS])
where the number_code is the NUMBER code used to retrieve the character.

Use of the CHR function (either with or without the optional USING NCHAR_CS clause) results in code that is not portable between ASCII- and EBCDIC-based machine architectures.

For single-byte character sets, if n > 256, then Oracle returns the binary equivalent of n mod 256. For multibyte character sets, n must resolve to one entire codepoint. Invalid codepoints are not validated, and the result of specifying invalid codepoints is indeterminate.

DBA Fundamentals II (1Z0-032 ) - What you need to know

What you should know to pass this test:

Understand the architecture of Oracle Net (called Net8 or SQL*Net in prior releases).

Know how to configure Oracle clients, servers, and the shared server option. Especially understand the different configuration options for clients and how to set up each. Know how to debug Oracle Net problems.

Understand the steps Oracle’s Listener takes in responding to user requests. How do these steps differ for bequeathing and the shared server option? What is the role of the dispatchers, and what SGA memory areas do they use?

For backup and recovery, understand Oracle’s architecture and how the database product handles database updates, transactions, redo logs, and archived logs. Which instance background processes have a role in these activities?

What is the difference between redo and undo records?

Know what transactions are, and how they relate to recovery scenarios.

Know what a database checkpoint is, what background processes are involved in it, and how it differs from a COMMIT.

Understand RMAN’s purpose and benefits, and when to use an RMAN catalog. Also be familiar with the RMAN commands, and installation and maintenance procedures.

Know how to backup a database, with and without RMAN.

Know how to perform complete and incomplete database recovery with and without RMAN.

Know the differences between ARCHIVELOG and NOARCHIVELOG mode databases. What are the advantages of each, and how do they differ when it comes to recovery?

Understand when you must shutdown a database to recover it, versus when you can do a “database-up” recovery.

What is the difference between “restore” and “recovery”? What is the role of the redo and archive logs in recovery? When can you recover without logs versus when are they required?

Understand the roles of LogMiner and Oracle Flashback Query in recovering from user error.

Understand the role and uses of the Export and Import utilities. How do they complement physical database backups and recoveries? Know the main Export and Import commands.

What is the DB_VERIFY utility and how is it used?

Be prepared to answer questions about which data dictionary views you would query to locate backup and recovery information.

Hot or Cold Backup?

Explain the difference between a hot backup and a cold backup and the benefits associated with each.

A hot backup means taking a backup of the database while it is still up and running where the database is in archive log mode. A cold backup means taking a backup of the database while it is shut down and does not require that it is in archive log mode.

The benefit of taking a hot backup is that the database is still available for use while the backup is occurring and you can recover the database to any point in time.

The benefit of taking a cold backup is that it is typically easier to administer the backup and recovery process. In addition, since you are taking cold backups the database does not require being in archive log mode and thus there will be a slight performance gain as the database is not cutting archive logs to disk.