这块要分两种情况进行试验，1.没有maxvalue分区。2.有maxvalue分区。
下面分别试验之：
A.没有maxvalue的range分区表增加分区。
1.创建分区表：
SQL> CREATE TABLE t_range_part (ID NUMBER)
2 PARTITION BY RANGE(ID)
3 (
4 PARTITION t_range_1 VALUES LESS THAN (10),
5 PARTITION t_range_2 VALUES LESS THAN (20),
6 PARTITION t_range_3 VALUES LESS THAN (30)
7 );
Table created
2.查看分区表信息：
SQL> select table_name,partition_name,high_value from dba_tab_partitions where table_name='T_RANGE_PART';
TABLE_NAME PARTITION_NAME HIGH_VALUE
------------------------------ ------------------------------ --------------------------------------------------------------------------------
T_RANGE_PART T_RANGE_1 10
T_RANGE_PART T_RANGE_2 20
T_RANGE_PART T_RANGE_3 30
3.添加分区：
SQL> alter table t_range_part add partition t_range_4 values less than (40);
Table altered
4.再次查看分区表信息：
SQL> select table_name,partition_name,high_value from dba_tab_partitions where table_name='T_RANGE_PART';
TABLE_NAME PARTITION_NAME HIGH_VALUE
------------------------------ ------------------------------ --------------------------------------------------------------------------------
T_RANGE_PART T_RANGE_1 10
T_RANGE_PART T_RANGE_2 20
T_RANGE_PART T_RANGE_3 30
T_RANGE_PART T_RANGE_4 40
由以上结果可以看出，分区添加成功！
B.有maxvalue分区的分区表增加分区。
1.创建分区表：
SQL> CREATE TABLE t_range_part (ID NUMBER)
2 PARTITION BY RANGE(ID)
3 (
4 PARTITION t_range_1 VALUES LESS THAN (10),
5 PARTITION t_range_2 VALUES LESS THAN (20),
6 PARTITION t_range_3 VALUES LESS THAN (30),
7 PARTITION t_range_max VALUES LESS THAN (MAXVALUE)
8 );
Table created
2.查看分区表信息：
SQL> select table_name,partition_name,high_value from dba_tab_partitions where table_name='T_RANGE_PART';
TABLE_NAME PARTITION_NAME HIGH_VALUE
------------------------------ ------------------------------ --------------------------------------------------------------------------------
T_RANGE_PART T_RANGE_1 10
T_RANGE_PART T_RANGE_2 20
T_RANGE_PART T_RANGE_3 30
T_RANGE_PART T_RANGE_MAX MAXVALUE
3.添加分区：
注意，有了maxvalue，就不能直接add partition，而是需要max分区split。下面分别试验：
SQL> alter table t_range_part add partition t_range_4 values less than (40);
alter table t_range_part add partition t_range_4 values less than (40)
ORA-14074: 分区界限必须调整为高于最后一个分区界限
SQL> alter table t_range_part split partition t_range_max at (40) into (partition t_range_4,partition t_range_max);
Table altered
4.查看分区表信息：
SQL> select table_name,partition_name,high_value from dba_tab_partitions where table_name='T_RANGE_PART';
TABLE_NAME PARTITION_NAME HIGH_VALUE
------------------------------ ------------------------------ --------------------------------------------------------------------------------
T_RANGE_PART T_RANGE_1 10
T_RANGE_PART T_RANGE_2 20
T_RANGE_PART T_RANGE_3 30
T_RANGE_PART T_RANGE_4 40
T_RANGE_PART T_RANGE_MAX MAXVALUE
结果看出，添加分区成功。
对于有maxvalue分区的分区表来说，其实切割最后一个分区。
--EOF
本文链接
/*变成大写*/
select Upper('abcde') from dual;
/*变成小写*/
select lower('ADCSE') from dual;
/*第一个字母变成大写*/
select Initcap('dkdkdkdkd') from dual;
/*合并字符*/
select concat('a', 'b')
from dual；
select 'a' || 'b'
from dual；
/*截取字符串*/
select substr('abcde', length('abcde') - 2) from dual;
/*从开始的位置截取要的个数字符串，-代表从后向前计算，+代表从前向后计算*/
select substr('abcde', -5, 3) from dual;
/*计算字符串长度 不区分半角全角*/
select Length(t.membername)
from MEMBERINFO t
/*替换*/
select replace('abcae', 'a', 'm') from dual;
/*查询匹配字符串所在的位置 相当于 index of*/
select Instr('Hello World', 'or') from dual;
/*左侧填充*/
select Lpad('Smith', 10, '*')
from dual
/*右侧填充*/
select Rpad('Smith', 10, '*')
from dual
/*去除左右空格*/
select trim(' dfd ') from dual;
/*数值函数*/
/*四舍五入*/
select round(415, -1) from dual;
select round(414.21, 1) from dual;
/*取余*/
select Mod(12, 11)
from dual；
/*
TRUNC（number,num_digits）
Number 需要截尾取整的数字。
Num_digits 用于指定取整精度的数字。Num_digits 的默认值为 0。
TRUNC()函数截取时不进行四舍五入
*/
select trunc(123.458) from dual; --123
select trunc(123.458, 0) from dual; --123
select trunc(123.458, 1) from dual; --123.4
select trunc(123.458, -1) from dual; --120
--union 并集 苏偶有内容都查询，重复的显示一次
select * from emp union select * from emp20;
--union all 并集 所有内容都显示 包括重复
select * from emp union all select * from emp20;
--intersect 交集
select * from emp intersect select * from emp20;
--minus 差集 只显示对方没有的 与顺序有关
select * from emp minus select * from emp20;
--子查询
--单行子查询
select *from emp where sal>(select sal from emp where empno=7566);
--多行子查询
select * from emp where sal>any(select avg(sal) from emp group by deptno);
select * from emp where sal>all(select all(sal) from emp group by deptno);
select * from emp where job in (select job from emp where ename ='Martin' or ename ='Smith');
--top N 查询
select *from emp where rownum=1 or rownum=2;
select * from emp where rownum<=5;
--分页查询
select *
from (select rownum no, e.*
from (select * from emp order by sal desc) e
where rownum <= 5)
where no >= 3
select *
from (select rownum no, e.* from (select * from emp order by sal desc) e)
where no >= 3
and no <= 5
--随即返回5条记录
select * from(select * from MEMBERINFO order by dbms_random.value())where rownum<=5;
--处理空值排序
select *from emp order by comm desc nulls last;
select *from emp order by comm desc nulls first;
--查询跳过表中的偶数行
select memberid from (select row_number() over (order by memberid) rn ,memberid from MEMBERINFO ) x where mod(rn,2)=1
--查询所有员工信息与其中工资最高和最低工资
select ename ,sal max(sal) over(),min(sal) over() from emp;
--