Oracle学习交流（2） -----管理数据库对象

 

--授予SCOTT创建同义词权限
grant create synonym to scott
--为EMP表添加方案同义词.
create synonym syn_emp for emp
create or replace  synonym syn_emp for emp
--查询同义词
select * from syn_emp

--为SCOTT用户创建公用同义词权限
GRANT create public synonym to scott
--创建公用同义词
create or replace public synonym syn_public_emp for scott.emp
grant select on syn_emp to hr
--用HR用户查看公用同义词 PS:注意,公用同义词不需要在名字前加方案名.
select * from syn_emp
--用HR用户查看SCOTT的方案同义词
select * from scott.syn_public_emp

drop synonym syn_emp
drop public synonym syn_public_emp --需要管理员权限

--序列
--创建序列 开始值1 无最大值 每次增长1 缓存10
create sequence seq_Test nomaxvalue cache 10 start with 1 increment by 1

create sequence seq_student start with 1 increment by 1
--查询下一个值
select seq_test.nextval from dual
--查询当前值
select seq_test.currval from dual
--删除序列
drop sequence seq_test

--视图
--创建视图 使用HR用户
create or replace view view_emp as
select first_name,last_name,salary,department_id from employees
--查询视图
select * from view_emp
--切换到SCOTT用户使用之前创建的STUDENT表
select * from student
create or replace view view_stu as
select SID,SNAME,SAGE,CLASSID from student with read only
select * from view_stu
insert into view_stu values(seq_student.nextval,'小黄',20,1)

--创建with check option 视图
create or replace view view_stu as
select SID,SNAME,SAGE,CLASSID from student where SNAME='小白' with check option

create or replace view view_stu as
select SID,SNAME,SAGE,CLASSID from student
select * from student
select * from view_stu
update view_stu set sage = 111 where sname = '小白'
insert into view_stu values(seq_student.nextval,'bbbb',12,1)
--insert into view_stu values(seq_student.nextval,'aaaa',12,1,sysdate)

--查询视图信息
select * from user_updatable_columns  a  where a.table_name=upper('view_stu')
select * from user_views a where a.VIEW_NAME=upper('view_stu')

--索引
create index ix_emp_firstName on emp(first_name)

create table tb_idx_test as
select * from dba_objects where owner in ('sys','system','public','SCOTT') --需要管理员权限 需要大写
--查看记录数
select count(*) from dba_objects

--创建用户xiaobai 密码123
create user xiaobai identified by 123
--设置xiaobai密码为1234
alter user xiaobai identified by 1234
--授权xiaobai用户登陆权限
grant connect,resource to xiaobai