Parameter to Force Logging 2

[Michael42]

Hey All,

1. What is the spfile parameter that FORCES all SQL to use LOGGING (i.e. generate Undo etc.)?

2. Can I disable this without having to bounce the instance?

Thanks,

Michael42

 

[Scunningham99]

Tablespaces :
U can force logging mode or take it out of force logging mode. The database must be open and in READ WRITE mode. Neither of these settings changes the default LOGGING or NOLOGGING mode of the tablespace.

ALTER TABLESPACE data_01 NOLOGGING;


database:-


In FORCE LOGGING mode, Oracle will log all changes in the database except for changes in temporary tablespaces and temporary segments. This setting takes precedence over and is independent of any NOLOGGING or FORCE LOGGING settings you specify for individual tablespaces and any NOLOGGING settings you specify for individual database objects.

If you specify FORCE LOGGING, Oracle waits for all ongoing unlogged operations to finish.

hth




Sy UK

 

[SantaMufasa]

whoa, Whoa, WHOA, Michael...

...parameter that FORCES all SQL to use LOGGING (i.e. generate Undo etc.)?...Can I disable this without having to bounce the instance?

Undo and Redo (as in "Redo Logging") are two entirely different activities. The commands that Sy is talking about affect "REDO LOGGING".


If you really mean UNDO (i.e, = Rollback entries), there is no way to disable UNDO/Rollback entries in Oracle.

And as a clarification:

UNDO/Rollback entries provide "read consistency" and the option to UNDO/Rollback a transaction if you decide that you wish to abandon changes. Also, Oracle uses such entries during Database Recovery to Undo/Rollback transactions that were in-flight at the time of an abnormal termination/shutdown abort of an Oracle Instance.

Redo entries are strictly for Database Recovery to reconstruct transactions that were in-flight at the time of an abnormal termination/shutdown abort of an Oracle Instance.

These two types of entries are very different in use from one another...UNDO is not optional.

Mufasa
(aka Dave of Sandy, Utah, USA)
[

http://www.dasages.com:

Providing low-cost remote Database Admin services]
Click here to join Utah Oracle Users Group on Tek-Tips if you use Oracle in Utah USA.

 

[Michael42]

SantaMufasa,

Thanks for the response. In my case I am performing large INSERTS into a test TS and table where I do NOT want to generate Redo.

If I understand Scunningham99's response, I think this database was set to ALTER DATABASE FORCE LOGGING;. I think you guys answered why this database kept generating logs even when I set both the TS and Tables were set to NOLOGGING.

Thanks again guys,

Michael42

 

[Michael42]

>> I do NOT want to generate Redo.


Ooop, I keep slipping on the terms. I do not want to FORCE LOGGING at the database level.

Thanks,

Michael42