Oracle technologists, database architects, database administrators, and senior database application developers are all very curious people by nature – even more curious than most people. So naturally database oriented issues evoke debates similar to “tastes great, less filling” from the famous beer commercial. You might be surprised by the scope and passion of such database debates. One area getting lots of discussion of recent...

In this part, Bert goes over some database instance checklist items to add to your virtualizatoin checklist.

At some point every DBA’s going to be asked if not forced to adopt virtualized databases. Oh yes we can argue until we’re blue in the face about performance worries this and other mostly technical concerns, but we’d simply be “fish swimming upstream”. The business has made a decision and while the business may permit appeals for concessions, those concessions will be neither automatic nor sympathetic. So unless we’re willing...

People are always asking about a 64-bit version of Toad. The short answer there is currently no 64-bit version of Toad, but the development team has heard the requests and is on top of the issue. But let’s examine some facts versus fiction regarding 64-bit to make sure you know the truth about 64-bit apps.

Creating a 64-bit application is easy – just recompile the code. FALSE. Assuming your compiler offers a 64-bit...

A common question is how to convince one’s management that Toad is to be preferred versus several other free alternatives – most notably Oracle’s SQL Developer tool. A simple answer is that Toad is quite arguably the “de facto standard” due to both tenure (over a decade) and market adoption (two million users). That translates into a mature tool offering a robust feature set and a large workforce pool. Thus a database tool...

Some time ago I wrote a blog titled “Push Button Benchmarking” to specifically point out that there is no such thing. That there is absolutely NO such thing as a “big red easy button” benchmarking tool that can “automagically” do everything without their users having extensive database skills (i.e. DBA or senior developer)...

A few years ago I wrote a blog titled “Run Toad off a USB Flash Drive”. It’s been a fairly popular blog, but it’s also quite out dated now – so I thought I’d update it.

The original blog had several shortcomings – mainly that it was specific to (i.e. only tested against) the current version of Toad at...

It’s not uncommon after a database has been in use for a short while for the UNDO tablespace to grow “artificially” large. In my case I have a little local database on my notebook where the UNDO started at 100 MB and over about a month’s time grew to over 3 GB. Since I backup my notebook’s local database by simply shutting the database and zipping up the %ORACLE_HOME%\oradata\SID directory – my zips take longer to compress...

The February issue of SQL Server Magazine  (yes – much like Darth Vader I dabble on the dark side) had a very interesting article by Vic Newell titled “4 Ways to purge Non-Partitioned Data”. The basic premise is that in...

I replied to a TOAD user question a few days back on our TOAD Yahoo group in response to a question about why TOAD seemingly did not audit their DDL commands when they had in fact successfully turned on database auditing. If you’re connected as SYS or any other user with either the SYSDBA or SYSOPER connection option (highlighted in the screen snapshot below), then your commands are not audited in any tool unless that database’s initialization parameter for AUDIT_SYS_OPERATIONS = TRUE.

...

I’ve written several blogs over the past couple years on how to speed up TOAD when its screens or utilities seem to take minutes instead of seconds. In many cases (i.e. Oracle versions 10.X and 11.X), the case is often that the DBA is not manually collecting statistics on the data dictionary – that many believe that Oracle does this all “Auto-Magically” for them. And as you can see in those various blogs – it does not.

...

Sometimes information systems professionals can draw some very useful analogies from the hard sciences. For example physics offers us one very useful concept – the states of matter. Figure 1 shows an excellent diagram that I copied from Wikipedia explaining the four states of matter.

 

...

In Part 1 of this topic I proposed that while SQL is not an overly complex language in theory the SELECT command syntax permits numerous equivalent ways to write queries. Therefore the SQL language really is not as simple as we generally believe.

 

Here’s the simple query I utilized as the starting point for discussion:

...

Most technical people working with Oracle databases have performed some degree of PL/SQL coding. With Oracle delivering many new database features via a PL/SQL API’s (e.g. DBMS_DATAPUMP, DBMS_SCHEDULER, DBMS_FLASHBAK, DBMS_RESOURCE_MANAGER, DBMS_REDEFINTION, etc), even DBA’s have become more engaged with PL/SQL. As for database developers – well they always knew what a jewel PL/SQL was for handling certain tasks. In fact it...

A lot of times people will post an issue to the Toad discussion forum or email me offline about Toad running too slow and what can they do. I’ve always told them to run the following two commands to correct the problem:

SQL is not an overly complex language in theory. The data manipulation language or DML consist of four primary commands: INSERT, UPDATE, DELETE and SELECT. Newer versions of Oracle also offer the MERGE command, which is an “upsert” – meaning it tries an update followed by an insert as a single command. Since the SELECT command is the one we use most since queries are the majority of database operations, let’s examine statement...

Many people firmly believe in Cary Millsap’s database optimization approach known as Method-R from his very popular book Optimizing Oracle Performance. That includes...

A little known feature in TOAD is the ability to monitor and track both the database’s disk space usage and disk IO patterns. Moreover TOAD offers the ability to perform projections for each as well.

Servers everywhere are being virtualized. Yet many DBA’s are hesitant to embrace virtualized database servers. I’ve been mystified by this anomaly, so I’ve asked those opposed for their rationale. While there are numerous arguments against, basically two pervasive themes surface from amongst all the replies.

First and foremost, DBA’s almost universally claim that their databases are “mission critical” and cannot suffer...

I wrote a blog a few weeks back titled TOAD 10.5 Freeware Runs on Linux. So here comes the second and more important part for many – getting the official commercial product installed and working on Linux. And to me great and very happy surprise, the install simply worked. Look at the screen snapshot below. It shows that I now have both the TOAD freeware and commercial installed and working side by side on my Ubuntu 10.04 Linux machine. Hurray!

...

Toad is a robust and mature tool – so there are many more features than most of us can ever hope to master. But one that’s very useful yet widely unknown is the ability in the Schema Browser to perform intelligent/contextual jumps to related items.

I’m running Toad for Oracle 10.5 freeware “natively via WINE” on Linux – specifically Ubuntu 10.04. Therefore you can now run the world’s favorite Oracle database tool on Linux. Goodbye Windows!

Quest recently released a new and updated version of the Toad for Oracle freeware. The old freeware was based on the old Toad 8.5 code base which came out before 10gR2, 11gR1, and 11gR2. So for example it had some problems supporting database versions and features that came out years later (e.g. 11g case sensitive passwords). The new freeware is based off the latest and...

The first blog on this topic demonstrated the two primary methods by which to automate a DBA task in Toad such as the popular Database Health Check report. We saw that either using the “DB Health Check” screen (i.e. METHOD-#1) or the “App Designer” directly (i.e. METHOD-#2) how to create the automated task or action – plus how to schedule it and...

Wouldn’t it be nice to have an email message every morning in your inbox regarding the relative health of your databases – specifically only exception type reporting for those that have issues? That way you could “hit the ground running” each morning and address any problems as soon as you read your email. With “Toad® DBA Suite for Oracle” you can easily setup receiving such automated database health check problem reports via email in just a few simple steps. Furthermore, you can also share all such automated Toad task definitions via email.

...

One of the most common TOAD questions or problem areas is with connecting to remote and/or local databases. The process and requirements are actually quite simple – yet this remains a high frequency question that seems to cause remarkable confusion. And not just for new Oracle or new TOAD users, as sometimes even old pros can forget and ask. They just often do a Homer Simpson “Doh”...

We all love TOAD – both the commercial and freeware versions. We love it because it’s simply the best Oracle productivity enhancing tool out there. A few weeks back, I mentioned the many other freeware offerings from Quest. So this week I thought I’d mention a few other freeware offerings that serve me nearly as much as TOAD – just in different functional areas. Here are my top five.

Many TOAD users ask “How can I run my SQL script against a collection or even all my databases all at once (i.e. in a single step)?” The problem is they generally ask this question while in the Editor – which by definition works with but a single active database connection at a time. But if you simply save the editor contents to a SQL script file – you can then use the TOAD Script Manager to execute that script against any...

Often database professionals need good freeware tools to get their job done. Sometimes it’s because there’s no funding for software. And in some cases a tool is needed to replace the purchased tool that ends up not being able to do the job. Finally in today’s world of open source, some people just hate to pay for tools – even if there is budget and/or the job clearly could benefit from commercial quality tools.

For...

It’s been a while since I wrote my data warehousing book, so I wanted to read something hip and new on the topic. Lucky for me someone suggested that I examine “Oracle Warehouse Builder 11g: Getting Started”...

Many people these days have to work on both traditional “online transactional processing” (i.e. OLTP) systems as well as data warehouses. But just what is a data warehouse? Generally speaking a data warehouse is nothing more than a specialized business reporting system used by mid to senior level management to research and develop a tactical plan and/or true long term strategy. Thus in many cases while these systems may be considered mission critical, they are not really true 24 X 7 systems. Often data warehouses are utilized during normal to extended business hours, with some batch activities for data loading and aggregation/summary calculations.

...

I want to start by thanking everyone who participated in the free TOAD book contest by submitting new feature ideas or enhancement requests. I received numerous good ideas. I also received about a third that were already in TOAD – and have been for a long time.  I actually can benefit from those emails too, since Quest can probably better expose and/or offer training on those issues. Remember too this was an entirely subjective...

When designing and constructing a successful (i.e. effective and efficient) relational database, there are two fundamental sets of rules or design principles that are universally accepted and generally practiced by database architects.

TOAD offers three distinct ways to connect to your database, as shown by the three tabs circled below on the connection screen. In this week’s blog we’ll examine the differences between the first two: TNS and Direct. No matter which of the three methods you choose, TOAD requires the Oracle “SQL*Net” client to be on your PC. TOAD needs to talk over the network to your database, and that requires certain Oracle network DLL files...

Last week’s blog was part 2 of a 3 part series about techniques for optimizing a notebook PC for running Oracle database within a virtual machine. In part 1 I detailed my “top ten” ideas for proper host (i.e. notebook) configuration. In part 2,...

Last week’s blog was part 1 of a 3 part series about techniques for optimizing a notebook PC for running Oracle database within a virtual machine. In part 1 I detailed my “top ten” ideas for proper host (i.e. notebook) configuration. This week I’ll offer a comparable...

People have often asked me “How do I optimize my Oracle virtual machines running on my meager notebook PC?” So I decided to put together this quick checklist to assist with just that question.

 

Let's assume that you have the typical recent or last generation of notebook PC, that is one with a dual core CPU and say a max 4 GB of RAM. Here's how to optimize your Oracle VM on that hardware. The advice below are...

While TOAD offers numerous capabilities, many people often spend a majority of their time simply “browsing” existing table data. Even when writing SQL or PL/SQL code to perform some business function or action, developers need to see what the data looks like in order to write the code. And of course business analysts and managers often look at the data in order to spot patterns or drive report design. For these needs people...

Face it, we’ve all been there – that moment when we’ve done something a wee bit too fast and then realize we probably shouldn’t have done it. Then we often generally say something like “stupid database” or “stupid TOAD’ or “stupid whatever. But there are ways to recover without having to go hat in hand to the DBA and beg forgiveness and obtain a recovery. More recent versions of Oracle offer several wonderful “Flashback” options...

I have written a book about Optimizing Oracle on VMware, plus have both a paper and presentation regarding the same. Furthermore I have also written blogs about Virtual Paranoia and a four-part series Oracle + VMware = Success. These basically all espouse essentially the same fundamental belief – that database virtualization is here to stay, and is rapidly moving towards mainstream.

 

But a couple legitimate questions or concerns are always brought up (and they are good questions). I’m going to address the three that I hear most often, because I believe having the answers to just these will knock down 80+% of all the roadblocks to virtualizing your databases.

...

One critical task for DBA's is maintaining their database versioning history. For example the database objects were in some specified state (in terms of their construction) on such and such a date. The key reason this is critical is that application updates and bug fixes often require database modifications. So the DBA must maintain a database versioning history that matches such changes. There are two chief reasons for this:...

This is the 3rd of 3 blogs on Toad for Oracle DBA features

This is the 2nd of 3 blogs on Toad for Oracle DBA features

This is the first of 3 blogs on DBA features in Toad for Oracle

Sometimes information systems professionals can get “caught up” in our own worlds. For example, project managers and their developers might focus on latest and greatest application development methodologies such as Extreme Programming (XP) or Agile Development. Likewise enterprise architects might be adopting some new or updated modeling conventions. While database administrators (DBA’s) are often engaged and engrossed in the latest database versions and their new features. Now also include the new versions of the software we use to build systems, hardware’s exponential growth, new programming languages and everything else in a state of flux in our worlds – it’s easy to see why we’re often so overwhelmed that we forget some very basic facts.

...

Dan Hotka and I wrote the TOAD Handbook back in 2003. Of course since then TOAD has changed quite a bit – both in terms of the graphical user interface and functionality. Thus we have updated the content and created the TOAD Handbook 2nd Edition,...

There are always those DBA’s who refuse to use GUI tools because they prefer scripts. And that’s OK – it’s a personal preference. But recently I’ve seen some stuff posted on the web and sent in email blasts suggesting that people who do use DBA GUI tools like TOAD and OEM are somehow suspect as to their skill levels. And yes, I am sure there are some people who might not know Oracle very well but get along just well enough...