Search

Top 60 Oracle Blogs

Recent comments

Its not about the outage

My Oracle Support had a fairly lengthy outage today right in the middle of the Australian business day.

But I’m not going to blog about that.  One thing I’ve learnt from many client sites is that people will understand and forgive things like outages, or errors, or crashes, or just plain wrong software, as long its evident that you are passionately working for the benefit of the user, that you were not lazy or flippant or learning from mistakes…

But one thing, perhaps the biggest thing, that customers will NOT tolerate, is when you don’t listen to what they’re trying to tell you

And that’s where MOS is suffering – not from outages, not from errors, but from not listening….

I logged this SR:

Problem: Making index partition unusable does not free underlying segment

Test Case:

SQL> sho parameter deferred

NAME TYPE VALUE
------------------------------------ ----------- ----------------------------------
deferred_segment_creation boolean TRUE

SQL> drop table T purge;

Table dropped.

SQL> create table T ( x timestamp, y int)
  2  PARTITION BY RANGE (x)
  3  INTERVAL( NUMTODSINTERVAL(1,'DAY'))
  4  (
  5  PARTITION ARCH_P0 VALUES LESS THAN (TIMESTAMP' 2009-01-01 00:00:00')
  6  )
  7  /

Table created.

SQL> insert into T values ('01-APR-13',1);
SQL> insert into T values ('02-APR-13',1);
SQL> insert into T values ('03-APR-13',1);
SQL> insert into T values ('04-APR-13',1);

SQL> create index IX on T ( x ) local;

Index created.

SQL> col partition_name new_value d
SQL> select segment_name, partition_name, bytes from user_segments;

SEGMENT_NAME PARTITION_NAME BYTES
---------------------------------------- ------------------------------ ----------
T SYS_P42997 8388608
T SYS_P42998 8388608
T SYS_P42999 8388608
T ARCH_P0 8388608
T SYS_P42996 8388608
IX ARCH_P0 65536
IX SYS_P43000 65536
IX SYS_P43001 65536
IX SYS_P43002 65536
IX SYS_P43003 65536

10 rows selected.

SQL> alter index IX modify partition &&d unusable;

Index altered.

SQL> select segment_name, partition_name, bytes from user_segments;

SEGMENT_NAME PARTITION_NAME BYTES
---------------------------------------- ------------------------------ ----------
T SYS_P42997 8388608
T SYS_P42998 8388608
T SYS_P42999 8388608
T ARCH_P0 8388608
T SYS_P42996 8388608
IX ARCH_P0 65536
IX SYS_P43000 65536
IX SYS_P43001 65536
IX SYS_P43002 65536
IX SYS_P43003 65536

10 rows selected.

Now before people criticise that I didn’t show version and platform, remember that in logging an SR, these things are provided as the SR is logged.  In this case, the platform is AIX and the version is 11.2.0.2.  I also posted a case into the SR showing the under 11.2.0.3 on Linux, the segment is indeed correctly dropped when the index partition is set to unusable.

Its a trivial test case, and my inquiry was simple – is it platform or version or both that is the issue.

But this post is about listening…

First response to the SR:

Your problem is this: "Unusable Index Segment Still Exists in DBA_SEGMENTS for Alter Table Move"

Well…I dont think so. Probably because the test case makes no mention of alter-table-move. 

I point this out via an SR update.

Second response to the SR:

You can drop the partition to reclaim free space

For starters, the ambiguity is risky advice.  Do they mean drop the table partition or the index partition ?  Might my next SR be "how can I recover the data I just dropped ?"

Secondly, its not addressing the original request.

So I’m more than happy to accept that an MOS outage happened….these things do in the IT world.  We try to avoid them, but they happen.

But its sad when the NON-technical components of IT, that of simple good listening skills also suffer regular "outages".