浅谈Oracle分区表之范围分区
来源:互联网 发布:杭州淘宝美工招聘信息 编辑:程序博客网 时间:2024/05/22 22:30
- SQL> begin
- 2 for i in 1..10000
- 3 loop
- 4 insert into sale_data values (i,'yang',sysdate);
- 5 commit;
- 6 end loop;
- 7* end;
- PL/SQL procedure successfully completed.
- SQL> begin
- 2 for i in 1..10000
- 3 loop
- 4 insert into sale_data values (i,'yang',sysdate+60);
- 5 commit;
- 6 end loop;
- 7* end;
- PL/SQL procedure successfully completed.
- SQL> exec dbms_stats.gather_table_stats('SALE','SALE_DATA',CASCADE => TRUE);
- PL/SQL procedure successfully completed.
- SQL> @?/sqlplus/admin/plustrce.sql;
- SQL>
- SQL> drop role plustrace;
- drop role plustrace
- *
- ERROR at line 1:
- ORA-01919: role 'PLUSTRACE' does not exist
- SQL> create role plustrace;
- Role created.
- SQL> grant select on v_$sesstat to plustrace;
- Grant succeeded.
- SQL> grant select on v_$statname to plustrace;
- Grant succeeded.
- SQL> grant select on v_$mystat to plustrace;
- Grant succeeded.
- SQL> grant plustrace to dba with admin option;
- Grant succeeded.
- SQL> set echo off
- SQL> grant plustrace to sale;
- Grant succeeded.
- SQL> select count(*) from sale_data partition(sales_03);
- COUNT(*)
- ----------
- 10000
- SQL> select count(*) from sale_data partition(sales_05);
- COUNT(*)
- ----------
- 10000
- SQL> select * from sale_data partition(sales_03) where rownum=1;
- SALE_ID SALESMAN_NAME SALES_DATE
- ---------- ------------------------------ -------------------
- 1 yang 2012-03-12:20:04:31
- SQL> select * from sale_data partition(sales_05) where rownum=1;
- SALE_ID SALESMAN_NAME SALES_DATE
- ---------- ------------------------------ -------------------
- 1 yang 2012-05-11:20:08:45
- SQL> alter table sale_data truncate partition sales_03;
- Table truncated.
- SQL> select count(*) from sale_data partition(sales_03);
- COUNT(*)
- ----------
- 0
- SQL> alter table sale_data add partition sales_13 values less than (to_date('01/02/2013','DD/MM/YYYY')) tablespace tbs_sale12;
- Table altered.
- SQL> alter table sale_data add partition sales_14 values less than (maxvalue) tablespace tbs_sale12;
- Table altered.
- SQL> select segment_name,partition_name,tablespace_name from user_segments where segment_name='IND_SALE_DATA_DATE' and partition_name in ('SALES_13','SALES_14');
- SEGMENT_NAME PARTITION_NAME TABLESPACE_NAME
- -------------------- -------------------- ------------------------------
- IND_SALE_DATA_DATE SALES_13 TBS_SALE12
- IND_SALE_DATA_DATE SALES_14 TBS_SALE12
- SQL> alter table sale_data drop partition sales_13;
- Table altered.
- SQL> alter table sale_data drop partition sales_14;
- Table altered.
- SQL> select segment_name,partition_name,tablespace_name from user_segments where segment_name='IND_SALE_DATA_DATE' and partition_name in ('SALES_13','SALES_14');
- no rows selected.
- SQL> create table emp (id number,first_name char(10))
- 2 partition by range(id)
- 3 (
- 4 partition p_1 values less than (10000) tablespace tbs_sale01,
- 5 partition p_2 values less than (20000) tablespace tbs_sale02,
- 6* partition p_3 values less than (30000) tablespace tbs_sale03)
- Table created.
- SQL> begin
- 2 for i in 1..20000
- 3 loop
- 4 insert into emp values (i,'t_i');
- 5 commit;
- 6 end loop;
- 7* end;
- PL/SQL procedure successfully completed.
- SQL> insert into emp values (30001,'t');
- insert into emp values (30001,'t')
- *
- ERROR at line 1:
- ORA-14400: inserted partition key does not map to any partition
- SQL> alter table emp add partition p_max values less than (maxvalue) tablespace tbs_sale04;
- Table altered.
- SQL> insert into emp values (30001,'t');
- 1 row created.
- SQL> commit;
- Commit complete.
- SQL> select * from emp partition(p_max);
- ID FIRST_NAME
- ---------- ----------
- 30001 t
- SQL> select partition_name,tablespace_name from user_segments where segment_name='EMP';
- PARTITION_NAME TABLESPACE_NAME
- ------------------------------ ------------------------------
- P_1 TBS_SALE01
- P_2 TBS_SALE02
- P_3 TBS_SALE03
- P_MAX TBS_SALE04
- SQL> alter table emp split partition p_1 at('9999') into (partition p_1_01 tablespace tbs_sale02,partition p_1_02 tablespace tbs_sale03);
- Table altered.
- SQL> select partition_name,tablespace_name from user_segments where segment_name='EMP';
- PARTITION_NAME TABLESPACE_NAME
- ------------------------------ ------------------------------
- P_2 TBS_SALE02
- P_3 TBS_SALE03
- P_MAX TBS_SALE04
- P_1_01 TBS_SALE02
- P_1_02 TBS_SALE03
- SQL> select count(*) from emp partition(p_1_01);
- COUNT(*)
- ----------
- 9998
- SQL> select count(*) from emp partition(p_1_02);
- COUNT(*)
- ----------
- 1
- 由此可见,at关键字指的是在这个点进行拆分,且包含这个点!
- SQL> alter table emp merge partitions p_1_01,p_1_02 into partition p_01;
- Table altered.
- SQL> select partition_name,tablespace_name from user_segments where segment_name='EMP';
- PARTITION_NAME TABLESPACE_NAME
- ------------------------------ ------------------------------
- P_01 USERS
- P_2 TBS_SALE02
- P_3 TBS_SALE03
- P_MAX TBS_SALE04
- SQL> select PARTITION_NAME ,TABLESPACE_NAME from user_segments where segment_name='EMP';
- PARTITION_NAME TABLESPACE_NAME
- ------------------------------ ------------------------------
- P_1 TBS_SALE01
- P_2 TBS_SALE02
- P_3 TBS_SALE03
- P_MAX TBS_SALE04
- SQL> select count(*) from emp partition (p_1);
- COUNT(*)
- ----------
- 9999
- SQL> select count(*) from emp partition (p_2);
- COUNT(*)
- ----------
- 10000
- SQL> select count(*) from emp partition (p_3);
- COUNT(*)
- ----------
- 1
- SQL> select count(*) from emp partition (p_max);
- COUNT(*)
- ----------
- 1
- SQL> conn /as sysdba
- Connected.
- SQL> create directory dir01 as '/home/oracle/dir01';
- Directory created.
- SQL> grant read,write on directory dir01 to sale;
- Grant succeeded.
- [oracle@rhel6 ~]$ expdp help=y
- Example: expdp scott/tiger DUMPFILE=scott.dmp DIRECTORY=dmpdir SCHEMAS=scott
- or TABLES=(T1:P1,T1:P2), if T1 is partitioned table
- [oracle@rhel6 ~]$ expdp sale/123456 directory=dir01 dumpfile=emp_p1.dmp logfile=emp_p1.log tables=emp:p_1
- Export: Release 10.2.0.1.0 - 64bit Production on Tuesday, 13 March, 2012 21:06:13
- Copyright (c) 2003, 2005, Oracle. All rights reserved.
- Connected to: Oracle Database 10g Enterprise Edition Release 10.2.0.1.0 - 64bit Production
- With the Partitioning, OLAP and Data Mining options
- Starting "SALE"."SYS_EXPORT_TABLE_01": sale/******** directory=dir01 dumpfile=emp_p1.dmp logfile=emp_p1.log tables=emp:p_1
- Estimate in progress using BLOCKS method...
- Processing object type TABLE_EXPORT/TABLE/TABLE_DATA
- Total estimation using BLOCKS method: 256 KB
- Processing object type TABLE_EXPORT/TABLE/TABLE
- Processing object type TABLE_EXPORT/TABLE/STATISTICS/TABLE_STATISTICS
- . . exported "SALE"."EMP":"P_1" 190.5 KB 9999 rows
- Master table "SALE"."SYS_EXPORT_TABLE_01" successfully loaded/unloaded
- ******************************************************************************
- Dump file set for SALE.SYS_EXPORT_TABLE_01 is:
- /home/oracle/dir01/emp_p1.dmp
- Job "SALE"."SYS_EXPORT_TABLE_01" successfully completed at 21:06:17
- [oracle@rhel6 ~]$ sqlplus sale/123456
- SQL*Plus: Release 10.2.0.1.0 - Production on Tue Mar 13 21:07:18 2012
- Copyright (c) 1982, 2005, Oracle. All rights reserved.
- Connected to:
- Oracle Database 10g Enterprise Edition Release 10.2.0.1.0 - 64bit Production
- With the Partitioning, OLAP and Data Mining options
- SQL> alter table emp truncate partition p_1;
- Table truncated.
- SQL> select count(*) from emp partition (p_1);
- COUNT(*)
- ----------
- 0
- [oracle@rhel6 ~]$ impdp sale/123456 directory=dir01 dumpfile=emp_p1.dmp logfile=emp_p1.log2 table_exists_action=append tables=emp:p_1
- Import: Release 10.2.0.1.0 - 64bit Production on Tuesday, 13 March, 2012 21:29:58
- Copyright (c) 2003, 2005, Oracle. All rights reserved.
- Connected to: Oracle Database 10g Enterprise Edition Release 10.2.0.1.0 - 64bit Production
- With the Partitioning, OLAP and Data Mining options
- Master table "SALE"."SYS_IMPORT_TABLE_01" successfully loaded/unloaded
- Starting "SALE"."SYS_IMPORT_TABLE_01": sale/******** directory=dir01 dumpfile=emp_p1.dmp logfile=emp_p1.log2 table_exists_action=append tables=emp:p_1
- Processing object type TABLE_EXPORT/TABLE/TABLE
- ORA-39152: Table "SALE"."EMP" exists. Data will be appended to existing table but all dependent metadata will be skipped due to table_exists_action of append
- Processing object type TABLE_EXPORT/TABLE/TABLE_DATA
- . . imported "SALE"."EMP":"P_1" 190.5 KB 9999 rows
- Processing object type TABLE_EXPORT/TABLE/STATISTICS/TABLE_STATISTICS
- Job "SALE"."SYS_IMPORT_TABLE_01" completed with 1 error(s) at 21:30:01
- SQL> conn sale/123456
- Connected.
- SQL> select count(*) from emp partition (p_1);
- COUNT(*)
- ----------
- 9999
- SQL> create table emp_exchange as select * from emp where 1=0;
- Table created.
- SQL> select count(*) from emp_exchange;
- COUNT(*)
- ----------
- 0
- SQL> select count(*) from emp partition(p_1);
- COUNT(*)
- ----------
- 9999
- SQL> alter table emp exchange partition p_1 with table emp_exchange;
- Table altered.
- SQL> select count(*) from emp partition(p_1);
- COUNT(*)
- ----------
- 0
- SQL> select count(*) from emp_exchange;
- COUNT(*)
- ----------
- 9999
- SQL> select tablespace_name from user_segments where segment_name='EMP_EXCHANGE';
- TABLESPACE_NAME
- ------------------------------
- TBS_SALE01
- SQL> select tablespace_name from user_segments where segment_name='EMP';
- TABLESPACE_NAME
- ------------------------------
- USERS
- TBS_SALE02
- TBS_SALE03
- TBS_SALE04
0 0
- 浅谈Oracle分区表之范围分区
- Oracle--分区表(范围分区、Hash分区、等)
- Oracle--分区表(范围分区、Hash分区、等)
- MySQL分区表--列范围分区
- oracle超大数据库表分区只复合分区表(范围散列分区表)
- ORACLE分区表、分区索引
- ORACLE分区表、分区索引
- Oracle 分区表增加分区
- oracle 分区表,分区索引
- ORACLE分区表、分区索引
- ORACLE分区表、分区索引
- ORACLE分区表、分区索引
- Oracle分区表 linst分区
- oracle分区之包含模板的'范围-列表分区'例子
- 获取分区表中分区名称和范围
- oracle分区表之交换分区 altertable exchange partition with table
- oracle分区表之交换分区 altertable exchange partition with table
- oracle分区表之交换分区 altertable exchange partition with table
- VS2008的MFC中CtreeCtrl默认选中第一个子节点
- 使用JDBC链接数据库之JDBC简介
- Caused by: java.lang.ClassNotFoundException: org.jboss.logging.BasicLogger 解决办法
- android conatcts搜索时偶现列表刷不出来
- [leetcode 21] Merge Two Sorted Lists
- 浅谈Oracle分区表之范围分区
- android如何让service不被杀死
- I2C总线
- 微软公有云事件中心(Azure Event Hubs)在开放物联网大会(OIOT)啼声初试
- java 23种常用设计模式之享元模式(FlyWeight)
- 修改oracle数据文件和表空间名字
- Unity抛物线的脚本
- 运动目标检测、阴影检测及目标跟踪中用得到的标准测试视频下载
- Class-forName和ClassLoader的区别