创建表样例

create or replace procedure table_procedure
AS
i_count integer;
begin
  select count(*) into i_count from user_objects t where t.OBJECT_TYPE ='TABLE' and t.OBJECT_NAME = upper('tableName');
    if i_count>0 then
        EXECUTE IMMEDIATE 'drop table tableName purge';
    end if;
end table_procedure;
/
call table_procedure();
drop procedure table_procedure;

--定义索引表空间
define idx_tablespace = 'indexname';

1.--创建未分区的表
create table tablename(
  DEVID    NUMBER(12) not null,
  ISBACKUP NUMBER(12) not null
)
;
--为表添加约束(此处为主键约束)
alter table tablename add constraint constraint_name primary key (DEVID);

-- 为表添加注释
comment on table tablename
  is '表的描述';
-- 为列添加注释
comment on column tablename.columnname
  is '未使用';

2.--创建分区的表
create table tablename(
  ID         NUMBER(10),
  ALARMINDEX NUMBER(32),
  DEVICEID   NUMBER(10)
)
partition by range (ALARMTIME)
interval (numtodsinterval(1, 'DAY'))
(
  --创建分区时初始创建一个默认分区
  partition dt0 values less than (to_date('2011-01-15','yyyy-mm-dd'))
)
;

3.--创建带索引的分区表
create table T_Tbale_INDEXHIS
(
  BEGINTIME    TIMESTAMP(9),
  TASKID       NUMBER(38),
  DEVICEID     NUMBER(38),
  BFRAMERESUME NUMBER(38,6)
)
partition by range (BEGINTIME)
interval (numtodsinterval(1, 'DAY'))
(
  partition name values less than (to_date('2011-01-15','yyyy-mm-dd'))
);
--创建分区索引(局部索引)
create index IDX_STAT_PB_INDEXHIS on T_Tbale_INDEXHIS
(taskid, deviceid) tablespace &idx_tablespace local;

4.--创建hash分区
create table T_table_STB
(
  STBID           VARCHAR2(64),
  USERID          VARCHAR2(128),
  POPID           NUMBER(12),
  MESSAGEVERSION  NUMBER(5)
)
partition by hash (POPID)
(
  partition P1,
  partition P2,
  partition P3
)
;
--打开row movement(目的是提高分区表的灵活性——允许更新Partition Key)
alter table T_table_STB enable row movement;