Oracle Reindex Script

If you wish to reindex all of your Oracle tables in one fell swoop, try the following script:

begin
FOR rec IN (SELECT index_name FROM user_indexes) LOOP
EXECUTE IMMEDIATE 'begin dbms_stats.gather_index_stats(user,:b1); end;' using rec.index_name;
END LOOP;
end;

To check that it has worked correctly, execute the following and look at the last_analyzed column

select index_name, index_type, table_name, uniqueness, num_rows, last_analyzed from user_indexes

ArtenSUITE Part 8: NLS ...

ArtenSUITE
A Development Journal
Part 8: NLS ...

After installing Oracle it’s important to setup the variables NLS_LANG and NLS_DATE_FORMAT. These variables determine the default settings that Oracle uses for Date formatting, Currency formatting, Sort Orders etc. More information is available here.

If Oracle is installed on a Windows server the NLS_LANG can be as an environment variable using the System Control Panel. If you are in the UK the settings you will want are as follows:

Key: NLS_LANG
Value: ENGLISH_UNITED KINGDOM.WE8ISO8859P1

NLS_DATE_FORMAT has to be set on a session by session basis, so you can either put it in the startup procedure for your application as such:

alter session set NLS_DATE_FORMAT = ‘dd/mm/yyyy’;

or you can implement it as a database trigger:

CREATE OR REPLACE TRIGGER "DATA_LOGON_TRIGGER"
after logon
ON DATABASE
begin
execute immediate
'alter session set nls_date_format = ''ddmmyyyy'' ';
end;
/
ALTER TRIGGER "DATA_LOGON_TRIGGER" ENABLE
/

"The man who has nothing to boast of but his illustrious ancestry is like the potato - the best part under ground." - Thomas Overbury

www.artenscience.co.uk
Honest Expert Independent Technology Advice for Business

More Oracle and REALBasic using MBS and Java

I have managed to get REALbasic talking to Oracle via the Java classes enabled by the MBS Java Plug In.

It was quite straightforward in the end, the code is shown below:

'Requires the Oracle Java Driver Resident in the Program Directory
'(ojdbc14.jar)
'Requires the MBS REALbasic Java Plugin.rbx to be in the Plugins Directory
'Requires the MBS REALbasic Main Plugin.rbx to be in the Plugins Directory

'This Function Should be Called as Follows:
'mCodeBaseDB.OpenOracleDatabase("jdbc:oracle:thin:@//HostIPAddress:PortNumber/ORACLESID","username","password")

dim j as JavaDatabaseMBS
dim r as JavaRecordSetMBS
dim f as FolderItem

f=GetFolderItem("ojdbc14.jar")

j=new JavaDatabaseMBS(f,"oracle.jdbc.driver.OracleDriver")
j.Host=strHostName
j.UserName = strUserName
j.Password = strPassWord

if j.Connect then
mCodebaseStd.DisplayInformationMessage("Successfully Connected to Oracle ...")
else
mCodebaseStd.DisplayStopMessage("Could Not Connect to Oracle ..."+EndOfLine+"Error Message: "+j.ErrorString+EndOfLine+"Error Code: "+str(j.Errorcode))
end if

The Oracle Java driver can be found in the /jdbc/lib directory of your ORACLE installation. This can be copied, and I have put it in the client application directory. The MBS Plugins need to be inside the Plugins directory of your REALbasic installation. The comments at the top of the method show how this method should be called to get a successful connection to Oracle, three parameters are needed, ORACLESID, UserName and Password.

The methods referred to when j.Connect is called are my own custom methods for displaying dialogs to the screen. They can be replaced by standard MsgBox calls.

My next challenge is seeing how powerful and reliable it is to access Oracle from REALbasic using these classes and Plug Ins :-)

"If you limit your choices only to what seems possible or reasonable, you disconnect yourself from what you truly want, and all that is left is a compromise." - Robert Fritz

www.artenscience.co.uk
Honest Expert Independent Technology Advice for Business

Oracle / REALBasic / Java Classes Etc.

I’ve been looking into the best database to use for a commercial product that I’m planning. As it stands I’ve settled on Oracle. My reasons are as follow:



I know how to use Oracle (a *good* starting point !).
The XE Edition is limited, but free and makes a good starter database for smaller systems.
Development against XE works identically against the bigger versions if an upgrade becomes necessary later.
Oracle is stable and extremely reliable.
Oracle can be installed on multiple Operating Systems.

The next choice is what to use to develop the front end software. I am very keen on writing software that works on multiple platforms, Windows and Mac OSX primarily, although I do support Linux wherever possible.

For the kind of software I want to write I don’t want it to be browser based. I know it’s the ‘in thing’ but still the *vast majority* of software is written for the desktop and I much prefer desktop based software myself. I don’t see it going away any time soon.


For cross platform based development there are a few choices, however by far the easiest and most straightforward is REALbasic from REAL Software. It has it’s limitations, but in it’s favour I can develop on the Mac and deploy to both Windows and Linux. In addition there is none of the extra hassle of needing the .NET Framework installed on Windows. One company, MonkeyBread Software from Germany seem to have built their business on providing ‘Plug Ins’ for REALbasic to enhance the capabilities of the development environment. It was to them I had to turn for a solution to connecting to Oracle from REALbasic.



REALBasic comes with an Oracle Plug In, however due to Oracle taking around 2 years to provide an Intel based driver for the Mac, the standard REALbasic Plug In does not now work (at least on Mac OSX). I’m sure they will remedy this at some point. In the meantime I have downloaded and been playing with the MonkeyBread Software ‘Java Plug In’. This allows the Java driver and Java classes to used from within REALbasic to access databases that cannot be connected to from REALbasic.

If all works as expected I will be sending MonkeyBread my credit card number for the 40 Euros or so they charge for the Plug In :-)

"We are all here for a spell; get all the good laughs you can." - Will Rogers

GENeSYS Documents

Since I merged GENeSYS Solutions with Arten Science I have had several emails asking me how to get hold of a couple of documents that used to be hosted on GENeSYS Solutions. I assume somebody had linked to the old address somewhere. The documents can now be found at the following links:

Oracle 10g Database Admin 1 Exam Cram / Study Guide

and

Securing the Network, for the Non Technical

"I live now on borrowed time, waiting in the anteroom for the summons that will inevitably come. And then - I go on to the next thing, whatever it is. One doesn't luckily have to bother about that." - Agatha Christie

OCA Study Guide eBook

I’ve finally finished the eBook version of my Oracle Certified Associate Exam Cram / Study Guide document. It is 66 pages long and available to download for free from here. Renowned Oracle guru, consultant and author Jonathan Lewis gave up his time to help with corrections and the technical editing.

Creating this document took up far more time than I envisaged, and was far more difficult than I expected ! I hope it is of use to someone :-)

"Trouble is only opportunity in work clothes." - Henry J Kaiser

www.artenscience.co.uk
Honest Expert Independent Technology Advice for Business

Oracle Now On Intel Mac OSX

The Oracle Instant Client has now arrived for MacOSX Intel. Although I couldn’t find any mention of it on the Oracle site, it does appear to be OSX Leopard only. So Tiger users are out of luck unfortunately. You can download the client, free of charge, here, and below you can see a successful SQLPlus connection from my OSX Terminal :-)



There is a couple of hoops to jump through to get it running, but it’s really quite straightforward. I will blog the steps required if anybody requests. Interestingly it support database from 8i onwards, which is good news for those who have legacy databases to deal with.

What this long awaited download gives us is native, direct access from our Mac applications on our Intel boxes, no need for ODBC drivers any more :-) I’m looking forward to giving it a bit of a workout, unfortunately I have some Windows C# programming and a couple of other things to finish first :-(

"Do definite good; first of all to yourself, then to definite persons." - John Lancaster Spalding

Oracle: Temporary Tables

Temporary Tables can be extremely useful when manipulating large amounts of data within Oracle, for storing totals or aggregate values. To setup a temporary table:

create global temporary table temp_table
(description VARCHAR2(30), count NUMBER)
on commit delete rows

or

create global temporary table temp_table
(description VARCHAR2(30), count NUMBER)
on commit preserve rows

Temporary tables are visible only to the session that inserted data into it. The data will be deleted at the end of the transaction (on commit delete rows) or when the session is terminated (on commit preserve rows). While they exist and are populated they can be used in the same way as any standard Oracle table.

Beware so long as you live, of judging people by appearances." - La Fontaine

Oracle: Locking 101

Locking data is essential to prevent more than one user altering data in the database at the same time. Oracle can lock one row, multiple rows, or the whole table. Oracle attempts to lock at the lowest level possible to ensure minimal impact on the database. It is possible using LOCK TABLE to lock manually lock a complete table. I have personally never needed to use this statement and it is certainly not recommended for most applications with more than one concurrent user.

Querying the database will *never* produce a lock using Oracle, and unlike some databases locking will not stop Oracle producing consistent queries, as the query works from the UNDO tablespace. This is the technology that allows a long running query started at 1200 to finish at 1400 and show the results of that query with all the data as it was when the query was started at 1200, regardless of changes to the data during the time the query was running.

The first user to request a lock, gets the lock and other users are queued on a FIFO basis. If the design of your application requires that control is returned to the user immediately in the event of the requested data being unavailable for locking then the NOWAIT parameter can be used.

It is important to realise that all locks are released when the transaction is terminated via either a COMMIT or ROLLBACK, whether issued explicitly or implicitly.

The table below shows the different kinds of table lock modes available:

Row Share
Allows connect access to the whole table, stops users locking the entire table for EXCLUSIVE.

Row Exclusive
As Row Share, but stops other users locking in SHARE mode
(used by DML such as INSERT, UPDATE and DELETE)

Share
Permits concurrent queries but prevents any updates to the table.
(used by CREATE INDEX)

Share Row Exclusive
To query the whole table, and allow others to do so, but prevent them locking the table in SHARE mode or doing any updates.

Exclusive
Permits queries, prevents any DML.
(used by DROP TABLE)

To monitor the current locks in Oracle you can use the Enterprise Manager or alternatively query the following views:

V$SESSION
V$TRANSACTION
V$LOCK
V$LOCKED_OBJECT

You can obtain explicit locks on individual rows using the select ... for update statement:

select * from ords_data where ords_ref = ‘SJC001’ for update;

If somebody else attempts to get a lock on that row their session will either wait or if using the NOWAIT parameter control will be returned to them immediately with an Oracle Error: ORA-00054. You can trap for this within your application and bring up a message explaining that the record is locked and to try again later.

"A book is a version of the world. If you do not like it, ignore it; or offer your own version in return." - Salman Rushdie

OSX / Realbasic and Oracle

Despite the Intel Macs having been around for a couple of years of so, it seems Oracle have not yet got around to creating a native Oracle DB client for OSX on Intel. Apparently it is due ‘soon’ according to Oracle. There are a couple of work-arounds fortunately and the one that seems to be working well for me so far, is creating a Universal Binary Macintosh application running against Universal Binary ODBC Drivers from Actual.

The Oracle ODBC Drivers are $29, seem to work well and are easy to install. An advantage of using these is that your other Mac applications such as Word, Excel and Filemaker can also access Oracle through these drivers.

I’ve yet to use them for any ‘heavy duty’ work however so time will tell just how suitable they are for me.

Actual also make ODBC drivers for the other popular SQL Databases.

"Don't let the bastards grind you down." - General Joseph W Stillwell

Oracle Top 'N' Queries

If we wanted to query all our Customers from an Oracle database, show the Account Code, Name and Balance and order by the Balance in descending order we would use something like this:

select accm1_cus_sup as Code, accmname as Name, accmledg_balance as Balance
from accm_data
where accm1_co_site = '01'
and accm1_rec_type = 1
order by accmledg_balance desc

What if we wanted to retrieve only the Top 10 Customers who owe us the most money ? The best way of doing that is as follow:

select * from
(
select accm1_cus_sup as Code, accmname as Name, accmledg_balance as Balance
from accm_data
where accm1_co_site = '01'
and accm1_rec_type = 1
order by accmledg_balance desc
)
where rownum <= 10

What we have done here is made our initial query into a subquery and used the ROWNUM pseudocolumn to limit the rows returned by the query. This method uses a table scan initially and the rows outside of the rownum count are discarded, giving you your Top 10 Customers by account balance.
Following on but using a slightly different example, what if we wished to display all our Customers in Account Code order, retrieving only ten at a time, maybe within a thin client or web page scenario ? This is how that can be accomplished:

select *
from ( select X.*, ROWNUM rownumber
from
(
select accm1_cus_sup as Code, accmname as Name
from accm_data
where accm1_co_site = '01'
and accm1_rec_type = 1
order by accm1_cus_sup
)
X
where ROWNUM <= 10)
where rownumber >= 0

Obviously when the user indicates they require the next 10 records you would alter the parameters within the final two WHERE statements accordingly. An important point here is that if the column you are using to order the data is not unique, you must append a unique identifier to the ORDER BY statement. This is usually done by adding TABLENAME.ROWID to the end of the ORDER BY statement.

"When you make a mistake, there are only three things you should ever do about it: admit it, learn from it, and don't repeat it." - Paul ‘Bear’ Bryant

Oracle: CUBE

ROLLUP and CUBE are extensions to the GROUP BY functionality of the Select statement. The following select statement demonstrates basic use of CUBE:

select soh_cucode, soh_owner, sum(soh_net) from guser.f_sohdr
where soh_date between '01 JUN 2007' and '30 JUN 2007'
group by cube (soh_cucode, soh_owner)

This statement will give output that looks like the following:



The output is very similar to the output from ROLLUP, with the exception that CUBE also performs a subtotal by Salesperson at the end of the results set, followed by the total for all Customers, the same as the ROLLUP option.

Between them CUBE and ROLLUP add an extra an very useful dimension to your SQL reporting toolkit. For more information or to explore the options further, consult the Oracle documentation.

Software Development * Security * Database * Technology * Training * Networking * Virtualisation
Technology Consultancy from GENeSYS Solutions
www.genesyssolutions.co.uk

"What counts is not necessarily the size of the dog in the fight - it's the size of the fight in the dog." - Dwight D Eisenhower

Oracle: ROLLUP

ROLLUP and CUBE are extensions to the GROUP BY functionality of the Select statement. The following select statement demonstrates basic use of ROLLUP:

select soh_cucode, soh_owner, sum(soh_net) from guser.f_sohdr
where soh_date between '01 JUN 2007' and '30 JUN 2007'
group by rollup (soh_cucode, soh_owner)

This statement will give output that looks like the following:



Within Customer Code the Grouping occurs via the Salesperson. For each Salesperson a Summary Total is shown and then finally a Total for that Customer, basically the sum of the values of each Salespersons orders. The bottom of the results set shows the Total Value of orders within the period for all Customers.

So basically the ROLLUP has worked like this:

        TOTAL ORDERS BY SALESPERSON
        TOTAL ORDERS BY CUSTOMER
        TOTAL ORDERS

"Not the power to remember, but its very opposite, the power to forget, is a necessary condition for our existence." Sholem Asch

Oracle Views

An Oracle database consists of a vast amount of underlying metadata that stores information about the database itself. This metadata is extremely cryptic and as such Oracle have provided ‘user friendly’ views to enable the user or DBA to view this data in a meaningful format.

There are two types of Oracle Views:

Data Dictionary Views
Dynamic Performance Views

Data Dictionary Views begin with DBA_ , USER_ or ALL_ . The difference is as follows: DBA_ Views show information on all the tables in the database, ALL_ shows information on all the tables that you own or have been granted access to. USER_ shows information on all the tables that you own.

There are some differences between Data Dictionary Views and Dynamic Performance Views:

Data Dictionary Views usually have plural names (DBA_TABLES), Dynamic Performance Views, singular (V$DATAFILE)
Data Dictionary Views are only available whilst the database is open, some V$ Views are available when the DB is not fully open
The data in Data Dictionary Views is usually UPPERCASE whilst V$ data is usually lowercase
The data in Data Dictionary Views is static and not cleared when the DB is restarted, V$ data is cleared after a restart

Examples of Data Dictionary Views

DBA_TABLES
DBA_TABLESPACES
DBA_USERS
DBA_VIEWS

Examples of Dynamic Performance Views

V$VERSION
V$OPTION
V$DATABASE
V$SQL

"The truth is not simply what you think it is; it is also the circumstances in which it is said, and to whom, why and how it is said." - Vaclav Havel

Oracle: COMMENT ON ...

A little known / little used feature of Oracle is the COMMENT ON TABLE / COMMENT ON COLUMN facility. Basically this allows each table and column to have a descriptive text applied to it. It is common to have descriptive text for both tables and columns contained within your application schema, or in a separate data dictionary type document, but it would be nice for that information to be available via external tools that access and / or modify the database without referring to separate documentation.

The feature is used in the following way:

COMMENT ON TABLE mytable IS ‘This is my Master Table’

COMMENT ON COLUMN mytable.primarykey IS ‘Unique ID from Sequence SEQ_MASTER’

The comments can be displayed by querying the following views:

DBA_TAB_COMMENTS
DBA_COL_COMMENTS
ALL_TAB_COMMENTS
ALL_COL_COMMENTS
USER_TAB_COMMENTS
USER_COL_COMMENTS

If you have a schema within your application that contains comments on both the table and column levels it should be very straightforward to write a small program to loop through your schema data and write those comments into Oracle. It could be a couple of hours well spent.

"The excellence of a gift lies in its appropriateness rather than in its value." - Charles Dudley Warner

Trouble with Oracle 10g on a VM

I have been having trouble until recently running Oracle 10G within a Virtual Machine environment. I have tried Parallels / VMWare / Virtual Server and Fusion. Always the same result, the database runs ok and maybe survives a couple of reboots, however sooner or later the database will refuse to start. The Operating System installed within the VM is Windows Server 2003 R2.

I have found a solution that works to restart the database successfully, although I have not yet found the reason for the problem in the first place. If you find yourself in a similar situation and you are presented with the following when attempting to connect to your Oracle Database:

ERROR:
ORA-01034: ORACLE Not Available
ORA-27101: Shared Memory Realm Does Not Exist

then this is what you need to do to start the database:

Firstly open the Command Line shell and perform the following (shown in bold):

C:\ sqplus /nolog
SQL> conn system/password as sysdba
SQL> shutdown abort
SQL> startup

Here is an alternative method that has also worked for me:

Create a file in the root of drive C: called startup.sql, these are the contents:

startup
exit

then create a file called Startup.Bat with the following contents:

c:\Oracle\product\10.2.0\<SID>\bin\sqlplus -s "/ as sysdba" @ c:\startup.sql

(Obviously replace <SID> with your database ORACLE_SID)

When you wish to start the database just run the startup.bat batch file.

That’s it. With luck your database with startup as required and all is well with the world. Now to find out why it happens in the first place ...

"You cannot escape the responsibility of tomorrow by evading it today." - Abraham Lincoln

Oracle: Flashback Query

The Oracle Flashback Query allows users to look backwards in time and see a consistent view of the database as it was sometime in the past. This facility is only relevant if your server is configured for Automatic Undo Management. How far backwards you can see is determined by the UNDO_RETENTION parameter (in seconds) which can be configured in the init.ora file or alternatively like this:

ALTER SYSTEM SET UNDO_RETENTION = NNNN

This facility is mainly used for recovering from accidental updates and deletes as it is not practical to set the UNDO_RETENTION to an extended period. Nevertheless it is a potentially very powerful facility.

"True humor is fun - it does not put down, kid, or mock. It makes people feel wonderful, not separate, different, and cut off. True humor has beneath it the understanding that we are all in this together." - Hugh Prather

Oracle: Flash Recovery

A new feature available in Oracle 10g is Oracle Flash Recovery. Flash Recovery is designed to make it easier and more straightforward to perform Oracle Backups and Recovery by providing a Centralised Location for all the files related to your database backup.

The Flash recovery Area is an area of a disk, or a separate disk, that is dedicated to the storage of database backup files and recovery information. It is held completely separate to all other areas of the Oracle database.

The Flash Recovery Area is used in the following ways:

Enterprise Manager can stores its backups here
Oracle can store the Archived Redo Logs here
RMAN can store and maintain details of it’s files here

In short: All files necessary to recover the database in the unlikely event of a media failure are stored as part of the Flash Recovery Area.

The default location for the Flash Recovery Area is ORACLE_BASE/flash_recovery_area. The size of this area defaults to 2GB but can be altered as necessary.

I see the Flash Recovery Area as a simple but extremely powerful and useful facility within Oracle 10g. Only the naming is unfortunate, this is not to be confused with another new feature that came out with 9i, the Flashback Query or Flashback Table facility.

"Everyone is necessarily the hero of his own life story." - John Barth

Oracle: Startup Procedure

The Oracle startup procedure follows these stages:

NOMOUNT: The Instance Starts and the Initialisation Parameter File is read and interpreted by Oracle.

MOUNT: The First Control File is read by the Oracle Instance.

OPEN: The Redo Log Files and the Datafiles are accessed by the Oracle Instance.

"Concentration comes out of a combination of confidence and hunger." - Arnold Palmer

Oracle: What Happens When ...

... An UPDATE statement is performed.

The following is an accurate (if fairly simplistic) description of what happens when an UPDATE statement is executed against an Oracle database.


The data set for the update is taken from the DB CACHE.

The ‘Pre Change’ data is written to the UNDO CACHE.

The ‘Post Update’ data is rewritten to the REDO CACHE as a delta (a piece of data that contains enough information to reconstruct the original data). The ‘Post Update’ data from the UNDO CACHE is also written to the REDO CACHE (alternatively known as the REDO LOG BUFFER).

After a COMMIT is issued the LOG WRITER Process (LGWR) writes the REDO CACHE to the REDO LOG FILES.

If ARCHIVELOG is enabled then the ARCHIVER Process (ARCH) writes a copy of the REDO LOG FILES to an alternative destination(s) (Up to 10) before the REDO LOGS are reused.

The CHECKPOINT Process (CKPT) is called regularly and this ensures that changed data (dirty blocks) are written from the DB CACHE and back to the datafiles. In addition this process updates the CONTROL FILES.

“Weaseling out of things is important to learn. It’s what separates us from the animals — except the weasel.” - Homer Simpson