ITPub博客

首页 > Linux操作系统 > Linux操作系统 > Oracle分区表

Oracle分区表

原创 Linux操作系统 作者:keeking 时间:2013-01-29 22:07:44 0 删除 编辑

查看所有用户分区表及分区策略(1、2级分区表均包括):



select dbms_metadata.get_ddl('TABLE','T_WELCOME_CALL_LIST') from DUAL

select dbms_metadata.get_ddl('TABLE','T_USER_ACTION_LOG','REPORT_STAGE') from DUAL


SELECT p.table_name AS 表名, decode(p.partitioning_key_count, 1, '主分区') AS 分区类型,
p.partitioning_type AS 分区类型, p.column_name AS 分区键,
decode(nvl(q.subpartitioning_key_count, 0), 0, '无子分区', 1, '子分区') AS 有无子分区,
q.subpartitioning_type AS 子分区类型, q.column_name AS 子分区键
FROM (SELECT a.table_name, a.partitioning_type, b.column_name, a.partitioning_key_count
FROM user_part_tables a, user_part_key_columns b
WHERE a.table_name = b.NAME
AND b.object_type = 'TABLE') p,
(SELECT a.table_name, a.subpartitioning_type, b.column_name, a.subpartitioning_key_count
FROM user_part_tables a, user_subpart_key_columns b
WHERE a.table_name = b.NAME
AND a.subpartitioning_key_count <> 0
AND b.object_type = 'TABLE') q
WHERE p.table_name = q.table_name(+)
ORDER BY 5,4,1;

显示数据库所有分区表的信息:DBA_PART_TABLES

显示当前用户可访问的所有分区表信息:ALL_PART_TABLES

显示当前用户所有分区表的信息:USER_PART_TABLES

显示表分区信息显示数据库所有分区表的详细分区信息:DBA_TAB_PARTITIONS

显示当前用户可访问的所有分区表的详细分区信息:ALL_TAB_PARTITIONS

显示当前用户所有分区表的详细分区信息:USER_TAB_PARTITIONS

显示子分区信息显示数据库所有组合分区表的子分区信息:DBA_TAB_SUBPARTITIONS

显示当前用户可访问的所有组合分区表的子分区信息:ALL_TAB_SUBPARTITIONS

显示当前用户所有组合分区表的子分区信息:USER_TAB_SUBPARTITIONS

显示分区列显示数据库所有分区表的分区列信息:DBA_PART_KEY_COLUMNS

显示当前用户可访问的所有分区表的分区列信息:ALL_PART_KEY_COLUMNS

显示当前用户所有分区表的分区列信息:USER_PART_KEY_COLUMNS

显示子分区列显示数据库所有分区表的子分区列信息:DBA_SUBPART_KEY_COLUMNS

显示当前用户可访问的所有分区表的子分区列信息:ALL_SUBPART_KEY_COLUMNS

显示当前用户所有分区表的子分区列信息: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;

如果我要将分区表中各个分区的数据都清空,可以用truncate table table_name直接删除;

也可以用:

alter table table_name truncate partition p1;

alter table table_name truncate partition p2;

alter table table_name truncate partition p3;

alter table table_name truncate partition p4;

alter table table_name truncate partition p5;

alter table table_name truncate partition p6;

逐个删除。

oracle分区表新增主分区及子分区语句

CREATE TABLE dw_account_logout_info
(
    logout_time DATE NOT NULL ,
    gateway_id INTEGER NOT NULL ,
    online_time NUMBER NOT NULL ,
    game_id INTEGER NOT NULL ,
    account VARCHAR2(32) NOT NULL ,
    role VARCHAR2(20) NULL
)
PARTITION BY RANGE (logout_time)
SUBPARTITION BY LIST (game_id)
(
PARTITION par_12_2009 VALUES LESS THAN(TO_DATE('2010-01-01','yyyy-mm-dd'))
(SUBPARTITION logout_122009_jx2wz VALUES (7) TABLESPACE logout_122009_jx2wz,
SUBPARTITION logout_122009_fs3 VALUES (11) TABLESPACE logout_122009_fs3),
PARTITION par_01_2010 VALUES LESS THAN(TO_DATE('2010-02-01','yyyy-mm-dd'))
(SUBPARTITION logout_012010_jx2wz VALUES (7) TABLESPACE logout_012010_jx2wz,
SUBPARTITION logout_012010_fs3 VALUES (11) TABLESPACE logout_012010_fs3))
新增主分区及子分区:
alter table DW_ACCOUNT_LOGOUT_INFO
  add partition PAR_06_2011 values less than (TO_DATE(' 2011-07-01 00:00:00', 'SYYYY-MM-DD HH24:MI:SS', 'NLS_CALENDAR=GREGORIAN'))
    tablespace DW_TBS
    storage
    (
      initial 64K
      minextents 1
      maxextents unlimited
    )
  (
    subpartition LOGOUT_052011_JX3 values (2) tablespace LOGOUT_062011_JX3
  );
单独新增子分区:
alter table DW_ACCOUNT_LOGOUT_INFO
  modify partition PAR_12_2009
    add subpartition LOGOUT_122009_JX3 values (2) tablespace logout_122009_jx3;
alter table DW_ACCOUNT_LOGOUT_INFO
  modify partition PAR_01_2010
    add subpartition LOGOUT_012010_JX3 values (2) tablespace logout_012010_jx3;


1、增加一个分区
ALTER TABLE sales
      ADD PARTITION jan96 VALUES LESS THAN ( '01-FEB-1999' )
      TABLESPACE tsx;
增加一个列表分区
ALTER TABLE q1_sales_by_region
   ADD PARTITION q1_nonmainland VALUES ('HI', 'PR')
      STORAGE (INITIAL 20K NEXT 20K) TABLESPACE tbs_3
      NOLOGGING;
2、合并分区

alter table dept coalesce partition

3、删除分区
ALTER TABLE sales DROP PARTITION dec98;
ALTER INDEX sales_area_ix REBUILD(如果含有全局索引);

4、合并分区
ALTER TABLE four_seasons
MERGE PARTITIONS quarter_one, quarter_two INTO PARTITION quarter_two;


Then, rebuild the local index for the affected partition.

-- Rebuild index for quarter_two, which has been marked unusable
-- because it has not had all of the data from Q1 added to it.
-- Rebuilding the index will correct this.
--
ALTER TABLE four_seasons MODIFY PARTITION
quarter_two REBUILD UNUSABLE LOCAL INDEXES;

5、移动一个分区
ALTER TABLE parts MOVE PARTITION depot2
     TABLESPACE ts094 NOLOGGING;

6、重建一个local索引
ALTER INDEX I_FOUR_SEASONS_L  REBUILD PARTITION I_QUARTER_FOUR

7、重命名一个分区
ALTER TABLE scubagear RENAME PARTITION sys_p636 TO tanks

8、一个分区拆分为两个分区
ALTER TABLE RANGE_EXAMPLE SPLIT PARTITION
      PART_1 at
      (TO_DATE(' 1994-05-01 00:00:00', 'SYYYY-MM-DD HH24:MI:SS'))
       INTO ( PARTITION  PART_1 tablespace st1,
        PARTITION  PART_3 tablespace users)

9、truncate一个分区
ALTER TABLE sales TRUNCATE PARTITION dec98;
ALTER INDEX sales_area_ix REBUILD;

10、如果存在约束的情况,先disable约束
ALTER TABLE sales
    DISABLE CONSTRAINT dname_sales1;
ALTER TABLE sales TRUNCATE PARTITTION dec94;
ALTER TABLE sales
    ENABLE CONSTRAINT dname_sales1;

--------------------------------------------------------------

1. create table tmp_test

( user_name varchar2(40),

  start_date  date

)

partition by range(start_date)       --选择分区的日期字段

interval(numtoyminterval(1,'MONTH')) --选择按月自动创建分区( partition P01 values less than ( to_Date(20110101,'yyyymmdd') )

--将20110101之前的数据放入初始分区P01

           );


2. partition by range(sales_date)--指定分区方法以及分区列

     (

      Partition part_01 values less than(to_date('2008-04-01','yyyy-mm-dd')) tablespace space01,

      Partition part_02 values less than(to_date('2008-07-01','yyyy-mm-dd')) tablespace space02,

      Partition part_03 values less than(to_date('2008-10-01','yyyy-mm-dd')) tablespace space03,

      Partition part_04 values less than(maxvalue) tablespace space04

     );


3. 按数字自动分区

partition by range (CONTRACT_ID)  

  INTERVAL (100000)      

  (  

  partition p01 values less than (100001)  

);


ALTER TABLE SMS_AUTO_RESULT SET INTERVAL(numtoyminterval(1,'MONTH')); -- 按月分区


4. alter table PINE.CFC_ORK_TAPE_CC add Partition CFC_ORK_TAPE_CC_P50 

values less than (TO_DATE(' 2012-02-01 00:00:00', 'SYYYY-MM-DD HH24:MI:SS', 'NLS_CALENDAR=GREGORIAN')) tablespace REPORT;


5. create table SMS_AUTO_RESULT

(

  SMS_ID       NUMBER not null,

  SEND_DATE    DATE,

  CS_PHONE     VARCHAR2(200 CHAR),

  CONTRACT_ID  NUMBER(20) not null,

  TEXT         VARCHAR2(2000),

  V0           VARCHAR2(100),

  V1           VARCHAR2(100),

  V2           VARCHAR2(100),

  V3           VARCHAR2(100),

  V4           VARCHAR2(100),

  V5           VARCHAR2(100),

  V6           VARCHAR2(100),

  V7           VARCHAR2(100),

  V8           VARCHAR2(100),

  V9           VARCHAR2(100),

  SMS_PRIORITY NUMBER(3),

  TEMPLET_NAME VARCHAR2(30),

  DEPT_NAME    VARCHAR2(10),

  COMPANY      VARCHAR2(10),

  LCU          VARCHAR2(60) default 'PKG_SMS_AUTOMATION_CO/PKG_SMS_AUTOMATION_OPS',

  LCD          DATE default sysdate

)    

 partition by range(SEND_DATE)       --选择分区的日期字段    

 interval(numtodsinterval(1,'DAY')) --选择按月自动创建分区 

 (

   PARTITION p01 values LESS THAN (TO_DATE('2012-03-19','YYYY-MM_DD'))

)


6.增加分区表

  alter table BILL_MONTHFEE_ZERO add Partition p_200409

  values less than (200409) tablespace ts_ziken;

  --删除一分区

  alter table part_tbl drop Partition part_tbl_08;

  --将一个分区分为两个分区

  alter table bill_monthfee_zero split Partition p_200409 at (200409)

  into (Partition p_200409_1 tablespace ts_ziken,

  Partition p_200409_2 tablespace ts_ziken_idx);

  --合并分区

  ALTER TABLE bill_monthfee_zero

  MERGE PARTITIONS p_200408, p_200409 INTO PARTITION p_all

  --将分区改名

  alter table bill_monthfee_zero rename Partition p_200408 to p_fee_200408

  --将分区改表空间

  alter table bill_monthfee_zero move Partition p_200409

  tablespace ts_ziken_01 nologging

  --查询特定分区

  select count(*) from BILL_MONTHFEE_ZERO partition (p_200407);

  --添加数据

  insert into bill_monthfee_zero select * from bill_monthfee_zero partition (p_200407)

  --分区表的导出

  userid=dxsq/teledoone@jndxsq154

  buffer=102400

  tables=bill_monthfee:P_200401,

  file=E:\exp_para\exp_dxsq_tables.dmp

  log=E:\exp_para\exp_dxsq_tables.log

  技巧:

  删除表中一个字段:

  alter table bill_monthfee_zero set unused column date_type;

  添加一个字段:alter table bill_monthfee_zero add date_type number(1);


来自 “ ITPUB博客 ” ,链接:http://blog.itpub.net/7899089/viewspace-753535/,如需转载,请注明出处,否则将追究法律责任。

请登录后发表评论 登录
全部评论

注册时间:2009-07-23

  • 博文量
    194
  • 访问量
    242135