TimArcher.com A blog about technology...and more.

So you keep extending your Oracle datafiles, but do you know what objects are the true space hogs? I use the query below, to look at the size of the objects in a specific tablespace.

SELECT owner,
       segment_name,
       segment_type,
       tablespace_name,
       bytes
FROM DBA_SEGMENTS
WHERE TABLESPACE_NAME = 'DEVELOPMENT'
ORDER BY bytes desc

This will give me a list of my tables, indexes, and other objects in the specified tablespace in order by their size in bytes. The largest objects will be displayed first based on my sort criteria.

From this output, I usually see various tables that I can start purging, such as log tables, temp data tables, etc. This part will be specific to your own environment, but you'll want to delete data you don't need anymore.

On the Oracle systems that I manage I typically like to add a simple layer of security by changing the port that the listener listens on. Given that I work for an institution that allows the public to connect to its internal network, and since that network has an Oracle Database server on it, I wanted to make it just a little bit harder to find our databases. I recognize that a good hacker will find our Oracle Database servers regardless of the port that they run on. However, by changing the listening port I hope to eliminate the potential for a member of the public bringing in an infected computer that tries to do something malicious to Oracle databases.

Many Oracle shops want their database to automatically start when their server boots up, and to automatically shutdown when they shutdown the server.

Below I will share with you the Oracle init script that I use on my server. It has been tested with Oracle 10gR2 on RedHat Linux AS3.

1. Login as the root user on your server.
2. Put the following script in the file named /etc/init.d/dbora:
   

   #!/bin/sh
   # chkconfig: 345 99 10
   # description: Oracle auto start-stop script.
   #
   # Change the value of ORACLE_HOME to specify the correct Oracle home
   # directory for your installation.
   
   
   
   
   
     

The need often arises to run Oracle SQL scripts or PL/SQL procedures from a shell script. For instance, in my environment we run a lot of jobs through cron. Part of the job may be to connect to the database and run a PL/SQL function or procedure. Below I will describe how to do this, and some of the various “extras” that I include in my shell scripts. All of the examples below have been tested on Oracle 10gR2 and RedHat AS 4.

The Basic Syntax

In its simplest format, you can call SQLPlus from a shell script. The basic format of a shell script doing this is:

#!/bin/sh
  

phpMyAdmin is a wonderful tool which allows you to use your web browser to administer your MySQL databases. It gives you a nice GUI to “create and drop databases, create/drop/alter tables, delete/edit/add fields, execute any SQL statement, manage keys on fields, manage privileges, [and] export data into various formats”.

This quick start is designed to help you get phpMyAdmin up and running lighting fast. I am currently running RedHat Linux AS3. Apache and php are already configured and running on my server. This post concentrates only on the specifics to installing phpMyAdmin 2.10.0.2.

Have you ever needed to generate a random number within your Oracle PL/SQL code? I recently had a need to just this. My requirement was to generate a random 6 digit pin number for users to login to our web systems with.

I found that the dbms_random package does exactly what I need, and is extremely simple to use.

My first test of this was just to run the statement:

select dbms_random.value
from dual

This will return a random number, greater than or equal to 0 and less than 1, with decimal 38 digits. When I ran this command my result was:
0.622324263489056

The DBMS_RANDOM.VALUE function can also take 2 arguments, the low number and the high number. By using these two arguments you can get a random Oracle number x, where x is greater than or equal to the low number and less than the high number.

Are you looking to backup your MySQL database? My site runs with MySQL as its backend database, and as such I wanted a backup script I can just drop in.

I have found a great open source utility called AutoMySQLBackup which can be downloaded from SourceForge.net. There is no need to reinvent the wheel writing your own, this one works extremely well.

Here are the steps I followed to configure this script.

1) Download the script from the AutoMySQLBackup site on sourceforge. My examples are based off of version 2.5 of this script.

One common piece of framework I have always needed when writing Oracle based applications is the ability to execute a host command in line with my PL/SQL code. Sometimes I do something as simple as echo a value to a file, to more complex shell scripts for calling programs to render PDF's, send emails, etc. One requirement of executing host commands on the database server is to also get the return code from the process I just called so my PL/SQL program can take an appropriate course of action.

To do this, I use a simple oracle external procedure. The example I’m presenting below has been tested and used on Oracle 10gR2 and RedHat Linux AS3.