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

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

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

3、ers命令來顯示dba_users的其他列desc dba_users查詢用戶所使用的表空間及其配額select tablespace_name the_name,username,blocksfrom dba_ts_quotas;權(quán)限管理為用戶ul授予create session系統(tǒng)權(quán)限sqlplus/nologcreate user ul identified by 1dm conn ul/ldm as sysdba;grant create session to ul;為用戶授予某角色所具有的所有權(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;為用戶授予對象權(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;基本對象管理表管理在做表管理的實(shí)驗(yàn)時，先需要以管理員身份創(chuàng)立一個表空間，并在該表空間上創(chuàng)立自己做實(shí)驗(yàn)要用的用戶，同時為該用戶授權(quán)，每個實(shí)驗(yàn)組以這一用戶登錄后進(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,包含三個字段(id、name和address),每個字段都具有各自的屬 性create table student(id char(5),name varchar( 10),address varchar2(50);向表student插入一條數(shù)據(jù)：insert into studentvalues,Tist;山西太原迎澤大街226號);另外一種方式來創(chuàng)立表create table student_copy as select * from s

7、tudent;增加 列alter table student add telephone char(15);判斷是否更改成功desc student;修改表名稱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ù)庫對象的管理create synonym si for stu;查看select * from s 1;創(chuàng)立序列IdSeq,并在向表stu插入數(shù)據(jù)時用其值來填充idcreate sequence Idseq