Oracle建表空间、建表、索引、序列

建表空间攻略

• 创建临时表空间

create temporary tablespace user_temp tempfile '[全路径]' size 1G autoextend off;

• 创建数据表空间

create tablesspace user_data datafile '[全路径]' size 1G autoextend off;

• 创建用户并且指定表空间

create user username identified by password default tablespace user_data temporary tablespace user_temp;

• 为创建的用户授权

授予系统权限：

grant create session to username;

授予角色权限：

grant dba to username;

授予表权限：

grant select, delete, insert, update on owner.tablename to username;

建表攻略

• 建表

create table owner.tablename (
UID number not null,
ID number not null,
TestID number not null，
Name varchar2(100 bytes) not null,
Name2 varchar2(150 bytes) not null,
Describe varchar2(250 bytes) not null default 'This is value of teesting'
) tablespace tablespacename;

• 建唯一索引(UID建立唯一索引,可指定tablepspace)

create unique index owner.ux_owner_tablename_UID on owner.tablename(UID);

• 建联合索引(Name、Name2建立联合索引)

create index owner.ix_owner_tablename_Name_Name2 on owner.tablename(Name, Name2);

• 建立序列

create sequence owner.seq_owner_tablename_UID maxvalue 999999999999 start with 2000 increment by 1 cache 1000;

• 删除表、删除索引

drop table owner.tablename;
drop index owner.ux_owner_tablename_UID;