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 184.108.40.206)
2 type int_list is table of number(12) index by pls_integer;
3 l_results int_list;
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...)
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));
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 )
998001 rows created.
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
You would think that since you’ve asked to drop everything, that well…everything could be dropped, but (more...)
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...)
We had an interesting issue on 220.127.116.11, 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...)
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:
SQL> create or replace
2 package PKG is
3 procedure P;
SQL> create or replace
2 package body (more...)
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..
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...)
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;
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...)