In the afternoon of November 3 Oracle guru Tom Kyte will talk about “11 Things about 11g” at the Logica office in Amstelveen (near Amsterdam). I have attended this presentation at ODTUG this year and I assure you it’s worthwhile! After his presentation Tom will also do an AskTom Live session. So a great opportunity [...]

Popularity: 169 points

APEX is famous for it’s useful applications, so when the idea poped into my head that I would like to track my personal contracts, APEX was first in line to develop such an application.
The purpose of this app is to be able to track longrunning contracts with their renewalperiods. One month before you should end [...]

Popularity: 217 points

An eyeopener for me when constructing sql queries in a complex environment was the use of analytic functions. As appose to aggregated functions, you can use more analytic functions on one table instead of constructing multiple inline views to accomplish “the same”.
For those of you who like to see examples:
select job_id
, sum(salary)
from employees
group by job_id;
has [...]

Popularity: 251 points

This was my second time at the Miracle Conference at Lalandia in Denmark.
Again it was “power-pact”.
Mogens Norgaard explained that a lot of conferences have about 80% information and only 20% networking possibilities. This conference is different. Mogens managed to increase the 20% networking up to 80% while still remaining 80% of usefull information. The way [...]

Popularity: 301 points

In Oracle 11g you now can modify gather_database_stats default values with set_global_prefs, set_database_prefs, set_schema_prefs, set_table_prefs in the dbms_stats package.
An even greater new feature is that in Oracle Database 11g, you can separate statistic gathering from statistic publishing.
This allows you to control when new statistics are published. Everybody must have run in to this once, [...]

Popularity: 230 points

In Oracle 11g you can enable the built-in Password Complexity Checker function.
Background info can be found in the Database Security Guide.
The Oracle Database provides a sample password verification function in the PL/SQL script UTLPWDMG.SQL
(located in ORACLE_BASE/ORACLE_HOME/RDBMS/ADMIN) that, when enabled, checks whether users are correctly creating or modifying their passwords. The UTLPWDMG.SQL script provides two password [...]

Popularity: 381 points

DBA’s can finally use the Automatic Interval Partitioning option.
Test steps following the Oracle By Example (OBE) scripts available at
http://otnbeta.us.oracle.com/db11gr1/trng/obes/index.htm:
– create interval partitioned table
create table newsales
( prod_id number(6) not null
, cust_id number not null
, time_id date not null
, channel_id char(1) not null
, promo_id number(6) not null
, quantity_sold number(3) not null
, amount_sold number(10,2) not null
)
partition by range [...]

Popularity: 233 points

The new 11g SQL plan management feature enables the optimizer to maintain a history of
execution plans for a SQL statement. Using the execution plan history the optimizer is
able to detect a new plan representing a plan change for a SQL statement. When the
optimizer detects a new plan, it stores the new plan and marks it [...]

Popularity: 211 points

The 11g Oracle DBA can now apply an invisible performance patch (an Invisible Index)
on a non-performing query on a production system, without any impact on other queries.
Invisible indexes are not considered by the optimizer. These indexes are only used with a hint.
Here is a demo.
SQL> conn scott/tiger
Connected.
SQL> select * from cat;
TABLE_NAME [...]

Popularity: 181 points

Flashback Data Archive (track all changes to a record over time).
For flashback data archive you need a tablespace first.
In this demo I have created a tablespace named testarea1
Now I can create a Flashback Archive with:
CREATE FLASHBACK ARCHIVE DEFAULT fla1 TABLESPACE testarea1
QUOTA 10G RETENTION 1 YEAR;
Flashback archive created.
You can Remove the Flashback Archive as well with:
DROP [...]

Popularity: 281 points