| tablespace with nologging mode [message #163538] |
Fri, 17 March 2006 02:58  |
kinjal
Messages: 124
Registered: June 2001
|
Senior Member |
|
|
Hello,
I am using Oracle Release 9.2.0.6.0
I have my tablespace in nologging mode. And still if I view my arch files it shows all DMLs on the tables that are with nologging option.
And because of this problem my server is runnning out or space sometimes. And I must have to take cold backup to remove all the archives.
What should do to ignore this DMLs?
or is this a bug or what?
Kinjal
|
|
|
|
|
|
|
|
| Re: tablespace with nologging mode [message #163624 is a reply to message #163538] |
Fri, 17 March 2006 09:26  |
smartin
Messages: 1803
Registered: March 2005
Location: Jacksonville, Florida
|
Senior Member |
|
|
Force logging can also be applied at the tablespace level, in addition to database level:
| Quote: |
FORCE LOGGING
Use this clause to put the tablespace into FORCE LOGGING mode. Oracle Database will log all changes to all objects in the tablespace except changes to temporary segments, overriding any NOLOGGING setting for individual objects. The database must be open and in READ WRITE mode.
This setting does not exclude the NOLOGGING attribute. That is, you can specify both FORCE LOGGING and NOLOGGING. In this case, NOLOGGING is the default logging mode for objects subsequently created in the tablespace, but the database ignores this default as long as the tablespace or the database is in FORCE LOGGING mode. If you subsequently take the tablespace out of FORCE LOGGING mode, then the NOLOGGING default is once again enforced.
|
Also, the nologging at the tablespace level can be overriden at the segment level:
| Quote: |
logging_clause
Specify the default logging attributes of all tables, indexes, materialized views, materialized view logs, and partitions within the tablespace. LOGGING is the default. This clause is not valid for a temporary or undo tablespace.
The tablespace-level logging attribute can be overridden by logging specifications at the table, index, materialized view, materialized view log, and partition levels.
|
[Updated on: Fri, 17 March 2006 09:27] Report message to a moderator |
|
|
|
| Re: tablespace with nologging mode [message #163627 is a reply to message #163538] |
Fri, 17 March 2006 09:32 |
smartin
Messages: 1803
Registered: March 2005
Location: Jacksonville, Florida
|
Senior Member |
|
|
Oh, and make sure you double check what nologging means, ie that it doesn't mean no logging at all, just reduced logging for certain operations.
And your server running out of space indicates you are in archivelog mode, which controls what you do with redo information, not how much redo information is generated in the first place.
| Quote: |
The size of a redo log generated for an operation in NOLOGGING mode is significantly smaller than the log generated in LOGGING mode.
In NOLOGGING mode, data is modified with minimal logging (to mark new extents INVALID and to record dictionary changes). When applied during media recovery, the extent invalidation records mark a range of blocks as logically corrupt, because the redo data is not fully logged. Therefore, if you cannot afford to lose the database object, then you should take a backup after the NOLOGGING operation.
NOLOGGING is supported in only a subset of the locations that support LOGGING. Only the following operations support the NOLOGGING mode:
DML:
Direct-path INSERT (serial or parallel) resulting either from an INSERT or a MERGE statement. NOLOGGING is not applicable to any UPDATE operations resulting from the MERGE statement.
Direct Loader (SQL*Loader)
DDL:
CREATE TABLE ... AS SELECT
CREATE TABLE ... LOB_storage_clause ... LOB_parameters ... NOCACHE | CACHE READS
ALTER TABLE ... LOB_storage_clause ... LOB_parameters ... NOCACHE | CACHE READS (to specify logging of newly created LOB columns)
ALTER TABLE ... modify_LOB_storage_clause ... modify_LOB_parameters ... NOCACHE | CACHE READS (to change logging of existing LOB columns)
ALTER TABLE ... MOVE
ALTER TABLE ... (all partition operations that involve data movement)
ALTER TABLE ... ADD PARTITION (hash partition only)
ALTER TABLE ... MERGE PARTITIONS
ALTER TABLE ... SPLIT PARTITION
ALTER TABLE ... MOVE PARTITION
ALTER TABLE ... MODIFY PARTITION ... ADD SUBPARTITION
ALTER TABLE ... MODIFY PARTITION ... COALESCE SUBPARTITION
CREATE INDEX
ALTER INDEX ... REBUILD
ALTER INDEX ... REBUILD [SUB]PARTITION
ALTER INDEX ... SPLIT PARTITION
|
[Updated on: Fri, 17 March 2006 09:33] Report message to a moderator |
|
|
|
] 
Blog
Search
Help
Members
Register
Login
Home
