Hint for desktop-Oracle developers - Insert into table with NOLOGGING

Here's the trick.
I've got a table with 2,2M rows for performance testing purposes, but now I wanted to export the data to a new, partitioned table. So logical would be just to INSERT INTO, right?

Well in my case it wasn't, because the redo that would have been created as a result, would have been too much for my PC (maybe it wouldn't have been, but I didn't want to waste my time). So I searched a bit because I remembered a way to make a "direct-path insert" with no redo log.

The answer was, as always, in the fine documentation of Oracle. LOGGING is the default, as in production you surely wouldn't want to lose any data, but in development there is no need for that kind of strickness.

SQL> alter table partitionedTable nologging;

Table altered.

SQL> insert into partitionedTable select * from noPartitionsWhatsoever nologging;

2198152 rows created.

SQL> alter table partitionedTable logging;

Table altered.

Daily Errorcode

From today on, I'm starting a new tradition. Every time I get an error, I'll post it here and also try to include the solution. Today's error was the "double-Duh type". Read on and you'll get it.


I wanted to do a little backup of my Win desktop's 10gR2 and it gave me the ORA-19809.
Searched a bit and Paul M. answered in the OTN forums:

Flashback Recovery and RMAN are independent from each other, but if you activate Flashback Recovery you'll need more space in Flash Recovery area, because of Flashback logs.

To avoid ORA-19809 you have to periodically free space in Flash Recovery area, and normally this is done by using RMAN :

$ oerr ora 19809
19809, 00000, "limit exceeded for recovery files"
//*Cause: The limit for recovery files specified by the
//        DB_RECOVERY_FILE_DEST_SIZE was exceeded.
//*Action:The error is accompanied by 19804. See message 19804 for further
//        details.
$ oerr ora 19804
19804, 00000, "cannot reclaim %s bytes disk space from %s limit"
// *Cause: Oracle cannot reclaim disk space of specified bytes from the
//         DB_RECOVERY_FILE_DEST_SIZE limit.
// *Action: There are five possible solutions:
//          1) Take frequent backup of recovery area using RMAN.
//          2) Consider changing RMAN retention policy.
//          3) Consider changing RMAN archivelog deletion policy.
//          4) Add disk space and increase DB_RECOVERY_FILE_DEST_SIZE.
//          5) Delete files from recovery area using RMAN.

So now I changed my DB_RECOVERY_FILE_DEST_SIZE from 2G to 4G just to be sure..
Cause I had the space and had no wish to change the retention policy. Tell you the truth, I don't even know what is the default retention policy for Enterprise Edition.

> shutdown

At this point I slapped my forehead and went Duh! - forgot the "immediate".
So I went for a walk around the building..

I had a revelation during the walk - Windows has the Service Manager where I can shut down services explicitly, so I did it. And voila - the server was successfully restarted as it came up automatically.

So now, with 4G of backup space, I started making a full backup - with Enterprise Manager again. Incremental level 0 means it is the basis for level 1 increments - level 0 being the first full backup and Level 1 being the "delta data" from the last Level 0.

RMAN> backup incremental level 0 cumulative device type disk tag '%TAG' database include current controlfile;


Finally if all was successfully completed, I remembered that my db was about 2,3GB so there was no possible way to fit the backup into 2G-s.
Time to slap my forehead again?