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
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.
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
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).
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.