Oracle表空间占用已满的问题

今天发现，原来设备的数据表空间只有5M，已经满了，上网去找发现需要进行扩展空间。

一、脚本修改方式：

----查询表空间使用情况---
使用DBA权限登陆
SELECT UPPER(F.TABLESPACE_NAME) "表空间名",
D.TOT_GROOTTE_MB "表空间大小(M)",
D.TOT_GROOTTE_MB - F.TOTAL_BYTES "已使用空间(M)",
TO_CHAR(ROUND((D.TOT_GROOTTE_MB - F.TOTAL_BYTES) / D.TOT_GROOTTE_MB * 100,2),'990.99') "使用比",
F.TOTAL_BYTES "空闲空间(M)",
F.MAX_BYTES "最大块(M)"
FROM (SELECT TABLESPACE_NAME,
ROUND(SUM(BYTES) / (1024 * 1024), 2) TOTAL_BYTES,
ROUND(MAX(BYTES) / (1024 * 1024), 2) MAX_BYTES
FROM SYS.DBA_FREE_SPACE
GROUP BY TABLESPACE_NAME) F,
(SELECT DD.TABLESPACE_NAME,
ROUND(SUM(DD.BYTES) / (1024 * 1024), 2) TOT_GROOTTE_MB
FROM SYS.DBA_DATA_FILES DD
GROUP BY DD.TABLESPACE_NAME) D
WHERE D.TABLESPACE_NAME = F.TABLESPACE_NAME
ORDER BY 4 DESC;
表空间名 表空间大小(M) 已使用空间(M) 使用比 空闲空间(M) 最大块(M)
------------------------------ ------------- ------------- ------- ----------- ----------
...
CCEN 10 8.5 85.00 1.5 .94
...

发现表空间只有1.5M的空闲,猜测可能是表空间自动扩展失败的问题(表空间的增长量太高，ORACLE默认是50%),修改表空间文件扩展方式:

SQL>ALTER DATABASE
DATAFILE '/u01/oracle/oradata/orcl/ccen01.dbf' AUTOEXTEND
ON NEXT 50M MAXSIZE UNLIMITED

问题解决.
查看表空间是否具有自动扩展的能力

SELECT T.TABLESPACE_NAME,D.FILE_NAME,
D.AUTOEXTENSIBLE,D.BYTES,D.MAXBYTES,D.STATUS
FROM DBA_TABLESPACES T,DBA_DATA_FILES D
WHERE T.TABLESPACE_NAME =D.TABLESPACE_NAME
ORDER BY TABLESPACE_NAME,FILE_NAME;

二、使用控制台方式：

打开ORACLE的企业管理器，到表空间选择要修改的表空间，双击点开表穿间的编缉界面，点击那只笔的图标，在编辑数据文件界面的存储选项卡上就可以修改“数据文件已满后自动扩展”修改希望的增量，和最大值后点应用进行保存。

三、从创建表空间到添加数据文件再到绑定用户并赋权使用的全流程，以及成功后的验证方法：

PANDA是我的用户名 MYSPACE是表空间名称 MYSPACE_temp是临时表空间名称 .dbf结尾为数据文件

1、创建数据表空间

初始大小10g,每次自动增长100m

create tablespace MYSPACE datafile '/home/oracle_datafile/MYSPACE01.dbf' size 10g autoextend ON next 100 m maxsize unlimited;

2、给表空间增加多个数据文件

每个文件大小10g,每次自动增长100m （这一步可直接给已有的表空间添加数据文件使用）

-- 根据需要添加一定数量的数据文件
alter tablespace MYSPACE add datafile '/home/oracle_datafile/MYSPACE02.dbf' size 10 g autoextend ON next 100 m maxsize unlimited;
alter tablespace MYSPACE add datafile '/home/oracle_datafile/MYSPACE03.dbf' size 10 g autoextend ON next 100 m maxsize unlimited;
alter tablespace MYSPACE add datafile '/home/oracle_datafile/MYSPACE04.dbf' size 10 g autoextend ON next 100 m maxsize unlimited;

3、创建临时表空间

用于保存系统中短期活动的数据比如进行一些运算和索引创建等，多数使用完会自动清理

create temporary tablespace MYSPACE_temp tempfile '/home/oracle_datafile/MYSPACE_temp.dbf' size 10g autoextend ON next 100m maxsize 30G;

4、创建用户并指定表空间和临时表空间

create user PANDA identified by PANDA default tablespace MYSPACE temporary tablespace MYSPACE_temp;

5、分配权限

grant connect,resource,dba to PANDA;

---

6、如何删除所有表？

-- 将查询所得结果，复制粘入command窗口即可
SELECT 'drop  table '|| table_name || ';' FROM USER_TABLES ORDER BY TABLE_NAME;

7、如何删除表空间以及对应的表空间文件？

drop tablespace MYSPACE INCLUDING CONTENTS AND DATAFILES CASCADE CONSTRAINTS;

8、如何查看表空间名和数据文件位置？

SELECT
	tablespace_name "表空间名",
	file_name "数据文件位置",
	round( maxbytes / ( 1024 * 1024 ), 2 ) AS "可扩展最大值（M）",
	autoextensible "自动扩展",
	STATUS "状态",
	increment_by "自动扩展增量" 
FROM
	dba_data_files 
ORDER BY
	tablespace_name;

9、如何查看表空间详情及使用情况？

SELECT
	upper( f.tablespace_name ) "表空间名",
	d.tot_grootte_mb "表空间大小（M）",
	d.tot_grootte_mb - f.total_bytes "已使用空间（M）",
	TO_CHAR ( ROUND( ( d.tot_grootte_mb - f.total_bytes ) / d.tot_grootte_mb * 100, 2 ), '990.99' ) "使用比",
	f.total_bytes "空闲空间（M）",
	f.max_bytes "最大块（M）" 
FROM
	(
SELECT
	tablespace_name,
	ROUND( sum( bytes ) / ( 1024 * 1024 ), 2 ) total_bytes,
	ROUND( MAX( bytes ) / ( 1024 * 1024 ), 2 ) max_bytes 
FROM
	sys.DBA_FREE_SPACE 
GROUP BY
	tablespace_name 
	) f,
	(
SELECT
	dd.tablespace_name,
	round( sum( dd.bytes ) / ( 1024 * 1024 ), 2 ) tot_grootte_mb 
FROM
	sys.DBA_DATA_FILES dd 
GROUP BY
	dd.tablespace_name 
	) d 
WHERE
	d.tablespace_name = f.tablespace_name 
ORDER BY
	4 DESC;

四、删除过期用户及空闲表空间释放空间

第一步：删除过期用户

DROP USER colin CASCADE;

第二步：查询过期用户相关的表空间是否还有对象

SELECT OWNER, SEGMENT_NAME, SEGMENT_TYPE, TABLESPACE_NAME
  FROM DBA_SEGMENTS
 WHERE TABLESPACE_NAME IN
       (SELECT TABLESPACE_NAME
          FROM DBA_TABLESPACES
         WHERE TABLESPACE_NAME LIKE '%COLIN%')

第三步：转移过期用户表空间上的对象到其他表空间

ALTER TABLE tab_colin MOVE TABLESPACE colin_song_tbs;

ALTER INDEX idx_tab_colin REBUILD TABLESPACE colin_song_idx_tbs;

第四步：查询无对象的表空间

SELECT 'DROP TABLESPACE ' || TABLESPACE_NAME ||
       ' INCLUDING CONTENTS AND DATAFILES;'
  FROM DBA_TABLESPACES
 WHERE NOT EXISTS (SELECT 12
          FROM DBA_SEGMENTS
         WHERE DBA_SEGMENTS.TABLESPACE_NAME =
               DBA_TABLESPACES.TABLESPACE_NAME);

第五步：删除之前

把上述查询出来的语句贴到文本编辑里检查下（ PS:这里会包含一个temp的临时表空间一定要去掉啊)

SQL> drop tablespace STAT_FTFY_CHRGSTAY_DTBS including contents and datafiles;
 
Tablespace dropped
SQL> drop tablespace STAT_FTFY_CHRGNEW_WIXTBS including contents and datafiles;
 
Tablespace dropped
SQL> drop tablespace STAT_FTFY_CHRGSMR_MIXTBS including contents and datafiles;
 
Tablespace dropped

最后去看下你的系统空间应该释放了吧