Tips and wisdom from 15 years of DBA experience

Thursday, August 27, 2009

How Do I Shrink Datafiles to Reclaim space?

There is probably a lot of available space that could be re-claimed by your database server operating system, but it is locked up in Oracle datafiles. Fortunately it is possible to shrink Oracle datafiles. This works in 10.2 and possibly earlier versions, but I am not sure - if you are on anything earlier than 10.2, you have other issues to worry about : )

Here is what this Tom Kyte script will give you:

  • The block size your db is using
  • The possible savings by shrinking each datafile and a total possible savings
  • The SQL to shrink each datafile as far as it can go

-- This script was written by Tom Kyte and retrieved from

set pages 0
set lin 150
set verify off
column file_name format a60 word_wrapped
column smallest format 999,990 heading "Smallest|Size|Poss."
column currsize format 999,990 heading "Current|Size"
column savings  format 999,990 heading "Poss.|Savings"
column sum format 999,999,999
break on report
compute sum of savings on report

column value new_val blksize
select value from v$parameter where name = 'db_block_size'

select file_name,
       ceil( (nvl(hwm,1)*&&blksize)/1024/1024 ) smallest,
       ceil( blocks*&&blksize/1024/1024) currsize,
       ceil( blocks*&&blksize/1024/1024) -
       ceil( (nvl(hwm,1)*&&blksize)/1024/1024 ) savings
from dba_data_files a,
     ( select file_id, max(block_id+blocks-1) hwm
         from dba_extents
        group by file_id ) b
where a.file_id = b.file_id(+)

column cmd format a95 word_wrapped

select 'alter database datafile '''||file_name||''' resize ' ||
       ceil( (nvl(hwm,1)*&&blksize)/1024/1024 )  || 'm;' cmd
from dba_data_files a,
     ( select file_id, max(block_id+blocks-1) hwm
         from dba_extents
        group by file_id ) b
where a.file_id = b.file_id(+)
  and ceil( blocks*&&blksize/1024/1024) -
      ceil( (nvl(hwm,1)*&&blksize)/1024/1024 ) > 0

Wednesday, August 19, 2009

HIPAA Compliance Documents

HIPAA, or “Health Insurance Portability and Accountability Act” of 1996 is a law, and the U.S. Department Of Health and Human Services created a set of rules that need to be followed in order to adhere to the law. Of course your company may have additional standards to comply with, so don't let this information override anything that your company may require.

Must an entity (business, person, etc.) comply with HIPAA?

Where can I read a Summary of the Rules that need to be followed to comply with HIPAA?

I am so anxious to read more! Where can I read the law?

Wow! This is so exciting! Where can I read the Rules that the US Dept. of Health and Human Services developed in order to help Health care providers adhere to the law?

Tuesday, August 18, 2009

Audit Logons in Oracle

There is more than one way to audit logons in Oracle. I will provide what I beleive to be the simplest method. This will give you the who,when and where of Oracle logons. A couple of notes about this method: It will store a new record at each Oracle session creation. If you use OEM, then you may get spammed with records from DBSNMP (or whatever user you are telling OEM to log in as).


$ sqlplus "/ as sysdba"
SQL> show parameter audit_trail;
(if it shows "db", you are done with setup. Go onto the next section)
SQL> alter system set audit_trail=db scope=spfile;
SQL> shutdown;
SQL> startup;
SQL> show parameter audit_trail;
(verify that the parameter equals "db")

Turning on Auditing:

$ sqlplus "/ as sysdba"
SQL> audit create session by session;
SQL> select count(*) from dba_audit_trail;
(should be zero)
SQL> exit;
$ sqlplus non-sys-user/pw
SQL> exit;
$ sqlplus "/ as sysdba"
SQL> select count(*) from dba_audit_trail;
(should be non-zero)

Done. Short and Sweet.


If you want to capture logons from SYS or anyone connecting with SYSOPER or SYSDBA, you need to issue the following, and bounce the database:

alter system set audit_sys_operations=true scope=spfile;

Monday, August 10, 2009

Oracle Apex on 10g using the PL/SQL Gateway? Yes!

Oracle Application Express is a very solid tool for rapid web application development that does not require knowledge of a programming language or HTML. It comes free with the Oracle database, and handles session tracking, security, database access etc. very simply and intuitively. For example, I had a form with pick lists, and I wanted to create a modify-able version of that form. I created an updateable report so that for each row you can click a little modify icon to the left of the row, which would bring up the form. The form had pick lists that were populated from a lookup table, and I was impressed that Oracle "knew" that I did not want the values from the lookup table but from the table that contained the row I just clicked on to modify. Nice.

Below are instructions for installing Apex 3.2 on Oracle 10g using the Embedded PL/SQL Gateway (EPG) and the http server that comes with the XML DB. This is not an "officially" supported configuration by Oracle. They do support APEX on 10g using an external web server. I have at least 5 forms used in production now that confirm that 10g using the EPG works, and I am going to show you exactly what you need to do to make it work. The Oracle documentation lacks some crucial elements, and the diagnostic information produced by the XML DB http server is, non-existent as far as I can discern. If anyone knows how to get the equivalent of an apache http log from the XML DB http server, please post a comment to share your wisdom!

APEX installation instructions:

First, download Oracle Application Express from Oracle OTN then unzip Oracle APEX and begin the installation process as follows:

$ unzip
$ cd apex
$ sqlplus "/ as sysdba"
SQL> @apexins sysaux sysaux temp /i/
SQL> @apxchpwd
(jot down the password that you chose)
SQL> @apex_epg_config /stuff


VERY IMPORTANT!! the /stuff directory above MUST be the directory that is one level above where the "apex" directory is located that you unzipped during installation. For example, if your apex directory is in /home/oracle/stuff, then choose /home/oracle/stuff. DO NOT choose /home/oracle/stuff/apex. Just a common stumbling block...

$ sqlplus "/ as sysdba"
SQL> @apxldimg.sql /stuff
(same dir you listed in previous step)
SQL> @apxxepwd.sql admin


This bit of code was off otn forums, and is necessary to prevent the annoying pop-up at the browser for user anonymous 5 times before letting you get to the web page authentication.

l_configxml XMLTYPE;
l_value VARCHAR2(5) := 'true'; -- (true/false)
l_configxml := DBMS_XDB.cfg_get();

IF l_configxml.existsNode('/xdbconfig/sysconfig/protocolconfig/httpconfig/allow-repository-anonymous-access') = 0 THEN
-- Add missing element.
SELECT insertChildXML
XMLType('' ||
l_value ||
INTO l_configxml
FROM dual;

DBMS_OUTPUT.put_line('Element inserted.');
-- Update existing element.
INTO l_configxml
FROM dual;

DBMS_OUTPUT.put_line('Element updated.');



If you have problems logging in, or with images not displaying correctly:

SQL> select * from dba_epg_dad_authorization;

-------------- ------------------------------


If the query above returns no rows or different output, do this:

dbms_epg.authorize_dad (
dad_name => 'APEX',
user => 'ANONYMOUS');


Re-check the dba_epg_dad_authorization query:

SQL> select * from dba_epg_dad_authorization;


If you are still having problems, check out the various dad attributes:

set serveroutput on size 1000000
FOR d IN 1..l_dadNames.COUNT LOOP
FOR a IN 1..l_attrValues.COUNT LOOP
DBMS_OUTPUT.PUT_LINE('- '||RPAD(l_attrNames(a),25)||' : '||l_attrValues(a));
FOR a IN 1..l_attrValues.COUNT LOOP
DBMS_OUTPUT.PUT_LINE('- '||RPAD('mapping',25)||' : '||l_attrValues(a));
FOR a IN ( SELECT username FROM dba_epg_dad_authorization WHERE dad_name = l_dadNames(d) )
DBMS_OUTPUT.PUT_LINE('- '||RPAD('authorized',25)||' : '||a.username);


If nothing from this output helps in your troubleshooting efforts, you can turn on the highest level of logging (debugging) which will write to udump in theory, but I have not seen anything show up there...:

SQL> execute dbms_epg.set_global_attribute('log-level', 7);

Friday, August 7, 2009

Proper Pagination design in Oracle using Analytic Functions

A common web development problem is how to prevent slogging the database with full table scans when trying to paginate a web page. I defer the solution to Tom Kyte, re-posting the section "Pagination in Getting Rows N Through M" in this article:

Pagination in Getting Rows N Through M

The last thing I'll take a look at is performing pagination with analytics. Pagination is common in Web-based applications: The user inputs a set of criteria, which runs a query and then lets the user click the Previous and Next buttons to page through the result set. To achieve this paging functionality, the application needs to be able to get a certain set of rows from the query. In the prior column dealing with ROWNUM and in the "Top-n Query" section above, I demonstrated how to achieve that pagination, but it required two levels of inline views and could be considered rather unwieldy. A syntactically easier way to write that pagination query is

select *
from (
select /*+ first_rows(25) */
over (order by something unique)rn
from your_tables )
where rn between :n and :m
order by rn;

For example, suppose you wanted to paginate through the ALL_OBJECTS view, wanted to show 25 rows on a page, and needed to show page 5. The query would be

SQL> variable n number
SQL> variable m number
SQL> exec :n := 101; :m := 125
PL/SQL procedure successfully completed.

SQL> select *
2 from (
3 select /*+ first_rows(25) */
4 object_id,object_name,
5 row_number() over
6 (order by object_id) rn
7 from all_objects)
8 where rn between :n and :m
9 order by rn;

--------- ----------- ---
102 I_SEQ1 101
103 I_OBJAUTH1 102
124 I_ACCESS1 123
125 I_TRIGGER1 124
126 I_TRIGGER2 125

25 rows selected.

I chose 25 for the FIRST_ROWS hint because that is my page size and pagination-style queries should be optimized to get the first page as fast as possible. I assign ROW_NUMBER to each row after sorting by OBJECT_ID. Then I use a simple BETWEEN to retrieve the specific rows I need (101 through 125 in this case).

You need to order by something unique for these pagination queries, so that ROW_NUMBER is assigned deterministically to the rows each and every time. Otherwise, you end up with a problem similar to what I pointed out with the salary top-n query. For more details on deterministic queries, go to Also, for complete information on using analytic functions, see (chapter 21 of the Data Warehousing Guide). In my opinion, analytic functions are the coolest thing to happen to SQL since the SELECT keyword was introduced.

Thursday, August 6, 2009

How do I apply metric thresholds to multiple databases in Oracle Enterprise Manager (OEM)?

I am very much a command line junkie, but have been warming up to Oracle Enterprise Manager quite a bit lately. My ol' monitoring toolbox of scripts are getting good competition from the performance tab, with the advent of active session history graphing, and being able to look at the state of a database at prior moments in the past. That said, there was one thing that took me way too long to figure out this morning. I simply wanted to change a metric warning and critical threshold on Process Limit Usage (%) and apply it to about 20 databases. No big deal. However, I could not do it through any of the related links on the database home page. Googling for related keywords yielded no direct hits. (If you are reading this from a Google search, then you owe me $42.6 billion dollars). Let me introduce you to OEM Monitoring Templates.

From OEM (Tested on EM 10g):

1. Click the small "Setup" link in the upper righthand corner

2. From the vertical navigation bar, click Monitoring Templates

3. Click "Create". Click the little flashlight to search for a target that you are going to use to build your metrics template from and click "Continue".

4. Choose a name for the template, and click on Metric Thresholds. You can modify the values here that have been copied from your target. What I did was chose "Select All" and then unselected the Metric and values I wanted to add to all databases, and clicked the "Remove Metrics from Template" button. Then I Hit "OK".

5. Here is where it gets can compare this template against your hosts by clicking "Compare Settings", to diff them before you blindly apply it to your entire set of DBs. Very Nice. Click OK when you are done.

6. You should be back on the "Monitoring Templates" screen where you can now click "Apply" to apply this template to various targets.

It would be nice to have this a bit more visible from within OEM. But then again it kinda makes me excited about finding new nuggets of simplification. Like a scavenger hunt, I guess...sort of?...nevermind.