----1、创建语法
Create User user_name
Identified By user_password
Default Tablespace tablespace_name
Temporary Tablespace temp_tablespace_name
profilr profile_name
Quota n M On | Unlimited On Tablespace_name
Password Expire
Account Lock|Unlock
--2、创建用户
SQL> Create User u1 Identified By u1 Default Tablespace EXAMPLE Temporary Tablespace TEMP2 Profile PROF1
2 Quota 1m On example
3 Password Expire
4 Account lock;
User created
SQL> Select username,account_status,to_char(lock_date,'yyyy-mm-dd hh24:mi:ss') As lock_date,
2 to_char(expiry_date,'yyyy-mm-dd hh24:mi:ss') As expiry_date,default_tablespace,temporary_tablespace From dba_users Where username='U1'
3 /
USERNAME ACCOUNT_STATUS LOCK_DATE EXPIRY_DATE DEFAULT_TABLESPACE TEMPORARY_TABLESPACE
------------------------------ -------------------------------- ------------------- ------------------- ------------------------------ ------------------------------
U1 EXPIRED & LOCKED 2015-08-14 23:55:56 2015-08-14 23:55:56 EXAMPLE TEMP2
SQL> grant create session,create table to u1;
Grant succeeded
SQL> conn u1/u1@local_orcl
ERROR:
ORA-28000: the account is locked
--3、解锁用户
SQL> alter user u1 account unlock;
User altered
--4、修改密码、默认表空间、默认临时表空间、默认配置文件
SQL> Alter User u1 Identified By u123 Default Tablespace Users Temporary Tablespace tmp_grp Profile Default;
User altered
--5、修改表空间配额
SQL> conn u1/u123@local_orcl
已连接。
SQL> Create Table test_u1(v Char(100)) Tablespace Users;
表已创建。
SQL> Insert Into test_u1 Values(1);
Insert Into test_u1 Values(1)
*
第 1 行出现错误:
ORA-01536: 超出表空间 'USERS' 的空间限额
SQL> conn system/his@local_orcl
已连接。
SQL> alter user u1 quota 10m on users;
用户已更改。
SQL> conn u1/u123@local_orcl
已连接。
SQL> Insert Into test_u1 Values(1);
已创建 1 行。
SQL> Insert Into test_u1 Values(2);
已创建 1 行。
SQL> commit;
提交完成。
--6 dba_ts_quotas视图
SQL> Alter User u1 Quota 20m On example;
User altered
SQL> Alter User u1 Quota 30m On Users;
User altered
SQL> Alter User u1 Quota Unlimited On System;
User altered
SQL> Select * From dba_ts_quotas Where username='U1';
TABLESPACE_NAME USERNAME BYTES MAX_BYTES BLOCKS MAX_BLOCKS DROPPED
------------------------------ ------------------------------ ---------- ---------- ---------- ---------- -------
SYSTEM U1 0 -1 0 -1 NO
USERS U1 65536 31457280 8 3840 NO
EXAMPLE U1 0 20971520 0 2560 NO
SQL> Select 20971520/1024/1024,31457280/1024/1024 From dual;
20971520/1024/1024 31457280/1024/1024
------------------ ------------------
20 30
SQL> alter user u1 quota 0m on users;
User altered
SQL> Select * From dba_ts_quotas Where username='U1';
TABLESPACE_NAME USERNAME BYTES MAX_BYTES BLOCKS MAX_BLOCKS DROPPED
------------------------------ ------------------------------ ---------- ---------- ---------- ---------- -------
SYSTEM U1 0 -1 0 -1 NO
EXAMPLE U1 0 20971520 0 2560 NO
SQL>
--7、删除用户
drop user user_name [cascade]
SQL> drop user u1 cascade;
User dropped
Create User user_name
Identified By user_password
Default Tablespace tablespace_name
Temporary Tablespace temp_tablespace_name
profilr profile_name
Quota n M On | Unlimited On Tablespace_name
Password Expire
Account Lock|Unlock
--2、创建用户
SQL> Create User u1 Identified By u1 Default Tablespace EXAMPLE Temporary Tablespace TEMP2 Profile PROF1
2 Quota 1m On example
3 Password Expire
4 Account lock;
User created
SQL> Select username,account_status,to_char(lock_date,'yyyy-mm-dd hh24:mi:ss') As lock_date,
2 to_char(expiry_date,'yyyy-mm-dd hh24:mi:ss') As expiry_date,default_tablespace,temporary_tablespace From dba_users Where username='U1'
3 /
USERNAME ACCOUNT_STATUS LOCK_DATE EXPIRY_DATE DEFAULT_TABLESPACE TEMPORARY_TABLESPACE
------------------------------ -------------------------------- ------------------- ------------------- ------------------------------ ------------------------------
U1 EXPIRED & LOCKED 2015-08-14 23:55:56 2015-08-14 23:55:56 EXAMPLE TEMP2
SQL> grant create session,create table to u1;
Grant succeeded
SQL> conn u1/u1@local_orcl
ERROR:
ORA-28000: the account is locked
--3、解锁用户
SQL> alter user u1 account unlock;
User altered
--4、修改密码、默认表空间、默认临时表空间、默认配置文件
SQL> Alter User u1 Identified By u123 Default Tablespace Users Temporary Tablespace tmp_grp Profile Default;
User altered
--5、修改表空间配额
SQL> conn u1/u123@local_orcl
已连接。
SQL> Create Table test_u1(v Char(100)) Tablespace Users;
表已创建。
SQL> Insert Into test_u1 Values(1);
Insert Into test_u1 Values(1)
*
第 1 行出现错误:
ORA-01536: 超出表空间 'USERS' 的空间限额
SQL> conn system/his@local_orcl
已连接。
SQL> alter user u1 quota 10m on users;
用户已更改。
SQL> conn u1/u123@local_orcl
已连接。
SQL> Insert Into test_u1 Values(1);
已创建 1 行。
SQL> Insert Into test_u1 Values(2);
已创建 1 行。
SQL> commit;
提交完成。
--6 dba_ts_quotas视图
SQL> Alter User u1 Quota 20m On example;
User altered
SQL> Alter User u1 Quota 30m On Users;
User altered
SQL> Alter User u1 Quota Unlimited On System;
User altered
SQL> Select * From dba_ts_quotas Where username='U1';
TABLESPACE_NAME USERNAME BYTES MAX_BYTES BLOCKS MAX_BLOCKS DROPPED
------------------------------ ------------------------------ ---------- ---------- ---------- ---------- -------
SYSTEM U1 0 -1 0 -1 NO
USERS U1 65536 31457280 8 3840 NO
EXAMPLE U1 0 20971520 0 2560 NO
SQL> Select 20971520/1024/1024,31457280/1024/1024 From dual;
20971520/1024/1024 31457280/1024/1024
------------------ ------------------
20 30
SQL> alter user u1 quota 0m on users;
User altered
SQL> Select * From dba_ts_quotas Where username='U1';
TABLESPACE_NAME USERNAME BYTES MAX_BYTES BLOCKS MAX_BLOCKS DROPPED
------------------------------ ------------------------------ ---------- ---------- ---------- ---------- -------
SYSTEM U1 0 -1 0 -1 NO
EXAMPLE U1 0 20971520 0 2560 NO
SQL>
--7、删除用户
drop user user_name [cascade]
SQL> drop user u1 cascade;
User dropped