----------->>日志组及日志文件添加删除或修改日志文件大小
假设现有三个日志组，每个组内有一个成员，每个成员的大小为1MB，现在想把此三个日志组的成员大小都改为10MB 


1、创建2个新的日志组
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、切换当前日志到新的日志组
alter system switch logfile;
alter system switch logfile;


3、删除旧的日志组
alter database drop logfile group 1;
alter database drop logfile group 2;
alter database drop logfile group 3;


4、操作系统下删除原日志组1、2、3中的文件


5、重建日志组1、2、3
alter database add logfile group 1 ('D:\ORACLE\ORADATA\ORADB\REDO01_1.LOG') size 10M;
alter database add logfile group 2 ('D:\ORACLE\ORADATA\ORADB\REDO02_1.LOG') size 10M;
alter database add logfile group 3 ('D:\ORACLE\ORADATA\ORADB\REDO03_1.LOG') size 10M;


6、切换日志组
alter system switch logfile;
alter system switch logfile;
alter system switch logfile;


7、删除中间过渡用的日志组4、5
alter database drop logfile group 4;
alter database drop logfile group 5;


8、到操作系统下删除原日志组4、5中的文件


9、备份当前的最新的控制文件
SQL> connect internal
SQL> alter database backup controlfile to trace resetlogs


--------------------------------------------->>收集schema统计信息
begin
dbms_stats.gather_schema_stats(ownname => 'SCOTT',
                               estimate_percent => dbms_stats.auto_sample_size,
                               method_opt => 'for all columns size repeat',
                               degree => 7);
end;

--------------------------------------------->>收集表统计信息
begin
dbms_stats.gather_table_stats(ownname => 'APPLE',
                               tabname => 'NORMAL_SPEED_15SECONDS',
                               estimate_percent => DBMS_STATS.AUTO_SAMPLE_SIZE);
end;

--------------------------------------------->>收集索引统计信息
begin
dbms_stats.gather_index_stats(ownname => 'APPLE',
                               indname => 'NORMAL_SPEED_15SECONDS',
                               estimate_percent => DBMS_STATS.AUTO_SAMPLE_SIZE,
                               method_opt =>'FOR ALL COLUMNS SIZE AUTO');
end;


10.查找没有使用绑定变量的SQL语句(查找A schema下硬解析5次的前十名问题sql)
with force_mathces as
(select l.force_matching_signature mathces,
max(l.sql_id || l.child_number) max_sql_child,
dense_rank() over(order by count(*) desc) ranking
from v$sql l
where l.force_matching_signature <> 0
and l.parsing_schema_name = 'FLT'
group by l.force_matching_signature
having count(*) > 5)
select v.sql_id, v.sql_text, v.parsing_schema_name, fm.mathces, fm.ranking
from force_mathces fm, v$sql v
where fm.max_sql_child = (v.sql_id || v.child_number)
and fm.ranking <= 10
order by fm.ranking;
---->>另外一种查询方法
with force_mathces as
(select l.force_matching_signature mathces,
max(l.sql_id || l.child_number) max_sql_child,
count(*) total
from v$sql l
where l.force_matching_signature <> 0
and l.parsing_schema_name = 'FLT'
group by l.force_matching_signature
having count(*) > 1)
select fm.total,v.sql_id, v.sql_text, v.parsing_schema_name, V.Exact_Matching_Signature,fm.mathces
from force_mathces fm, v$sql v
where fm.max_sql_child = (v.sql_id || v.child_number) and V.Exact_Matching_Signature <> fm.mathces 
and v.sql_text not like 'with force_mathces as%'
order by fm.total desc;

-->>差异:第一种方法根据热度排名进行限定查询，第二种在第一种基础上较为实用，也排除了自查询sql，当然好可以使用rownum进行筛选.而且第一种还有个bug:由于开启了oracle自动收集统计信息功能，导致部分使用绑定变量的sql也被筛选了出来，第二种方式进行了兼容处理.

11.表空间使用率统计脚本:
    select total.tablespace_name,round(total.MB, 2) as Total_MB,round(total.MB - free.MB, 2) as Used_MB,round((1 - free.MB/total.MB) * 100,2) || '%' as     Used_Pct
    from (select tablespace_name, sum(bytes)/1024/1024 as MB from dba_free_space group by tablespace_name) free,
    (select tablespace_name, sum(bytes)/1024/1024 as MB from dba_data_files group by tablespace_name) total
    where free.tablespace_name = total.tablespace_name and total.tablespace_name like 'SYS%';