— 查看用户的表空间配额
SELECT username, tablespace_name, max_bytes, bytes_used, round((bytes_used/max_bytes)*100,2) as used_percent
FROM dba_ts_quotas
WHERE username = ‘SCOTT’;– 为用户设置表空间配额
ALTER USER scott QUOTA 100M ON users;– 为用户设置无限制配额
ALTER USER scott QUOTA UNLIMITED ON users;– 撤销用户的表空间配额
ALTER USER scott QUOTA 0 ON users;

— 查看所有用户的表空间配额
SELECT username, tablespace_name, max_bytes, bytes_used
FROM dba_ts_quotas
ORDER BY username, tablespace_name;– 查看用户默认表空间
SELECT username, default_tablespace, temporary_tablespace
FROM dba_users
WHERE username = ‘SCOTT’;

— 为多个用户设置相同的表空间配额
— 创建一个PL/SQL脚本
DECLARE
CURSOR c_users IS
SELECT username FROM dba_users WHERE username LIKE ‘APP_%’;BEGIN
FOR r_user IN c_users LOOP
EXECUTE IMMEDIATE ‘ALTER USER ‘ || r_user.username || ‘ QUOTA 500M ON users’;END LOOP;END;/

— 1. 创建新用户
SQL> CREATE USER app_user IDENTIFIED BY password DEFAULT TABLESPACE users TEMPORARY TABLESPACE temp;User created.

— 2. 授予基本权限
SQL> GRANT CONNECT, RESOURCE TO app_user;Grant succeeded.

— 3. 设置表空间配额
SQL> ALTER USER app_user QUOTA 200M ON users;User altered.

— 4. 验证配额设置
SQL> SELECT username, tablespace_name, max_bytes, bytes_used
FROM dba_ts_quotas
WHERE username = ‘FGAPP_USER’;USERNAME TABLESPACE_NAME MAX_BYTES BYTES_USED
———- ——————– ———- ———-
FGAPP_USER USERS 209715200 0

— 1. 查看用户当前配额和使用情况
SQL> SELECT username, tablespace_name, max_bytes, bytes_used,
round((bytes_used/max_bytes)*100,2) as used_percent
FROM dba_ts_quotas
WHERE username = ‘SCOTT’;USERNAME TABLESPACE_NAME MAX_BYTES BYTES_USED USED_PERCENT
———- ——————– ———- ———- ————
SCOTT USERS 104857600 98765432 94.19

— 2. 增加用户配额
SQL> ALTER USER scott QUOTA 500M ON users;User altered.

— 3. 验证配额调整
SQL> SELECT username, tablespace_name, max_bytes, bytes_used,
round((bytes_used/max_bytes)*100,2) as used_percent
FROM dba_ts_quotas
WHERE username = ‘SCOTT’;USERNAME TABLESPACE_NAME MAX_BYTES BYTES_USED USED_PERCENT
———- ——————– ———- ———- ————
SCOTT USERS 524288000 98765432 18.84

— 创建监控脚本，检查配额使用超过80%的用户
SELECT username, tablespace_name,
round(max_bytes/1024/1024,2) as max_mb,
round(bytes_used/1024/1024,2) as used_mb,
round((bytes_used/max_bytes)*100,2) as used_percent
FROM dba_ts_quotas
WHERE max_bytes > 0 AND (bytes_used/max_bytes)*100 > 80
ORDER BY used_percent DESC;– 输出结果示例
USERNAME TABLESPACE_NAME MAX_MB USED_MB USED_PERCENT
———- ——————– ———- ———- ————
SCOTT USERS 100 94.19 94.19
FGAPP_USER USERS 200 165.32 82.66