Skip to Main Content
  • Questions
  • Can logging turned off in PL/SQL programs

Breadcrumb

Question and Answer

Tom Kyte

Thanks for the question, Sundara.

Asked: June 28, 2000 - 1:23 pm UTC

Last updated: May 19, 2006 - 12:03 pm UTC

Version: 8.1.6

Viewed 1000+ times

You Asked

Hi,

I have created few tables with nologging option, but when I do DML operations through a PL/SQL
procedure, it generates log. Is there anyway I can turnoff logging while
PL/SQL execution?

Thank you,

Murthy. S

and Tom said...

The NOLOGGING option on the CREATE table only affects the actual operation of the CREATE TABLE itself. All subsequent operations are logged as normal. The "nologging" option is used with statements like:

create table T nologging as select * from T2;


The initial population of T is done in an unrecoverable fashion. All subsequent operations are done in a manner that can be rolled back and recovered from. They have to be -- the assumption is that multiple people are accessing the table (need to be able to rollback and such) and that the instance might fail - making it so the table needs recovery (if the instance failed during the create table -- we recover simply by rolling back the insertions into the data dictionary).

There is an operation known as a direct path insert (done via the INSERT /*+ APPEND */ into ....) that may be used if you are doing a massive insert (its not for a single row insert). Beyond that -- all changes are logged.

Rating

  (5 ratings)

Is this answer out of date? If it is, please let us know via a Comment

Comments

tablespace vs INSERT /*+ (oracle 92)

Juan Carlos Reyes, August 27, 2002 - 10:35 am UTC

Hi tom
I do a big amount of insert to a table, only for an archive generation, if it fails it can be rerun, there is no need for logging, but it takes a lot of time.

In this insert what is better to use INSERT /*+
APPEND */ into ....)
or to set the tablespace to NOLOGGING

Which is the difference

Thank again you Tom

Tom Kyte
August 27, 2002 - 10:42 am UTC

If you do not care to be able to do media recovery on this object, alter the table to nologging (not the whole tablespace) and then do the insert append, then alter the object BACK and do a BACKUP of at least that tablespace.

A reader, August 27, 2002 - 11:10 am UTC

Thank you

don't want to get logged

John, January 12, 2003 - 10:19 am UTC

Hi Tom,

I am using an oracle 9.0.1 SE. I have background process which has following code:
loop
begin
delete from table_a where type='type_1';
insert into table_a
select * from table_a@remote where type='type_1';
commit;
end;
dbms_lock.sleep(10);
end loop;

I don't care the log of the table_a because I can get full copy from remote database at any time. Is it possible to un-log the change in the redo-log since it is big amount of the log.

Thanks.


Tom Kyte
January 12, 2003 - 11:44 am UTC

nope. You do want to get logged -- you just don't know it. corruption is a terrible thing.

Seems like a wasteful process here -- why not just do distributed queries for the things that need type_1 data in the first place. Every 10 seconds -- if that is generating lots of redo -- I cannot imagine why you would do it that way.

What about Materialized views?

Damon Jebb, May 15, 2003 - 4:37 am UTC

This may well explain why we are having problems, but I'm not sure. We have a large number of materialised views which were generating significant amounts of redo log when refreshing. Sometime ago we turned off the logging option on most of the MV's and thought we had solved the problem. Until a couple of days ago, when we found the database not started after the over-night backup because the disk space for redo logs had run out (a fairly common problem for us).

Looking at the times when the log switches occurred it is clear that it is when the database is running the MV refreshes as it is outside normal working hours.

Is it possible to prevent the MV refreshes from generating redo logs? It seems pointless generating redo on MV's, as all the supporting data to generate the views is available and logged elsewhere anyway.

Thanks

Damon

Tom Kyte
May 15, 2003 - 9:37 am UTC

so, you have not sized your system properly -- and you know it -- but you keep on living with it anyway.


full refreshes of MV's that are not in MV's groups will be done via truncate and INSERT /*+ APPEND */. If the MV's were set to nologging, they would generate redo ONLY for the indexes they had on them.


Is nologging a solution?

Parikshit Paul, May 19, 2006 - 6:20 am UTC

Hi Tom,
In our database, archivelog is being generated at the rate of 50 GB per hour whereas the rman archive backup to tape is around 20 GB per hour.
As such the archive file system get full every now and then due to which we have to stop the application ( the application is supposed to run 24 * 7), let the archive logs clear out and then restart the application.
Can you suggest a way to get around this problem?
Is making some of the tables nologging (where huge inserts take place) a solution? (Not a good solution I feel).

Tom Kyte
May 19, 2006 - 12:03 pm UTC

get more tape drives pops into my head immediately?

use disk instead of tape. (if you are 24x7, you must be "important" to the business therefore, architecting a hardware solution that meets the business need is a reasonable request)

I cannot advise you to use nologging (or not) simply because it radically affects your backup and recovery, only happens during certain special operations (nologging is usually "of no relevance" for most statements - they are logged anyway), does not affect index maintainence and so on.

More to Explore

PL/SQL demos

Check out more PL/SQL tutorials on our LiveSQL tool.

PL/SQL docs

PL/SQL reference manual from the Oracle documentation library