oracle分区总结

–1.普通分区脚本 需要手动创建新分区
create table t_factor_value_2017
(
stat_month CHAR(6) not null,
channel_id CHAR(2) not null,
sales_id CHAR(7) not null,
branch_id CHAR(7),
version_id VARCHAR2(20)
)
partition by list (STAT_MONTH)
(
partition P202008 values (‘202008’),
partition P202009 values (‘202009’),
partition P202010 values (‘202010’),
partition P202011 values (‘202011’),
partition P202012 values (‘202012’),
partition PARTDEF values (default)
);
–2. 区间分区 解决手动创建新分区
create table empCYH
(empno number(4),
ename varchar2(20),
job varchar2(20),
hiredate date,
deptno number(2))
PARTITION BY RANGE (hiredate)
INTERVAL (NUMTOYMINTERVAL(1,‘YEAR’))
(PARTITION part_1999 VALUES LESS THAN
(TO_DATE(‘2000-01-01’,‘YYYY-MM-DD’)));
commit;

select a.*,a.rowid from empCYH a;

–3.系统分区 提供了一个额外的选项使得用户可以完全控制数据存放的分区，创建本地索引会自动根据表的分区对索引进行分区。注意：必须要在插入数据的时候，告诉oracle要具体放在哪个分区。

create table sales
(sales_id number,
product_code number ,
state_code number)
partition by system
(partition p1 tablespace users,
partition p2 tablespace users);
create index in_sales_state on sales(state_code) local;

–操作
insert into sales partition(p1) values(1,101,1);
delete sales partition (p1) where state_code=‘1’;

–4.虚拟列分区 支持所有的基本分区策略
–比如所有账户号码的前两个数字代表银行的分行，通过虚拟列分区进行分行的分区
create table accountsCYH
(acc_no number(10) not null,
acc_name varchar2(50) not null,
acc_branch number(2) generated always as
(to_number(substr(to_char(acc_no),1,2))))
partition by list (acc_branch)
(partition main_branch values(1),
partition NY_branch values(2),
partition chicago_branch values(4),
partition miami_branch values(11));

select a.*,a.rowid from accountsCYH a;

还原表空间扩容
–MM查询表空间大小
select * from dba_data_files a where a.TABLESPACE_NAME = ‘UNDOTBS1’;
–MM给表空间大小扩容
alter tablespace UNDOTBS1 add datafile ‘/picclife/app/oracle/oradata/smis/undotbs02.dbf’ size 1000m autoextend on next 200m;

SELECT tablespace_name FROM dba_tablespaces WHERE contents=‘UNDO’;

------查看还原表空间使用情况
select b.tablespace_name as “表空间”,
b.file_name as “物理文件名”,
b.bytes / 1024 / 1024 as “当前大小(M)”,
(b.bytes - sum(nvl(a.bytes, 0))) / 1024 / 1024 as “已使用(M)”,
substr((b.bytes - sum(nvl(a.bytes, 0))) / (b.bytes) * 100, 1, 5) as “使用率(%)”,
case b.autoextensible
when ‘YES’ then ‘是’
else ‘否’
end as “是否自增”,
b.maxbytes / 1024 / 1024 as “自增最大容量(M)”
from dba_free_space a, dba_data_files b
where a.file_id = b.file_id
and a.tablespace_name in (‘UNDOTBS1’)
group by b.tablespace_name, b.file_name, b.bytes, b.autoextensible, b.maxbytes
order by b.tablespace_name;

–参考地址：
/* http://blog.csdn.net/labixiaofeng/article/details/73483143 */