Drop Down MenusCSS Drop Down MenuPure CSS Dropdown Menu

Nologging and force logging examples

Nologging and force logging

NOLOGGING can be used to prevent bulk operations from logging too much information to
Oracle's Redo log files.On the other hand, FORCE LOGGING can be used on tablespace or
database level to force logging of changes to the redo. This may be required for sites
that are mining log data, using Oracle Streams or using Data Guard (standby databases).
Nologging[edit]

NOLOGGING can be used to minimize the amount of redo generated by Oracle. Only the
following operations can make use of nologging:

SQL*Loader in direct mode
INSERT /*+APPEND*/ ...
CTAS

ALTER TABLE statements (move/add/split/merge partitions)
CREATE INDEX

ALTER INDEX statements (move/add/split/merge partitions)
To create a table in NOLOGGING mode:

SQL> CREATE TABLE t1 (c1 NUMBER) NOLOGGING;
Table created.

To enable NOLOGGING for a table:

ALTER TABLE t1 NOLOGGING;
Table altered.
Force logging[edit]

A tablespace or the entire database is either in force logging or no force
logging mode. To see which it is, run:

SQL> SELECT force_logging FROM v$database;
FOR
---
NO
or
SQL> SELECT tablespace_name, force_logging FROM dba_tablespaces;
TABLESPACE_NAME                FOR
------------------------------ ---
SYSTEM                         NO
...
To enable force logging:
SQL> ALTER DATABASE force logging;
Database altered.
SQL> ALTER TABLESPACE users FORCE LOGGING;
Tablespace altered.
To disable:
SQL> ALTER DATABASE no force logging;
Database altered.
SQL> ALTER TABLESPACE users NO FORCE LOGGING;
Tablespace altered.

Comments

Popular posts from this blog

ORA-01261: Parameter db_recovery_file_dest destination string cannot be translated ORA-01262: Stat failed on a file destination directory Linux-x86_64 Error: 2: No such file or directory

How to Get Table Size, Database Size, Indexes Size, schema Size, Tablespace Size, column Size in PostgreSQL Database

PostgreSQL Database startup / shutdown /restart

PostgreSQL Pgbadger Installation On Linux

PostgreSQL pgBadger