2

When I create a GLOBAL TEMPORARY TABLE in Oracle 11g (11.2.0.1.0), and then add a new column to it, Oracle does not update the LAST_DDL_TIME column of the system catalog views (e.g. DBA_OBJECTS):

create global temporary table test# (foo varchar2(3));
select last_ddl_time from dba_objects where object_name='TEST#';
-- Shows created time

-- Wait a minute 

alter table test# add (bar varchar(3));
select last_ddl_time from dba_objects where object_name='TEST#';
-- Shows NO UPDATE

-- Wait a minute

alter table test# drop (bar);
select last_ddl_time from dba_objects where object_name='TEST#';
-- Shows time when column was dropped

However, when I execute the same sequence of SQL statements in Oracle 12c (12.1.0.2.0), then LAST_DDL_TIME does reflect newly-added columns.

create global temporary table test# (foo varchar2(3));
select last_ddl_time from dba_objects where object_name='TEST#';
-- Shows created time

-- Wait a minute 

alter table test# add (bar varchar(3));
select last_ddl_time from dba_objects where object_name='TEST#';
-- Shows time when column was added

-- Wait a minute

alter table test# drop (bar);
select last_ddl_time from dba_objects where object_name='TEST#';
-- Shows time when column was dropped

This behavior appears to be very specific to adding columns to GLOBAL TEMPORARY TABLES, and to Oracle 11g; renaming or dropping columns does affect LAST_DDL_TIME as expected.

Is this a known Oracle 11g bug?! If so, is there an official Oracle patch update for it?

Dan Lenski
  • 76,929
  • 13
  • 76
  • 124
  • Do you have access to metalink? You shouldn't have to wait a minute - the system views will be updated as part of the last DDL commit. – Ben Nov 07 '16 at 17:32
  • 4
    You're using the very first, unpatched release of 11g. There have been several patchsets released. I'd generally start by installing those to get yourself to 11.2.0.4 before you start looking for one-off patches. – Justin Cave Nov 07 '16 at 17:42
  • Thanks @JustinCave, this is a very good point. I am not the administrator for this database and would have to convince the DBAs to upgrade it. If I can point to a specific patch release in which it's fixed, it would help my case a lot. Unfortunately, I've been able to find it. :( – Dan Lenski Nov 07 '16 at 18:18
  • 1
    Well, were it me, I'd probably emphasize the multiple years of security fixes that are in the accumulated patchsets over something seemingly minor like this. Assuming you have access to Oracle Support, you might be able to find a bug but you may need to file a ticket to find out if there is an existing bug. Of course, Oracle Support is undoubtedly going to (repeatedly) suggest that you apply the patchsets first to see if that fixes the problem. :-) – Justin Cave Nov 07 '16 at 21:20
  • 1
    Works correctly on 11.2.0.4. – DCookie Nov 07 '16 at 21:56
  • Thanks, @JustinCave and @DCookie… this is really helpful! (If I had my way, we'd be using Postgresql instead, but that's a whole 'nother can of worms :-P) – Dan Lenski Nov 07 '16 at 22:49

0 Answers0