表空间

--查看表空间2
select a.tablespace_name 表空间名,
round(a.total_size) "表空间大小(MB)",
round(a.total_size) - round(b.free_size, 3) "已使用空间(MB)",
round(b.free_size, 3) "空闲空间(MB)",
round(b.free_size / total_size * 100, 2) || '%' 空闲比,
round((a.total_size - b.free_size) / total_size * 100, 2) || '%' 使用比
from (select tablespace_name, sum(bytes) / 1024 / 1024 total_size
from dba_data_files
group by tablespace_name) a,
(select tablespace_name, sum(bytes) / 1024 / 1024 free_size
from dba_free_space
group by tablespace_name) b
where a.tablespace_name = b.tablespace_name(+);
--查看表空间2
select a.TABLESPACE_NAME,sum(a.BYTES)/1024/1024 from user_free_space a group by a.TABLESPACE_NAME
--删除表空建
/* drop tablespace TEST1
alter tablespace "TEST1" add datafile '+BJXG_CS_CBPC_DATA/DCSDB/DATAFILE/test2.dbf' size 10M
autoextend on next 10m
maxsize 100m*/
--修改表空间文件大小
alter tablespace "DCMEXT_DATA" add datafile '+BJXG_CS_CBPC_DATA/DCSDB/DATAFILE/dcmext_data02.dbf' size 128M
autoextend on next 1024m
maxsize 30720m
--创建表空间
create tablespace DCMEXT_DATA
logging
datafile '+BJXG_CS_CBPC02_DB_DATA/DCSCB/DATAFILE/dcmext_data01.dbf'
size 128m
autoextend on next 1024m maxsize 30720m
extent management local;
--BJXG_CS_CBPC02_DB_DATA
select * from dba_data_files
select name,TOTAL_MB,FREE_MB from v$asm_diskgroup
select * from v$asm_diskgroup
select * from v$asm_disk
select * from v$asm_disk_stat a;

 

 

1.oracle查询表空间是否已满

select dbf.tablespace_name,
dbf.totalspace "总量(M)",
dbf.totalblocks as 总块数,
dfs.freespace "剩余总量(M)",
dfs.freeblocks "剩余块数",
(dfs.freespace / dbf.totalspace) * 100 "空闲比例"
from (select t.tablespace_name,
sum(t.bytes) / 1024 / 1024 totalspace,
sum(t.blocks) totalblocks
from dba_data_files t
group by t.tablespace_name) dbf,
(select tt.tablespace_name,
sum(tt.bytes) / 1024 / 1024 freespace,
sum(tt.blocks) freeblocks
from dba_free_space tt
group by tt.tablespace_name) dfs
where trim(dbf.tablespace_name) = trim(dfs.tablespace_name)；

 

或者使用

SELECT Total.name "Tablespace Name",
Free_space,
(total_space - Free_space) Used_space,
total_space
FROM (select tablespace_name, sum(bytes / 1024 / 1024) Free_Space
from sys.dba_free_space
group by tablespace_name) Free,
(select b.name, sum(bytes / 1024 / 1024) TOTAL_SPACE
from sys.v_$datafile a, sys.v_$tablespace B
where a.ts# = b.ts#
group by b.name) Total
WHERE Free.Tablespace_name = Total.name;

或者使用
select a.TABLESPACE_NAME,sum(a.BYTES)/1024/1024 from user_free_space a group by a.TABLESPACE_NAME

2.再查看表空间是否开启了自动扩展的功能

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;

 

从上表可以看出，所有的表空间都开启了自动扩展的功能，而且状态都是available的；

综合上述检查结果，可断定遇到的问题是因为可能性1—表空间不足导致。解决办法也就是扩大表空间

扩大表空间的四种方法：
1、增加数据文件
ALTER TABLESPACE ***_TRD ADD DATAFILE
‘D:\Oracle\PRODUCT\10.2.0\ORADATA\DBFILE\TRD_2.DBF’ SIZE 1024M;

2、增加数据文件并允许自动增长
ALTER TABLESPACE ***_TRD ADD DATAFILE
‘D:\ORACLE\PRODUCT\10.2.0\ORADATA\DBFILE\TRD_2.DBF’ SIZE 1024M AUTOEXTEND ON NEXT 8M MAXSIZE 10240M;

3、允许已存在的数据文件自动增长
ALTER DATABASE DATAFILE ‘D:\ORACLE\PRODUCT\10.2.0\ORADATA\DBFILE\TRD.DBF’
AUTOEXTEND ON NEXT 8M MAXSIZE 10240M;

4、手工改变已存在数据文件的大小
ALTER DATABASE DATAFILE ‘D:\ORACLE\PRODUCT\10.2.0\ORADATA\DBFILE\TRD.DBF’
RESIZE 10240M;

实际解决过程中，我们使用的是方法2。