Oracle 创建表空间、创建用户、用户授权、密码过期、删除用户、删除表空间

查看表空间使用情况

SELECT
	a.tablespace_name "表空间名",
	total "表空间大小",
	( total - free ) "表空间使用大小",
	total / ( 1024 * 1024 ) "表空间大小（M）",
	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 
	AND a.TABLESPACE_NAME LIKE 'LA%'

查看表空间存储位置

SELECT
	t1.name as "表空间名",
	t2.name as "表空文件路径" 
FROM
	v$tablespace t1,
	v$datafile t2 
WHERE
	t1.ts# = t2.ts#;

创建临时表空间

CREATE temporary tablespace user_temp_jcs 
tempfile 'E:\APP\ADMINISTRATOR\ORADATA\ORCL\user_temp_jcs.DBF' 
SIZE 50m autoextend ON 
next 50m maxsize 2048m extent management local;

创建表空间

CREATE tablespace user_test_jcs 
logging 
datafile 'E:\APP\ADMINISTRATOR\ORADATA\ORCL\user_test_jcs.DBF' 
SIZE 50m autoextend ON 
next 50m maxsize 2048m extent management local;

创建用户

CREATE USER user_jcs IDENTIFIED BY "user_jcs" DEFAULT tablespace user_test_jcs;

赋予权限

GRANT CONNECT, RESOURCE, dba TO user_jcs;

删除用户

DROP USER user_jcs cascade;

删除表空间 和 文件

DROP tablespace user_test_jcs including contents AND datafiles;

解决用户账号密码过期问题

-- 查看用户的 profise是哪个，一般是default
SELECT username, profile FROM dba_users
WHERE username LIKE 'LA%'

-- 查看指定概要文件的密码有效期设置
select * from dba_profiles s where s.profile = 'DEFAULT' and RESOURCE_name = 'PASSWORD_LIFE_TIME'

-- 将密码有效期设置为无限期
ALTER profile DEFAULT limit password_life_time UNLIMITED;