Many times I am asked how to tailor the initialization parameters for a specific process or user. Essentially the easiest way to set custom initialization parameters for a particular session is to use the ALTER SESSION SET command in a logon trigger keyed to a specific schema or schemas. For example, say we had an application where we wanted to setup cursor sharing and all of the users who will need it are prefixed with “APPL”...

I thought I would take a moment out of my busy Quest Code Tester development efforts (that is, heads-down coding, writing specifications, working with developers in Chicago, Columbus, St. Louis and Russia) to give thanks to Toad.

 

"Way back" in October (that's the way it feels, already), Quest released Toad 9. My initial reaction, probably like many other Toad users (and I do feel like I am quite the typical...

I recently went on a very nice vacation to Curacao. Before I went on vacation the Oracle 10g Grid Control (10.2.0.2) I had installed on my Laptop for monitoring my home office systems was working. I took my laptop with me to allow offload of pictures from my cameras and to track email while I was away. When I returned home and hooked my laptop back up to my home network Grid Control wouldn’t start and of course even with the...

In old version of Oracle database, it is easy to control the join path by reordering the tables in table list after the FROM clause, but it is getting difficult to control the join path in today’s cost based SQL optimizer, I am going to introduce method that is applicable in most databases such as Oracle, Sybase, DB2 and SQL Server in the following:

To control a join path, we cannot tell the database SQL optimizer which path is the best one to select. Instead, we add something to the syntax of the SQL statement that causes an increase to the cost of the current join path selected by database SQL optimizer. Let’s take a look at the following example of two table join scenarios.

You probably don't give this a second thought, but sometimes the question rears its ugly and bewildering head in the code you are writing – precisely because Oracle itself provides multiple answers to the question, right from inside its own code base. Let's take a closer look.

 

When NO_DATA_FOUND is raised, the error message is:

 

ORA-1403: no data found

 

So...is that character between...

Last month I wrote about “Why Reverse Engineering is Always Worthwhile.” So the logical next issue to examine is forward engineering – its different approaches and its many comparative benefits. The best way to do this is via an analogical example J

I live in Dallas-Fort Worth. Let’s assume that I drove down to Austin at the start of the this year’s college football season to watch my #1 Ohio State Buckeyes play against...

Oracle’s Oracle10g requires more memory than was ever required before. If you utilize any of the new features such as automatic storage management (ASM) and automatic shared memory management (ASMM) then you really need to pay attention to what memory is doing in 10g.

I remember reading “Brave New World” when I was in high school, we needn’t mention the year. I was fascinated by the system described in the book that seemed so logical on the surface but needed so many props underneath to keep it going. It reminds me at times of some of the Oracle management that goes on these days.

What exactly do I mean? Many times Oracle management tools that look good on the surface are actually...

The Nested Loop join operation is the basic join operation which is supported by most RDBMS, since it requires less memory and less temporary space. Normally, it can provide faster data response time than other join operations. But, the path of a Nested Loop join will significantly affect the speed of the join operation. Let’s use a two table join as an example to understand how this works.

When an exception is raised, one of the most important pieces of information a programmer would love to get hold of is the line of code that caused the exception to be raised.  Before Oracle 10g came long, the only way to get this information was to allow the exception to go unhandled and then view the error stack. The following chain of program calls shows you what I mean.

CREATE OR REPLACE PROCEDURE my_procedure_1...

I often see DBAs and developers who don’t know the first thing about tuning SQL. They ask “Why do I need to know that, can’t Oracle tune itself?” The truth of the matter is that Oracle is, for the most part, able to do a pretty decent job of tuning itself if it is given enough information and the queries or tuning tasks aren’t too complex. However, there are times when Johnny must tune.

What do I mean by “enough information”?...

All those big brain technoids always make such a big deal out of testing. Too many bugs in our software! Bugs cost money! Bugs make us look bad! Test your code before you give it to your users! Test your code before you write your code! Test your code before you learn how to write code! C'mon, I say, give us a break!

Here's the way I look at it: we could bust our buttons working extra special hard to get rid of lots...

With each new release of a database, be it Oracle or one of their competitors, we hear the cry that this release will make DBAs as we know them obsolete. We hear again and again how this automated feature set or that new GUI interface will automate the DBA job. So far, all of these claims of DBA obsolescence have been for naught, to mis-quote Samual Clements “The reports of the DBAs death are greatly exaggerated.” When...

Welcome to my Toad World blog! I will offer on this blog a wide variety of tips and incredibly deep and surprising insights on the Oracle PL/SQL language (the object of my obsessive personality for the last 10+ years).  I look forward to your responses and critiques. That is: I hope you will engage with these ideas and feel perfectly comfortable with disagreeing with me, offering other, perhaps better, ideas, and in general...

For years, commercial database manufacturers have fought an endless battle to improve the performance of inserting, updating, deleting, and retrieving information stored in the database. Despite their continual efforts and hard work, we have not seen a significant improvement in the performance of most Relational Database Management Systems (RDBMS). Users still suffer from under-performing SQL statements and database experts...

In many shops that I visit, neither the DBA’s nor the database developers place much value on performing data modeling. Often there are seemingly sound reasons for this exclusion: the historically high price of data modeling tools, data models are not required as a project milestone or deliverable, and quite often the database was simply either inherited or supports a third party application – and thus is not open for modification....

When I started working with Oracle in 1990 there weren’t many SQL tuning tools available. At most you had the Oracle supplied tools which allowed you to capture an explain plan and minimal tracing. Most SQL tuning consisted of either encouraging the use to a particular index by making sure the column in the index was on the leading edge of the index or, defeating the use of the index through null concatenation to character...