Oracle创建数据表空间,用户,授权,表,索引,簇 2008-10-11 16:03:43
标签:Oracle
//创建临时表空间(日志文件)
create temporary tablespace test_temp tempfile 'E:\oracle\test_temp01.dbf' size 32m autoextend on next 32m maxsize 2048m extent management local; //创建数据表空间
create tablespace test_data logging datafile 'E:\oracle\test_data01.dbf' size 32m autoextend on next 32m maxsize 2048m extent management local; //创建用户并指定表空间
create user 用户名 identified by 密码 default tablespace test_data temporary tablespace test_temp; //给用户授予权限
grant connect,resource to 用户名
————————————————————————————————————
create cluster s_t_cluster(tid number(8));--创建索引簇
----------------------------------------------------1 --建立簇表stu create table stu( tid number(8), sname varchar2(50), sinfo varchar2(50), constraint pk_tid primary key(tid) )cluster s_t_cluster(tid) --建立簇表sc create table sc( tid number(8), score number(8), constraint fk_tid foreign key(tid) references stu(tid) )cluster s_t_cluster(tid) ------------------------------------------------------2 --建立簇索引 create index s_t_idx on cluster s_t_cluster; ------------------------------------------------------3 --插入数据 insert into stu(tid,sname,sinfo)values(1,'haha','usa'); insert into stu(tid,sname,sinfo)values(2,'gaga','Japan'); insert into sc values(1,90); insert into sc values(2,85); --查询 select s.sname,s.sinfo,i.score from stu s,sc i where s.tid=i.tid --建立序列 create sequence stu_SEQ minvalue 1 maxvalue 99999999 start with 3 increment by 1 cache 20; --用命令插入N条数据往表stu
declare x number; begin for x in 1..100000 loop insert into stu values(stu_seq.nextval,'名字'||x,'地址'||x); end loop; end; --------------------------------------------- --用命令插入N条数据往表sc declare x number; begin for x in 1..10000 loop insert into sc values(sc_seq.nextval,x+50); end loop; end; --------------------------------------------- --查询 select s.sname,s.sinfo,i.score from stu s,sc i where s.tid=i.tid--未加索引时的普通查询太慢了 --使用索引簇查询 select s.sname,s.sinfo,i.score from stu s,sc i where s.tid=i.tid ————————————————————————————————————————
//创建表,序列号(sequence) create table test1(tid number(8), tname varchar2(50), tbd date, constraint pk_tid primary key(tid) ) select * from test1 ============================================================== create sequence test1Seq --自定义的序列名 increment by 1 --每次加几个,即递增的间隔 start with 1 --从1开始计数 nomaxvalue --不设置最大值 nocycle --一直累加,不循环 cache 10; ============================================================== insert into test1(tid,tname,tbd)values(test1Seq.Nextval,'ccc',sysdate); |
|