Where did all the decimals go?

 Database concepts  8 Responses »
May 132013
  
create table fractional_digits_disappear (
  without_warning_when_put_in_an integer
);

insert into fractional_digits_disappear (
  without_warning_when_put_in_an
) values (
  3.141592
);

select *
from    fractional_digits_disappear;

WITHOUT_WARNING_WHEN_PUT_IN_AN
------------------------------
                             3

The fractional seconds of timestamps are also silently lost when these are inserted into dates.
Not raising an error for this could be confusing, and potentially an "inexplicable" bug waiting to happen.

The NON-EXISTENT Edition

 Database concepts  2 Responses »
May 062013
  
drop procedure is_not_really_gone_but;

select object_name || ' ' || object_type 
                 as "Continues on"
from   user_objects_ae
where  object_name = 
           'IS_NOT_REALLY_GONE_BUT';

Continues on
-----------------------------------
IS_NOT_REALLY_GONE_BUT NON-EXISTENT

create table is_not_really_gone_but ( 
  until_replaced            number,
  by_a_non_editioned_object varchar2(1) 
                            default 'Y',
  which_we_then_drop        varchar2(1)
);

drop table is_not_really_gone_but;

select object_name || ' ' || object_type 
from   user_objects_ae
where  object_name = 
           'IS_NOT_REALLY_GONE_BUT';

no rows selected
If you often create test procedures/fucntions (like me), you can find the _ae dictionary views 
quickly fill with these "NON-EXISTENT" objects. This can be confusing once you start working with 
editions. Hoepfully Oracle will introduce some form of purge option when dropping PL/SQL to provide 
a nicer way to get rid these

For a full script, use this. (NB – requires 11gR2 to see the effect)

Sequence out of fetch? More problems with committing in loops

 Bad practices, Database concepts  No Responses »
Apr 292013
  
create sequence ora 
start  with -1002 minvalue -1002;

begin
  for commits in (
    select loops 
    from   queries
    where  we = 'specify'
    for    update
  ) loop
    dbms_output.put_line(
      'Causes an ' || sqlerrm(ora.nextval));
    commit;
  end loop;
end;
/

Error report:
ORA-01002: fetch out of sequence
Issuing a commit after opening a cursor with the "for update" releases the row locks
and invalidates the cursor, meaning you can no longer fetch data out of it!

Click here for a complete script to see this in action.

A Private Place to Manage Working Data

 Database concepts  No Responses »
Apr 222013
  
create global temporary table  
      contains_data_that_is (
  only_visible_to_one_session 
    varchar2(1)  default 'Y',
  and_by 
    varchar2(50) default 'removed on commit',
  unless_you_state 
    integer
) on commit preserve rows;

I've always found the "global" naming of temporary tables slightly confusing, 
as this implies the data is visible across all sessions. 
I guess it could be to distinguish these from SQL server temporary tables, 
which can be created on the fly in a single session.
If you know better, please enlighten me in the comments!

For a demo of temporary tables in action, use this.

One Database, One Package, Multiple Codepaths

 Database concepts  No Responses »
Apr 192013
  
create edition allows_one_plsql_object 
as child of itself_with_differing_code;

comment on edition allows_one_plsql_object is 
'so you can release changes with no dowmtime!';

Releasing changes to server side PL/SQL in 24x7, always up databases can be a big challenge. Ensuring the release 
worked correctly can be impossible without renaming the objects in the new version (or other more complicated 
solutions), making this one of the few valid arguments for not using stored procedure in your database. Editioning 
resolves this by allowing you to release, test and (possibly) rollback changes without affecting the live version! :)

As always, you can find a good write up of how edition based redefintion works with examples on oracle-base.

What has normalization ever done for us?

 Database concepts  2 Responses »
Apr 082013
  
select benefit
from   normalisation
where  benefit not in (
    'Preventing update anomalies',
    'Ensuring uniqueness of rows',
    'Supporting (unknown) ad-hoc SQL queries',
    'Allowing extensions to the data model',
    'Representing entity relations',
    'Improving consistency by avoiding redundancy'
);

no rows selected

Apart from all that, there's no benefits right?! ;) I'm sure there's more than those listed; 
if you've got any to add just put them in the comments and I'll update the post

Real Progress (of SQL statements)

 Database concepts  No Responses »
Mar 222013
  
with the_sql_monitor as (
  select a_great_way_to_see,
         real_time_execution_plans
  from   your_painfully_slow_queries
  where  tuning_pack_license = 'Y'
  and    the_boss = 'screaming for the result'
)
select * 
from   the_sql_monitor
where  real_time_execution_plans = 
           'taking !$@# forever to run';
  
This really is an outstanding feature to help track the progress of that 4 hour SQL query,
showing which part of the execution plan is chewing up all the time so you know where to direct your tuning efforts

For a great description on using the SQL monitor, have a read of this oracle base article.

Protect your database from renegade query writers

 Database concepts  No Responses »
Mar 182013
  
create profile highly_restricted limit 
  cpu_per_session 1
  sessions_per_user 1
  logical_reads_per_session 10
  logical_reads_per_call 10;

alter user cowboy_developer 
profile highly_restricted;

Profiles have been superceded by the DB Resource Manager for limiting resource usage, 
however profiles are still very useful for setting password timeouts, locks, etc.

To try this out yourself, use this script.

Yes, we really are different SQL statements (as far as Oracle’s concerned)

 Database concepts  No Responses »
Mar 112013
  
select things_that_look_the_same_but
from   the_optimizers_point_of_view,
       are_different
where  we_clog_up_the_library_cache = 'Y'
and    cause_extra_hard_parses = 'Y';

SELECT things_that_look_the_same_but
FROM   the_optimizers_point_of_view,
       are_different
WHERE  we_clog_up_the_library_cache = 'Y'
AND    cause_extra_hard_parses = 'Y';

SELECT THINGS_THAT_LOOK_THE_SAME_BUT
FROM   THE_OPTIMIZERS_POINT_OF_VIEW,
       ARE_DIFFERENT
WHERE  WE_CLOG_UP_THE_LIBRARY_CACHE = 'Y'
AND    CAUSE_EXTRA_HARD_PARSES = 'Y';
A good argument for standardising SQL formatting is that different people writing the same SQL statement
will all write it the same way, reducing the chance of this issue occurring

For proof of this, use this script.

(Not) Getting the first value in an ordered set

 Bad practices, Database concepts  No Responses »
Mar 042013
  
select a_random_row
from   this_table
where  rownum = 1
and    i_wanted_the_first = 'Y'
order  by according_to_this;
This issue is slightly more insidious than last week's, though based on a similar misunderstanding. 
This query can appear to be workingly correctly, until the physical ordering of data changes 
(e.g. when migrating to a new environment); at which point it becomes apparent that the order by is done after rownum

For the correct way to do top-n analysis in Oracle, see this.

 Older Entries