文章目录
今天发现,原来设备的数据表空间只有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
最后去看下你的系统空间应该释放了吧