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