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

Only In America ...

Rockdale USA. Police have initiated a program of placing a yellow sticker on your car if you happen to have left valuables on display. The sticker warns of the dangers of leaving valuables where thieves may see them.

Of course if you happen to be a thief you can just target the cars with yellow stickers .... mmmmm ....

For the article see here.

"If you are pained by external things, it is not they that disturb you, but your own judgment of them. And it is in your power to wipe out that judgment now." - Marcus Aurelius Antoninus

Would You Trust ...

Would you trust the UK Government to run a National ID Card Database ?

The recent loss of a disk containing 25 Million Child Benefit details is incredible. Not only did they lose the disk but they neglected to encrypt the information on it. It is quite possible to, very simply and cheaply, encrypt information in such a way that would make that disk worthless to anyone who gained access to it.

Instead, Bank Account details, Dates of Birth, National Insurance Numbers for millions of people are out in the wild. This should *never* happen. Ever. There is absolutely, categorically no excuse for this.

Pathetic.

"The guy who takes a chance, who walks the line between the known and unknown, who is unafraid of failure, will succeed." - Gordon Parks

Security, Slippery Slope ...

In October 2007 a law came into being that allows the government to force you to hand over encryption keys so that they can access your secured information. To the layman this is a necessary weapon in the war against terrorism, and that is exactly how it was sold to the public.

So why this month was this ‘Section 49 / Section 51’ law used to force a group of animal rights activists to hand over the encryption keys for their computers ?

A law that is only 2 months old and is already being used and abused by the powers that be. The ‘threat of terrorism’ is being used by both the British and American Governments to implement laws that would not be passed otherwise. Our personal freedoms are being eroded daily and most of us have not even noticed - yet.

Thanks for Bruce Schneier for revealing this.

"To die is nothing; but it is terrible not to live." - Victor Hugo

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