We currently are working on a bug with Oracle Support with dbms_stats in 188.8.131.52.
That may be the subject of a latter post, but in a nutshell, here’s the issue
SQL> exec dbms_stats.gather_table_stats('MY_SCHEMA', 'MY_SCHEMA');
BEGIN dbms_stats.gather_table_stats('MY_SCHEMA', 'MY_SCHEMA'); END;
ERROR at line 1:
ORA-21700: object does not exist or is marked for delete
Now obviously that’s not meant to be happening, and it pertains to incremental stats on a database (more...)
Every …. single …. time….
Why does index monitoring make me scratch my head and charge off to google so many times…Well, I’m over it, so time to put it on my blog (even though its already on many other places) so I do not get caught out anymore :-)
It always starts like this:
- Turn on index monitoring on some indexes
- Come back later, and get ready to check on my results
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...)