查看所有用户分区表及分区策略(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/，如需转载，请注明出处，否则将追究法律责任。
转载于:http://blog.itpub.net/7899089/viewspace-753535/
496
被折叠的 条评论
为什么被折叠?
到【灌水乐园】发言
