oracle 新建表空间，增加、关联到用户，删除表空间

1、创建临时表空间

create temporary tablespace DEMO_DATA_TEMP
tempfile 'D:\ORADATA\DEMO_DATA_TEMP.dbf'
size 50m
autoextend on
next 50m maxsize 20480m
extent management local;
 

2、创建表空间(自增长，最大20G)

create tablespace DEMO_DATA
logging  
datafile 'D:\ORADATA\DEMO_DATA.DBF' 
size 50m  
autoextend on  
next 50m maxsize 20480m  
extent management local;

3、扩容表空间(增加表空间文件，自增长)

alter tablespace DCERP_DATA add datafile 'D:\ORADATA\DEMO_DATA01.DBF' size 50m autoextend on next 50m maxsize 20G;

4、新增用户、分配表空间（使用dba用户操作）

create user testuser identified by new1q2w3e
default tablespace DEMO_DATA
temporary tablespace DEMO_DATA_TEMP;

5、分配权限

grant connect,resource,dba to testuser;

6、删除表空间

drop tablespace DEMO_DATA including contents and datafiles;

7、查看表空间使用情况

1、
select
b.file_name 物理文件名,
b.tablespace_name 表空间,
b.bytes/1024/1024 大小M,
(b.bytes-sum(nvl(a.bytes,0)))/1024/1024 已使用M,
substr((b.bytes-sum(nvl(a.bytes,0)))/(b.bytes)*100,1,5) 利用率
from dba_free_space a,dba_data_files b
where a.file_id=b.file_id
group by b.tablespace_name,b.file_name,b.bytes
order by b.tablespace_name
2、
SELECT a.tablespace_name,
a.bytes total,
b.bytes used,
c.bytes free,
(b.bytes * 100) / a.bytes "% USED ",
(c.bytes * 100) / a.bytes "% FREE "
FROM sys.sm$ts_avail a, sys.sm$ts_used b, sys.sm$ts_free c
WHERE a.tablespace_name = b.tablespace_name
AND a.tablespace_name = c.tablespace_name;