Session killin’ time

As developers, sometimes we set something running that we wish we hadn’t Smile  And naturally, we’d like to be good IT citizens and clean up the mess as quick as we can.  (For most of us, this means – cover our tracks before the phone rings about smoke coming out of the server).  But of course, getting an administrator to hand over the trigger to let you have the ALTER SYSTEM KILL SESSION command is probably unlikely because…well… it’s just a bad bad idea.  So here’s a wrapper which might serve as a starting point for you.  It expose the kill system command to you, but in a restricted set of circumstances.

Sample Usage

By default, we report any session that has a status of active or killed. We’ll see the session details, whether it’s running or blocked, plus the SQL ID etc.


SQL> select * from table(sys.my_session.s);
 
Session       User/Elapsed Secs       Current SQL     Status        Program                                 Blocked by Session
--------------------------------------------------------------------------------------------------------------------------------
257,5501      ASKTOM (0)              89uk42w1xkdty   ACTIVE        sqlplus.exe-comcdona
261,8848      QDB_PROD (33747)                        KILLED        SQL Developer-stevenfeuerstein
368,43752     ASKTOM (12)             a40p1nyb24j18   ACTIVE        sqlplus.exe-comcdona

Alternatively, we can pass in “ALL” to see all sessions


SQL> select * from table(sys.my_session.s('all'));
 
Session       User/Elapsed Secs       Current SQL     Status        Program                                 Blocked by Session
--------------------------------------------------------------------------------------------------------------------------------
1,13808       SYS (1769614)                           INACTIVE      VKTM-oracle
2,23469       SYS (1769611)                           INACTIVE      DIAG-oracle
3,36185       SYS (1769611)                           INACTIVE      DBW0-oracle
4,21472       SYS (1769611)                           INACTIVE      SMON-oracle
5,8033        SYS (1769611)                           INACTIVE      PXMN-oracle
6,16680       SYS (1769605)                           INACTIVE      TMON-oracle
7,61493       SYS (1769605)                           INACTIVE      ARC3-oracle
9,16830       APEX_PUBLIC_USER (1122)                 INACTIVE      APEX Listener-oracle
11,3902       SYS (1769590)                           INACTIVE      CJQ0-oracle
12,20631      SYS (1769587)                           INACTIVE      QM00-oracle
14,50003      APEX_LISTENER (949)                     INACTIVE      APEX Listener-oracle
17,2037       SYS (1769585)                           INACTIVE      Q009-oracle
22,41550      SYS (125)                               INACTIVE      W002-oracle
26,52963      ORDS_PUBLIC_USER (99637)                INACTIVE      APEX Listener-oracle
28,27784      SYS (3693)                              INACTIVE      W007-oracle
30,9396       ORDS_PUBLIC_USER (1306)                 INACTIVE      APEX Listener-oracle
119,21406     SYS (1769614)                           INACTIVE      GEN0-oracle
120,9696      SYS (1769611)                           INACTIVE      DBRM-oracle
121,65040     SYS (1769611)                           INACTIVE      LGWR-oracle
122,64828     SYS (1769611)                           INACTIVE      LG01-oracle
123,65400     SYS (1769611)                           INACTIVE      MMON-oracle
...
...

By default, you can just call the “kill” routine and we’ll look for a single session that

  • has been active
  • for more than 10 seconds,
  • is owned by you,
  • is within a list of known user accounts is running on your terminal,
  • came from sqlplus or sql developer,
  • is not a parallel slave

etc etc….Basically you edit the code (at the end of this blog) to be as restrictive as required to protect people from themselves Smile

I’m just dbms_output-ing in this case, but you get the idea. We first try a kill, and then attempt a disconnect as well.


SQL> exec sys.my_session.kill
alter system kill session '368,43752' immediate
alter system disconnect session '368,43752' 
 
PL/SQL procedure successfully completed.

You can see that this was the only applicable ASKTOM process from the active list in the first query.

If we don’t find a session, or find more than 1 session, you’ll get errors, eg


SQL> exec sys.my_session.kill
BEGIN my_session.kill; END;
 
*
ERROR at line 1:
ORA-20000: No suitable session found for killing
 
SQL> exec sys.my_session.kill
BEGIN my_session.kill; END;
 
*
ERROR at line 1:
ORA-20000: More than one session found.  Pass in the SID number

In the latter case, you can do a more selective kill by passing in the SID, eg


SQL> exec sys.my_session.kill(368)
alter system kill session '368,43752' immediate
 
PL/SQL procedure successfully completed.

Enjoy !

Source Code


create or replace
package sys.my_session is

  procedure kill(p_sid number default null);

  function s(p_type varchar2 default 'ACTIVE') return sys.odcivarchar2list pipelined;

end;
/

--
-- Choose (wisely) which schemas you want to give access to this
--
grant execute on sys.my_session to ???????;


create or replace
package body sys.my_session is

procedure kill(p_sid number default null) is
  l_sid     int;
  l_serial# int;
  l_user    varchar2(30) := user;
begin
  select s.sid, s.serial#
  into   l_sid, l_serial#
  from   v$session s,
         v$px_session p

  --
  -- Here is where you implement all the rules for controlling exactly *what* sessions
  -- people will be allowed to kill.
  --

  --
  -- must be my own login
  --
  where  s.username = l_user
  --
  -- must be running something for 10 seconds or more
  --
  and    s.status = 'ACTIVE'
  and    s.last_call_et > 10
  --
  -- must be SQL Dev or SQL Plus
  --
  and    ( upper(s.program) like '%SQLPLUS%'
        or upper(s.program) like '%SQL%DEVELOPER%'
         )
  --
  -- must be an account we're allowed to kill (just in case they circumvent the 'own login' check above)
  --
  and   s.username in ('ASKTOM','QDB_PROD')
  --
  -- must be on the same machine as the problem session
  --
  and   upper(s.terminal) = upper(sys_context('USERENV','TERMINAL'))
  --
  -- allow override for explicit sid
  --
  and   s.sid = nvl(p_sid,s.sid)
  --
  -- must not be a parallel slave
  --
  and   s.sid = p.sid(+)
  and   s.serial# = p.serial#(+)
  and   s.sid != p.qcsid(+)
  and   p.sid is null;

  begin
    dbms_output.put_line('alter system kill session '''||l_sid||','||l_serial#||''' immediate');
--    execute immediate 'alter system kill session '''||l_sid||','||l_serial#||''' immediate';
  exception
    when others then null;
  end;

  begin
    dbms_output.put_line('alter system disconnect session '''||l_sid||','||l_serial#||'''');
--    execute immediate 'alter system disconnect session '''||l_sid||','||l_serial#||'''';
  exception
    when others then null;
  end;
  
exception
  --
  -- No session found, means you're fine or trying to be nasty
  --
  when no_data_found then
     raise_application_error(-20000,'No suitable session found for killing');
  --
  -- More than one session found, means we'll need the sid explicitly
  --
  when too_many_rows then
     raise_application_error(-20000,'More than one session found.  Pass in the SID number');
end;

function s(p_type varchar2 default 'ACTIVE') return sys.odcivarchar2list pipelined is
begin
 for i in ( 
    select row_number() over ( order by s.sid)  r, s.sid, s.serial#,
              nvl(s.username,'SYS')
      || ' ('||s.last_call_et||')' username,
      case when s.lockwait is null then
           case when s.username is null then
              nvl2(j.job_sid,'ACTIVE','INACTIVE')
           else
              s.status
           end
      else 'BLOCKED'
      end status,
      nvl(s.sql_id,' ') sql_id,
      nvl(case when s.program is not null then
             ( case when s.program like 'oracle%(%)%' then regexp_substr(s.program,'^oracle.*\((.*)\).*$',1, 1, 'i', 1)
                    else s.program
                    end )
           when s.username is null then ( select p.program
                                          from   v$process p
                                          where  s.PADDR = p.ADDR )
           end || '-' || s.osuser,' ') program ,
           nvl(to_char(blocking_session),' ') blocking_session,
           nvl(case
             when blocking_session is null then cast(null as varchar2(1))
             else
              cast(( select substr(s1.osuser||'-'||s1.program,1,60)
                from   v$session s1
                where s1.sid = s.blocking_session
              ) as varchar2(60))
           end,' ') blocker
    from v$session s,
              ( select sid job_sid
                from   v$lock
                where  type = 'JQ' ) j
    where s.sid = j.job_sid(+)
    and (
          ( upper(p_type) = 'ACTIVE' 
            and s.status in ('ACTIVE','KILLED')
            and ( s.username is not null or ( s.username is null and s.last_call_et < 300 ) )
          ) 
          or
          upper(p_type) = 'ALL'
        )
    order by s.sid
 ) 
 loop
   if i.r = 1 then
     pipe row ( rpad('Session',14)||
                rpad('User/Elapsed Secs',24)||
                rpad('Current SQL',16)||
                rpad('Status',14)||
                rpad('Program',40)||
                rpad('Blocked by Session',20)
               );
     pipe row ( rpad('-',14,'-')||
                rpad('-',24,'-')||
                rpad('-',16,'-')||
                rpad('-',14,'-')||
                rpad('-',40,'-')||
                rpad('-',20,'-')
               );
   end if;
   pipe row ( rpad(i.sid||','||i.serial#,14)||
              rpad(i.username,24)||
              rpad(i.sql_id,16)||
              rpad(i.status,14)||
              rpad(substr(i.program,1,38),40)||
              lpad(i.blocker,20)
            );
 end loop;

end;

end;
/
sho err
select * from table(my_session.s('all'));

Transportable Tablespace–part 2

I did a little demo of sharing a tablespace between two databases a few days back – you can see the details here or by just scrolling down Smile if you’re on the home page.

To avoid clouding the demonstration I omitted something in the details, but I’ll share that now, because it could be critical depending on how you currently use transportable tablespaces.

Let me do the most basic of examples now, transporting a tablespace from one database to another:

First, we make our tablespace read only, and Datapump export out the metadata


SQL> alter tablespace DEMO read only;

Tablespace altered.

SQL> exit
Disconnected from Oracle Database 12c Enterprise Edition Release 12.1.0.2.0 - 64bit Production
With the Partitioning, OLAP, Advanced Analytics and Real Application Testing options

C:\>expdp transport_tablespaces=DEMO directory=TEMP dumpfile=tts.dmp

Export: Release 12.1.0.2.0 - Production on Tue Apr 18 14:16:06 2017

Copyright (c) 1982, 2016, Oracle and/or its affiliates.  All rights reserved.

Username: / as sysdba

Connected to: Oracle Database 12c Enterprise Edition Release 12.1.0.2.0 - 64bit Production
With the Partitioning, OLAP, Advanced Analytics and Real Application Testing options
Starting "SYS"."SYS_EXPORT_TRANSPORTABLE_01":  /******** AS SYSDBA transport_tablespaces=DEMO directory=TEMP dumpfile=tts.dmp
Processing object type TRANSPORTABLE_EXPORT/PLUGTS_BLK
Processing object type TRANSPORTABLE_EXPORT/TABLE
Processing object type TRANSPORTABLE_EXPORT/TABLE_STATISTICS
Processing object type TRANSPORTABLE_EXPORT/STATISTICS/MARKER
Processing object type TRANSPORTABLE_EXPORT/POST_INSTANCE/PLUGTS_BLK
Master table "SYS"."SYS_EXPORT_TRANSPORTABLE_01" successfully loaded/unloaded
******************************************************************************
Dump file set for SYS.SYS_EXPORT_TRANSPORTABLE_01 is:
  C:\TEMP\TTS.DMP
******************************************************************************
Datafiles required for transportable tablespace DEMO:
  C:\ORACLE\ORADATA\NP12\DEMO.DBF
Job "SYS"."SYS_EXPORT_TRANSPORTABLE_01" successfully completed at Tue Apr 18 14:17:03 2017 elapsed 0 00:00:50

Then, I copy the datafile(s) to the target location and Datapump import the metadata.


C:\>copy C:\oracle\oradata\np12\DEMO.DBF C:\oracle\oradata\db122\DEMO.DBF
        1 file(s) copied.

C:\>impdp transport_datafiles=C:\oracle\oradata\db122\DEMO.DBF directory=TEMP dumpfile=tts.dmp

Import: Release 12.2.0.1.0 - Production on Tue Apr 18 14:17:27 2017

Copyright (c) 1982, 2017, Oracle and/or its affiliates.  All rights reserved.

Username: / as sysdba

Connected to: Oracle Database 12c Enterprise Edition Release 12.2.0.1.0 - 64bit Production
Master table "SYS"."SYS_IMPORT_TRANSPORTABLE_01" successfully loaded/unloaded
Starting "SYS"."SYS_IMPORT_TRANSPORTABLE_01":  /******** AS SYSDBA transport_datafiles=C:\oracle\oradata\db122\DEMO.DBF directory=TEMP dumpfile=tts.dmp
Processing object type TRANSPORTABLE_EXPORT/PLUGTS_BLK
Processing object type TRANSPORTABLE_EXPORT/TABLE
Processing object type TRANSPORTABLE_EXPORT/TABLE_STATISTICS
Processing object type TRANSPORTABLE_EXPORT/STATISTICS/MARKER
Processing object type TRANSPORTABLE_EXPORT/POST_INSTANCE/PLUGTS_BLK
Job "SYS"."SYS_IMPORT_TRANSPORTABLE_01" successfully completed at Tue Apr 18 14:17:46 2017 elapsed 0 00:00:15

And voila, there is my tablespace in the target database…


C:\>sql122

SQL*Plus: Release 12.2.0.1.0 Production on Tue Apr 18 14:19:08 2017

Copyright (c) 1982, 2016, Oracle.  All rights reserved.

Last Successful login time: Tue Apr 18 2017 14:14:19 +08:00

Connected to:
Oracle Database 12c Enterprise Edition Release 12.2.0.1.0 - 64bit Production



SQL> select * from dba_tablespaces
  2  where tablespace_name = 'DEMO'
  3  @pr
==============================
TABLESPACE_NAME               : DEMO
BLOCK_SIZE                    : 8192
INITIAL_EXTENT                : 1048576
NEXT_EXTENT                   : 1048576
MIN_EXTENTS                   : 1
MAX_EXTENTS                   : 2147483645
MAX_SIZE                      : 2147483645
PCT_INCREASE                  : 0
MIN_EXTLEN                    : 1048576
STATUS                        : READ ONLY
CONTENTS                      : PERMANENT
LOGGING                       : LOGGING
FORCE_LOGGING                 : NO
EXTENT_MANAGEMENT             : LOCAL
ALLOCATION_TYPE               : UNIFORM
PLUGGED_IN                    : YES
SEGMENT_SPACE_MANAGEMENT      : AUTO
DEF_TAB_COMPRESSION           : DISABLED
RETENTION                     : NOT APPLY
BIGFILE                       : NO
PREDICATE_EVALUATION          : HOST
ENCRYPTED                     : NO
COMPRESS_FOR                  :
DEF_INMEMORY                  : DISABLED
DEF_INMEMORY_PRIORITY         :
DEF_INMEMORY_DISTRIBUTE       :
DEF_INMEMORY_COMPRESSION      :
DEF_INMEMORY_DUPLICATE        :
SHARED                        : SHARED
DEF_INDEX_COMPRESSION         : DISABLED
INDEX_COMPRESS_FOR            :
DEF_CELLMEMORY                :
DEF_INMEMORY_SERVICE          :
DEF_INMEMORY_SERVICE_NAME     :
LOST_WRITE_PROTECT            : OFF
CHUNK_TABLESPACE              : N

PL/SQL procedure successfully completed.

“Gee thanks, Connor” I can hear you muttering. A demonstration of the flippin’ obvious!

But there is one thing that is not apparent from the export or import logs. Let’s take a squizz at the database alert log for the target, that is, the database we imported the tablespace into.


DW00 started with pid=69, OS id=632, wid=1, job SYS.SYS_IMPORT_TRANSPORTABLE_01
2017-04-18T14:17:34.208631+08:00
Plug in tablespace DEMO with datafile
  'C:\oracle\oradata\db122\DEMO.DBF'
2017-04-18T14:17:46.199645+08:00
ALTER TABLESPACE "DEMO" READ WRITE
Completed: ALTER TABLESPACE "DEMO" READ WRITE
2017-04-18T14:17:46.665512+08:00
ALTER TABLESPACE "DEMO" READ ONLY
Completed: ALTER TABLESPACE "DEMO" READ ONLY

That is a change in 12c. Whilst our imported tablespace ends up as read only as it has always done, during the import process, there was a small window where the tablespace was READ WRITE. This is needed to make some metadata corrections to the tablespace on the way in.

So if you do intend to share tablespaces between databases, that is, share a single copy of the file, make sure take some precautions. On my Windows laptop, standard Windows file locking prohibited me from causing any damage to my source datafile, but on other platforms you might to set those files to read only at the OS level just in case. Of course, you’ll then see a warning during the Datapump import saying that the momentary change to read/write could not be done, but that is not a critical problem.  The transport will still complete.

Sharing a tablespace between 2 databases

I was reading an interesting discussion today about multiple databases each containing large amounts of read-only data.  If that read-only data is common, then it would make sense to have a single copy of that data and have both databases share it.

Well, as long as you can isolate that data into its own tablespace, then you can do that easily with Oracle by transporting the metadata between two databases and leaving the files in place.

Here’s an example

Source database


SQL> select banner from v$version;

BANNER
--------------------------------------------------------------------------------
Oracle Database 12c Enterprise Edition Release 12.1.0.2.0 - 64bit Production
PL/SQL Release 12.1.0.2.0 - Production
CORE    12.1.0.2.0      Production
TNS for 64-bit Windows: Version 12.1.0.2.0 - Production
NLSRTL Version 12.1.0.2.0 - Production

SQL> create tablespace i_am_on_121 datafile 'C:\oracle\oradata\tts\my_tspace' size 50m;

Tablespace created.

SQL> create table t tablespace i_am_on_121 as select * from dba_objects;

Table created.

SQL> alter tablespace i_am_on_121 read only;

Tablespace altered.

C:\>expdp transport_tablespaces=i_am_on_121 directory=TEMP dumpfile=tts.dmp

Export: Release 12.1.0.2.0 - Production on Fri Apr 14 08:50:24 2017

Copyright (c) 1982, 2016, Oracle and/or its affiliates.  All rights reserved.

Username: mcdonac/*****

Connected to: Oracle Database 12c Enterprise Edition Release 12.1.0.2.0 - 64bit Production
With the Partitioning, OLAP, Advanced Analytics and Real Application Testing options
Starting "MCDONAC"."SYS_EXPORT_TRANSPORTABLE_01":  mcdonac/******** transport_tablespaces=i_am_on_121 directory=TEMP dumpfile=tts.dmp
Processing object type TRANSPORTABLE_EXPORT/PLUGTS_BLK
Processing object type TRANSPORTABLE_EXPORT/TABLE
Processing object type TRANSPORTABLE_EXPORT/TABLE_STATISTICS
Processing object type TRANSPORTABLE_EXPORT/STATISTICS/MARKER
Processing object type TRANSPORTABLE_EXPORT/POST_INSTANCE/PLUGTS_BLK
Master table "MCDONAC"."SYS_EXPORT_TRANSPORTABLE_01" successfully loaded/unloaded
******************************************************************************
Dump file set for MCDONAC.SYS_EXPORT_TRANSPORTABLE_01 is:
  C:\TEMP\TTS.DMP
******************************************************************************
Datafiles required for transportable tablespace I_AM_ON_121:
  C:\ORACLE\ORADATA\TTS\MY_TSPACE
Job "MCDONAC"."SYS_EXPORT_TRANSPORTABLE_01" successfully completed at Fri Apr 14 08:51:16 2017 elapsed 0 00:00:47

and then we import it into a different database (and this one even is a different version!).

Target database


C:\Users\hamcdc>impdp transport_datafiles=C:\oracle\oradata\tts\my_tspace directory=TEMP dumpfile=tts.dmp

Import: Release 12.2.0.1.0 - Production on Fri Apr 14 08:51:28 2017

Copyright (c) 1982, 2017, Oracle and/or its affiliates.  All rights reserved.

Username: mcdonac/*****

Connected to: Oracle Database 12c Enterprise Edition Release 12.2.0.1.0 - 64bit Production
Master table "MCDONAC"."SYS_IMPORT_TRANSPORTABLE_01" successfully loaded/unloaded
Starting "MCDONAC"."SYS_IMPORT_TRANSPORTABLE_01":  mcdonac/******** transport_datafiles=C:\oracle\oradata\tts\my_tspace directory=TEMP dumpfile=tts.dmp
Processing object type TRANSPORTABLE_EXPORT/PLUGTS_BLK
Processing object type TRANSPORTABLE_EXPORT/TABLE
Processing object type TRANSPORTABLE_EXPORT/TABLE_STATISTICS
Processing object type TRANSPORTABLE_EXPORT/STATISTICS/MARKER


SQL> select banner from v$version;

BANNER
--------------------------------------------------------------------------------
Oracle Database 12c Enterprise Edition Release 12.2.0.1.0 - 64bit Production
PL/SQL Release 12.2.0.1.0 - Production
CORE    12.2.0.1.0      Production
TNS for 64-bit Windows: Version 12.2.0.1.0 - Production
NLSRTL Version 12.2.0.1.0 - Production

SQL> select count(*) from t;

  COUNT(*)
----------
     92934

SQL> select * from dba_tablespaces
  2  where tablespace_name = 'I_AM_ON_121'
  3  @pr
==============================
TABLESPACE_NAME               : I_AM_ON_121
BLOCK_SIZE                    : 8192
INITIAL_EXTENT                : 65536
NEXT_EXTENT                   :
MIN_EXTENTS                   : 1
MAX_EXTENTS                   : 2147483645
MAX_SIZE                      : 2147483645
PCT_INCREASE                  :
MIN_EXTLEN                    : 65536
STATUS                        : READ ONLY
CONTENTS                      : PERMANENT
LOGGING                       : LOGGING
FORCE_LOGGING                 : NO
EXTENT_MANAGEMENT             : LOCAL
ALLOCATION_TYPE               : SYSTEM
PLUGGED_IN                    : YES
SEGMENT_SPACE_MANAGEMENT      : AUTO
DEF_TAB_COMPRESSION           : DISABLED
RETENTION                     : NOT APPLY
BIGFILE                       : NO
PREDICATE_EVALUATION          : HOST
ENCRYPTED                     : NO
COMPRESS_FOR                  :
DEF_INMEMORY                  : DISABLED
DEF_INMEMORY_PRIORITY         :
DEF_INMEMORY_DISTRIBUTE       :
DEF_INMEMORY_COMPRESSION      :
DEF_INMEMORY_DUPLICATE        :
SHARED                        : SHARED
DEF_INDEX_COMPRESSION         : DISABLED
INDEX_COMPRESS_FOR            :
DEF_CELLMEMORY                :
DEF_INMEMORY_SERVICE          :
DEF_INMEMORY_SERVICE_NAME     :
LOST_WRITE_PROTECT            : OFF
CHUNK_TABLESPACE              : N

PL/SQL procedure successfully completed.

SUM is better than DISTINCT

There is a good chance that (based on this blog post title) that you’re expecting a post on SQL, and that’s understandable. But I’ll come clean nice and early – that was just to lure you in Smile 

The post is about SUM and DISTINCT, but not in the technical sense.

A few days ago, fellow OakTable member Jonathan Lewis put a post on his blog: https://jonathanlewis.wordpress.com/2017/04/10/ask-jonathan/ where he is launching a mechanism where you can pose questions to him, and he will select topics of interest and write about them in the UKOUG Oracle Scene magazine.  What I found hilarious was that people started emailing me saying “Are you concerned about AskTOM?”.  Now, last time I checked, we don’t have Google Ads or Facebook Ads on AskTOM, so it is not as if we have some fiscal need to keep the website hits high, increase our “social engagement”, or whatever the latest buzzword is for that sort of thing.  If it was – you see lots of cat videos and references to Justin Bieber in our answers Smile

AskTOM does one thing…and one thing only – we try to help out the Oracle community, to make them more successful.  It’s what Tom did in the past, and it’s what the team do now.  That’s the same reason why our answers will often refer people to links / blog posts / tutorials outside of the oracle.com domain, for example, to the excellent work of some of the Oracle Aces and Ace Directors.  It’s about getting good information and getting the job done.  Similarly, when I give talks on AskTom to user groups, one of the things I encourage people to do is form their own “AskMe” concept within their own IT shops – get people asking questions, and discussing solutions at the department level, the organizational level, and ultimately the global community level.  Then we all benefit – we all win.

So rather than being “concerned”, I’m thrilled by anyone that wants to put their hand up and say “Hey, I’m here to help out the community”.  I encourage any of us who are experienced practitioners in the Oracle community to do the same.

The SUM of contributors to the Oracle community will be much better for that community than just a few DISTINCT people !  … and hence the title of the post.

Apex Interactive Grid and IOT’s

I love the interactive grid in Application Express.  And here’s why… (Warning: Ranting mode is now on Smile)

You can tell people

  • here’s an application built with almost no code, in fact, you probably could have built it yourself
  • it’s multi-user, with optimistic locking built in for you
  • it’s secure
  • it’s backed up and recoverable,
  • it’s scales ridiculously well,
  • it doesn’t need any complicated middle tier, or software libraries,
  • it can be accessed anywhere you have a browser…which is…anywhere!
  • it has responsive look and feel,
  • it was built with software that doesn’t cost a single dollar,
  • it centralises the data so you have a single source of truth

and after you have told them all of that….do you know what they’ll say ?

“Yeah…but I like to double-click on a field to edit it…So I’ll just use Excel and store it on my hard drive”

AGGGGHHHHHHHH!!!!!!!!!!!!!  Somebody…..shoot….me……

Interactive grids blow that flaccid argument out of the water!  So get on board to Application Express 5.1+ for some interactive grid awesome-ness.

One discovery I did make with interactive grids, is that if you based the grid on an Index-Organized table, you will erroneously get a ROWID column in your grid

int_grid_iot_proj_short

 

This is not a major drama – just delete the item from the designer and it will still work just fine, and this minor detail is fixed in an upcoming release.

Apex patch for 5.1

If you are running Application Express, there is a new patch available.  Lots of fixes which you can read about here

http://www.oracle.com/technetwork/developer-tools/apex/application-express/apex-511-patch-set-notes-3661846.html

I just downloaded patch 25341386 and followed the installation instructions and it went through with no problems at all in just a few minutes.


Verifying Application Express version...
...have version 5.1.0.00.45

PL/SQL procedure successfully completed.

Verifying Application Express version...
...have version 5.1.0.00.45

PL/SQL procedure successfully completed.


PL/SQL procedure successfully completed.


Session altered.

Stopping and disabling APEX jobs

PL/SQL procedure successfully completed.


PL/SQL procedure successfully completed.


Session altered.


Grant succeeded.

Verifying Application Express version...
...have version 5.1.0.00.45

PL/SQL procedure successfully completed.


PL/SQL procedure successfully completed.


Session altered.


...
...
...

Validating Application Express
(09:02:38) Starting validate_apex for APEX_050100
(09:02:41) Checking missing sys privileges
(09:02:41) Recompiling
(09:02:42) Checking for objects that are still invalid
(09:02:42) Key object existence check
(09:02:42) Setting DBMS Registry for APEX to valid
(09:02:42) Exiting validate_apex

PL/SQL procedure successfully completed.


PL/SQL procedure successfully completed.


PL/SQL procedure successfully completed.

timing for: Complete Patch
Elapsed: 00:02:33.76

Enjoy !

End of an era …

Four years ago I wrote about a little volunteer project that my partner did.  A small association that provided outdoor experiences and facilities for kids with physical impairments needed a system to record member and volunteer details, plus a few other bits and pieces.  We built an Apex solution running on XE.  This week, they became part of a larger government initiative, and thus their Apex application was no longer needed and the information migrated to a centralised service.  There was a tinge of sadness about that, but I also was pleased with the outcomes of this “project” namely:

  • It ran for 4 years with virtually never an outage besides those related to power etc.  (After all, their “server” was just a PC in the secretary’s office Smile)
  • Their PC’s etc went through several iterations of patching, upgrades, replacements etc and the system was unaffected because it was entirely run in the browser
  • We never had a single issue with the database
  • Minimal maintenance needed.  In fact, the only “serious” bit of work needed after go live was when we discovered that their external drive (where we stored our database backups) was from time to time removed to be used for offsite file transfers, and when it was re-attached they would assign it a new drive letter.  So we adjusted our backup script to cycle through drive letters to “find” the disk and adjust the RMAN backup details accordingly.

That’s one of the great things with Apex, and a database-centric model.  It is just so well insulated from all the things that change most frequently, that is, those elements closest to the client.

So yesterday I took a final datapump export of the system as a “just in case” measure, and uninstalled the application and its dependencies from the PC.  But for four years, they had a successful application that provided all of their data entry needs and all of their reporting needs, and besides checking an occasional email to ensure the backups were working ok, took very little of my time. And surely that’s what all IT applications “aspire” to be – stuff that just plain works.

It never let them down and never cost them a cent.  You can’t tick any more boxes than that Smile