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!
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!
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)
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.
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.
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 ;)
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.
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.
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
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.