Oracle數(shù)據(jù)庫(kù)用戶(hù)管理源代碼

1、Oracle數(shù)據(jù)庫(kù)用戶(hù)管理源代碼創(chuàng)立用戶(hù)sqlplus/nologconn sys/ldm as sysdba;create user ul identified by 1dm default tablespace users temporary tablespace temp quot IM on users account lock;更改用戶(hù)create tablespace tldatafile e:tl_0Ldbf size 2M autoallocate;alter user ul default tablespace tl quota IM on tl account unlock

2、 /刪除用戶(hù)drop user ul cascade;查詢(xún)用戶(hù)創(chuàng)立用戶(hù)u2create user u2 identified by qy default tablespace tl;可以通過(guò)某些視圖（如dba_users）來(lái)查看數(shù)據(jù)庫(kù)中該用戶(hù)的一些信息column username format alOcolumn profile format alOcolumn status format alOselect username,profile,account_status status,createdfrom dba_userswhere username=U2,;通過(guò)desc dba_us

3、ers命令來(lái)顯示dba_users的其他列desc dba_users查詢(xún)用戶(hù)所使用的表空間及其配額select tablespace_name the_name,username,blocksfrom dba_ts_quotas;權(quán)限管理為用戶(hù)ul授予create session系統(tǒng)權(quán)限sqlplus/nologcreate user ul identified by 1dm conn ul/ldm as sysdba;grant create session to ul;為用戶(hù)授予某角色所具有的所有權(quán)限conn sys/ldm as sysdba;create role rl;grant

4、create session,create table, create trigger,create procedureto rlwith admin option;回收系統(tǒng)權(quán)限conn ul/ldmcolumn username format alOcolumn privilege format a20select * from user_sys_privs;conn sys/ldm as sysdba;revoke create session,grant any rolefrom u 1;為用戶(hù)授予對(duì)象權(quán)限conn sys/ldm as sysdba;create table table

5、 1(id char(10);insert into tabletvalues(nWelcome!n);grant select on sys.tablel to u2;conn u2/ldmselect * from sys.tablel;基本對(duì)象管理表管理在做表管理的實(shí)驗(yàn)時(shí)，先需要以管理員身份創(chuàng)立一個(gè)表空間，并在該表空間上創(chuàng)立自己做實(shí)驗(yàn)要用的用戶(hù)，同時(shí)為該用戶(hù)授權(quán)，每個(gè)實(shí)驗(yàn)組以這一用戶(hù)登錄后進(jìn)行本節(jié)的實(shí)驗(yàn)。conn sys/ldm as sysdbmcreate tablespace ts8 datafile fts8_l.dbf size 10M;create user u22 iden

6、tified by 1dmdefault tablespace ts8;grant dba to u22;conn u22/ldm;創(chuàng)立表student,包含三個(gè)字段(id、name和address),每個(gè)字段都具有各自的屬 性create table student(id char(5),name varchar( 10),address varchar2(50);向表student插入一條數(shù)據(jù)：insert into studentvalues,Tist;山西太原迎澤大街226號(hào));另外一種方式來(lái)創(chuàng)立表create table student_copy as select * from s

7、tudent;增加 列alter table student add telephone char(15);判斷是否更改成功desc student;修改表名稱(chēng)rename student to stu;刪除表drop table student_copy;查看表desc student_copy;創(chuàng)立視圖create view stu_view(name,telephone)asselect name,telephone form stu;查看select * from stu view;向視圖中插入數(shù)據(jù)insert into stu_viewvalues(王五，查看select * from stu view;刪除視圖drop view stu_view;其他數(shù)據(jù)庫(kù)對(duì)象的管理create synonym si for stu;查看select * from s 1;創(chuàng)立序列IdSeq,并在向表stu插入數(shù)據(jù)時(shí)用其值來(lái)填充idcreate sequence Idseq