SQL is a case insensitive language. That means, you can write any SQL statement in uppercase or lowercase.

SQL> SELECT DUMMY FROM DUAL;
D
-
X

SQL> select dummy from dual;
D
-
X


Some formatters like Toad and some courseware like Skillsoft E-Learning advise to use UPPERCASE for commands and lowercase for tables and columns.


/* Formatted on 2008/09/19 10:00
(Formatter Plus v4.8.8) */
SELECT dummy
FROM DUAL;


Well, Toad decided to put DUAL in uppercase. Anyway, this is no authority, define your own guideline and stick to it. If you use TOAD a lot, it is maybe OK to use lowercase for non-keywords.

The column dummy is actually the uppercase “DUMMY” column of “DUAL”. Putting double quotes around a column of table makes it case sensitive, so “DUMMY” is not “dummy”.

SQL> select "dummy" from "dual";
select "dummy" from "dual"
*
Error at line 1
ORA-00942: table or view does not exist

SQL> SELECT "DUMMY" FROM "DUAL";
D
-
X


OK, something very bad about Toad formatter is (at least the version I am using) that it considers unquoted password to be case insensitive. Which is wrong. The password, in 11g, is case sensitive, even when not in double quotes. Therefore, be sure to put the right case when creating scripts and do not format your CREATE USER statements with Toad or use double quotes for disambiguation!

SQL> ALTER USER scott IDENTIFIED BY BIG;
User altered.
SQL> connect scott/BIG
Connected.
SQL> /* Formatted on 2008/09/19 10:17 (Formatter Plus v4.8.8) */
SQL> ALTER USER scott IDENTIFIED BY big;
SQL> connect scott/BIG
ERROR:
ORA-01017: invalid username/password; logon denied
Warning: You are no longer connected to ORACLE.


Today I opened two SR about flashback archive in 11g. In one of them, I complained that user SCOTT was not allowed to create a flashback archive. In the doc that I downloaded a few weeks ago I read :
Prerequisites
You must have the FLASHBACK ARCHIVE ADMINISTER system privilege to create a flashback data archive. This privilege can be granted only by a user with DBA privileges. In addition, you must have the CREATE TABLESPACE system privilege to create a flashback data archive, as well as sufficient quota on the tablespace in which the historical information will reside.

So as I was getting an ORA-55611, I opened a SR. The support engineer pointed me to the online documentation where I was astonished to read :
Prerequisites
You must have the FLASHBACK ARCHIVE ADMINISTER system privilege to create a flashback data archive. In addition, you must have the CREATE TABLESPACE system privilege to create a flashback data archive, as well as sufficient quota on the tablespace in which the historical information will reside. To designate a flashback data archive as the system default flashback data archive, you must be logged in as SYSDBA.

Well, Read The Fine Online Manual !!!

The second tar is related to long retention (about the age of the earth)

SQL> alter flashback archive fba01
modify retention 4106694757 year;

SQL> select retention_in_days
from DBA_FLASHBACK_ARCHIVE;
RETENTION_IN_DAYS
-----------------
1


I am always delighted to read the top features by Arup Nanda.

He started his 11g series : Oracle Database 11g: The Top Features for DBAs and Developers

There are many partitioning enhancements. The most exciting feature for me is the INTERVAL partitioning. A huge cause of downtime and waste of storage is the range partitioning. In 10g and before, a partitioning by dates required that the partition are defined before values are inserted.

Now we have automatic partition creation 😀


create table t(d date)
partition by range(d)
interval(interval '1' month)
(partition p1 values less than (date '0001-01-01'));


One partition must be created manually, here the partition will contain all dates from 1-JAN-4712BC to 31-DEC-0000 (which is not a legal date by the way)

There is also new syntax to query the partition
SQL> insert into t values (date '2000-01-10');

SQL> select * from t partition for (date '2000-01-01');
D
-------------------
10.01.2000 00:00:00
20.01.2000 00:00:00


Note the syntax can be used in any form of partitioning. Here in a list-list composite

SQL> create table t(x number, y number)
partition by list(x)
subpartition by list(y)
subpartition template (
subpartition sp1 values(1),
subpartition sp2 values(2))
(partition values(1), partition values(2));

SQL> select * from t subpartition for (1,2);
X Y
---------- ----------
1 2

Ok, one more feature Arup introduced is the REF partitioning, where you have a schema with both the parent and child tables partitioned, and you want to partition on a column of the parent table that is not in the child table (as you had bitmap join indexes, you have now ref partitions). Check it on his site.

Finally Arup explained SYSTEM partitioning, which is not inconceivable, but will hardly be used.

Imagine you have a table containing just one single LOB column, and a LOB cannot be used as a partition key.

SQL> create table t(x clob)
partition by system (
partition p1,
partition p2,
partition p3,
partition p4);

Table created.


So far this seems fine. So what the problem? You cannot insert in that table!
SQL> insert into t values(1);
insert into t values(1)
*
ERROR at line 1:
ORA-14701: partition-extended name or bind variable
must be used for DMLs on tables partitioned by the
System method


so you must define in which partition you want to add data. For example round robin. Or random. Whatever.


SQL> insert into t partition (P1) values ('x');

1 row created.


If you want to use bind variable, you can use dataobj_to_partition

SQL> select object_id
from user_objects
where object_name='T'
and subobject_name is not null;
OBJECT_ID
----------
55852
55853
55854
55855

1 row created.


Actually, SYSTEM partitioning is misleading, YOU are responsible for choosing the partition in which you want to insert, not the system

One of the problem with flashback queries in 10g and before is that you never know if it will works, especially you cannot expect to have flashback queries working for very old tables.

Let’s imagine you want to export your CUSTOMER as of 30/6/2007. No chance in 10g…

Well, with 11g, you can create a flashback archive, and it will save all change until end of retention (many years if you want).

Here it is :
SQL> connect / as sysdba
Connected.
SQL> create tablespace s;

Tablespace dropped.


note that a month is 30 days. If you try to create a flashback archive in a non-empty tablespace you may get
ORA-55603: Invalid Flashback Archive command
which is not a very helpful message