Oracle Database 用户管理
创建用户SQL> create user tpcc identified by tpcc default tablespace tpcc temporary tablespace temp;
User created.
SQL> set line 200
SQL> col username for a30
SQL> col account_status for a20
SQL> select username,account_status,created from dba_users where username = 'TPCC';
USERNAME ACCOUNT_STATUS CREATED
------------------------------ -------------------- -------------------
TPCC OPEN 2018-06-22 07:39:09
SQL> col default_tablespace for a30
SQL> col temporary_tablespace for a30
SQL> select username,default_tablespace,temporary_tablespace from dba_users where username='TPCC';
USERNAME DEFAULT_TABLESPACE TEMPORARY_TABLESPACE
------------------------------ ------------------------------ ------------------------------
TPCC TPCC TEMP
修改用户的默认表空间
SQL> alter user tpcc default tablespace soe;
User altered.
修改用户的临时表空间
SQL> alter user tpcc temporary tablespace temp01;
User altered.
SQL> select username,default_tablespace,temporary_tablespace from dba_users where username='TPCC';
USERNAME DEFAULT_TABLESPACE TEMPORARY_TABLESPACE
------------------------------ ------------------------------ ------------------------------
TPCC SOE TEMP01
密码过期
SQL> alter user tpcc password expire;
User altered.
SQL> SQL> select username,account_status,created from dba_users where username = 'TPCC';
USERNAME ACCOUNT_STATUS CREATED
------------------------------ -------------------- -------------------
TPCC EXPIRED 2018-06-22 07:39:09
锁定用户
SQL> alter user tpcc account lock;
User altered.
SQL> select username,account_status,created from dba_users where username = 'TPCC';
USERNAME ACCOUNT_STATUS CREATED
------------------------------ -------------------- -------------------
TPCC EXPIRED & LOCKED 2018-06-22 07:39:09
解锁用户
SQL> alter user tpcc account unlock;
User altered.
SQL> select username,account_status,created from dba_users where username = 'TPCC';
USERNAME ACCOUNT_STATUS CREATED
------------------------------ -------------------- -------------------
TPCC EXPIRED 2018-06-22 07:39:09
修改用户密码
SQL> alter user tpcc identified by tpcc01;
User altered.
SQL> select username,account_status,created from dba_users where username = 'TPCC';
USERNAME ACCOUNT_STATUS CREATED
------------------------------ -------------------- -------------------
TPCC OPEN 2018-06-22 07:39:09
表空间配额
SQL> alter user tpcc quota 500M on soe;
User altered.
SQL> select username,tablespace_name,
case max_bytes when -1 then '-1'
else to_char(max_bytes/1024/1024 || 'M') END AS "QUOTA"
from dba_ts_quotas
order by username;
USERNAME TABLESPACE_NAME QUOTA
------------------------------ ------------------------------ --------------------
APPQOSSYS SYSAUX -1
FLOWS_FILES SYSAUX -1
OLAPSYS SYSAUX -1
SYSMAN SYSAUX -1
TPCC TPCC -1
TPCC SOE 500M
SQL> alter user tpcc quota unlimited on soe;
User altered.
SQL> select username,tablespace_name,
case max_bytes when -1 then '-1'
else to_char(max_bytes/1024/1024 || 'M') END AS "QUOTA"
from dba_ts_quotas
order by username;
USERNAME TABLESPACE_NAME QUOTA
------------------------------ ------------------------------ --------------------
APPQOSSYS SYSAUX -1
FLOWS_FILES SYSAUX -1
OLAPSYS SYSAUX -1
SYSMAN SYSAUX -1
TPCC TPCC -1
TPCC SOE -1
删除用户
SQL> drop user tpcc cascade;
User dropped.
页:
[1]