创建表样例

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;