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:
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;