Oracle資料庫---序列、索引、同義詞

--創建序列
create sequence deptno_seq
start with 50
increment by 10
maxvalue 70
cache 3;

--為了方便演示，創建了一個和dept表結構相同的deptnew表
create table deptnew
as
select * from dept;

--向表中插入數據，並通過序列給主鍵列deptno提供數據
insert into deptnew(deptno,dname,loc)values(deptno_seq.nextval,'test_dname','test_loc');
select * from deptnew;

--currval
select deptno_seq.currval from dual;

--查詢序列
select sequence_name, min_value, max_value, increment_by, cycle_flag, cache_size, last_number
from user_sequences;

--向資料庫提交插入的數據
commit;

--修改序列
alter sequence deptno_seq
maxvalue 90;

--測試
insert into deptnew(deptno,dname,loc)values(deptno_seq.nextval,'test_dname','test_loc');
select * from deptnew;
rollback;
insert into deptnew(deptno,dname,loc)values(deptno_seq.nextval,'test_dname','test_loc');
select * from deptnew;
commit;

--刪除序列
drop sequence deptno_seq;

--創建單列索引
create index idx_ename on emp(ename);

--創建複合索引
create index idx_deptno_job on emp(deptno,job);

--創建唯一索引
create unique index idx_dname on dept(dname);

--創建非唯一索引
create index idx_job on emp(job);

--查詢索引
select uic.index_name, uic.column_name, uic.column_position, ui.uniqueness
from user_indexes ui, user_ind_columns uic
where uic.index_name = ui.index_name and ui.table_name='EMP';

--刪除索引
drop index idx_dname;

--system用戶的演示代碼

--給scott用戶授予創建公共同義詞的許可權
grant create public synonym to scott;

--給scott用戶授予創建私有同義詞的許可權
grant create synonym to scott;

--測試
--使用scott用戶下的私有同義詞
select * from scott.en;

--使用scott用戶下的公共同義詞
select * from dn;

--給scott用戶授予刪除公共同義詞的許可權
grant drop public synonym to scott;

--scott用戶的演示代碼

--創建公共同義詞
create public synonym dn for scott.deptnew;

--使用公共同義詞
select * from dn;

--創建私有同義詞
create synonym en for scott.empnew;

--使用私有同義詞
select * from en;

--查看私有同義詞
select synonym_name,table_owner,table_name
from syn
where synonym_name = 'EN';

--查看公共同義詞
select synonym_name,table_owner,table_name
from all_synonyms
where synonym_name = 'DN';

--刪除公共同義詞
drop public synonym dn;

--刪除私有同義詞
drop synonym en;