Mar 172014
 
create table from_12c_onwards (
  you_can_have_your_pk number 
    generated always as identity,
  so_you_dont_have_to_type_it varchar2(10)
);

insert into from_12c_onwards (
  so_you_dont_have_to_type_it
) values (
  'yes'
);

insert into from_12c_onwards (
  so_you_dont_have_to_type_it
) values (
  'Yes'
);

insert into from_12c_onwards (
  so_you_dont_have_to_type_it
) values (
  'YES!'
);

select * from from_12c_onwards;

YOU_CAN_HAVE_YOUR_PK SO_YOU_DON
-------------------- ----------
                   1 yes
                   2 Yes
                   3 YES!

If you’d like more details, there’s a nice article on oracle-base discussing how identity columns work. If you prefer, you can assign a sequence to be a column default instead, as discussed here.

Feb 172014
 

Two tables that look the same, but for some reason I can insert my pounds (£) into the first one, but not the second:

create table i_can_insert (
  into_this varchar2(1)
);

insert into i_can_insert (into_this)
values ('£');

-- I execute some code here

create table but_i_cant_insert (
  into_this varchar2(1)
);

insert into but_i_cant_insert (into_this)
values ('£');

ORA-12899: value too large for column

Why? Put your answers in the comments!

Oct 282013
 

If you want the colums in your tables to appear in a different order, you can clone the table, drop the old and rename the new:

-- hard way
create table i_want_to_reorder (
  is_confusing              integer,
  because_the_current_order integer
);

create table reordered_columns as 
  select because_the_current_order,
         is_confusing
  from   i_want_to_reorder;

drop table i_want_to_reorder purge;

rename reordered_columns to i_want_to_reorder;

drop table i_want_to_reorder purge;

Alternatively, you can do this much quicker (especially if the table is “large”) by just creating a view with the new column order:

-- easy way
create table i_want_to_reorder (
  is_confusing              integer,
  because_the_current_order integer
);

rename i_want_to_reorder 
to i_want_to_reorder_tab;

create view i_want_to_reorder as
  select because_the_current_order,
         is_confusing
  from   i_want_to_reorder_tab;

drop view i_want_to_reorder;
drop table i_want_to_reorder_tab purge;

If your application is setup nicely and has synonyms to your table (rather than connecting directly) then you just need to repoint the synonyms (rather than renaming). Of course, this always begs the question why do you need to re-order the column list – I can’t think of any (good) reasons.

UPDATE As pointed out by Matthias in the comments, there’s the 12c approach: make columns invisiable and visible again:

-- 12c way
create table i_want_to_reorder (
  is_confusing              integer,
  because_the_current_order integer
); 

alter table i_want_to_reorder 
modify ( is_confusing invisible );

alter table i_want_to_reorder 
modify ( is_confusing visible );

desc i_want_to_reorder

BECAUSE_THE_CURRENT_ORDER NUMBER(38)
IS_CONFUSING	          NUMBER(38)
Sep 162013
 
create cluster your_data_together (
  for_super_efficient_access integer
);

create table when_i_am_frequently_queried (
  and_performance_matters    varchar2(1) 
                               default 'Y',
  for_super_efficient_access integer
)
cluster your_data_together (
  for_super_efficient_access
);

create table and_joined_to_me (
  clustering_places_us_in    integer,
  the_same_physical_location integer,
  for_super_efficient_access integer
)
cluster your_data_together (
  for_super_efficient_access
);
Not to be confused with RAC (real application clusters), table clusters can 
be used to locate records with a common key into one place. This saves some
space as the cluster key is only stored once for all the tables in it. The 
bigger advantage is that all the tables in a cluster are physically located 
in the same place, so joins between these tables require fewer I/O operations 
than the equivalent heap tables.

If you’d like to know more, then have a look at the documentation or Iggy Fernandez has an example here (scroll to the “Appendix” section)

Jun 242013
 
create table roll_your_own_time_types (
  where_everything_is_a varchar2(5) 
);

alter table roll_your_own_time_types 
add constraint so_we_are_sure_it_is_a_time 
check (regexp_like(
  where_everything_is_a, 
  '[0-2][0-9]:[0-5][0-9]')
);

alter table roll_your_own_time_types add (
  when_we_should_have_an 
    interval day(0) to second
);

In addition to saving you from having to write validaiton code, using an interval implements
all the arthimetic operations for you as well, so you can easily find the difference between
two times. This kind of datatype crime is more common with dates, but I suspect that's
because it's rare times are stored with no date component.

As usual, there’s an oracle-base article detailing how to use interval datatypes if you want a more thorough explanation.

May 312013
 
create table baby as 
  select /*+ parallel (9) */months
  from   mothers;

ORA-12827: insufficient parallel query slaves 
(requested 9, available 1, parallel_min_percent 9)
12827. 00000 -  
"insufficient parallel query slaves available"

One for all the project managers to take note of - there's some tasks which 
simply can't be done any faster by throwing more people/resources at them ;)
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.
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.

Mar 252013
 
create table one_true_lookup_table (
  one_table_to_find_them integer primary key,
  one_table_to_bring_them_all varchar2(1000)
) tablespace and_in_the_darkness_bind_them;

Just like the one ring, the one true lookup table (OTLT) is a concept that should be thrown into Mount Doom

For discussions as to why the OTLT is a bad idea, have a look here, here and here.

Feb 112013
 
create table physically_ordered_data (
  can_vastly_reduce_your_io   integer,
  and_reduce_buffer_cache_use integer,
  when_querying               varchar2(1) 
                              default 'Y',
  constraint on_the_leading_columns_of_the
  primary key (
    can_vastly_reduce_your_io,
    and_reduce_buffer_cache_use
  ),
  just_define_your_table_as   integer
) organization index;
IOTs are woefully under-used in Oracle. While there are some provisos, 
the benefits can be great when fetching several rows from a table via the first columns in the primary key

Martin Widlake has an excellent series on IOTs explaining how they work and their benefits and caveats. I thoroughly recommend you read it.