一、指定默认表空间

--查看系统默认表空间、字符集、临时表空间

SQL> col PROPERTY_NAME for a50;

SQL> col PROPERTY_VALUE for a30;

SQL> select property_name,property_value from database_properties;

PROPERTY_NAME PROPERTY_VALUE

-------------------------------------------------- ------------------------------

DICT.BASE 2

DEFAULT_TEMP_TABLESPACE TEMP_GRP

DEFAULT_PERMANENT_TABLESPACE USERS

DBTIMEZONE +08:00

DEFAULT_TBS_TYPE SMALLFILE

NLS_LANGUAGE AMERICAN

NLS_TERRITORY AMERICA

NLS_CURRENCY $

NLS_ISO_CURRENCY AMERICA

NLS_NUMERIC_CHARACTERS .,

NLS_CHARACTERSET ZHS16GBK

PROPERTY_NAME PROPERTY_VALUE

-------------------------------------------------- ------------------------------

NLS_CALENDAR GREGORIAN

NLS_DATE_FORMAT DD-MON-RR

NLS_DATE_LANGUAGE AMERICAN

NLS_SORT BINARY

NLS_TIME_FORMAT HH.MI.SSXFF AM

NLS_TIMESTAMP_FORMAT DD-MON-RR HH.MI.SSXFF AM

NLS_TIME_TZ_FORMAT HH.MI.SSXFF AM TZR

NLS_TIMESTAMP_TZ_FORMAT DD-MON-RR HH.MI.SSXFF AM TZR

NLS_DUAL_CURRENCY $

NLS_COMP BINARY

NLS_LENGTH_SEMANTICS BYTE

PROPERTY_NAME PROPERTY_VALUE

-------------------------------------------------- ------------------------------

NLS_NCHAR_CONV_EXCP FALSE

NLS_NCHAR_CHARACTERSET AL16UTF16

NLS_RDBMS_VERSION 10.2.0.1.0

GLOBAL_DB_NAME PROD

EXPORT_VIEWS_VERSION 8

27 rows selected.

--修改系统默认表空间

SQL> alter database default tablespace users;

Database altered.

--修改用户默认表空间

SQL> col username for a20;

SQL> col default_tablespace for a20;

SQL> col temporary_tablespace for a20;

SQL> select username,default_tablespace,temporary_tablespace from dba_users ;

USERNAME DEFAULT_TABLESPACE TEMPORARY_TABLESPACE

-------------------- -------------------- --------------------

OUTLN SYSTEM TEMP_GRP

SCOTT USERS TEMPTS2

TOM USERS TEMP_GRP

SYS SYSTEM TEMP_GRP

SYSTEM SYSTEM TEMP_GRP

TSMSYS USERS TEMP_GRP

DIP USERS TEMP_GRP

DBSNMP SYSAUX TEMP_GRP

8 rows selected.

SQL> alter user scott default tablespace users;

User altered.

用户创建时，设置默认表空间

二、用户验证方式

--oracle数据库验证方式一

SQL> create user zhen identified by zhen default tablespace users;

User created.

--oracle数据库验证方式二（初次登录需修改初始密码）

SQL> create user alice identified by alice password expire;

User created.

--赋予连接的权限

SQL> grant create session to zhen,alice;

--alice用户连接

SQL> conn alice/alice

ERROR:

ORA-28001: the password has expired

Changing password for alice

New password:

Retype new password:

Password changed

Connected.

SQL> show user

USER is "ALICE"

--操作系统用户认证

--查看os_authent_prefix参数信息

SQL> show parameter auth

NAME TYPE VALUE

------------------------------------ ----------- ------------------------------

os_authent_prefix string ops$

remote_os_authent boolean FALSE

--创建操作系统认证用户（oracle是操作系统已注册用户），赋予create session权限

SQL> create user ops$oracle identified externally;

User created.

SQL> grant create session to ops$oracle;

Grant succeeded.

--操作系统认证用户连接

SQL> conn /

Connected.

SQL> show user

USER is "OPS$ORACLE"

三、指定表空间的配额

设置配额quota（给用户赋予create table权限后，要分配配额，该用户才能真正使用create table的权限）

--dba用户给zhen赋予create table权限

SQL> grant create table to zhen;

Grant succeeded.

--连接zhen用户，并执行create table语句

SQL> conn zhen/zhen

Connected.

SQL> create table test(id number(8));

create table test(id number(8))

*

ERROR at line 1:

ORA-01950: no privileges on tablespace 'USERS'

由此可见，虽有了create table权限，但因没有分配配额，执行create table时，还是会报错（无权使用默认表空间）

注意：create table好比盖楼，若用户只有create table的权限，并不能盖楼，还要设置配额，比如alter user zhen quota 10m on users。

如果想在任何表空间无配额限制，可以分配unlimited tablespace权限。或者分配connect，resource角色。

--sys用户设置zhen用户有限quota

SQL> alter user zhen quota 10m on users;

User altered.

--zhen用户再执行create table权限

SQL> create table test(id number(8));

Table created.

由此可见，设置quota后，create table成功，但因quota是有限制的，当达到这个限制额后，再执行create table就会报错。

--查看配额信息，及设置无限制quota

SQL> desc dba_ts_quotas;

Name Null? Type

----------------------------------------------------- -------- ------------------------------------

TABLESPACE_NAME NOT NULL VARCHAR2(30)

USERNAME NOT NULL VARCHAR2(30)

BYTES NUMBER

MAX_BYTES NUMBER

BLOCKS NUMBER

MAX_BLOCKS NUMBER

DROPPED VARCHAR2(3)

SQL> select username,tablespace_name,bytes/1024/1024 m,max_bytes/1024/1024 max_m from dba_ts_quotas;

USERNAME TABLESPACE_NAME M MAX_M

------------------------------ ------------------------------ ---------- ----------

ZHEN USERS .0625 10

SQL> alter user zhen quota unlimited on users;

User altered.

SQL> select username,tablespace_name,bytes/1024/1024 m,max_bytes/1024/1024 max_m from dba_ts_quotas;

USERNAME TABLESPACE_NAME M MAX_M

------------------------------ ------------------------------ ---------- ----------

ZHEN USERS .0625 -9.537E-07

--收回配额（不能再新建表，但原来创建的表还可用，直到达到最初设置的配额）

SQL> alter user zhen quota 0m on users;

User altered.

SQL> create table test2(id number(8));

create table test2(id number(8))

*

ERROR at line 1:

ORA-01536: space quota exceeded for tablespace 'USERS'

SQL> insert into test values(1);

1 row created.

SQL> commit;

Commit complete.

--在任何表空间无配额限制（一块分配connect，resource权限）

SQL> create user ha identified by ha;

User created.

SQL> grant connect,resource to ha;

Grant succeeded

SQL> conn ha/ha

Connected.

SQL> create table test(id number(8));

Table created.

SQL> select * from user_sys_privs;

USERNAME PRIVILEGE ADM

------------------------------ ---------------------------------------- ---

HA UNLIMITED TABLESPACE NO

四、删除用户

SQL> drop user zhen;

drop user zhen

*

ERROR at line 1:

ORA-01922: CASCADE must be specified to drop 'ZHEN'

SQL> drop user zhen cascade;

User dropped.

SQL> conn alice/alice

Connected.

SQL> show user

USER is "ALICE"

SQL> drop user alice cascade;

drop user alice cascade

*

ERROR at line 1:

ORA-01940: cannot drop a user that is currently connected

注意：无法删除当前连接用户

--dba杀连接用户的进程

SQL> select username,sid,serial# from v$session where username is not null;

USERNAME SID SERIAL#

------------------------------ ---------- ----------

ALICE 272 31

SYS 274 9

SQL> alter system kill session '272,31';

System altered.

--alice用户无法做任何操作

SQL> select * from tab;

select * from tab

*

ERROR at line 1:

ORA-00028: your session has been killed

--dba用户再次执行删除alice用户操作

SQL> drop user alice cascade;

User dropped.

操作系统杀进程方式

--查看操作系统进程号

SQL> select p.spid,s.machine,s.username,s.sid,s.serial#,s.status

2 from v$session s,v$process p

3 where s.paddr=p.addr

4 and s.username is not null;

SPID MACHINE USERNAME SID SERIAL# STATUS

------------ ---------- ------------------------------ ---------- ---------- --------

3645 gc1 SYS 274 9 ACTIVE

4081 gc1 HA 269 26 INACTIVE

--若要杀掉ha用户进程，则用操作系统方式分别如下

unix系统：kill spid（如:kill 4081）

linux系统：kill -9 spid （如:kill -9 4081 ）

windows系统：orakill sid spid （如:orakill PROD 4081）

ITPub博客

用户管理

最新文章