oracle log related nologging

2010-10-11  来源:本站原创  分类:Database  人气:175 

In fact nologging and table mode, insert mode, the database operating mode (archived / unarchived) have a great relationship:

Summarized as follows:

Note that append is a hint;

Generally we can use

insert / * + append + / into mytable values ​​(1, 'alan');

Database in archive mode

When table mode is logging state, whether or no append append mode mode, redo will be generated.

When table mode is nologging state, only append mode does not generate redo.

Database in non archive mode

Whether or nologing in logging mode, append mode will not generate redo, and no append mode will generate redo.

If I want to see whether it is a table logging state can be

select table_name, logging from dba_tables where table_name = 'tablename';

Modify the state of logging table: alter table table_name nologging

Then there is an internal Oracle internal parameter: _disable_logging default is false

By changing to true allows Oracle to modify records in the table does not record exactly when the redo, the parameters to be very use. Usually, we only used as a performance test.

force logging (forced log) mode:

Command:

alter database force logging to make the Oracle redo whatever operations are to write.

Through select force_logging from v $ database can see the current database log mode to force the state.

Another: the partition table data deletion method

If there is a global index:
alter table table_name truncate partition NO_ partition number UPDATE GLOBAL INDEXES;
Board index or no index:
alter table table_name truncate partition NO_ partition number;

Partition table, partition index and global index:
Data in a table over 20 million or more than 2G of space occupied, it is recommended to establish the partition table.
create table ta (c1 int, c2 varchar2 (16), c3 varchar2 (64), c4 int constraint pk_ta primary key (c1)) partition by range (c1) (partition p1 values ​​less than (10000000), partition p2 values ​​less than (20000000), partition p3 values ​​less than (30000000), partition p4 values ​​less than (maxvalue));
Index and global index partitioning:
Partitioned index is created separately on all the indexes for each area, it can automatically maintain, in drop or truncate a partition without affecting the other partitions of the index using the index, that is, the index will fail to maintain them more convenient, but little impact on query performance.
create index idx_ta_c2 on ta (c2) local (partition p1, partition p2, partition p3, partition p4); or create index idx_ta_c2 on ta (c2) local;
Also in the create unique index idx_ta_c2 on ta (c2) local; the system will report ORA-14039 error, because ta table partitioning column is c1, oracle does not support the partition table to create PK primary key primary key column does not contain the partitioning column, create additional constraints (unique) can not.
Global index is created on the table in the whole index, it can create its own partition, the partition and the partition table can not the same, that is, it is independent of the index. In the drop or truncate a partition to create an index alter index idx_xx rebuild, you can alter table table_name drop partition partition_name update global indexes; achieve, but take a long time in the reconstruction index. You can check user_indexes, user_part_indexes and user_ind_partitions view to see the index is valid.
create index idx_ta_c3 on ta (c3);
Or the global index is divided into several areas (note the partition and the partition table is not the same):
create index idx_ta_c4 on ta (c4) global partition by range (c4) (partition ip1 values ​​less than (10000), partition ip2 values ​​less than (20000), partition ip3 values ​​less than (maxvalue));
Note that the index on the guide column after column and range to be consistent, otherwise there will be ORA-14038 error.
oracle will automatically create a global index of primary key <br /> If you want the primary key columns in partitioned index is created, unless the primary key, including the partition key, there is the primary key built in two or more columns.
Frequently deleted in the partition table when more frequent data updates and maintenance easy to avoid using the global index.

alter system archive log STOP ;---- need to reboot?
alter system archive log start ;---- need to reboot?
select * from v $ logfile;
select * from v $ log_history;
select dbid, name, created, log_mode from v $ database;

相关文章
  • oracle log related nologging 2010-10-11

    In fact nologging and table mode, insert mode, the database operating mode (archived / unarchived) have a great relationship: Summarized as follows: Note that append is a hint; Generally we can use insert / * + append + / into mytable values ​​(1, 'a

  • Oracle MTS related issues 2010-11-28

    Oracle MTS related issues I. What is MTS MTS = Multi-Threaded Server ORACLE SERVER MTS is an optional configuration options, is relative DEDICATE way, for its biggest advantage is to not increase the physical resources (memory), under the premise sup

  • ORACLE log About using UTL_FILE 2011-02-25

    ORACLE log About using UTL_FILE Author: wangmin 1: The administrator user login, such as: conn sys / beyond @ beyond as sysdba 2: Configuring UTL_FILE operational directory Use the command: alter system set utl_file_dir = '/ opt/oracle/oracle10g/log/

  • Related nologging oracle log 2010-10-11

    In fact nologging and table mode, insert mode, the database operating mode (archived / unarchived) has a great relationship: Summarized as follows: Note that append is a hint; We can use the general insert / * + append + / into mytable values (1, 'al

  • Oracle log file analysis 2010-04-29

    First, explain how to analyze the LogMiner From now, the only way of Oracle is to use the log provided by Oracle LogMiner to For , Oracle database, all changes are recorded in the log, Danshi log message is we can not understand, while the LogMiner i

  • redo log related operation learning 2010-03-25

    1.Creating Redo Log Groups and Members 1.1.Creating Redo Log Groups ALTER DATABASE ADD LOGFILE GROUP 10 ('/oracle/dbs/log1c.rdo', '/oracle/dbs/log2c.rdo') SIZE 500K; 1.2.Creating Redo Log Members ALTER DATABASE ADD LOGFILE MEMBER '/ oracle/dbs/log2b.

  • Understand and use Oracle log analysis tools-LogMiner 2010-04-29

    This article is reproduced, the first author is unknown, please contact me. Oracle LogMiner is Oracle 8i from the product after the company provided a very useful analysis of the actual tool, the tool can be easily obtained using the Oracle redo log

  • oracle log analysis tools to LogMiner use (combat) 2010-04-29

    To install the LogMiner tool, you must first run the following two scripts so that the two are in the script must be run as SYS user. The first script used to create DBMS_LOGMNR package, the package used to analyze the log files. The second script is

  • Modify the oracle log file size 2010-05-22

    1, create 2 new log group alter database add logfile group 4 ('D: \ ORACLE \ ORADATA \ ORADB \ REDO04_1.LOG') size 1024k; alter database add logfile group 5 ('D: \ ORACLE \ ORADATA \ ORADB \ REDO05_1.LOG') size 1024k; 2, switch the current log to a n

  • Oracle log mining operation experiments 2010-06-10

    Analysis: next Pharaoh (http://wallimn.javaeye.com), my original, reproduced Please keep my information. Log Mining to learn the basic knowledge of a few days and today finally decided to be a hands-on experiments. The process is very simple experime

  • RedHat install Oracle 10g related 2010-09-10

    To verify that the system meets the minimum requirements for Oracle 10g database to root user to log in and run the following command. To view the available RAM and swap space, run the following command: # Grep MemTotal / proc / meminfo MemTotal: 108

  • Oracle log file error handling 2010-09-16

    Transfer from: http://hi.baidu.com/dashuaiwang/blog/item/47cc680ec35055c37acbe1f8.html Open the oracle database error occurred: ORA-00313: Unable to open log group 1 (thread 1) of the members of the ORA-00312: online log 1 thread 1: 'D: \ ORACLE \ OR

  • Oracle log analysis tools - LogMiner Detailed 2010-12-22

    LogMiner ---- Oracle 8i provided after the self through the form of SQL commands to query and resolve redo (redo) and undo (remove) log tool. 1. Install LogMiner Oracle LogMiner is a log analysis tool that comes with the installation as long as you c

  • sql oracle user related 2011-05-25

    1. View user has the database object Sql Code select object_name from user_objects; 2. View constraint information Sql Code select constraint_name from user_constraints; 3. See the user has the table Sql Code select table_name from user_tables; Or Sq

  • Oracle log files error handling 2010-09-16

    Transfer: http://hi.baidu.com/dashuaiwang/blog/item/47cc680ec35055c37acbe1f8.html Open the oracle database error has occurred: ORA-00313: Unable to open log group 1 (thread 1) of the members of the ORA-00312: online log 1 thread 1: 'D: \ ORACLE \ ORA

  • oracle log file is missing (ORA-O1109: database not open) approach (reproduced) 2011-01-20

    This is my website to find the solution in the other missing log files oracle approach, to help me solve the problem of lost logs. So specifically in turn want to help everyone. Original: http://blogold.chinaunix.net/u1/40226/showart_1968470.html ora

  • oracle log. partition index 2011-04-21

    Recently summarized some of the issues dealing with the database the next: 1. Oracle10G view the system log: E: \ oracle \ product \ 10.2.0 \ admin \ "oracle_sid" \ bdump E: \ oracle \ product \ 10.2.0 \ admin \ "oracle_sid" \ udump E:

  • oracle log mining logminer 2011-07-11

    REVIEW: First, listen to the story section of it ~ Two, logminer the basic method 3, the actual examples are likely to be used First, listen to the story section of it ~ p109 oracle redo log file contains the user data and database data dictionary al

  • Oracle log files in the custom Login 2011-09-14

    Sign in window system for Oracle set the login session of debugging information to customize login.sql. In the case of default implementation of Oracle's default login directory sql directory in login.sql (D: \ app \ Administrator \ product \ 11.1.0

  • Introduction to the ORACLE ORACLE log management 2011-09-26

    ORACLE database's log file $ ORACLE_BASE / admin / orasid / bdump / alert_orasid.log redo log records the conversion, the database startup and shutdown, the database structure changes, rollback segment changes, deadlock, an internal error and other i