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