当前位置:   article > 正文

Oracle12c中表的分区

oracle12c range 分区表
切换用户时最好用su - 用户名 进入

创建新的用户名及密码:
SQL>create user abc identified by abc;
切换用户:
SQL>conn abc/abc;
切换最高用户:
SQL>conn / as sysdba;
为用户添加权限:
SQL>grant dba to abc;

sqlplus / as sysdba 已sysdba用户进入数据库
相关命令:
desc 表名 列出表的各个字段

查询当前用户下的表:
SQL>select tname from tab;

对表的分区:
例如:创建表:SQL> CREATE TABLE test2(userId number(8), username varchar2(40), tep number (20)) PARTITION BY RANGE (userId)(PARTITION p1 VALUES LESS THAN (10000), PARTITION p2 VALUES LESS THAN (20000), PARTITION p3 VALUES LESS THAN (30000) );

对表增加两个区:SQL> ALTER TABLE test2 ADD PARTITION p4 VALUES LESS THAN (35000), PARTITION p5 VALUES LESS THAN (40000);

撤销多个分区:
SQL> ALTER TABLE test2 DROP PARTITIONS p4,p5;
合并多个分区:
SQL> ALTER TABLE test2 TRUNCATE PARTITONS p4,p5;

要保持索引更新,使用UPDATE INDEXES或UPDATE GLOBAL INDEXES语句
SQL> ALTER TABLE test2 DROP PARTITIONS p4,p5 UPDATE GLOBAL INDEXES;
SQL> ALTER TABLE test2 TRUNCATE PARTITIONS p4,p5 UPDATE GLOBAL INDEXES;

将单个分区分割为多个新分区:
SQL> CREATE TABLE test2 (userId number(8), username varchar2(40), tep number (20)) PARTITION BY RANGE (sal) (PARTITION p1 VALUES LESS THAN (10000), PARTITION p2 VALUES LESS THAN (20000), PARTITION p_max (MAXVALUE) );
SQL> ALTER TABLE test2 SPLIT PARTITION p_max INTO (PARTITION p3 VALUES LESS THAN (25000), PARTITION p4 VALUES LESS THAN (30000), PARTITION p_max);

将多个分区合并为一个分区:
SQL> ALTER TABLE test2 MERGE PARTITIONS p3,p4,p5 INTO PARTITION p_merge;

如果分区范围形成序列,你可以使用如下示例:
SQL> ALTER TABLE test2 MERGE PARTITIONS p3 TO p5 INTO PARTITION p_merge;

查看当前用户所有分区表分区详细信息:
SQL> select table_name,partition_name from user_tab_partitions;

显示当前用户所有分区表的信息
SQL> select table_name from user_part_tables;
本文内容由网友自发贡献,转载请注明出处:https://www.wpsshop.cn/w/天景科技苑/article/detail/892690
推荐阅读
相关标签
  

闽ICP备14008679号