Learning is not a spectator sport

Complete listing of posts

  • v$object_usage catches me out every time 🙂

    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:… Read more

  • 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… Read 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… Read 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… Read 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… Read 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,… Read more

  • Analytics Demo Scripts

    Video 2: Ranking rows https://livesql.oracle.com/apex/livesql/s/ch1tefsz0mc75pk4ix2jbfja7 Video 3: More options for ranking rows https://livesql.oracle.com/apex/livesql/s/clebs8f5n4kr2b3vjvnvvgxkn Video 4: Using the Tabibitosan method for grouping sets of rows https://livesql.oracle.com/apex/livesql/s/cljrgh80cczwjtptlpk2e5081 Video 5: CUME_DIST, PERCENT_RANK and NTILE https://livesql.oracle.com/apex/livesql/s/clq6jmj1jzju4k23v3tnbzo8u Video 6: Dealing with NULLS https://livesql.oracle.com/apex/livesql/s/cokpdete3hiy3jo0yzoa4eerz Video 7: Analytics as predicates https://livesql.oracle.com/apex/livesql/s/cokv9220ffzt8iqs7udqsv5vm Video 8: The partition clause https://livesql.oracle.com/apex/livesql/file/content_ERYV6B909XK196IAKLBUQIJSN.html Video 9: Aggregation with partitions… Read 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… Read 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,… Read more

  • 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… Read 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… Read more

  • 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… Read more