oracle 一些比较实用的用法

--如何扩展分区

ALTER TABLE T_C_PICS_LOCAL_SEARCHRESULT SPLIT PARTITION P_MAX_VALUE AT (TO_DATE(' 2013-03-11 00:00:00', 'SYYYY-MM-DD HH24:MI:SS', 'NLS_CALENDAR=GREGORIAN')) INTO (PARTITION P201303_1, PARTITION P_MAX_VALUE);
ALTER TABLE T_C_PICS_LOCAL_SEARCHRESULT SPLIT PARTITION P_MAX_VALUE AT (TO_DATE(' 2013-03-21 00:00:00', 'SYYYY-MM-DD HH24:MI:SS', 'NLS_CALENDAR=GREGORIAN')) INTO (PARTITION P201303_2, PARTITION P_MAX_VALUE);

ALTER TABLE T_C_PICS_LOCAL_SEARCHRESULT SPLIT PARTITION P_MAX_VALUE AT (TO_DATE(' 2013-03-31 00:00:00', 'SYYYY-MM-DD HH24:MI:SS', 'NLS_CALENDAR=GREGORIAN')) INTO (PARTITION P201303_3, PARTITION P_MAX_VALUE);

--创建分区索引

create index IDX_PICS_BIZMSG_CALLERNO_N on T_C_PICS_BIZMSG_SENDHIS (ACCEPTNO) local tablespace TBLSP_HANDSETALL_IDX1;

--普通表修改成分区表

-- 重命名表 T_C_PICS_BIZMSG_SENDHIS
RENAME T_C_PICS_BIZMSG_SENDHIS TO T_C_PICS_BIZMSG_SENDHIS_O;

-- 建立分区表
create table T_C_PICS_BIZMSG_SENDHIS
(
  ID         VARCHAR2(20),
  CREATETIME DATE,
  CONTENT    CLOB,
  DYNCHAR10  VARCHAR2(500)
)
partition by range (CREATETIME)
(
  partition P201201_1 values less than (TO_DATE(' 2012-01-11 00:00:00', 'SYYYY-MM-DD HH24:MI:SS', 'NLS_CALENDAR=GREGORIAN'))
    tablespace TBLSP_HANDSETALL_GROUP1,
  partition P_MAX_VALUE values less than (MAXVALUE)
    tablespace TBLSP_HANDSETALL_GROUP1
);

--导入原表数据到新的分区表

--查询表占的空间

SELECT T.OWNER,
               T.SEGMENT_NAME,
               TRUNC(SUM(T.BYTES) / 1024 / 1024) AS TOTAL
          FROM DBA_SEGMENTS T
         WHERE T.SEGMENT_TYPE LIKE 'TABLE%'
         GROUP BY T.OWNER, T.SEGMENT_NAME
        HAVING SUM(T.BYTES) / 1024 < 500 * 1024

--创建表空间

create tablespace tests datafile 'D:\test\test.dbf' size 500M autoextend on uniform size 20M;

--导出dmp文件

exp 'handsetall/"4rfv$RFV"'@telesale file=/arc2/handsetall_data.dmp log=/arc2/handsetall_data.log parfile=/arc2/handsetall_500 buffer=81920000

parfile=/arc2/handsetall_500 代表使用文件中的内容作为参数

-- 导入

imp 'handsetall/"4rfv$RFV"'@to_222 fromuser=handsetall touser=handsetall file=/arc2/handsetall_data.dmp log=/arc2/handsetall_data_imp.log ignore=y buffer=40960000 rows=y