2013年4月27日 星期六
10:50
1、概念
(1)schema ： user.object 就是用户创建的对象
(2)用户认证方式：
os 认证
database 认证
2、建立 database认证的用户
SQL @ prod > create user rose
identified by oracle
default tablespace users
temporary tablespace temp
quota 10m on users 【用户配额限制】
password expire; ——【用户一登录密码就过期，需要重新设定】
User created.
SQL @ prod > grant create session to rose;
Grant succeeded.
SQL @ prod > conn rose/oracle
ERROR:
ORA-28001: the password has expired
Changing password for rose ….
New password: ...
Retype new password: ...
Password changed
Connected.
3、建立OS认证(操作系统认证)用户(sys 用户属于os 认证)
SQL @ prod > show parameter auth
NAME TYPE VALUE
------------------------------------ ----------- ------------------------------
os_authent_prefix string ops$ 【——创建OS认证的用户名前面一定要加上这个参数】
remote_os_authent boolean FALSE
SQL @ prod > create user ops$oracle ——创建用户，不要加双引号
identified externally
profile default
default tablespace users
temporary tablespace temp
quota 10m on users
SQL @ prod > select username,account_status from dba_users;
USERNAME ACCOUNT_STATUS
--------------- -------------------------
OUTLN OPEN
SYS OPEN
SYSTEM OPEN
ROSE OPEN
SCOTT OPEN
ops$oracle OPEN
TOM OPEN
DBSNMP EXPIRED & LOCKED
TSMSYS EXPIRED & LOCKED
DIP EXPIRED & LOCKED
SQL @ prod > select username ,password ,PROFILE,DEFAULT_TABLESPACe,TEMPORARY_TABLESPACE from dba_users;
USERNAME PASSWORD PROFILE DEFAULT_TABLESP TEMPORARY_TABLE
--------------- -------------------- --------------- --------------- ---------------
OUTLN 4A3BA55E08595C81 DEFAULT SYSTEM TEMP
SYS 8A8F025737A9097A DEFAULT SYSTEM TEMP
SYSTEM 2D594E86F93B17A1 DEFAULT SYSTEM TEMP
ROSE 1166A1F535AF6EFB DEFAULT USERS TEMP
SCOTT F894844C34402B67 DEFAULT USERS TEMP
ops$oracle EXTERNAL DEFAULT USERS TEMP
TOM 0473A0A9140BFBD7 DEFAULT USERS TEMP
DBSNMP E066D214D5421CCC DEFAULT SYSAUX TEMP
TSMSYS 3DF26A8B17D0F29F DEFAULT USERS TEMP
DIP CE4A36B8E06CA59C DEFAULT USERS TEMP
SQL @ prod > grant create session to ops$oracle;
Grant succeeded.
SQL @ prod > exit
Disconnected from Oracle Database 10g Enterprise Edition Release 10.2.0.2.0 - Production
With the Partitioning, OLAP and Data Mining options
[oracle@solaris10 ~]$ id
uid=100(oracle) gid=100(oinstall)
[oracle@solaris10 ~]$ sqlplus / ——【登录不需要提供用户名和密码(oracle用户必须属于os的 oinstall 组 )】
SQL*Plus: Release 10.2.0.2.0 - Production on Wed Mar 14 16:07:43 2012
Copyright (c) 1982, 2005, Oracle. All Rights Reserved.
Connected to:
Oracle Database 10g Enterprise Edition Release 10.2.0.2.0 - Production
With the Partitioning, OLAP and Data Mining options
SQL @ prod > show user
USER is "OPS$ORACLE"
4、quota 管理：(对象的最大存储空间，用户在表空间上建立对象，必须在相应的tablespace 上获得quota)
SQL @ prod > select TABLESPACE_NAME,USERNAME,BYTES/1024,MAX_BYTES/1024/1024 from dba_ts_quotas;
TABLESPACE_NAME USERNAME BYTES/1024 MAX_BYTES/1024/1024
-------------------- --------------- ---------- -------------------
USERS ROSE 0 10
USERS OPS$ORACLE 0 10
——【BYTES 已经使用过的配额，MAX_BYTES所分配的配额】
SQL @ prod > grant create table to rose;
Grant succeeded.
SQL @ prod > grant select on scott.emp to rose;
Grant succeeded.
SQL @ prod > conn rose/rose
Connected.
SQL @ prod > create table emp1 as select * from scott.emp;
Table created.
SQL @ prod > conn /as sysdba
Connected.
SQL @ prod > select TABLESPACE_NAME,USERNAME,BYTES/1024,MAX_BYTES/1024/1024 from dba_ts_quotas
where username='ROSE';
TABLESPACE_NAME USERNAME BYTES/1024 MAX_BYTES/1024/1024
-------------------- --------------- ---------- -------------------
USERS ROSE 64 10
——回收quota【只能回收用户未使用的磁盘配额】
SQL @ prod > alter user rose quota 0 on users;
User altered.
SQL @ prod > select TABLESPACE_NAME,USERNAME,bytes/1024,max_bytes/1024/1024 from dba_ts_quotas where username='ROSE';
no rows selected 【——已经回收，但仍然可以插入数据，因为之前已经使用的磁盘配额没用完】
SQL @ prod > conn rose/rose
Connected.
ROSE @ prod > insert into emp1 select * from emp1;
14 rows created.
ROSE @ prod > /
28 rows created.
ROSE @ prod > /
56 rows created.
ROSE @ prod > insert into emp1 select * from emp1
*
ERROR at line 1:
【ORA-01536: space quota exceeded for tablespace 'USERS'
ROSE @ prod > analyze table emp1 compute statistics; ——没磁盘配额了 】
Table analyzed.
ROSE @ prod > select table_name,num_rows ,blocks,empty_blocks from user_tables;
TABLE_NAME NUM_ROWS BLOCKS EMPTY_BLOCKS
------------------------------ ---------- ---------- ------------
EMP1 448 8 0
5、删除用户【会话中的用户不能被删除】
SYS @ prod > select username,sid,serial# from v$session
where username is not null;
USERNAME SID SERIAL#
---------- ---------- ----------
SYS 31 84
ROSE 39 31
SYS @ prod > drop user rose;——会话中的用户不能被删除
drop user rose
*
ERROR at line 1:
ORA-01940: cannot drop a user that is currently connected
——强制关闭用户会话
SYS @ prod > alter system kill session '39,31';——‘SID，SERIAL#’
System altered.
SYS @ prod > select * from emp1;
select * from emp1
*
ERROR at line 1:
ORA-00028: your session has been killed
SYS @ prod > drop user rose cascade; ——将用户所有的对象都一起删除
User dropped.
磁盘配额
create user xxx quota 50m on system 指定用户在system 上50M 的磁盘空间。
alter system kill session 'sid,serial#' 杀掉用户进程
desc v$process 查找 ADDR SPID
desc session_privs 用户权限。
desc session_roles
set role develogment , manager
desc dba_role_privs;
alter user xxxx default role
转载于:https://www.cnblogs.com/iyoume2008/p/7526494.html
相关资源：OCA认证考试指南（1Z0-051）：Oracle Database 11g SQL Fundamentals I--详细书签版