view counter
Home

PL/SQL Feed

Oracle PL/SQL (Procedural Language/Structured Query Language) resources, news, and support articles.

More Seasonal SQL

select 'Enjoy your ' ||
substr(tzname, instr(tzname, '/')+1) ||
' ' || substr(text, 8, 3) || 's!'
as "Season's Greetings"
from v$timezone_names, all_source
where tzabbrev = 'EASST'
and tzname like

Mar 29, 2013 2:00am

SQL*Plus tips. #1

If you are using SQL*Plus, you are likely to use the input parameters. And if you omit one of them, SQL*Plus will show prompt for it, like this:

Mar 28, 2013 4:58pm

Lord of the Lookup Tables

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

Mar 25, 2013 2:00am

Defining "the same results" for "unnecessary code" quizzes

The discussions around choice 4 of the 19 March quiz, in particularly those titled "Choice 4's commit is unnecessary" and 'The "last_name" column is unnecessary in Choice 4', made me realize that I need to offer a more explicit definition of what it means for two blogs to have the "same result."

Mar 24, 2013 8:27am

Book Review: OCA Oracle Database 11g: Database Administration I: A Real-World Certification Guide by Steve Ries

The book OCA Oracle Database 11g: Database Administration I: A Real-World Certification Guide by Packt publishing has been published. The book has been authored by Steve Ries and I was the technical reviewer of the book. It was pleasure reviewing the book as the style of presentation, topics coverage and authenticity of content was pretty impressive.

Mar 23, 2013 5:24am

Real Progress (of SQL statements)

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';

Mar 22, 2013 2:00am

Protect your database from renegade query writers

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

Mar 18, 2013 2:00am

Object Table Function View

Somebody was trying to create a striped view based on a table’s start_date and end_date temporal columns. They asked for some help, so here are the steps.

Basically, you create a user-defined data type, or structure:

Mar 17, 2013 9:59pm

Example of controlling “direct path reads” decision through SQL profile hints (index_stats/table_stats)

Previously i showed not obvious example with hint “INDEX_STATS(“OWNER”.”TABLE_NAME”, “INDEX_NAME”, scale, blocks=X, rows=Y)“. Strictly speaking i don’t know how exactly cbo calculates number of index leaf blocks in that case: in those examples they was 1981 for “blocks=1, rows=50″ and 49525 for “blocks=5, rows=10″.
But i know that with “INDEX_STATS(“OWNER”.”TABLE_NAME”, “INDEX_NAME”, scale, blocks=X)” i can set exact blocks number.
Also those test-cases didn’t show when occurs decision changing. So todays my test will show it.

Mar 17, 2013 4:33pm
view counter