Interval Partitions in Oracle 11g

Among many other enhancements in Oracle 11g, interval partition is definitely a good one for DBAs. There is a good article here discussing it (and other partition enhancements).

In one of our projects, we are looking for sub-partitions within an interval partition, and it's well able to handle. Here's an extended discussion of the link above.

This time we have a interval partition, and list subpartitions inside.

CREATE TABLE interval_tab (
  id           NUMBER,
  code         VARCHAR2(10),
  description  VARCHAR2(50),
  created_date DATE
)
PARTITION BY RANGE (created_date)
INTERVAL (NUMTOYMINTERVAL(1,'MONTH'))
SUBPARTITION BY LIST( code )
SUBPARTITION TEMPLATE
  ( SUBPARTITION CD_01 VALUES ('ONE'),
    SUBPARTITION CD_02 VALUES ('TWO'),
    SUBPARTITION CD_03 VALUES ('THREE')
  )
(
   PARTITION part_01 values LESS THAN (TO_DATE('01-NOV-2007','DD-MON-YYYY'))
);

INSERT INTO interval_tab VALUES (1, 'ONE', 'One', TO_DATE('16-OCT-2007', 'DD-MON-YYYY'));
INSERT INTO interval_tab VALUES (2, 'TWO', 'Two', TO_DATE('31-OCT-2007', 'DD-MON-YYYY'));
COMMIT;
EXEC DBMS_STATS.gather_table_stats(USER, 'INTERVAL_TAB');

COLUMN partition_name FORMAT A20
COLUMN subpartition_name FORMAT A20
COLUMN high_value FORMAT A10

SELECT partition_name, subpartition_name, high_value, num_rows
FROM   user_tab_subpartitions
where table_name = 'INTERVAL_TAB'
ORDER BY table_name, partition_name, subpartition_name;

PARTITION_NAME       SUBPARTITION_NAME    HIGH_VALUE   NUM_ROWS
-------------------- -------------------- ---------- ----------
PART_01              PART_01_CD_01        'ONE'               1
PART_01              PART_01_CD_02        'TWO'               1
PART_01              PART_01_CD_03        'THREE'             0

By adding more data that expands the partitions, you'll see the subpartitions are generated as well.

INSERT INTO interval_tab VALUES (3, 'THREE', 'Three', TO_DATE('01-NOV-2007', 'DD-MON-YYYY'));
INSERT INTO interval_tab VALUES (4, 'TWO', 'TWO', TO_DATE('30-NOV-2007', 'DD-MON-YYYY'));
COMMIT;
EXEC DBMS_STATS.gather_table_stats(USER, 'INTERVAL_TAB');

SELECT partition_name, subpartition_name, high_value, num_rows
FROM   user_tab_subpartitions
where table_name = 'INTERVAL_TAB'
ORDER BY table_name, partition_name, subpartition_name;

PARTITION_NAME       SUBPARTITION_NAME    HIGH_VALUE   NUM_ROWS
-------------------- -------------------- ---------- ----------
PART_01              PART_01_CD_01        'ONE'               1
PART_01              PART_01_CD_02        'TWO'               1
PART_01              PART_01_CD_03        'THREE'             0
SYS_P40              SYS_SUBP37           'ONE'               0
SYS_P40              SYS_SUBP38           'TWO'               1
SYS_P40              SYS_SUBP39           'THREE'             1

Subpartition names are automatically with system name, which is not I'm expecting: it's better to named as partition + subpartition_template, ie, SYS_P40_CD_01.

Encrypt in WebLogic

A colleague gave me a piece of code to plug into my WebLogic 10 JDBC configure file:

<jdbc-data-source ...
...
<password-encrypted>{3DES}xxxxxxxxxxxxxxx</password-encrypted>
...
</jdbc-data-source>

When I copied over, and tried to start WebLogic, got an exception:

weblogic.management.ManagementRuntimeException: com.rsa.jsafe.JSAFE_PaddingException: Could not perform unpadding: invalid pad byte.

It's really not telling what you'll have to do. After some research, I figured out I need to re-encrypt the password. Here's the utility to use:

java -cp <weblogic_home>\server\lib\weblogic.jar -Dweblogic.RootDirectory=<your_domain_dir> weblogic.security.Encrypt <password>

Pasted the result to replace old password, it worked fine.

Slow When Bulk Inserting Records to Database Using Lotus Notes Agent

I don't do Lotus Notes program. But our Notes developers told me that it was extremely slow to insert records into Oracle (SQL Server as well) database. One example is that 1 million rows took 8 hours.

After some investigation, I found that they were constructing full SQLs, instead of using parameters. Below is a piece of their code:

    Dim con As ODBCConnection
   Dim qry As ODBCQuery
   Dim result As ODBCResultSet

   Set qry = New ODBCQuery
   Set result = New ODBCResultSet
   con.ConnectTo("")
   Set qry.Connection = con

   for each document loop
       qry.SQL = "insert into person (fname,lname) values( '" & v_fname & 
                  "','" & v_lname & "' )"
       Set result.Query = qry
   end loop
   ...

The high-lite is the trouble. Whenever this was called, I noticed so many queries on Oracle data dictionary views. It seems to me that Notes was parsing the SQL, and the second statement sometimes took about 2 seconds.

To solve it, I did some research and found the parameters was useful.

    qry.SQL = "insert into person (fname,lname) values( ?fname?, ?lname? )"
    Set result.Query = qry
    for each document loop
        Call result.SetParameter( fname, "'" & v_fname & "'" )
        Call result.SetParameter( lname, "'" & v_lname & "'" )
    end loop

The performance improved greatly, one example is 30K records reduced time from 4 hours to 10 minutes. Still high, but Notes spends most of the time preparing data.

Accessing Non-exsits Item in Oracle Associative Array

Look at this piece of code:

 set serveroutput on
 declare
  type MONTH_TYPE is table of varchar(20) index by binary_integer;

  month_table   MONTH_TYPE;
begin
  month_table(1) := 'Jan';
  month_table(2) := 'Feb';

  if month_table(3) is null then
    dbms_output.put_line( 'March is not defined.' );
  end if;
end;
/

What you'll get? You may think the print line.

However, you'll get an error:

    ERROR at line 1:
ORA-01403: no data found
ORA-06512: at line 9

Well, associative array is working the same way as table (is that why it's defined as TABLE of ...), and month_table is similar to select value from month_table into v..., so need to have an exception handling.

    set serveroutput on
 declare
   type MONTH_TYPE is table of varchar(20) index by binary_integer;

   month_table   MONTH_TYPE;
 begin
   month_table(1) := 'Jan';
   month_table(2) := 'Feb';

   if month_table(3) is null then
     dbms_output.put_line( 'March is not defined.' );
   end if;
 exception
   when NO_DATA_FOUND then
     dbms_output.put_line( 'March is not found.' );
 end;
 /

Then, you'll get: March is not found.

Track Long Operations in Oracle

You may need to run a process on 1 million rows and the whole process takes a few hours to finish. In Oracle, there is a way that you can track the process.

Here's a piece of example code in PL/SQL, it takes advantage of package DBMS_APPLICATION_INFO.

 declare
   -- main variables
   ...

   -- long op info
   v_rindex     PLS_INTEGER;
   v_slno       PLS_INTEGER;
   v_totalwork  NUMBER;
   v_sofar      NUMBER;
   v_obj        PLS_INTEGER;

   v_op_name    varchar(100) := 'My work';
   v_units      varchar(100) := 'rows processed';
 begin
   -- Calculate total work (number of rows to be processed, etc.)
   select count(*) into v_totalwork from ...;

   v_sofar := 0;
   v_rindex     := DBMS_APPLICATION_INFO.set_session_longops_nohint;

   for ... -- A loop to process your work
   loop
     ...  -- do your work here.

     -- log longops view.
     v_sofar := v_sofar + 1;
     if mod( v_sofar, 500 ) = 0 then   -- log your operation every 500 rounds
        DBMS_APPLICATION_INFO.set_session_longops(rindex   => v_rindex,
                                               slno        => v_slno,
                                               op_name     => v_op_name,
                                               target      => v_obj,
                                               context     => 0,
                                               sofar       => v_sofar,
                                               totalwork   => v_totalwork,
                                               target_desc => 'Some description here',
                                               units       => v_units);
     end if;

   end loop;

   -- mark the end
   DBMS_APPLICATION_INFO.set_session_longops(rindex   => v_rindex,
                                          slno        => v_slno,
                                          op_name     => v_op_name,
                                          target      => v_obj,
                                          context     => 0,
                                          sofar       => v_sofar,
                                          totalwork   => v_totalwork,
                                          target_desc => 'Some description here',
                                          units       => v_units);

 end;
 /

To view the status of the process, run query:

 select sid, serial#, opname, sofar, totalwork, start_time, last_update_time
 from v$session_longops where opname = 'My Work';

Columns sofar and totalwork show you how much work has been done so far.

Another Way to Reorgnize Table in Oracle

After inserting/deleting from a table for a long time, the table may contain much spared space that hurts the full table scan greatly. Instead of export and import, or recreate it. Move it from one tablespace to another is another way.

Here's a case I did recently. I noticed a table is slow when running full table scan, however it contains only 30K rows, and each row is not that big.

select segment_name, sum(bytes)/1024/1024 MB_Bytes, sum(blocks) blocks, sum(extents) extents
from user_segments
where segment_name = 'BAD_TABLE'
group by segment_name;

SEGMENT_NAME        MB_BYTES   BLOCKS     EXTENTS
------------------- ---------- ---------- ----------
BAD_TABLE           220        28160      99

OK, move it to another tablespace:

alter table BAD_TABLE move tablespace tb_another;

Run that check again:

select segment_name, sum(bytes)/1024/1024 MB_Bytes, sum(blocks) blocks, sum(extents) extents
from user_segments
where segment_name = 'BAD_TABLE'
group by segment_name;

SEGMENT_NAME           MB_BYTES   BLOCKS     EXTENTS
---------------------- ---------- ---------- ----------
BAD_TABLE              32         4096       47

It's much smaller. Well, you may want to move it back to its original tablespace.

Oracle Database Console Credential Failures on Windows

There is an error message in Oracle Database console:

Connection to host as user USER failed: ERROR: Wrong password for user

Usually you find this error when trying to connect to host, for example, you want to start/shutdown, or backup the database. It had confused me for a long time. Then I found an solution.

1. Provide the 'Log on as a batch job' privilege.

• Go to control panel/administrative tools
• click on "local security policy"
• click on "local policies"
• click on "user rights assignments"
• double click on "log on as a batch job"
• click on "add" and add the user(s) that you're going to use in database console.

2. Set credentials and test

• Go to the Preferences link in the database console page
• click on Preferred Credentials (link on the left menu)
• under "Target Type: Host" click on "set credentials"
• enter the OS user(s) for whom you have set "logon as a batch job" privilege
• click on "Test"

Then you should see the backup is working.