--上机1
--创建表空间,大小为10M,文件大小可自动扩展,允许文件扩展的最大限度为无限制
create tablespace tp_orders
datafile 'E:\作业\Y2\第一学期\第二章\tb.dbf'--注意!!!!,这个一旦创建就不要删,删除后你就登陆不了Oracle了
size 10M autoextend on ;
--创建用户,密码bdqn,,默认表空间tp_orders
create user A_oe
identified by bdqn
default tablespace tp_orders;
--授予connect,resource权限和访问某用户的表
grant connect,resource to A_oe;
grant select on system.employee to A_oe;
--上机2
序列在Sequences文件夹看
--创建新表,命名为deptBak
create sequence dept_seq
start with 60 --从60开始
increment by 10 --间隔10
maxvalue 10000; --最大值10000
insert into dept (deptno,dname) values(dept_seq.nextval,'sdf');--使用序列将数据插入到dept表中
drop sequence dept_seq;--删除序列
--创建deptBak表将dept的数据插入到deptBak中
create table deptBak
as
select * from dept;
create sequence dept_seq
start with 60
increment by 10
maxvalue 10000;
insert into deptBak (deptno,dname) values(dept_seq.nextval,'vc');
select dept_seq.currval from dual--查看序列当前值
--上机3
create public synonym p_sy_dept for dept;--创建公有同义词
grant select on dept to A_oe;--授予A_oe查看该表的权限
select * from p_sy_dept;--在A_oe用户下查看公有同义词
--上机4
create unique index index_cid on customers(customers_id); --唯一索引
create bitmap index index_terr on customers(nls_territory); --位图索引
create unique index index_name on customers(cust_last_name,cust_first_name);--组合索引
--上机5
--范围分区
create table rangeOrders
(
rDate Date not null
)
partition by range (rDate)
(
partition p1 values less than (to_date('2005/01/01','yyyy-mm-dd')),
partition p2 values less than (to_date('2006/01/01','yyyy-mm-dd')),
partition p3 values less than (to_date('2007/01/01','yyyy-mm-dd')),,
partition p4 values less than (to_date('2008/01/01','yyyy-mm-dd')),
partition p5 values less than (to_date('2009/01/01','yyyy-mm-dd'))
partition p6 values less than (maxvalue)
);
select * from rangeOrders partition(p3);--查看第三个分区的数据
delete from rangeOrders partition(p3);--删除第三个分区的数据
insert into rangeOrders values(to_date('2013/01/01','yyyy/mm/dd'))--插入数据
--上机6
--间隔分区
create table intervalOrders
(
oDate date not null
)
partition by range(oDate)
interval(numtoyminterval(1,'YEAR'))--每1年为一个分区
(partition p1 values less than (to_date('2005/01/01','yyyy-mm-dd')));
--插入数据
insert into intervalOrders values(to_date('2003/12/01','yyyy-mm-dd'))
--查询分区情况
select table_name,partition_name
from user_tab_partitions
where table_name=upper('p1');
--查询分区数据
select * from intervalOrders partition(p1);
--删除分区
ALTER TABLE intervalOrders TRUNCATE PARTITION p1;
--删除全部分区
ALTER TABLE 表名 DROP PARTITION 分区名;
--简答2
1)
create table Stock_Received
(
Stock_ID number(4) not null,
Stock_Date date ,
Cost varchar2(20)
)
truncate table Stock_Received--删除表
--创建序列
create sequence myseq
start with 1000
increment by 10
maxvalue 1100
cycle
2)
grant create public synonym to A_oe --授予A_oe创建同义词的权限
grant initial to
create public synonym p_Stock_Received for Stock_Received;--在A_oe用户下创建公有同义词
drop public synonym p_Stock_Received;--删除公有同义词
select * from p_Stock_Received;--查看公有同义词
3)
create table Stock
(
Stock_ID number(4) not null,
Stock_Date date ,
Cost varchar2(20)
)
partition by range (Stock_Date)
(
partition p1 values less than(to_date('2008/01/01','yyyy-mm-dd')),
partition p2 values less than(to_date('2009/01/01','yyyy-mm-dd')),
partition p3 values less than(to_date('2010/01/01','yyyy-mm-dd')),
partition p6 values less than (maxvalue)
)
alter user A_oe default tablespace tp_orders;
create unique index index_cid on Stock(Stock_ID); --唯一索引
Y2 基于SSH框架的企业级应用开发 第二章 上机+课后
最新推荐文章于 2022-11-19 14:34:31 发布