Oracle PL/SQL

PL/SQL is Oracle's procedural programming language. It allows you to use various control structures and conditional arguments to construct more complicated logical structures than are available through pure SQL.
All of the standard Oracle SQL functions are available for use, and some have been extended increasing their functionality or range. Furthermore, some SQL functions are available as stand alone functions within PL/SQL.

Conditional Statements

IF - THEN - ELSE - END IF
There is a time when you need to test something for its validity. The IF statement allows you to test something and depending on the result (either true or false) proceed through the logic of your program.
IF ( 1 = 1 ) THEN
  .... proceed with program
END IF;
This can be further extended to allow for processing if the condition is not met...
IF ( 1 = 1 ) THEN
  .... proceed with program
ELSE
  .... 1 does not = 1 wow!
END IF;
And even further extended to allow for alternate nested processing if the condition is not met...and another conditionis to be tested.
IF ( 1 = 1 ) THEN
  .... proceed with program
ELSIF ( 1 = 2 ) THEN
  .... 1  = 2 wow!
END IF;
If statements can be nested one inside the other - as long as the nested statement is fully enclosed within one of the processing blocks of the IF or ELSE sections. Be careful when nesting IF statements as they can be difficult to read, even if you develop them. If you need more than 3 or 4 levels of nesting then you should probably rework the code to eliminate this.
An IF statement always evaluates to TRUE or FALSE and several evaluations can be strung together in a single IF statement using AND or OR.
IF ( 1= 1 ) AND ( 2 = 2 ) THEN
  ... proceed with program
END IF;
Taking a fairly typical scenario, where we need to test a VARCHAR2 to see if it contains all numbers, we could attempt to cast the VARCHAR2 to a number using the TO_NUMBER function and then trap any error in the EXCEPTION section (more about exception handling later in this course), but that defeats the purpose of this exercise where we want control to remain within the IF statement. Breaking down the problem we notice several things:

1. We start with a VARCHAR2
2. It may (or may not) be all numbers.

Applying several SQL functions against the VARCHAR2 we approach the problem in a different way. Why not attempt to remove all the number characters and see if anything is left - such that
IF NVL(LENGTH(LTRIM('12345a','0123456789')),0) = 0 THEN
  ... we have a number
ELSE
  ... not a number
END IF;
As can be seen from the above example there may be more than one way to approach a problem, and invariably there usually is. So explaining above in more detail gives the following LTRIM the string of any numbers '0123456789'. Then see if anything is left in the string, hence the length function. The NVL is there because once the contents of a varchar have been removed it comtains NULL and its LENGTH is also NULL.

CASE - WHEN - END CASE
Looping
LOOP - END LOOP
WHILE LOOP - END WHILE
Cursors
Program Units
Functions
Procedures
Packages
Triggers

Overview of PL/SQL

PL/SQL is the best method available for writing and managing stored procedures that work with Oracle data. PL/SQL code consists of three subblocks-the declaration section, the executable section, and the exception handler. In addition, PL/SQL can be used in four different programming constructs. The types are procedures and functions, packages, and triggers. Procedures and functions are similar in that they both contain a series of instructions that PL/SQL will execute. However, the main difference is that a function will always return one and only one value. Procedures can return more than that number as output parameters. Packages are collected libraries of PL/SQL procedures and functions that have an interface to tell others what procedures and functions are available as well as their parameters, and the body contains the actual code executed by those procedures and functions. Triggers are special PL/SQL blocks that execute when a triggering event occurs. Events that fire triggers include any SQL statement.
Declaring and Using Variables
The declaration section allows for the declaration of variables and constants. A variable can have either a simple or "scalar" datatype, such as NUMBER or VARCHAR2. Alternately, a variable can have a referential datatype that uses reference to a table column to derive its datatype. Constants can be declared in the declaration section in the same way as variables, but with the addition of a constant keyword and with a value assigned. If a value is not assigned to a constant in the declaration section, an error will occur. In the executable section, a variable can have a value assigned to it at any point using the assignment expression (:=).
Using Implicit Cursor Attributes
Using PL/SQL allows the developer to produce code that integrates seamlessly with access to the Oracle database. There are no special characters or keywords required for "embedding" SQL statements into PL/SQL, because SQL is an extension of PL/SQL. As such, there really is no embedding at all. Every SQL statement executes in a cursor. When a cursor is not named, it is called an implicit cursor. PL/SQL allows the developer to investigate certain return status features in conjunction with the implicit cursors that run.
These implicit cursor attributes include %notfound and %found to identify if records were found or not found by the SQL statement; %notfound, which tells the developer how many rows were processed by the statement; and %isopen, which determines if the cursor is open and active in the database.
Conditional Statements and Process Flow
Conditional process control is made possible in PL/SQL with the use of if-then-else statements. The if statement uses a Boolean logic comparison to evaluate whether to execute the series of statements after the then clause. If the comparison evaluates to TRUE, the then clause is executed. If it evaluates to FALSE, then the code in the else statement is executed. Nested if statements can be placed in the else clause of an if statement, allowing for the development of code blocks that handle a number of different cases or situations.
Using Loops
Process flow can be controlled in PL/SQL with the use of loops as well. There are several different types of loops, from simple loop-exit statements to loop-exit when statements, while loop statements, and for loop statements. A simple loop-exit statement consists of the loop and end loop keywords enclosing the statements that will be executed repeatedly, with a special if-then statement designed to identify if an exit condition has been reached. The if-then statement can be eliminated by using an exit when statement to identify the exit condition. The entire process of identifying the exit condition as part of the steps executed in the loop can be eliminated with the use of a while loop statement. The exit condition is identified in the while clause of the statement. Finally, the for loop statement can be used in cases where the developer wants the code executing repeatedly for a specified number of times.
Explicit Cursor Handling
Cursor manipulation is useful for situations where a certain operation must be performed on each row returned from a query. A cursor is simply an address in memory where a SQL statement executes. A cursor can be explicitly named with the use of the cursor cursor_name is statement, followed by the SQL statement that will comprise the cursor. The cursor cursor_name is statement is used to define the cursor in the declaration section only. Once declared, the cursor must be opened, parsed, and executed before its rows can be manipulated. This process is executed with the open statement. Once the cursor is declared and opened, rows from the resultant dataset can be obtained if the SQL statement defining the cursor was a select using the fetch statement. Both loose variables for each column's value or a PL/SQL record may be used to store fetched values from a cursor for manipulation in the statement.
CURSOR FOR Loops
Executing each of the operations associated with cursor manipulation can be simplified in situations where the user will be looping through the cursor results using the cursor for loop statement. The cursor for loops handle many aspects of cursor manipulation explicitly. These steps include including opening, parsing, and executing the cursor statement, fetching the value from the statement, handling the exit when data not found condition, and even implicitly declaring the appropriate record type for a variable identified by the loop in which to store the fetched values from the query.
Error Handling
The exception handler is arguably the finest feature PL/SQL offers. In it, the developer can handle certain types of predefined exceptions without explicitly coding error-handling routines. The developer can also associate user-defined exceptions with standard Oracle errors, thereby eliminating the coding of an error check in the executable section. This step requires defining the exception using the exception_init pragma and coding a routine that handles the error when it occurs in the exception handler.
For completely user-defined errors that do not raise Oracle errors, the user can declare an exception and code a programmatic check in the execution section of the PL/SQL block, followed by some routine to execute when the error occurs in the exception handler. A special predefined exception called others can be coded into the exception handler as well to function as a catchall for any exception that occurs that has no exception-handling process defined. Once an exception is raised, control passes from the execution section of the block to the exception handler. Once the exception handler has completed, control is passed to the process that called the PL/SQL block.

Avoid escaping quotes by using user-defined quotes

You’re probably quite familiar with the practice of escaping quotes and other special characters in code. But it can sure be a pain if you or your program has to insert lengthy segments of text. Fortunately, Oracle10g eliminated the necessity of escaping quotes in SQL statements by introducing the ability to have user-defined quote characters.

Prior to Oracle10g, if you wanted to include quotes in text, you had to escape the quote with another quote such as:

SELECT 'Watch your p''s and q''s around mother' from dual;
Now with Oracle10g, you can rewrite this as:

SELECT Q'!Watch your p's and q's around mother!' from dual;
Note that the quoted strings starts with the letter Q, followed by a single quote and the new quote character. It ends with the new quote character and a single quote. I used a exclamation mark (!) as our quote character, but you can use other characters if you’d like. Now you can put any quoted text in between your quote characters.
Another feature is using opening and closing braces q'[text with ']' or q'{text with '}'. The choice is yours, but definately a lot simpler than trying to remember how many times you escaped the single quote.

You can use this feature in PL/SQL as well, like this:

CREATE or REPLACE PROCEDURE testquot (pi_name VARCHAR2)
IS
begin
DBMS_OUTPUT.PUT_LINE('Name is: 'pi_name);
end;
/

Then invoke the procedure in an anonymous block such as:

DECLARE
l_var varchar2(100):= Q'!Watch your p's and q's around mother!';
BEGIN
testquot(l_var);
END;
/

The output is:

Name is: Watch your p's and q's around mother

Oracle Greatest Function

Oracle/PLSQL: Greatest Function

Here is what the documentation says :
GREATEST- GREATEST returns the greatest of the list of expressions. All expressions after the first are implicitly converted to the datatype of the first expression before the comparison. Oracle compares the exprsessions using nonpadded comparison semantics. Character comparison is based on the value of the character in the database character set. One character is greater than another if it has a highercharacter set value. If the value returned by this function is character data, its datatype is always VARCHAR2.

Syntax:
The syntax for the greatest function is:
greatest( expr1, expr2, ... expr_n )
expr1, expr2, . expr_n are expressions that are evaluated by the greatest function.

Example:
SELECT GREATEST ('A', 'B', 'C') "Greatest" FROM DUAL;
Greatest
--------
C

SELECT GREATEST (1, 9, 11) "Greatest" FROM DUAL;
Greatest
--------
11

SELECT GREATEST ('1', '9', '11') "Greatest" FROM DUAL;
Greatest
--------
9

GREATEST is not to be used on a singular item. Although it will return values with single item, as input the effect is not useful. Use GREATEST when you need to get the greatest values from a list of values. MAX on the other hand will return the max value on a column.

Applies To: * Oracle 8i, Oracle 9i, Oracle 10g, Oracle 11g

RMAN Commands

RMAN


RMAN is short for Recovery Manager.

When the databases use the controlfile as catalog.

Following is a short list of the most common commands.

To connect to RMAN:>>

rman target /

 
 show full backups:>> list backup of database;
 show backups:>> list backup summary;
 show the archivelogs:>> list archivelog all;
 show the datafiles:>> report schema;
 show RMAN settings:>> show all;
 remove a backup:>> delete backupset #;
 remove several backups:>> delete noprompt backup of database completed before 'sysdate-7';
 remove old archivelogs:>> delete noprompt archivelog until time 'sysdate-7';
 test restore of backup:>> restore database validate;
 test restore of controlfile:>> restore controlfile validate;
 check validity:>> crosscheck backup;
 see what needs backing up:>> report need backup;

Oracle Text Indexing

Text Indexes


Oracle Text Indexing


There is a mechanism in the Oracle database to do more than just index a column. These are called domain indexes, one of which is a text index. (Others include Spatial and XML).



Limitations we Know About.


The limitations of the ordinary character column index are all too apparent to many database developers. The current indexes allow for a match from the first position in the column (surname LIKE ‘SMI%’). The match will find all surnames that start with the initial letters ‘SMI’ and have any number of characters following. To gain any benefit from the index the match must be exact in the initial few characters, including case and spacing. To find a match for a column where only the word ending is known (surname LIKE ‘%ING’ or the word does not start at the beginning position in the column (name LIKE ‘%JOHN%’).



Why?


Context indexes can search millions of rows of documents faster than most LIKE operations can run over 10000 rows of character data. You may (will) notice several new tables (created by and maintained by Oracle) for each text index, these hold the reverse tree blobs used to match tokens (search terms) to documents (columns, URLs, files etc....).



The user CTXSYS maintains all the context operations and metadata.



Starting with Context Indexes


At some stage it becomes necessary to search for a word in a column of text. This is where a domain index shows its strengths.



Superficially the index is created as would a normal index on a text column but includes the following :

CREATE INDEX idx_test_index ON mytab(mycol)
INDEXTYPE IS CTXSYS.CONTEXT;

There are many options available here to do with the storage and treatment of the column, but beyond the scope of this paper.



So there is a text index and what has changed.


For a start the way the data is queried has changed.

SELECT *
  FROM mytab
 WHERE contains (mycol, 'dummy') > 0;

This will return all rows from mytab where the column mycol has the word dummy anywhere contained anywhere along its length.



Example

MYTAB : MYCOL
This is the first row for test
This is the second row for test



Searching for rows with the word ‘this’ in would look something like:

SELECT *
  FROM mytab
 WHERE contains (mycol, 'this') > 0;

And the result...... both rows are returned (case is ignored, though there is the option in the contains clause to ask for a case match).



What if you need to narrow things down more and have more than one word to use in the search but you are unsure of their order or if there are words between your search terms?

SELECT *
  FROM mytab
 WHERE contains (mycol, 'this and second') > 0;

And the result would be the second row.



This is the second row for test



Keywords


From the above query you can see that keyword can be included in the search string to and the list of words is extensive but the most common words are

· AND

· OR

· NOT

· NEAR

· FUZZY

· %

· ?



If you are searching for a keyword you can wrap the word in curly braces {and} and it will not be parsed as an instruction, so you could have a search string that looks like

‘{cats} and {and} and {dogs}’

Scoring


Scoring helps to find the most relevant document. This is similar to the mechanism used by Google etc. to return the most relevant results.


It works by ‘counting’ the number of ‘hits’ in each document and ranking the documents in order of hits, unfortunately this means that – like Google – you may not get the results you want merely the results with the most matches of search terms.



Progressive Relaxation
You may be asked to return results, regardless of the accuracy, and this mechanism allows you to ensure that the ‘bad’ results appear last.

The search term is progressively relaxed – according to rules you set up – and the results gathered and returned according to the level of match they achieved.

SELECT /*+FIRST_ROWS(10)*/
       *
  FROM my_search_table
 WHERE contains
          (searchcolumn,
           'odeon cinematransform((TOKENS, "", "", " "))transform((TOKENS,"", "", " and "))transform((TOKENS, "", "", " , "))transform((TOKENS,"$", "", " and "))'
          ,1) > 0;

In the query above we are searching for odeon cinema and the rules are

Search for the phrase ‘odeon cinema’
Search for the words ‘odeon’ and ‘cinema’ anywhere in the document.
Search for the words ‘odeon’ or ‘cinema’ anywhere in the document.
Search for words ending like ‘odeon’ and ‘cinema’


The query can be rewritten as

SELECT /*+FIRST_ROWS(10)*/
       *
  FROM my_search_table se
 WHERE contains
          (searchcolumn,
           'odeon cinemaodeon and cinemaodeon, cinema$odeon and $cinema',1
          ) > 0;

Which may or may not be more apparent.



Advanced features:


Multi table queries:

So far we have only looked at single column indexes, but it is possible to have one index over multiple columns in the same table, multiple columns across multiple tables and master detail table / columns.



Ignoring words

You can create a personalised stoplist where you do not want the word ‘the’ to be indexed. An index created using this stoplist would return no results if ‘the’ was the only search term.



Thesaurus

You can create a thesaurus that holds all the abbreviations / expansions of words e.g. st – street , rd – road etc..... You would store either abbreviation or expansion and can query on either.



Fuzzy matching

In much the same way that SOUNDEX works you can ask for fuzzy matching either accepting the defaults by using the ? (question mark) operator, or a finer control using the fuzzy operator where you specify the ‘degree’ of fuzziness you require and how many steps removed from the original you are willing to look.



These are probably the most commonly used features of text searching, but there are many more, but as with all domain indexes can be tricky (infuriating) to implement.

External Tables in Oracle

External Tables

Uploading Daily Files.

If you can load a file using SQLLDR you can now load the same file using external tables. Caveat - as long as the file is on the database server.

You will first need to prove that you can load the file to the database using SQLLDR.

Then, as DBA, create a directory in the database and grant read and write to your user. You need write access to the directory, because Oracle will update the logfile each time you 'load' the file. The logfile could in theory be placed in another 'writeable'
directory.

create or replace directory COMET_LOAD as '/app/toolkit/COMET';
grant read on directory COMET_LOAD to cometimport;
grant write on directory COMET_LOAD to cometimport;

Then using SQLLDR you can generate the commands to build your external table

 SQLLDR .....all your commands...... EXTERNAL_TABLE=GENERATE_ONLY.

Opening up the logfile will reveal the create table command necessary to build an external table on the file mentioned in your SQLLDR statement.

CREATE TABLE COMETIMPORT.LINK_DYNAMIC_EXTERNAL
(
 TOOLKIT_LINK_ID     VARCHAR2(255 BYTE),
 CONGESTIONPERCENT   NUMBER,
 CURRENTFLOW         NUMBER,
 AVERAGESPEED        NUMBER,
 LINKTRAVELTIME      NUMBER,
 LASTUPDATED         DATE,
 EXITBLOCKED         NUMBER,
 LINKSTATUS          NUMBER,
 JOURNEYCOUNT        NUMBER,
 TYPE_OF_DAY         VARCHAR2(255 BYTE),
 DAYSLICE            VARCHAR2(255 BYTE),
 TOOLKIT_TYPE_OF_DAY VARCHAR2(255 BYTE)
)
ORGANIZATION EXTERNAL
( TYPE ORACLE_LOADER
  DEFAULT DIRECTORY COMET_LOAD
  ACCESS PARAMETERS
  ( RECORDS DELIMITED BY NEWLINE CHARACTERSET US7ASCII
    BADFILE 'COMET_LOAD':'comet_bad_xt.bad'
    LOGFILE 'COMET_LOAD':'comet_lg_xt.log'
    READSIZE 1048576
    FIELDS TERMINATED BY "," OPTIONALLY ENCLOSED BY '"' LDRTRIM
    MISSING FIELD VALUES ARE NULL
    REJECT ROWS WITH ALL NULL FIELDS
    (
    TOOLKIT_LINK_ID CHAR(255)
      TERMINATED BY "," OPTIONALLY ENCLOSED BY '"',
    CONGESTIONPERCENT CHAR(255)
      TERMINATED BY "," OPTIONALLY ENCLOSED BY '"',
    CURRENTFLOW CHAR(255)
      TERMINATED BY "," OPTIONALLY ENCLOSED BY '"',
    AVERAGESPEED CHAR(255)
      TERMINATED BY "," OPTIONALLY ENCLOSED BY '"',
    LINKTRAVELTIME CHAR(255)
      TERMINATED BY "," OPTIONALLY ENCLOSED BY '"',
    LASTUPDATED CHAR(255)
      TERMINATED BY "," OPTIONALLY ENCLOSED BY '"'
    DATE_FORMAT DATE MASK "YYYY-MM-DD HH24:MI:SS",
    EXITBLOCKED CHAR(255)
      TERMINATED BY "," OPTIONALLY ENCLOSED BY '"',
    LINKSTATUS CHAR(255)
      TERMINATED BY "," OPTIONALLY ENCLOSED BY '"',
    JOURNEYCOUNT CHAR(255)
      TERMINATED BY "," OPTIONALLY ENCLOSED BY '"',
    TYPE_OF_DAY CHAR(255)
      TERMINATED BY "," OPTIONALLY ENCLOSED BY '"',
    DAYSLICE CHAR(255)
      TERMINATED BY "," OPTIONALLY ENCLOSED BY '"',
    TOOLKIT_TYPE_OF_DAY CHAR(255)
      TERMINATED BY "," OPTIONALLY ENCLOSED BY '"'
    )
  )
  LOCATION (COMET_LOAD:'linkdynamic20090121.csv')
)
REJECT LIMIT UNLIMITED
NOPARALLEL
NOMONITORING;

You can create the table without the file being present, querying it without the file present will generate an error.

Extracting the data is then done as an insert into...... select * from .... query

If you need to load another file, with a different name, you can perform an alter table command.

ALTER TABLE link_dynamic_external LOCATION ('linkdynamic20090101.csv');

Navigating XML in Oracle

A few code snippets for navigating and extracting dynamically from XML in PL/SQL.

--The executable section of the traverse_and_display procedure. 
BEGIN
  -- get all elements
  nodes := xmldom.getElementsByTagName (doc, '*');

  -- loop through elements
  FOR node_index IN 0 .. xmldom.getLength (nodes) - 1
  LOOP
     one_node := xmldom.item (nodes, node_index);

     display_element (one_node);

     node_map := xmldom.getAttributes (one_node);

     FOR attr_index IN
        0 .. xmldom.getLength (node_map) - 1
     LOOP
        display_attribute (node_map, attr_index);
     END LOOP;
  END LOOP;
END traverse_and_display;



-- The code required for displaying the name and value of an element. 
PROCEDURE display_element (node IN xmldom.DOMNode)
IS
  one_element   xmldom.DOMElement;
  value_node    xmldom.DOMNode;
BEGIN
  one_element := xmldom.makeElement (node);
  DBMS_OUTPUT.put_line ('Element: ' ||
                       xmldom.getTagName (one_element
                       )
  );
  value_node := xmldom.getFirstChild (node);
  DBMS_OUTPUT.put_line ('Value: ' ||
                       xmldom.getNodeValue (value_node
                       )
  );
END;



--Displaying the value of an attribute. 
PROCEDURE display_attribute (
  node_map     IN   xmldom.DOMNamedNodeMap,
  attr_index   IN   PLS_INTEGER
)
IS
  one_node   xmldom.DOMNode;
  attrname   VARCHAR2 (100);
  attrval    VARCHAR2 (100);
BEGIN
  one_node := xmldom.item (node_map, attr_index);
  attrname := xmldom.getNodeName (one_node);
  attrval := xmldom.getNodeValue (one_node);
  DBMS_OUTPUT.put_line ('   ' || 
        attrname || ' = ' || attrval
  );
END;

Virtual Private Databases (VPDs)

The Virtual Private Database (VPD) concept allows multiple users and applications to access a common shared database server while logically separating their data based on security policies. Thus each set of users or applications appears to have its own “virtual private database.”

Create security policies that are associated with tables or views. (Associating security policies with tables and views, rather than applications or user ids, means that the security system is contained and managed from within the database, rather than in applications.)

Security policies can be created for the different SQL statements (SELECT, INSERT, UPDATE and DELETE). The server invokes the security policy for the table or view that is accessed with the particular SQL statement. This is implemented by Oracle’s transparent rewrite of the query where it appends a WHERE clause that reflects the proper security policy. For example, this SELECT statement:

SELECT * FROM DEPARTMENT_TABLE ;

might be internally rewritten as this statement after applying a security policy for the DEPARTMENT_TABLE when SELECTs are involved:

SELECT * FROM DEPARTMENT_TABLE
WHERE DEPT = ‘ACCOUNTING’;

The GUI to create and manage security policies is called the Oracle Policy Manager. It is part of the Oracle Enterprise Manager (OEM) GUI. With it, you can create and manage security policies, associate them with tables and views, and create and manage application contexts (described below).

• VPDs were introduced in Oracle8i, and 9i greatly extends their usefulness through new features:
  
• Secure application roles
  
• Global application contexts
  
• Partitioned fine-grained access control

The next few sections discuss these new features.

Secure Application Role

8i introduced Secure Application Context, the ability to tailor or limit access to tables and views based on attributes of the user’s session. 9i takes this a step further. Now you can assign a set of security roles for each application, then assign users to the roles. Here are the steps to set this up:

1. Create the role. The role does not refer to a password. Instead it refers to a stored procedure that authenticates whether the user is allowed to use this role.
This example creates a Security Application Role, which is authenticated by the procedure verify_dba_manager:
CREATE ROLE dba_manager IDENTIFIED USING verify_dba_manager;

2. Now write the procedure that authenticates the user for this role. It should contain logic that either verifies the user can be set to this role, or that the user is not eligible to use this role. The Oracle procedure DBMS_SESSION.SET_ROLE is used for this purpose. Here’s an example:
CREATE OR REPLACE PROCEDURE verify_dba_manager
AUTHID CURRENT_USER IS instring VARCHAR2(30);
BEGIN
.... IF (user context is appropriate) ...THEN
DBMS_SESSION.SET_ROLE(‘dba_manager’);
ELSE
RETURN;
END IF;
END;

3. When the application starts, it should enable the role(s) it will use by the
SET_ROLE statement.
Remember that as in 8i, an application can query its context by using the DBMS_SESSION.SYS_CONTEXT package:
SYS_CONTEXT(‘userenv’, ‘attribute’) ;

Global Application Context

When building web applications, for performance reasons you typically use a middle tier with Oracle’s connection pooling. You’ll also want to pool and reuse application contexts – this is referred to as Global Application Context. This is more scalable because applications reuse Global Application Contexts instead of creating individual user sessions. 9i contains a set of procedures to support this feature in its package DBMS_SESSION:

Procedure:
SET_CONTEXT - Sets the global application context
CLEAR_CONTEXT - Clears the context
SET_IDENTIFIER - Associates a pre-established global application context (an existing database connection) with a particular client session
CLEAR_IDENTIFIER - Releases the client session’s access to the global application context
SYS_CONTEXT - Retrieves info about the context associated with the SET_IDENTIFIER that was executed

Here are the steps to using global application contexts:
1. Create a global application context, using the ACCESSED GLOBALLY phrase, to show that it is available for reuse:
CREATE CONTEXT dba_manager USING dba.init ACCESSED GLOBALLY;
2. The middle tier application starts up and establishes database connections. When a user logs in, the application assigns a temporary identifier ID for it. The application returns the ID as a cookie residing on the user’s machine and browser, or maintains it itself.
3. When the application invokes the ‘dba.init’ package in order to initialize the application context, the package issues SET_CONTEXT commands using the temporary ID to set the application’s context.
4. The application gets an existing database connection for this user session by issuing SET_IDENTIFER.
5. The user uses the database application and makes authorized database calls.
6. When done, the client’s access to the global application context is released by CLEAR_IDENTIFIER and CLEAR_CONTEXT.

Partitioned Fine-Grained Access Control

In 9i, you can assign multiple security policies for a table or view. All policies must be satisfied to get data access. In other words, Oracle treats multiple policies as if there is a logical AND relating them.

For better control of the security facility, several Security Policies may be combined into a Security Policy Group. The Group is then associated with an application context, known as the driving application context. Oracle refers to the driving application context to identify and apply the security policy group when users try to reference the data. Oracle applies all security policies in that group to determine if the user can access the data. So security policy groups allow you to partition fine-grained access control based on the driving application context.

In the Oracle Policy Manager GUI tool, there is a folder for Fine-Grained Access Control, and under it, a folder for the Policy Groups. Security Policies are organized under a security policy group (by default called SYS_DEFAULT). Oracle provides the DBMS_RLS package for working with policies, with such procedures as CREATE_POLICY_GROUP, ADD_GROUPED_POLICY, and ADD_POLICY_CONTEXT.

Here’s an example of how to use partitioned fine-grained access control:
Two groups of users have different security needs. Set up a driving application context for each user group (call them USER_1_CONTEXT and USER_2_CONTEXT).
Now set up a security policy group for each (call them USER_1_POLICY_GROUP and USER_2_POLICY_GROUP).

When a user in the first driving application context tries to access data, Oracle applies all security policies in USER_1_POLICY_GROUP and then all in SYS_DEFAULT. When a user in the USER_2_CONTEXT tries to access data, Oracle applies all policies in USER_2_POLICY_GROUP, and then those in SYS_DEFAULT. Only if all policies are satisfied can the user access data.

If you do not set up the driving application context, or if it is NULL, Oracle executes all the policies associated with the table or view. This ensures applications can not circumvent security.
Fine Grained Auditing (FGA)

You can create and manage audit policies using the new Oracle package DBMS_FGA and its procedures ADD_POLICY, DROP_POLICY, DISABLE_POLICY, and ENABLE_POLICY.
When you create an audit policy, it is stored in Oracle’s data dictionary table DBA_AUDIT_POLICIES. When the policy is triggered (through appropriate data access), the audit event data is stored in table DBA_FGA_AUDIT_TRAIL. The information collected includes session id, username, timestamp, policy name, and the SQL query text.

The audit policy is only triggered when the condition you specified in the ADD_POLICY statement is met.

You can also specify an audit column to restrict audits. Only queries meeting the audit condition and referencing the audit column will be audited. Take this example audit policy:
DBMS_FGA.ADD_POLICY (
object_schema => ‘dba_group’,
object_name => ‘dept’,
policy_name => ‘audit_drones’,
audit_condition => ‘emp_status = ‘‘DRONE’ ’ ’,
audit_column => ‘drone_id’ ) ;

Prior to adding the audit_column, any access to the object that meets the audit_condition triggers an audit event. Adding the audit_column, as in the policy definition above, means the audit only occurs if the audit_condition is met and the audit_column is referenced in the user’s query.

You can additionally specify an audit event handler when creating an audit policy. Add lines like these to the sample code above to specify the event handler:
handler_schema => ‘dba’,
handler_module => ‘dba_drone_handler’,

Now, code the audit event handler named DBA_DRONE_HANDLER like any other stored procedure, using the CREATE OR REPLACE PROCEDURE statement.

Datafiles and Tablespaces

Datafiles

Every datafile has two associated file numbers:

An absolute file number uniquely identifies a datafile in the database, Arelative file number uniquely identifies a datafile within a tablespace

At least one datafile is required for the SYSTEM tablespace of a database. You can add datafiles to tablespaces, subject to the datafile limits:

Operating system limit, Oracle system limit, Control file upper bound, Instance or SGA upper bound.

The first datafile must be at least 7M to contain the initial data dictionary and rollback segment. Tablespace location is determined by the physical location of the datafiles that constitute that tablespace. Datafiles should not be stored on the same disk drive that stores the database's redo log files. When creating a tablespace, you should estimate the potential size of the database objects and add sufficient files or devices accordingly. To add datafiles to a tablespace, you use the ALTER TABLESPACE...ADD DATAFILE statement. You can create datafiles or alter existing datafiles so that they automatically increase in size when more space is needed. This will:

Reduce the need for immediate intervention when a tablespace runs out of space. Ensure applications will not halt because of running out of space.

You may find out if a datafile is auto-extensible by querying DBA_DATA_FILES. You can specify automatic file extension by specifying an AUTOEXTEND ON clause when you create datafiles. You can enable or disable automatic file extension for existing datafiles using the SQL statement ALTER DATABASE. You can manually resize a datafile using the SQL statement ALTER DATABASE. Offline datafiles cannot be accessed. Files of a read-only tablespace can independently be taken online or offline using the DATAFILE option of the ALTER DATABASE statement. If you want to bring a datafile online or take it offline, you must have the ALTER DATABASE system privilege.

To bring a datafile online or take it offline, the database must be opened in exclusive mode.

Tablespaces

You may use the SQL statement CREATE TABLESPACE or CREATE TEMPORARY TABLESPACE to create a new tablespace You may use the ALTER TABLESPACE or ALTER DATABASE statements to alter the tablespace With Oracle8i, you can create locally managed tablespaces Locally managed tablespaces use bitmaps but NOT SQL dictionary tables to track used and free space. temporary tablespaces:

• improve the concurrence of multiple sort operations,
• reduce multiple sort operations overhead,
• avoid Oracle space management operations.

All tablespaces are initially created as read-write you may use the READ ONLY keywords in the ALTER TABLESPACE statement to change a tablespace to read-only you may use the READ WRITE keywords in the ALTER TABLESPACE SQL statement to change a tablespace to allow write operations SYSTEM tablespace can never be made read-only. Read-only tablespace:

prevents updates on all tables in the tablespace regardless of a user's update privilege level, eliminate the need to perform backup and recovery of large, static portions of a database, provide a means of completely protecting historical data.

Any tablespace except the SYSTEM tablespace can be dropped. Once a tablespace has been dropped, the tablespace's data is not recoverable. Back up the database completely immediately before and after dropping a tablespace When dropping a tablespace, only the file pointers in the control files are dropped. To free previously used disk space, delete the datafiles of the dropped tablespace using the appropriate operating system commands Partitioned Tables cannot be moved via transportable tablespaces when only a subset of the partitioned table is contained in the set of tablespaces.

Storage Parameters

PCTFREE

set the percentage of a block to be reserved for possible updates to rows that already are contained in that block, after the free space in a data block reaches PCTFREE, no new rows are inserted in that block until the percentage of space used falls below PCTUSED, can specify any integer between 0 and 99 (inclusive) for PCTUSED, the sum of PCTUSED and PCTFREE should not exceed 100.

smaller PCTFREE:

Reserves less room for updates, Allows inserts to fill the block more completely, suitable for a segment that is rarely changed.

larger PCTFREE:

Reserves more room for future updates, May require more blocks for the same amount of inserted data, May improve update performance, suitable for segments that are frequently updated.

PCTUSED

smaller PCTUSED: Reduces processing costs incurred during UPDATE and DELETE, Increases unused space.

larger PCTUSED: Improves space efficiency, Increases processing cost during INSERTs and UPDATEs.

Other parameters:

INITIAL - size, in bytes, of the first extent allocated when a segment is created

NEXT- size, in bytes, of the next incremental extent to be allocated for a segment

PCTINCREASE – percentage by which each incremental extent grows over the last incremental extent allocated for a segment 0 means all incremental extents are the same size it cannot be negative.

MINEXTENTS - total number of extents to be allocated when the segment is created

MAXEXTENTS - total number of extents, including the first, that can ever be allocated for the segment FREELIST GROUPS - number of groups of free lists for the database object you are creating FREELISTS - number of free lists for each of the free list groups for the schema object

OPTIMAL – for use with rollback segments

BUFFER_POOL – defines a default buffer pool (cache) for a schema object Not valid for tablespaces Not valid for rollback segments.Rollback Segments

Private rollback segment: acquired explicitly by an instance when the instance opens the database if it is named in the ROLLBACK_SEGMENTS parameter in the initialization parameter file, form a pool of rollback segments for use by that any instance requiring a rollback segment, database with the Parallel Server option can have only public segments, public and private rollback segments are identical on database with no Parallel Server option, When an instance starts, it acquires TRANSACTIONS/TRANSACTIONS_PER_ROLLBACK_SEGMENT rollback segments by default, To ensure that the instance acquires particular rollback segments that have particular sizes or particular tablespaces, specify the rollback segments by name via the ROLLBACK_SEGMENTS parameter. Recommended:
create one tablespace specifically to hold all rollback segments, separated with the data.

V$ Table Reference

DATABASE BACKUPS, ARCHIVE FILES, AND RECOVERY

V$ARCHIVE
V$ARCHIVED_LOG
V$ARCHIVE_DEST
V$BACKUP
V$BACKUP_CORRUPTION
V$BACKUP_DATAFILE
V$BACKUP_DEVICE
V$BACKUP_PIECE
V$BACKUP_REDOLOG
V$BACKUP_SET
V$DELETED_OBJECT
V$RECOVERY_FILE_STATUS
V$RECOVERY_LOG
V$RECOVERY_STATUS
V$RECOVER_FILE

CACHE MANAGEMENT

V$CACHE
V$DB_OBJECT_CACHE
V$LIBRARYCACHE
V$ROWCACHE
V$SUBCACHE

CONTROL FILES

V$CONTROLFILE
V$CONTROLFILE_RECORD_SECTION

CURSORS AND SQL STATEMENTS

V$OPEN_CURSOR
V$SQL
V$SQLAREA
V$SQLTEXT
V$SQLTEXT_WITH_NEWLINES
V$SQL_BIND_DATA
V$SQL_BIND_METADATA
V$SQL_CURSOR
V$SQL_SHARED_MEMORY

DATABASES AND INSTANCES

V$ACTIVE_INSTANCES
V$BGPROCESS
V$BH
V$COMPATIBILITY
V$COMPATSEG
V$COPY_CORRUPTION
V$DATABASE
V$DATAFILE
V$DATAFILE_COPY
V$DATAFILE_HEADER
V$DBFILE
V$DBLINK
V$DB_PIPES
V$INSTANCE
V$LICENSE
V$OFFLINE_RANGE
V$OPTION
V$SGA
V$SGASTAT
V$TABLESPACE
V$VERSION

SQL*LOADER DIRECT LOAD OPTION

V$LOADCSTAT
V$LOADPSTAT
V$LOADTSTAT

FIXED VIEWS

V$FIXED_TABLE
V$FIXED_VIEW_DEFINITION
V$INDEXED_FIXED_COLUMN

I/O

V$FILESTAT
V$WAITSTAT

LATCHES AND LOCKS

V$BUFFER_POOL
V$CACHE_LOCK
V$CLASS_PING
V$DLM_CONVERT_LOCAL
V$DLM_CONVERT_REMOTE
V$DLM_LATCH
V$DLM_MISC
V$ENQUEUE_LOCK
V$EVENT_NAME
V$FALSE_PING
V$FILE_PING
V$LATCH
V$LATCHHOLDER
V$LATCHNAME
V$LATCH_CHILDREN
V$LATCH_MISSES
V$LATCH_PARENT
V$LOCK
V$LOCK_ACTIVITY
V$LOCK_ELEMENT
V$LOCKED_OBJECT
V$LOCKS_WITH_COLLISIONS
V$PING
V$RESOURCE
V$RESOURCE_LIMIT
V$TRANSACTION_ENQUEUE
V$_LOCK
V$_LOCK1

MISCELLANEOUS

V$TIMER
V$TYPE_SIZE
V$_SEQUENCES

MULTI-THREADED AND PARALLEL SERVERS

V$CIRCUIT
V$DISPATCHER
V$DISPATCHER_RATE
V$MTS
V$QUEUE
V$REQDIST
V$SHARED_SERVER
V$THREAD

OVERALL SYSTEM PERFORMANCE

V$GLOBAL_TRANSACTION
V$OBJECT_DEPENDENCY
V$SHARED_POOL_RESERVED
V$SORT_SEGMENT
V$SORT_USAGE
V$STATNAME
V$SYSSTAT
V$SYSTEM_CURSOR_CACHE
V$SYSTEM_EVENT
V$TRANSACTION

PARALLEL QUERY OPTION

V$EXECUTION
V$EXECUTION_LOCATION
V$PQ_SESSTAT
V$PQ_SLAVE
V$PQ_SYSSTAT
V$PQ_TQSTAT

ORACLE PARAMETERS

V$NLS_PARAMETERS
V$NLS_VALID_VALUES
V$PARAMETER
V$SYSTEM_PARAMETER

REDO LOGS

V$LOG
V$LOGFILE
V$LOGHIST
V$LOG_HISTORY

ROLLBACK SEGMENTS

V$ROLLNAME
V$ROLLSTAT

SECURITY AND PRIVILEGES

V$ENABLEDPRIVS
V$PWFILE_USERS

SESSIONS

V$ACCESS
V$MYSTAT
V$PROCESS
V$SESSION
V$SESSION_CONNECT_INFO
V$SESSION_CURSOR_CACHE
V$SESSION_EVENT
V$SESSION_LONGOPS
V$SESSION_OBJECT_CACHE
V$SESSION_WAIT
V$SESSTAT
V$SESS_IO

Create a sequence AAAAA - ZZZZZ

Create a sequence AAAAA - ZZZZZ

--sequence AAAAA to ZZZZZ....... wow.

 select   CHR(65+MOD((rownum-1)/(26*26*26*26), 26))
        ||CHR(65+MOD((rownum-1)/(26*26*26), 26))
        ||CHR(65+MOD((rownum-1)/(26*26), 26))
        ||CHR(65+MOD((rownum-1)/26, 26))
        ||CHR(65+MOD((rownum-1), 26)) as AA_ZZ_sequence
   from dual 
connect by level <= 26*26*26*26*26;

This is along the lines of a trick sql statement and demonstrates what can be achieved when you use the connect by terminology.

What is a DBA?

A DBA is seen as the best person to go to when there is a database problem and even sometimes a general problem. Why? Well a DBA is usually someone who has spent most of their working life solving problems and finding ways to do things more simply or clearly. More often than not, a DBA will have the answer to a database problem or can point the way to the solution.

A DBA will have a general background and working knowledge of may different areas of IT:

Data administration – The data administration role, although important, is often undefined in many IT organizations. Those responsibilities, by default, are usually awarded to the shop's database administration unit. Data administrators view data from the business perspective and must have an understanding of the business to be truly effective. DBAs organize, categorize and model data based on the relationships between the data elements themselves and the business rules that govern them. Data administrators provide the framework for defining and interpreting data and its structure enabling the organization to share timely and accurate data across diverse program areas resulting in sound information-based decisions.

Operating system – The only folks that spend more time in the operating system than DBAs are the system administrators themselves. Database administrators must have an intimate knowledge of the operating systems and hardware platforms their databases are running on. DBAs automate many functions and are usually accomplished operating system scriptwriters. They have a strong understanding of operating system kernel parameters, disk and file subsystems, operating system performance monitoring tools and various operating system commands.

Networking – Database administrators are responsible for end-to-end performance management. End users don't care where the bottleneck is, they just want their data returned quickly. DBAs need to have expertise in basic networking concepts, terminologies and technology to converse intelligently with LAN administrators.

Data Security – Much to the consternation of many business data owners, the DBA is usually the shop's data security specialist. They have complete jurisdiction over the data stored in their database environments. The DBA uses the internal security features of the database to ensure that the data is available only to authorized users.

Oracle Database Administration Responsibilities.
------------------------------------------------

There is no one exhaustive list of all the duties that a DBA may be asked to perform. As a general rule though, a DBA will encounter most combinations of possible tasks in the progression of their career.

Those new to Oracle should work through topics at their leisure by picking one area and reading the Oracle documentation, trying out the exercises and attempting various smaller projects to try out your new skills. Then move on to a new area. You will soon find that after completing a few sections there are similarities in ideas and overlaps in knowledge between areas of Oracle.

Taking Oracle Classroom Education.
------------------------------------

When is the best time to take the classes? This may sound trite, but it is best to follow Oracle's recommendations on the sequence of classes. Take the intro classes before taking the more advanced classes. If you have the luxury (meaning you aren't the only DBA in your shop), gain some day-to-day experience before taking the more advanced classes (SQL or database tuning, backup and recovery, etc.). You shouldn't be asking questions like "What is an init.ora parameter file, anyway?" in a tuning or backup and recovery class. Instructors don't have the time and your fellow students won't have the patience to bring you up to speed before continuing on to more advanced topics.

If it is an emergency situation, like your shop's DBA gives two week's notice (Oracle DBAs are now considered to be migratory workers by many companies) bring yourself up to speed by:

Reading as much information as you can on the class you are taking before you take it. Oracle press books, Oracle's Technet web site and non-Oracle consulting company's web sites contain a wealth of information. Read the course descriptions and course content at Oracle Education's website ( http://education.oracle.com). You may not know the mechanics, but you do need to know the lingo and the concepts used.

When you attend the class, inform your instructor that you don't have a lot of day-to-day experience. We want you to get the most out of class, we'll help you by staying later, coming in earlier and giving you reading recommendations.

Familiarize yourself with the next day's material by reading it the night before. If an instructor sees that you are making an extra effort to overcome your lack of day-to-day experience by coming in early, staying late and being prepared, they will be more prone to help you. Instructors like to see people excited about what we are teaching. Seeing someone enthused about learning makes us want to make sure they get the most out of class. Don't let your ego get in the way of you getting the utmost benefit of the class you are taking - ask questions and get involved!

Oracle9i Curriculum Changes.
------------------------------
The Oracle9i Instructor Led Training (ILT) Classes have been dramatically changed for Oracle9i. The Oracle9i curriculum consists of the following ILT classes:

Introduction to Oracle9i: SQL (5 Days) - This class prepares students for Oracle Certification Test #170-007. You'll notice that the title is a little different that the Oracle8i Introductory Class. The Oracle8i class titled "Introduction to Oracle8i SQL and PL/SQL" included several chapters on understanding and writing Oracle's procedural language PL/SQL. PL/SQL is no longer taught in the Oracle9i:SQL introductory class. It has been replaced with information on Oracle's ISQL*Plus product, correlated subqueries, GROUP BY extensions (ROLLUP, CUBE), multitable INSERT statements and how to write SQL statements that generate SQL statements.

Introduction to Oracle8i SQL and PL/SQL (5 days) - Although titled as an Oracle8i class, the class is intended to prepare students for Oracle9i Certification Test #170-001 (Introduction to Oracle SQL and PL/SQL). The class provides in-depth information on SQL including information on SQL basics, joins, aggregations and subqueries. In addition, the class also provides information on basic PL/SQL programming.

Oracle9i Database Administration Fundamentals I (5 Days) - The Oracle9i Database Administration Fundamentals I class prepares students for Certification Test #170-001. The new Oracle9i DBA intro class is much like it's Oracle8i counterpart that was titled "Enterprise DBA Part1A: Architecture and Administration." Although much of the material remains the same, there are a few changes that should be noted: the export and import information has been moved to the Fundamentals II class, Oracle's load utility (SQL*Loader) is no longer covered and more time is spent learning and using Oracle's administrative toolkit Oracle Enterprise Manager.

Oracle9i Database Administration Fundamentals II (5 Days) - This class prepares students for Certification Test #170-032. The class combines a small subset of the material covered in the two-day Oracle8i "Enterprise DBA Part3: Network Administration" class with all of the information covered in the four-day "Enterprise DBA Part1B: Backup and Recovery" class. The class also contains information on Oracle's Export and Import utilities for good measure.

Oracle9i Database Performance Tuning (5 Days) - The Oracle9i database tuning class prepares students for Certification Test #170-033 and follows the same general format of the Oracle8i tuning class with four days of classroom instruction followed by a one-day workshop.


Oracle9i Oracle Certifications.
-------------------------------
Oracle has also changed the certification process for Oracle9i. Database administrators wanting to become Oracle8i Certified Professionals (OCP) were required to pass 5 certification tests, one for each database administration class that Oracle offered: Intro, DBA Part 1A: Architecture and Administration, DBA Part 1B: Backup and Recovery, DBA Part 2: Tuning and Performance and DBA Part 3: Network Administration. Oracle has changed the certification process for Oracle9i by adding two new certifications (Associates and Masters) and requiring additional hands-on classroom training to obtain Oracle Certified Professional certification.

Oracle Certified Database Associate (OCA).
------------------------------------------
Two exams are required to become an Oracle Certified Database Associate. Those wanting to become Oracle Certified Database Associates must pass either the "Intro To Oracle9I: SQL" or the "Intro to Oracle: SQL and PL/SQL" certification tests and pass the "Oracle9i Database Administration Fundamentals I" exam.

The minimum scoring requirements and test durations for the Oracle Certified Database Associate tests are as follows: Introduction to Oracle: SQL and PL/SQL - Oracle Certification Test #170-007 contains 57 questions. The test requires 68% (39 questions) to be correctly answered to pass. The test must be completed within two hours.

Introduction to Oracle9i: SQL – Oracle Certification Test #1Z0-007 contains 57 questions. The test requires 70% (40 questions) to be answered correctly to pass. The test must be completed within two hours.

Note: This is the only certification test that can be taken online at the Oracle Education website (http://education.oracle.com). If you do not have good Internet access, Oracle also allows the test to be taken at an Oracle University Training Center or an Authorized Prometric Testing Center. All other certification tests must be taken at a Prometric (see section on Prometric Testing Centers below).

Oracle Database: Fundamentals I - Oracle Certification Test #170-031 contains 60 questions. The test requires 73% (44 questions) to be correctly answered to pass. Students must complete the test within 1.5 hours.

Oracle Certified Database Professional (OCP).
---------------------------------------------
Administrators wanting to become Oracle Certified Database Professionals must first start their journey by earning their Oracle Certified Database Associate certification. In addition, candidates must also pass two additional tests: "Oracle9i Database Administration Fundamentals II" and "Oracle9i Database Performance Tuning". The final requirement is to attend at least one of the following Oracle University hands-on courses:

Oracle9i Introduction to SQL
Oracle9i Database Fundamentals I
Oracle9i Database Fundamentals II
Oracle9i Database Performance Tuning
Oracle9i Database New Features
Introduction to Oracle: SQL and PL/SQL

The minimum scoring requirements and test durations for the
Oracle Certified Database Professional tests are as follows:

Oracle Database: Fundamentals II - Oracle Certification Test #170-032 contains 63 questions. The test requires a 77% correct answer score (49 questions) to be correctly answered to pass. Students must complete the test within 1.5 hours.

Oracle Database: Performance Tuning - Oracle Certification Test #170-033 contains 59 questions. The Performance Tuning test requires a 64% correct answer score (38 questions) to be correctly answered to pass. Students must complete the test within 1.5 hours.

Oracle Certified Master Database Administrator (OCM).
-----------------------------------------------------

Those wanting to reach "Oracle Nirvana" and become Oracle Certified Masters must first earn their Oracle Certified Professional (OCP) certification. Candidates must also attend two of the eight advanced Oracle University hands-on courses listed below:

Oracle Enterprise Manager 9i
Oracle9i SQL Tuning Workshop
Oracle9i Database: Implement Partitioning
Oracle9i Database: Advanced Replication
Oracle9i Database: Spatial
Oracle9i Database: Warehouse Administration
Oracle9i Database: Security
Oracle9i: Real Application Clusters

The final step is to attend (and pass) a two-day live application event that requires participants to complete a series of scenarios and resolve technical problems in an Oracle9i database environment. Attendees will be scored on their ability to successfully complete the assigned tasks.

Oracle 9i DBA OCP Upgrade Path.
-------------------------------
Database administrators wanting to continue to stay current as an Oracle Certified Professional are able to upgrade their certifications by completing the Oracle migration exams. Oracle Education provides multiple upgrade certification tests to allow administrators to upgrade their current certification to Oracle 9i OCP.

The number of tests the DBA must take depends upon their current certification. OCP DBAs must complete each upgrade test in order to upgrade their OCP credentials. An Oracle7.3 DBA OCPs would be required to pass exam #1Z0-010, 1Z0-020 and 1Z0-030 to upgrade their OCP credential to Oracle9i Database Administrator.

Each upgrade exam closely follows the material provided in the Oracle database new features classes, which provide information on all of the new "bells and whistles" contained in the release. Administrators studying for the upgrade certification should focus on the material provided in the New Features section of the documentation provided with the Oracle software.

Listed below are the migration exams that are currently available to previously certified Oracle DBAs (the descriptions are from our Oracle instructor's website):

Upgrade Exam: Oracle7.3 to Oracle8 OCP DBA (#1Z0-010). - This exam covers information provided in the Oracle8 New Features for Administrators class. The exam focuses on partitioned tables and indexes, parallelizing INSERT, UPDATE and DELETE operations, extended ROWIDS, defining object-relational objects, managing large objects ( i.e. LOBS, CLOBS), advanced queuing, index organized tables and Oracle8 security enhancements.

Upgrade Exam: Oracle8 to Oracle8i OCP DBA (#1Z0-020) - The Oracle8i upgrade exam covers information provided by the Oracle8i New Features for Administrators class. This exam focuses on the Oracle Java implementation, optimizer and query performance improvements, materialized views, bitmap index and index-organized enhancements and range, hash and composite partitioning. The exam also covers Oracle installer enhancements, locally managed tablespaces, transportable tablespaces and the Oracle8i database resource manager.

Upgrade Exam: Oracle8i to Oracle9i OCP DBA (#1Z0-030) - Like its aforementioned counterparts, the Oracle9i upgrade covers information provided by the Oracle9i New Features for Administrators Class. The exam covers information on fine grained auditing, partitioned fine grained access control, secure application roles, global context, flashback query, resumable space allocation, SPFILEs, log miner enhancements and recovery manager new features.

Other Recommend Classes.
------------------------
Oracle Education provides dozens of additional classes on Oracle technologies. These additional classes provide more in-depth information in key areas of database administration. A few recommendations on additional classes follow (the course descriptions are straight from our instructor's website):

Oracle9I Program with PL/SQL. An excellent class for database administrators wanting to learn more about Oracle's procedural language. This course introduces students to PL/SQL and helps them understand the benefits of this powerful programming language. Students learn to create stored procedures, SQL functions, packages, and database triggers.

Oracle9i Database: SQL Tuning Workshop R2. This course is designed to give the student a firm foundation in the art of SQL tuning. The participant learns the skills and toolsets used to effectively tune SQL statements. The course contains numerous workshops that allow students to practice their SQL tuning skills. The students learn EXPLAIN, SQL Trace and TKPROF, SQL*Plus AUTOTRACE.

Oracle Enterprise Manager 9i. This course is taught on Oracle9i Release 2. Students learn how to use Oracle Enterprise Manager to effectively administer a multiple database environment. This class is a must attend for those that want to make sure that they are utilizing Oracle Enterprise Manager to its fullest potential.

Oracle9i: New Features for Administrators R2. This course introduces students to the new features in contained in Oracle9i. All of the latest features are discussed and tested.

Managing Oracle on Linux. Students learn how to configure and administer the Oracle9i database on Linux. Database creation, configuration, automated startup/shutdown scripts, file system choices are just a few of the topics covered in this class. Hands-on lab exercise help students reinforce the knowledge obtained from lectures.

Preparing for the Oracle Certified Professional Exams.
------------------------------------------------------
The best time to take the exam is a week or two after taking the Oracle class that the exam pertains to. Passing the certification test is much easier when the information is fresh. The class workbook should be used as the primary study guide. I have passed every exam I have taken by studying only the information contained in the class workbooks. The classes are not required to obtain Oracle9i OCP certification, but the requirements have changed for Oracle9i OCP certification (see Oracle9i Oracle Certifications above).

The Oracle Education website (http://education.oracle.com) allows administrators to purchase practice exam tests. Free sample questions are also available. Practice tests provide the administrator with a firm understanding of the areas that they are strong in as well as the areas where they need to shore up their knowledge.

The Oracle provided practice tests provide a thorough coverage of the Oracle certification requirements and use the same test question technology as the real exams including simulations, scenarios, hot spots and case studies. Other practice test features include:

Tutorials and text references that enhance the learning process.

Random generation of test questions provides a dynamic and challenging testing environment.

Testing and grading by objective to allow administrators to focus on specific areas.

Thorough review of all answers (both correct and incorrect).

More questions provided than any other source.

Taking the Certification Exams.
-------------------------------
Oracle partners with Prometric Testing Centers to provide testing centers throughout the world. The Prometric Testing Center website ( http://www.2test.com/) provides a test center locator to help you find testing centers in your area.

The following hints and tips will prepare you for the day you take your certification tests:

You must have two forms of identification, both containing your signature. One must be a government issued photo identification.

Try to show up early (at least 15 minutes) before your scheduled exam. If you show up more than 15 minutes late, the testing center coordinator has the option of cancelling your exam and asking you to reschedule your test.

You cannot bring any notes or scratch paper to the testing center. Paper will be provided by the testing center and will be destroyed when you leave.

Testing center personnel will provide you with a brief overview of the testing process. The computer will have a demo that will show you how to answer and review test questions.

Don't leave any questions unanswered. All test questions left unanswered will be marked as incorrect.

Your exam score is provided to you immediately and the exam results are forwarded to Oracle Certification Program management. Make sure you keep a copy of your test results for your records.

If you fail a test, you must wait at least 30 days before retaking it (except for exam #1Z0-007 Introduction to Oracle9i: SQL).

Finding Information Quickly - The Key to Success.
-------------------------------------------------
If you remember anything from this whitepaper, make it the
following statement:

The hallmark of a being a good DBA is not knowing everything, but knowing where to look when you don't.

But there is so much information available on Oracle that it tends to become overwhelming. How do you find that one facet of information, that one explanation you are looking for when you are confronted with seemingly endless sources of information? Here's a hint, GO TO THE MANUALS FIRST. The Concepts manual is a good start, closely followed by the SQL*PLUS Users Guide, the Administrator's Guide, the Reference manual and the SQL Reference manual. The next two should be Oracle Backup and Recovery Concepts and the Oracle Enterprise Manager User's Reference Manual. If you don't find the information you are looking for in Oracle's Technical Reference Guides, then look elsewhere. Whether you have been burned or not, you must trust the information they provide. This paper will provide you with alternative sources of information but they are NOT intended to be substitutions for the vendor's reference guides.

A very experienced co-worker of mine was at a customer site installing an Oracle9i database on LINUX. He was reading the installation manual when the customer demanded to know why he was reading the manual when he was supposed to be "the high-priced expert." He quickly replied, "I'm reading the manual because I am an expert." As your experience grows, you'll find that you'll become just like my co-worker, an avid user of the reference guides and not afraid to admit it.

Reference Manuals.
------------------
It is good practice to keep a set of reference manuals for each major release of the database you are administering. Oracle does have a tendency to change default values for object specifications. In addition, each new release contains new parameters that affect the database's configuration. When you receive the latest and greatest version of Oracle's database (one of the benefits of purchasing support), turn straight to the "OracleX New Features" section to find out what impact the new release will have on your daily administrative activities. You'll also find many new features that haven't been covered by Oracle's new release whitepapers and marketing propaganda.

Oracle Internal Resources.
--------------------------
The Oracle websites contain a wealth of information on the Oracle product sets. The trick is knowing where to look. Some may think that Oracle webmasters rewrite the websites from time to time just to make it challenging for us to find the information we are looking for.

The following Oracle websites are favorites of mine and are ranked according to my personal preference:

metalink.oracle.com - Oracle's premier web support service is available to all customers who have current support service contracts. Oracle MetaLink allows customers to log and track service requests. Metalink also allows users to search Oracle's support and bug databases. When you experience an Oracle problem, look up the return code (if one is provided) in the Oracle reference manuals. If you are unable to solve the problem, search the Metalink bug database using the return code or error message as the search criteria. The website also contains a patch and patchset download area, product availability and life cycle information and technical libraries containing whitepapers and informational documents.

docs.oracle.com – Oracle's technical reference manual website. This website stores technical reference manuals for Oracle7, Oracle8, Oracle8i, Oracle9i, Oracle RDB, Oracle Gateways and Applications 10.7, 11 and 11i. A quick and easy way to get access to the information you need.

partner.oracle.com – If you are an Oracle partner, (and there are a lot of us), then this is the website for you. Oracle's partner website contains information on partner initiatives and provides customized portlets categorized into partner activity and job role.

technet.oracle.com - Technet's software download area allows visitors to download virtually any product Oracle markets. Visitors are also able to view Oracle documentation, download product whitepapers, search for jobs that use Oracle technologies and obtain information on Oracle education.

education.oracle.com – Oracle University's web site contains information on Oracle education including course descriptions, class schedules, self-study courses and certification requirements.

www.oracle.com - Oracle's home page on the web.

External Resources.
-------------------
Non-Oracle websites are also excellent sources of information. The Internet has an abundance of web sites containing hundreds of scripts, tips, tricks and techniques. Some of my favorites are:

www.dbazine.com - How can you not love this website? The contributing authors list reads like a "who's who" of the database industry. Topics range from entry-level discussions to information that even the most experienced database user would find enlightening. Experts like Mullins, Inmon, Ensor, Celko and Burleson provide readers with articles that are topical and interesting. Great articles and a pleasing, easy-to-navigate website makes DBAZine the place to go for database information.

www.orafaq.com - Orafaq discussion forums are excellent sources of information. Post a question to hundreds of experienced Oracle DBAs and you'll find out just how helpful Orafaq can be. Orafaq provides an intelligent search engine that visitors can use to search the discussion forums for topics of interest. The website also provides hints, tips, scripts, whitepapers and an on-line chatroom.

www.oracle.com/oramag - Oracle Corporation's own technical magazine. Oracle Magazine provides readers with product announcements, customer testimonials, technical information and upcoming events. Oracle magazine is available in hardcopy and on the web.

www.lazydba.com - Why write scripts when you can download them from the web? There are numerous web sites to choose from but this site is one of my favorites. The scripts are written by numerous contributors and, on the whole, well written. Find the script that solves your problem, download it, test and implement!

www.orsweb.com - Another excellent site that contains dozens of useful scripts.

What Operating System for Oracle?

What Operating System should I choose for my Oracle installation?

There is no convincing some people. They assume and believe that if you throw enough hardware at a problem all performance issues will be miraculously resolved. This means only buying the best iron and the most configurable operating system to get the most blazing performance ever seen on the planet.

This may be the case for the top 5 percent of companies where multi-processor machines, gigabytes of memory and terabytes of storage are just for the development machine (as if). Most companies can quite happily get by on a dual processor machine of adequate performance.

Then the arguments start. Which operating system should they use? The *nix diehards will choose their favourite Unix, lots will consider Linux and some will go with Windows. Now, I have worked with Oracle databases on all three (or should that be two) types of operating systems, and lately I have come to the conclusion that Windows is not the evil beast many consider it to be.

Before a flame war starts, consider this, many smaller companies may not have access to a Unix System Administrator / DBA / Developer. They are run on tight budgets and usually on systems where someone with a little knowledge is left in charge to get on with it. A scary situation but all too true in many circumstances.

This is where windows makes sense. Just about everyone can use windows. There are minimal configuration issues and the installation and maintenance of Oracle is relatively pain free. On Linux and Unix this is not always the case, there may be kernel parameters to change and rpms to install.

Surprisingly the performance differences are not all that great, and I anticipate more features and more control with the upcoming release of Oracle 11g. The limitations of the Windows operating system are well known, and for the majority of developers and users of Oracle technology the issue of what OS lies behind the database shouldn't even be a worry.

After all, Oracle on Windows behaves exactly like Oracle on Linux or Unix.

How to Interview.

DBA Interviews

I have just been involved in a series of interviews for a new Oracle DBA and I was quite upset by some of the candidates lack of knowledge or superficial knowledge. Unfortunately the interviews were a combined effort and I had to cram as many technical type questions as I could into the 30-45 minutes available while working through each applicants CV and then a quick question or two to determine their grasp of the fundamentals.

The role was advertised as Production DBA with some development experience, and buried in the job spec there was mention of PL/SQL and SQL. Over half of the candidates confessed to not "really knowing" PL/SQL because they are DBAs. Some went as far as saying that their SQL skills weren't all that good. What!!!!

After sifting through the huge pile of CVs and selecting those I thought best matched the role regardless of amount of experience. The theory being that someone more junior or with less experience will train into the job or, if they are determined enough, ask around and beg for work, anything to get experience. This is a large organisation and the chances for advancement are yours to make.

But I was shocked, shocked by the lack of preparedness for interview by most of the candidates and, more shockingly, lying about their skill set and experience. OK, I can understand inflating things by a small margin, but to change a passing familiarity with a subject into 5 years worth of experience on a CV is blatant lying. Needless to say, despite my best efforts at CV sifting, some blaggers got through. This annoyed me because I don't like giving up 2 hours of my work time, when I could be productive, to listen to someone out and out lie to me. Seeing as this all happens at the beginning of the interview then leaves me another hour or so to stew without revealing boredom or displeasure.

In the end we settled on a pleasant guy who was not the strongest technically, but crucially most impressed when it came to presenting himself. We actually interviewed someone who spent most of the time answering to the table, and this is for a role involving constant communication. He was open to all ideas, and was able to demonstrate to me the difference between what he knew (solidly) and what he knew (passingly). So we all know where we stand.

So take note if you are about to apply for a position. If you feel your lack of experience will let you down work on your presentation skills. As an industry we are deficient in people skills, but the people interviewing you either are technical themselves and understand the mindset or are in personnel and just don't understand coyness.

Yes it is very different being on the other side of the table for a change and enlightening. I would recommend that technical people take time to prepare well for the basic questions, the obvious questions, those are the ones that stand out.

Converting Between Character Sets

If you have extended characters with accents you can convert them consistently and safely as follows:

SELECT CONVERT ('ediária', 'US7ASCII', 'WE8ISO8859P1') AS converted 
  FROM DUAL;

ediaria

Consistent and already available for you to use.

Number to Words Conversion

This is something useful only as a trick or a quick hack.

How do you get from a number to the same number spelled out in words.

Here is something that works perfectly well but was not intended to be used in the way this example shows. It is a mistreatment of the to_char function based on a manipulating a date for output.

SELECT n, TO_CHAR (DATE '-4712-01-01' + (n - 1), 'jspth')
  FROM (SELECT 1721058 n
          FROM DUAL);

one million seven hundred twenty-one thousand fifty-eighth

So it does work - just don't rely on it - it is after all a to_char on a date field.

Learning Oracle

I have seen so many questions posted on discussion boards all with the same subject - How do I Learn Oracle?

This is an unfortunate misunderstanding. Oracle is more than one product to learn. Everyone using Oracle, myself included, will know SQL, possibly something about the responsibilities of a DBA and something about operating systems at a minimum. There is no "one" thing to learn, no course where you start at the beginning and come out the other end "knowing" Oracle.

What tends to happen, is that you start out learning some SQL and PL/SQL. This should be the most fundamental part of starting a career in using Oracle. Then you either expand your skill set out into another area, DBA for example, or you concentrate on enhancing your knowledge. Unlike most careers, there is no clear path that once started down means you have burned your bridges and cannot back up and try something else.

After a few years most Oracle professionals have settled into a niche that suits them, some as developers, some as administrators, some as hybrids, some doing Forms/Reports etc.... The field of possible skills is so vast and increasing all the time that it is normal to specialise in one area. All areas of knowledge have one core commonality - SQL.

Learn it well.

Take half an hour a week to look up a function in the documentation and try it out. Look through the provided packages - there may be something there you could use. I guess my message is - There is only one way to learn Oracle start with SQL and keep learning until the day you leave.

Quick Text Count

Ever wondered how a search engine gives you such a quick answer: 5000 pages match your search term....

Here's how to do it in Oracle using Oracle Text looking for the words my and search.

declare
lcount number;
begin
 lcount := ctx_query.count_hits(index_name => 'IDX_IM_SEARCHIDX',
                                text_query => 'my and search',                        
                                exact => false);
dbms_output.put_line('Number of matching docs '||lcount);
end;

The call to count_hits can be adjusted for accuracy. (exact => true) takes longer to complete but is accurate while (exact => false) gives a best guess, usually very close anyway.

Note no column was queried. The index was hit directly and assuming there is no thesaurus or fuzzy matching taking place results are extremely fast.

Longops

Just how long do I have to wait for my long running SQL to complete?

SELECT *
  FROM v$session_longops
 WHERE sofar < totalwork;

Shows you what long running operations are in progress.

You can tie this back to your - or another sessions SQL.

CUBE

I just found this script in my collection

SELECT *
  FROM (SELECT   c1, c2, c3, c4, c5, c6
          FROM (SELECT 'a' c1, 'b' c2, 'c' c3, 'd' c4, 'e' c5, 'f' c6
                  FROM DUAL)
         GROUP BY CUBE (c1, c2, c3, c4, c5, c6));


C1 C2 C3 C4 C5 C6
               
               f
            e   
            e  f
         d      
         d     f
         d  e   
         d  e  f
      c         
      c        f
      c     e   
      c     e  f
      c  d      
      c  d     f
      c  d  e   
      c  d  e  f
   b            
   b           f
   b        e   
   b        e  f
   b     d     
   b     d     f
   b     d  e  
   b     d  e  f
   b  c         
   b  c        f
   b  c     e   
   b  c     e  f
   b  c  d      
   b  c  d     f
   b  c  d  e   
   b  c  d  e  f
a               
a              f
a           e   
a           e  f
a        d     
a        d     f
a        d  e  
a        d  e  f
a     c        
a     c        f
a     c     e  
a     c     e  f
a     c  d     
a     c  d     f
a     c  d  e   
a     c  d  e  f
a  b            
a  b           f
a  b        e   
a  b        e  f
a  b     d      
a  b     d     f
a  b     d  e   
a  b     d  e  f
a  b  c         
a  b  c        f
a  b  c     e   
a  b  c     e  f
a  b  c  d      
a  b  c  d     f
a  b  c  d  e   
a  b  c  d  e  f

and it illustrates the beauty (and complexity) of CUBE.

A very simple SQL example that shows off what is possible from something very simple.