Oracle - 64. page

alter system switch logfile；执行一次，就只切换一个日志租，比如当前是group1，那么执行一次，就切换到group2
alter system archive log current；必须置数据库于归档模式下

ative状态的redolog 是可能把数据写进磁盘了，也可能没写进，所以这时候要删除这个组的重做联机日志时候就最好执行一下如下的命令：
alter system switch logfile；
alter system checkpoint；

在oracle中，将一个非分区表转换为分区表 (split partition 和exchange partition)
将一个非分区表转换为分区表
思路：
HZMH_Bak_SALE是(数据量上百万条，列比较多)一个非分区表，此时创建一个与HZMH_Bak_SALE同结构的分区表luda_partition，维护数据，
删除HZMH_Bak_SALE表，将luda_partition更名为ludatou_pat，删除luda_partition

步骤：
查询非分区表数据
select to_char(DATE, ‘yyyymm’), count(*) from HZMH_Bak_SALE group by to_char(DATE, ‘yyyymm’) order by to_char(DATE, ‘yyyymm’);
创建分区表（结构和非分区表HZMH_Bak_SALE相同）
create table luda_partition
(
PIG_ID VARCHAR2(12) not null,
DATE DATE not null,
NUM_NO VARCHAR2(12),
……
)
tablespace ludatou
partition by range(DATE)(
partition rest values less than (maxvalue))

交换数据（数据从非分区表到分区表）
alter table luda_partition exchange partition rest with table HZMH_Bak_SALE;

查询分区表数据
select to_char(DATE, ‘mm-yyyy’), count(*) from HZMH_Bak_SALE group by to_char(DATE, ‘mm-yyyy’);

–辅助脚本
select ‘alter table luda_partition split partition rest at (to_date(”’
|| to_char(DATE, ‘yyyymm’) ||”’,”yyyymm”)) into (partition p’
|| to_char(DATE, ‘yyyymm’)
|| ‘, partition rest);’
from luda_partition
group by to_char(DATE, ‘yyyymm’)
order by to_char(DATE, ‘yyyymm’)

–5、对rest分区进行拆分
alter table luda_partition split partition rest at (to_date(‘201008′,’yyyymm’)) into (partition p201008, partition rest);
alter table luda_partition split partition rest at (to_date(‘201009′,’yyyymm’)) into (partition p201009, partition rest);
alter table luda_partition split partition rest at (to_date(‘201010′,’yyyymm’)) into (partition p201010, partition rest);
alter table luda_partition split partition rest at (to_date(‘201011′,’yyyymm’)) into (partition p201011, partition rest);
alter table luda_partition split partition rest at (to_date(‘201012′,’yyyymm’)) into (partition p201012, partition rest);

–6、删除非分区表
drop table HZMH_Bak_SALE

–7、将分区表重命名为原非分区表名
alter table luda_partition rename to HZMH_Bak_SALE

–8、删除中间处理过程的分区表
drop table luda_partition

如果已存在分区表，要将未分区表（与已分区表结构完全相同）加入到分区中：

alter table HZMH_Bak_SALE exchange partition p201008 with table luda_test;
alter table HZMH_Bak_SALE exchange partition p201009 with table luda_test_t;

如果luda_test_t不符合分区规则，需要指定without validation 来禁止检查
alter table HZMH_Bak_SALE exchange partition p201009 with table luda_test_t without validation;

如果luda_test表中的数据是符合分区规则的，那指定without validation 之后，不会对luda_test进行全表扫描，
则会缩短exchange时间

参考手册：

Administrator Guide

关于CBO和系统收集信息的操作

使用analyze 收集统计信息
Analyze Statement
The ANALYZE statement can be used to gather statistics for a specific table, index or cluster. The statistics can be computed exactly, or estimated based on a specific number of rows, or a percentage of rows:

ANALYZE TABLE employees COMPUTE STATISTICS;
ANALYZE INDEX employees_pk COMPUTE STATISTICS;

ANALYZE TABLE employees ESTIMATE STATISTICS SAMPLE 100 ROWS;
ANALYZE TABLE employees ESTIMATE STATISTICS SAMPLE 15 PERCENT;DBMS_UTILITY

使用DBMS_UTILITY收集对象或者数据库的统计信息

EXEC DBMS_UTILITY.analyze_schema(‘SCOTT’,’COMPUTE’);
EXEC DBMS_UTILITY.analyze_schema(‘SCOTT’,’ESTIMATE’, estimate_rows => 100);
EXEC DBMS_UTILITY.analyze_schema(‘SCOTT’,’ESTIMATE’, estimate_percent => 15);

EXEC DBMS_UTILITY.analyze_database(‘COMPUTE’);
EXEC DBMS_UTILITY.analyze_database(‘ESTIMATE’, estimate_rows => 100);
EXEC DBMS_UTILITY.analyze_database(‘ESTIMATE’, estimate_percent => 15);DBMS_STATS

使用DBMS_STATS收集对象或者数据库的统计信息
The DBMS_STATS package was introduced in Oracle 8i and is Oracles preferred method of gathering object statistics. Oracle list a number of benefits to using it including parallel execution,
long term storage of statistics and transfer of statistics between servers. Once again, it follows a similar format to the other methods:

EXEC DBMS_STATS.gather_database_stats;
EXEC DBMS_STATS.gather_database_stats(estimate_percent => 15);

EXEC DBMS_STATS.gather_schema_stats(‘SCOTT’);
EXEC DBMS_STATS.gather_schema_stats(‘SCOTT’, estimate_percent => 15);

EXEC DBMS_STATS.gather_table_stats(‘SCOTT’, ‘EMPLOYEES’);
EXEC DBMS_STATS.gather_table_stats(‘SCOTT’, ‘EMPLOYEES’, estimate_percent => 15);

EXEC DBMS_STATS.gather_index_stats(‘SCOTT’, ‘EMPLOYEES_PK’);
EXEC DBMS_STATS.gather_index_stats(‘SCOTT’, ‘EMPLOYEES_PK’, estimate_percent => 15);This package also gives you the ability to delete statistics:

EXEC DBMS_STATS.delete_database_stats;
EXEC DBMS_STATS.delete_schema_stats(‘SCOTT’);
EXEC DBMS_STATS.delete_table_stats(‘SCOTT’, ‘EMPLOYEES’);
EXEC DBMS_STATS.delete_index_stats(‘SCOTT’, ‘EMPLOYEES_PK’);Scheduling Stats