Have you ever hosed up your memory parameters such that you cannot get Oracle to even enter the startup nomount state?
Here I’ll do just that by setting sga_max_size to a value less than sga_target, and then trying to start the database.
First a sanity check so you can see the memory parameters. In this case I am using ASMM (automatic shared memory management), and not the new 11g AMM (automatic memory management) features.
continue reading…
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.
continue reading…
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.
To do this you first have to setup your listener to listen on a different port, and then you have to change your tnsnames.ora file to reference the new port. It really is rather simple and I will demonstrate through the steps below. The steps below have been tested on Oracle 10gR2 running on RedHat AS4.
continue reading…
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.
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.
In its simplest format, you can call SQLPlus from a shell script. The basic format of a shell script doing this is:
#!/bin/sh sqlplus system/manager@prod_db <<ENDOFSQL select sysdate from dual; exit; ENDOFSQL
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: continue reading…
One minor thing I do on all of my unix based Oracle database servers is setup the shell prompt to give me some key information. Namely, I always want to see my current directory, the user I am logged in as, the server I am logged into, and my current ORACLE_SID. Since I typically have many servers, oracle instances, and shell sessions open to manage them all, its easy to get confused as to what server and database your executing commands against.
My solution to this is to setup my shell prompt to show all of this information. The instructions I provide below will show you how to do this on a RedHat Linux AS3 system. It should work on most other Linux systems, and needs minor modifications for use on Sun Solaris systems. continue reading…
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.
Here’s how it works: continue reading…
