oracle表空间操作

1.查询cosem用户所用表空间
 

select distinct tablespace_name from dba_tables where owner='COSEM';
select distinct tablespace_name from dba_indexes where owner='COSEM';

2.查询表空间使用情况

SELECT
	a.tablespace_name "表空间名",
	a.bytes / 1024 / 1024 "表空间大小(M)",
	( a.bytes - b.bytes ) / 1024 / 1024 "已使用空间(M)",
	b.bytes / 1024 / 1024 "空闲空间(M)",
	round( ( ( a.bytes - b.bytes ) / a.bytes ) * 100, 2 ) "使用比" 
FROM
	( SELECT tablespace_name, sum( bytes ) bytes FROM dba_data_files GROUP BY tablespace_name ) a,
	( SELECT tablespace_name, sum( bytes ) bytes, max( bytes ) largest FROM dba_free_space GROUP BY tablespace_name ) b 
WHERE
	a.tablespace_name = b.tablespace_name 
ORDER BY
	( ( a.bytes - b.bytes ) / a.bytes ) DESC

3.创建表空间

不自动扩展：

create tablespace SH datafile '/oradata/jydb/SH.dbf' size 20g;

自动扩展：

create tablespace SH datafile '/oradata/jydb/SH.dbf' size 50m autoextend on next 50m maxsize 4000m extent management local;

5.删除表空间

drop tablespace SH including contents and datafiles;

6.若表空间是默认表空间无法删除，设置其他默认表空间再删除

alter database default tablespace SH;

7.表空间扩容

alter database datafile '/oradata/jydb/USERS.dbf' resize 5000m;

8.创建用户并设置默认表空间

create user cosem identified by sa default tablespace SH;