Oracle数据库表空间常用操作

Oracle数据库表空间常用操作

我们在使用Oracle数据库时，为了减少查询表时间，通常会对表建索引。而建索引又涉及到表空间的改变，因此，我们又需要对数据库的表空间进行扩展。

查看表空间

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 

创建表空间

create tablespace test01  
datafile 'C:\APP\ADMIN\ORADATA\ORCL\test01.dbf'  
size 10G  
autoextend on;

查询表空间

select * from DBA_DATA_FILES;

修改用户默认的表空间

alter user testuser default tablespace test01;

查询用户默认表空间

select * from user_users;

查询临时表空间

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

扩展表空间

增加表空间数据文件：

ALTER TABLESPACE 表空间名 ADD DATAFILE '数据文件路径' SIZE 500M AUTOEXTEND ON NEXT 1M MAXSIZE UNLIMITED;

增加临时表空间数据文件：

alter tablespace temp add tempfile '数据文件路径' size 10G AUTOEXTEND ON ;

回收表空间

1.生成回收表空间的sql语句

select  'alter database datafile '''||a.file_name||''' resize '    ||round(a.filesize - (a.filesize - c.hwmsize-100) *0.8)||'M;',  a.filesize,c.hwmsize 
from  ( select file_id,file_name,round(bytes/1024/1024) filesize from dba_data_files ) a,
( select file_id,round(max(block_id)*8/1024) HWMsize from dba_extents group by file_id) c 
where a.file_id = c.file_id   and a.filesize - c.hwmsize > 100

2.执行回收sql语句

alter database datafile 'D:\APP\ADMIN\ORADATA\ORCL\USERS02.DBF' resize 8791M;

3.收缩临时表空间
执行sql语句时，出现报错：无法通过128(在表空间TEMP中)扩展temp段，临时表空间最大容量为32G，执行以下命令收缩临时表空间

alter tablespace temp shrink space;

新建表索引

create index 索引名称 on 表名(列名);