--Oracle9i以上版本,利用在线重定义功能
CREATE TABLE T (ID NUMBER PRIMARY KEY, order_no varchar2(40), order_date DATE);
--unable to create INITIAL extent for segment in tablespace hmit_hyt
/*
有2个方法:
1。增大现在表空间数据文件的大小.alter database datafile 'XXX' resize XXXXM;
2。给这个表空间增加数据文件:alter tablespace XXX add datafile 'XXX' size XXXM autoextend on next XXM maxsize XXXXM;
*/
alter tablespace hmit_hyt add DATAFILE 'D:\app\hmit\oradata\mydata\HMIT_HYT_1.DBF' size 500M autoextend on next 50M maxsize 4096M;
INSERT INTO t SELECT ROWNUM , product_no, sys_time FROM warehouse_inventory;
--检查重定义的合理性
EXEC DBMS_REDEFINITION.CAN_REDEF_TABLE('hmit_hyt', 'T', DBMS_REDEFINITION.CONS_USE_PK);--PL/SQL procedure successfully completed
CREATE TABLE T_NEW (ID NUMBER PRIMARY KEY, order_no varchar2(40), order_date DATE) PARTITION BY RANGE (order_date)
(PARTITION P1 VALUES LESS THAN (TO_DATE('2009-7-1', 'YYYY-MM-DD')),
PARTITION P2 VALUES LESS THAN (TO_DATE('2010-3-1', 'YYYY-MM-DD')),
PARTITION P4 VALUES LESS THAN (MAXVALUE));
--如果分区表和原表列名相同,可以用如下方式进行:
BEGIN
DBMS_REDEFINITION.start_redef_table(
uname => 'HMIT_HYT',
orig_table => 't',
int_table => 't_new');
END;
/
--如果分区表的列名和原表不一致,那么在开始重定义的时候,需要重新指定映射关系
EXEC DBMS_REDEFINITION.START_REDEF_TABLE(
'HMIT_HYT',
't',
't_new',
'ID ID, create_date TIME', -- 在这里指定新的映射关系
DBMS_REDEFINITION.CONS_USE_PK);
--不指定表分区查看SALES表信息:
SELECT COUNT(*) FROM t_new;
--指定P1表分区查询SALES表信息:
SELECT COUNT(*) FROM t_new PARTITION(P1) UNION
SELECT COUNT(*) FROM t_new PARTITION(P2) UNION
SELECT COUNT(*) FROM t_new PARTITION(P4);
-- 同步新表,这是可选的操作
SQL> BEGIN
DBMS_REDEFINITION.start_redef_table(
uname => 'HMIT_HYT',
orig_table => 't',
int_table => 't_new');
END;;
/
-- 创建索引,在线重定义只重定义数据,索引还需要单独建立。
create index index_order_no on t_new(order_no);
-- 收集新表的统计信息
exec dbms_stats.gather_table_stats('hmit_hyt', 't_new', cascade => true);
-- 结束重定义
BEGIN
dbms_redefinition.finish_redef_table(
uname => 'hmit_hyt',
orig_table => 't',
int_table => 't_new');
END;
/
--不指定表分区查看SALES表信息:
SELECT COUNT(*) FROM t;
--指定P1表分区查询SALES表信息:
SELECT COUNT(*) FROM t PARTITION(P1) UNION
SELECT COUNT(*) FROM t PARTITION(P2) UNION
SELECT COUNT(*) FROM t PARTITION(P4);
-- 删除临时表
DROP TABLE t_new;
-- 索引重命名
ALTER INDEX index_order_no RENAME TO index_t_order_no;
-- 验证
select partitioned from user_tables where table_name = UPPER('t');
select partition_name from user_tab_partitions where table_name = UPPER('t');
--更改分区表的默认存储表空间
create tablespace HMIT_HYT_t_p1
logging
datafile 'D:\app\hmit\oradata\mydata\HMIT_HYT_t_p1.dbf'
size 32m
autoextend on
next 32m maxsize 2048m
extent management local;
alter table t move partition p1 tablespace HMIT_HYT_T_P1;
SELECT * FROM dba_tab_partitions WHERE table_name ='T';
SELECT * FROM user_tablespaces;
SELECT * FROM user_tables;
-- 拆分分区
--拆分分区将一个分区拆分两个新分区,拆分后原来分区不再存在。注意不能对HASH类型的分区进行拆分。
ALTER TABLE t PARTITION P2 AT(TO_DATE('2010-03-01','YYYY-MM-DD'))
INTO (PARTITION P21,PARTITION P22);
--显示数据库所有分区表的信息:
select * from DBA_PART_TABLES
--显示当前用户可访问的所有分区表信息:
select * from ALL_PART_TABLES
--显示当前用户所有分区表的信息:
select * from USER_PART_TABLES
--显示表分区信息 显示数据库所有分区表的详细分区信息:
select * from DBA_TAB_PARTITIONS
--显示当前用户可访问的所有分区表的详细分区信息:
select * from ALL_TAB_PARTITIONS
--显示当前用户所有分区表的详细分区信息:
select * from USER_TAB_PARTITIONS
--显示子分区信息 显示数据库所有组合分区表的子分区信息:
select * from DBA_TAB_SUBPARTITIONS
--显示当前用户可访问的所有组合分区表的子分区信息:
select * from ALL_TAB_SUBPARTITIONS
--显示当前用户所有组合分区表的子分区信息:
select * from USER_TAB_SUBPARTITIONS
--显示分区列 显示数据库所有分区表的分区列信息:
select * from DBA_PART_KEY_COLUMNS
--显示当前用户可访问的所有分区表的分区列信息:
select * from ALL_PART_KEY_COLUMNS
--显示当前用户所有分区表的分区列信息:
select * from USER_PART_KEY_COLUMNS
--显示子分区列 显示数据库所有分区表的子分区列信息:
select * from DBA_SUBPART_KEY_COLUMNS
--显示当前用户可访问的所有分区表的子分区列信息:
select * from ALL_SUBPART_KEY_COLUMNS
--显示当前用户所有分区表的子分区列信息:
select * from USER_SUBPART_KEY_COLUMNS
--怎样查询出oracle数据库中所有的的分区表
select * from user_tables a where a.partitioned='YES'
--删除一个表的数据是
truncate table table_name;
--删除分区表一个分区的数据是
alter table table_name truncate partition p5;
注:分区根据具体情况选择。
表分区有以下优点:
1、数据查询:数据被存储到多个文件上,减少了I/O负载,查询速度提高。
2、数据修剪:保存历史数据非常的理想。
3、备份:将大表的数据分成多个文件,方便备份和恢复。
CREATE TABLE T (ID NUMBER PRIMARY KEY, order_no varchar2(40), order_date DATE);
--unable to create INITIAL extent for segment in tablespace hmit_hyt
/*
有2个方法:
1。增大现在表空间数据文件的大小.alter database datafile 'XXX' resize XXXXM;
2。给这个表空间增加数据文件:alter tablespace XXX add datafile 'XXX' size XXXM autoextend on next XXM maxsize XXXXM;
*/
alter tablespace hmit_hyt add DATAFILE 'D:\app\hmit\oradata\mydata\HMIT_HYT_1.DBF' size 500M autoextend on next 50M maxsize 4096M;
INSERT INTO t SELECT ROWNUM , product_no, sys_time FROM warehouse_inventory;
--检查重定义的合理性
EXEC DBMS_REDEFINITION.CAN_REDEF_TABLE('hmit_hyt', 'T', DBMS_REDEFINITION.CONS_USE_PK);--PL/SQL procedure successfully completed
CREATE TABLE T_NEW (ID NUMBER PRIMARY KEY, order_no varchar2(40), order_date DATE) PARTITION BY RANGE (order_date)
(PARTITION P1 VALUES LESS THAN (TO_DATE('2009-7-1', 'YYYY-MM-DD')),
PARTITION P2 VALUES LESS THAN (TO_DATE('2010-3-1', 'YYYY-MM-DD')),
PARTITION P4 VALUES LESS THAN (MAXVALUE));
--如果分区表和原表列名相同,可以用如下方式进行:
BEGIN
DBMS_REDEFINITION.start_redef_table(
uname => 'HMIT_HYT',
orig_table => 't',
int_table => 't_new');
END;
/
--如果分区表的列名和原表不一致,那么在开始重定义的时候,需要重新指定映射关系
EXEC DBMS_REDEFINITION.START_REDEF_TABLE(
'HMIT_HYT',
't',
't_new',
'ID ID, create_date TIME', -- 在这里指定新的映射关系
DBMS_REDEFINITION.CONS_USE_PK);
--不指定表分区查看SALES表信息:
SELECT COUNT(*) FROM t_new;
--指定P1表分区查询SALES表信息:
SELECT COUNT(*) FROM t_new PARTITION(P1) UNION
SELECT COUNT(*) FROM t_new PARTITION(P2) UNION
SELECT COUNT(*) FROM t_new PARTITION(P4);
-- 同步新表,这是可选的操作
SQL> BEGIN
DBMS_REDEFINITION.start_redef_table(
uname => 'HMIT_HYT',
orig_table => 't',
int_table => 't_new');
END;;
/
-- 创建索引,在线重定义只重定义数据,索引还需要单独建立。
create index index_order_no on t_new(order_no);
-- 收集新表的统计信息
exec dbms_stats.gather_table_stats('hmit_hyt', 't_new', cascade => true);
-- 结束重定义
BEGIN
dbms_redefinition.finish_redef_table(
uname => 'hmit_hyt',
orig_table => 't',
int_table => 't_new');
END;
/
--不指定表分区查看SALES表信息:
SELECT COUNT(*) FROM t;
--指定P1表分区查询SALES表信息:
SELECT COUNT(*) FROM t PARTITION(P1) UNION
SELECT COUNT(*) FROM t PARTITION(P2) UNION
SELECT COUNT(*) FROM t PARTITION(P4);
-- 删除临时表
DROP TABLE t_new;
-- 索引重命名
ALTER INDEX index_order_no RENAME TO index_t_order_no;
-- 验证
select partitioned from user_tables where table_name = UPPER('t');
select partition_name from user_tab_partitions where table_name = UPPER('t');
--更改分区表的默认存储表空间
create tablespace HMIT_HYT_t_p1
logging
datafile 'D:\app\hmit\oradata\mydata\HMIT_HYT_t_p1.dbf'
size 32m
autoextend on
next 32m maxsize 2048m
extent management local;
alter table t move partition p1 tablespace HMIT_HYT_T_P1;
SELECT * FROM dba_tab_partitions WHERE table_name ='T';
SELECT * FROM user_tablespaces;
SELECT * FROM user_tables;
-- 拆分分区
--拆分分区将一个分区拆分两个新分区,拆分后原来分区不再存在。注意不能对HASH类型的分区进行拆分。
ALTER TABLE t PARTITION P2 AT(TO_DATE('2010-03-01','YYYY-MM-DD'))
INTO (PARTITION P21,PARTITION P22);
--显示数据库所有分区表的信息:
select * from DBA_PART_TABLES
--显示当前用户可访问的所有分区表信息:
select * from ALL_PART_TABLES
--显示当前用户所有分区表的信息:
select * from USER_PART_TABLES
--显示表分区信息 显示数据库所有分区表的详细分区信息:
select * from DBA_TAB_PARTITIONS
--显示当前用户可访问的所有分区表的详细分区信息:
select * from ALL_TAB_PARTITIONS
--显示当前用户所有分区表的详细分区信息:
select * from USER_TAB_PARTITIONS
--显示子分区信息 显示数据库所有组合分区表的子分区信息:
select * from DBA_TAB_SUBPARTITIONS
--显示当前用户可访问的所有组合分区表的子分区信息:
select * from ALL_TAB_SUBPARTITIONS
--显示当前用户所有组合分区表的子分区信息:
select * from USER_TAB_SUBPARTITIONS
--显示分区列 显示数据库所有分区表的分区列信息:
select * from DBA_PART_KEY_COLUMNS
--显示当前用户可访问的所有分区表的分区列信息:
select * from ALL_PART_KEY_COLUMNS
--显示当前用户所有分区表的分区列信息:
select * from USER_PART_KEY_COLUMNS
--显示子分区列 显示数据库所有分区表的子分区列信息:
select * from DBA_SUBPART_KEY_COLUMNS
--显示当前用户可访问的所有分区表的子分区列信息:
select * from ALL_SUBPART_KEY_COLUMNS
--显示当前用户所有分区表的子分区列信息:
select * from USER_SUBPART_KEY_COLUMNS
--怎样查询出oracle数据库中所有的的分区表
select * from user_tables a where a.partitioned='YES'
--删除一个表的数据是
truncate table table_name;
--删除分区表一个分区的数据是
alter table table_name truncate partition p5;
注:分区根据具体情况选择。
表分区有以下优点:
1、数据查询:数据被存储到多个文件上,减少了I/O负载,查询速度提高。
2、数据修剪:保存历史数据非常的理想。
3、备份:将大表的数据分成多个文件,方便备份和恢复。
4、并行性:可以同时向表中进行DML操作,并行性性能提高。