Oracle表空间操作常用sql

–查看现有表空间物理存储位置：

select tablespace_name,file_id,bytes/1024/1024,file_name from dba_data_files order by file_id;

–查看临时表空间：

select tablespace_name,file_name,bytes/1024/1024 file_size,autoextensible from dba_temp_files;

/*创建临时表空间 */

create temporary tablespace 临时表空间名  
tempfile '物理路径/qzdata_temp.001' 
size 1g   --初始内存  
autoextend on  
next 100m maxsize 10g --单次递增大小及最大内存
extent management local;  

/*创建数据表空间 */

create tablespace 表空间名  
logging  
datafile '物理路径/qzdata.001' 
size 10g --初始内存  
autoextend on  
next 100m maxsize 30g  --单次递增大小及最大内存 
extent management local;

/*删除表空间及数据文件（非空表空间） */

drop tablespace 表空间名 including contents and datafiles;

/*创建用户并指定表空间 */

create user 用户名 identified by 密码  
default tablespace 表空间名  
temporary tablespace 临时表空间名;

/*给用户授予权限 */

grant connect,resource,IMP_FULL_DATABASE to 用户名;

/* 扩展表空间 */

alter tablespace 表空间名 add 
datafile '物理路径/qzdata.002' 
size 10g --扩容初始内存
autoextend on next 100m maxsize 30g; --递增大小

/* 查询表空间占用情况 */

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
order by round((total - free) / total, 4) * 100 desc

select
  a.tablespace_name, total, free, total-free as used, substr(free/total * 100, 1, 5) as "FREE%", substr((total - free)/total * 100, 1, 5) as "USED%"
from
  (select tablespace_name, sum(bytes)/1024/1024 as total from dba_data_files group by tablespace_name) a,
  (select tablespace_name, sum(bytes)/1024/1024 as free from dba_free_space group by tablespace_name) b
where a.tablespace_name = b.tablespace_name
order by a.tablespace_name;

常见问题：

(1)oracle创建表空间权限不足，报错如下：

ERROR at line 1:
ORA-01119: error in creating database file '*****/**.dbf'
ORA-27040: file create error, unable to create file
Linux-x86_64 Error: 13: Permission denied

解决方法：

chown -R oracle:oinstall /data/oracle/***（表空间所在目录）　　
#设置目录所有者为oinstall用户组的oracle用户