SQL语法（七） 用户及表空间

前言

            本章将学习oracle的表空间以及用户的相关操作。

范例

1.查看表空间的使用情况

SELECT a.tablespace_name "表空间名", 
total "表空间大小", 
free "表空间剩余大小", 
(total - free) "表空间使用大小", 
total / (1024 * 1024) "表空间大小(M)", 
free / (1024 * 1024) "表空间剩余大小(M)", 
(total - free) / (1024 * 1024) "表空间使用大小(M)", 
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;

2.新建临时表空间

create temporary tablespace user_temp  
tempfile 'D:\app\wangjian\oradata\orcl\user_temp.dbf'
size 50m
autoextend on
next 50m maxsize 20480m
extent management local;

3.创建数据表空间

create tablespace test_data  
logging  
datafile 'D:\app\wangjian\oradata\orcl\test_data.dbf'
size 50m  
autoextend on  
next 50m maxsize 20480m  
extent management local; 

4.创建用户并指定表空间

create user username identified by password  
default tablespace test_data  
temporary tablespace user_temp;

5.给用户赋予权限

grant connect,resource,dba to username; 

Q&A：

如何解决用户账号密码过期的问题？

操作流程：（一定要设置，生产库新建的用户密码有效期180天，如不即时更换，将导致数据库无法连接）

（1）查看用户的proifle是哪个，一般是default：

sql>SELECT username,PROFILE FROM dba_users;

（2）查看指定概要文件（如default）的密码有效期设置：

sql>SELECT * FROM dba_profiles s WHERE s.profile='DEFAULT' AND resource_name='PASSWORD_LIFE_TIME';

（3）将密码有效期由默认的180天修改成“无限制”：

sql>ALTER PROFILE DEFAULT LIMIT PASSWORD_LIFE_TIME UNLIMITED;

修改之后不需要重启动数据库，会立即生效。

附录：用户及表空间示例

--1.查看表空间的使用情况
SELECT a.tablespace_name "表空间名", 
total "表空间大小", 
free "表空间剩余大小", 
(total - free) "表空间使用大小", 
total / (1024 * 1024) "表空间大小(M)", 
free / (1024 * 1024) "表空间剩余大小(M)", 
(total - free) / (1024 * 1024) "表空间使用大小(M)", 
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;
--2.新建临时表空间
create temporary tablespace user_temp  
tempfile 'C:\app\jwang\oradata\orcl\user_temp.dbf'
size 50m
autoextend on
next 50m maxsize 20480m
extent management local;
--3.新建数据表空间
create tablespace test_data  
logging  
datafile 'C:\app\jwang\oradata\orcl\test_data.dbf'
size 50m  
autoextend on  
next 50m maxsize 20480m  
extent management local; 
--4.新建用户
create user jwang identified by "jwang"  
default tablespace test_data  
temporary tablespace user_temp;
--5.赋予权限
grant connect,resource,dba to jwang; 
--删除用户
drop user jwang cascade;
--删除表空间
drop tablespace user_temp including contents and datafiles;

--解决用户账号密码过期的问题（一定要设置，生产库新建的用户密码有效期180天，如不即时更换，将导致数据库无法连接）

--（1）查看用户的profile是哪个，一般是default：

SELECT username,PROFILE FROM dba_users;

--（2）查看指定概要文件（如default）的密码有效期设置：

SELECT * FROM dba_profiles s WHERE s.profile='DEFAULT' AND resource_name='PASSWORD_LIFE_TIME';

--（3）将密码有效期由默认的180天修改成“无限制”：

ALTER PROFILE DEFAULT LIMIT PASSWORD_LIFE_TIME UNLIMITED;

附录：操作视频

SQL语法之用户及表空间