RETURNING BULK COLLECT and database links

Looks like the nice PL/SQL facility for returning a set of updated rows is restricted when it comes to database links

(This tested on 12.1.0.1)

SQL> declare
  2    type int_list is table of number(12) index by pls_integer;
  3    l_results int_list;
  4
  5  begin
  6    update MY_TABLE b
  7    set b.my_col = ( select max(last_ddl_time) from user_objects@dblink where object_id = b.key_col)
  8    where b.my_col is null
  9    returning b. (more...)

GROUP BY – wrong results in 12.1.0.2

I encountered this during some testing – the optimizer can be a little overzealous when it encounters nested group-by’s

SQL> create table T ( ts number not null, c char(10));
 
Table created.
 
SQL> insert into T
  2  select 100+dbms_random.value(1,50),'x'
  3  from ( select 1 from dual  connect by level < 1000 ),
  4       ( select 1 from dual  connect by level < 1000 )
  5  /
 
998001 rows created.
 
SQL> commit;
 
Commit complete.
 
SQL>
 (more...)

Inappropriate behaviour

You pick up little funny things in the day to day with Oracle.

Like this one when you try to drop a tablespace with a queue table in it:

drop tablespace MY_TSPACE including contents
*
ERROR at line 1:
ORA-00604: error occurred at recursive SQL level 1
ORA-24005: Inappropriate utilities used to perform DDL on AQ table
SCOTT.QUEUE_TABLE

You would think that since you’ve asked to drop everything, that well…everything could be dropped, but (more...)

Data Pump import makes me crabby

I’m sitting here watching the import of a moderately sized database via transportable tablespaces.  You know…the thing you use when a full export / import would be too slow, and this is meant to be … well…fast.

And fast it is.. until it reaches the following step:

Processing object type TRANSPORTABLE_EXPORT/TABLE_STATISTICS

Now understandably, there’s plenty of stats to import, so its fair that it should take a little while.  But a quick look (more...)

Slow external table access

We had an interesting issue on 12.1.0.1, where users were reporting very slow performance on queries to external tables. When I tried to replicate the problem, everything seemed just fine, so I initially reported back the familiar "Well, it works on my PC" :-) [Just kidding]

Anyway, connecting by proxy to one of their accounts, did reveal the error, which suggested something to do with privileges.  A sql trace revealed that (more...)

ORA-4068 and CONSTANT keyword…good and bad

Anyone that has ever coded PLSQL will be familiar with the error ORA-4068, where you had some state persisted in a session due to a package variable, and then when you change the package, the state is cleared along with an ORA-4068.  Here’s a quick example:

 

Session 1 

SQL> create or replace
  2  package PKG is
  3    procedure P;
  4  end;
  5  /

Package created.

SQL> create or replace
  2  package body  (more...)

truncated ddl in 12c (and 11.2.0.4)

Just curious if anyone else is seeing this behaviour..

SQL> set long 500000
SQL> @pt "select * from v$sql where sql_id = '1km492z723vpu'"
 
SQL_TEXT                      : alter table scott.emp
SQL_FULLTEXT                  : alter table scott.emp
SQL_ID                        : 1km492z723vpu
...

Suffice to say, there’s a lot more to that SQL statement, but its lost in v$sql. Once complete, it does appear in full in v$sqlstats, but its a pain when you cant see the whole statement..


AUSOUG conference Perth

The annual two day AUSOUG conference in Perth is well underway, and so far, its been a wonderfully successful event. In particular, we Australians are notorious for not really getting into the networking thing, so conferences often have a lot of ‘awkward silences’ when sessions are not on, and we are meant to be (god forbid) *talking* to each other :-)

But this year has been different. Lots of banter, lots of discussion. Also, the (more...)

A simple 12c query with a cool result …

Its not immediately obvious the significance of this query…but trust me…you’ll love it :-)

SQL> select table_name, column_name from dba_tab_cols
  2  where column_name like '%_VC' escape ''
  3  and owner = 'SYS'
  4  order by 1,2;

TABLE_NAME                     COLUMN_NAME
------------------------------ ----------------------
ALL_CONSTRAINTS                SEARCH_CONDITION_VC
ALL_VIEWS                      TEXT_VC
CDB_CONSTRAINTS                SEARCH_CONDITION_VC
CDB_VIEWS                      TEXT_VC
DBA_CONSTRAINTS                SEARCH_CONDITION_VC
DBA_VIEWS                      TEXT_VC
INT$DBA_CONSTRAINTS            SEARCH_CONDITION_VC
INT$DBA_VIEWS                  TEXT_VC
INT$INT$DBA_CONSTRAINTS        SEARCH_CONDITION_VC
USER_CONSTRAINTS               SEARCH_CONDITION_VC
USER_VIEWS                     TEXT_VC

Openworld

Well, the annual spectacle of enormous proportions has come to a conclusion again. And thats probably the first reason I’d recommend OpenWorld to anyone who works with Oracle who has never been to it. It’s a jaw dropping moment just to see the scale of the event, and how impressively its organised in terms of facilities, lunches, registration and the like.

But onto the technical elements, here’s my impressions of this years conference:

1) Big (more...)