FORCE LOGGING On Oracle

dbtut

6 years ago

Before you read this article, I suggest you read the article “What is logging_clause On Oracle (LOGGING or NOLOGGING)” to better understand the concept of LOGGING.

You may need to force all objects in the database to work in the LOGGING structure, especially if you are transferring data to the standby.

But in some cases you can choose NOLOGGING so that the operation to be performed is faster, or does not consume resources (for example, the INDEX REBUILD operation).

With the help of the following script you can force all objects in the database to work in LOGGING or NOLOGGING structure.

ALTER DATABASE FORCE LOGGING;
ALTER DATABASE NO FORCE LOGGING;

1 2	ALTER DATABASE FORCE LOGGING; ALTER DATABASE NO FORCE LOGGING;

With the help of the following scripts you can use the FORCE LOGGING option on the database and tablespace basis.

ALTER TABLESPACE TABLESPACE_NAME FORCE LOGGING;
ALTER TABLESPACE TABLESPACE_NAME NO FORCE LOGGING;

1 2	ALTER TABLESPACE TABLESPACE_NAME FORCE LOGGING; ALTER TABLESPACE TABLESPACE_NAME NO FORCE LOGGING;

select name,force_logging from v$database;
select tablespace_name,force_logging from dba_tablespaces;

1 2	select name,force_logging from v$database; select tablespace_name,force_logging from dba_tablespaces;