Oracle账号解锁、查询表使用率、表自动扩展

#查询ZhangSan账号锁定状态
select username,account_status from dba_users where username='ZhangSan';

#将ZhangSan账号解锁
alter user ZhangSan account unlock;

#查询ZhangSan账号密码
select username, password from dba_users where username='ZhangSan';

#修改ZhangSan账号密码为ZhangSan_123
alter user ZhangSan identified  ZhangSan_123;

#测试ZhangSan账号能否登录
connect ZhangSan/ZhangSan_123;

#查询表空间使用情况
SELECT a.tablespace_name "表空间名", 
total "表空间大小", 
free "表空间剩余大小", 
(total - free) "表空间使用大小", 
total / (1024 * 1024 * 1024) "表空间大小(G)", 
free / (1024 * 1024 * 1024) "表空间剩余大小(G)", 
(total - free) / (1024 * 1024 * 1024) "表空间使用大小(G)", 
round((total - free) / total, 4) * 100 "使用率 %" 
FROM (SELECT tablespace_name, SUM(bytes) free 
FROM dba_free_space 
GROUP BY tablespace_name) a, 
(SELECT tablespace_name, SUM(bytes) total 
FROM dba_data_files 
GROUP BY tablespace_name) b 
WHERE a.tablespace_name = b.tablespace_name ;

#自动扩展表空间，指定大小，每次增加5GB，数据文件最大30GB
alter database datafile '/arp/oraarp/oradata/ams08.dbf' autoextend on next 5G maxsize 30G;