--查看表空间--
select * from dba_data_files;
select tablespace_name,sum(bytes),sum(free),sum(free)*100/sum(bytes) from (select
b.file_id file_ID,
b.tablespace_name tablespace_name,
b.bytes Bytes,
(b.bytes-sum(nvl(a.bytes,0))) used,
sum(nvl(a.bytes,0)) free,
sum(nvl(a.bytes,0))/(b.bytes)*100 Percent
from dba_free_space a,dba_data_files b
where a.file_id=b.file_id
group by b.tablespace_name,b.file_id,b.bytes
order by b.file_id) group by tablespace_name order by sum(free)*100/sum(bytes);
--查看表空间是否自动扩展--
select file_name,autoextensible,increment_by from dba_data_files;
--表空间自动扩展--
alter database datafile 'path:\datafile name' autoextend on next 1M maxsize 100M;
--自动扩展实例--
alter database datafile 'E:\ORACLE\PRODUCT\10.2.0\ORADATA\HZDB\CODRP_ATTACHMENT.ORA'
autoextend on next 50M maxsize 1000M;
--表空间大小--
select tablespace_name,count(*),sum(blocks),sum(bytes)/1024/1024
from dba_data_files
group by tablespace_name;
--表空间使用情况--
select df.tablespace_name "表空间名",totalspace "总空间M",freespace "剩余空间M"
,round((1-freespace/totalspace)*100,2) "使用率%"
from
(select tablespace_name,round(sum(bytes)/1024/1024) totalspace
from dba_data_files group by tablespace_name) df,
(select tablespace_name,round(sum(bytes)/1024/1024) freespace
from dba_free_space group by tablespace_name) fs
where df.tablespace_name=fs.tablespace_name;
--查看表空间使用状况--
oracle表空间的事情状况要经常查看,一般空闲比例过低的时候就应该考虑增大表看空间了。
查看方法如下SQL:
方法一:
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
当发现有的表空间不够的错误时,处理如下:
1:找出该表空间对应的数据文件及路径
select * from dba_data_files t where t.tablespace_name = 'ARD'
2:增大数据文件
alter database datafile '全路径的数据文件名称' resize ***M
3:增加数据文件
alter tablespace 表空间名称
add datafile '全路径的数据文件名称' ***M
注解:表空间尽量让free百分比保持在10%以上,如果低于10%就增加datafile或者resizedatafile,一般数据文件不要超过2G
select * from dba_data_files;
select tablespace_name,sum(bytes),sum(free),sum(free)*100/sum(bytes) from (select
b.file_id file_ID,
b.tablespace_name tablespace_name,
b.bytes Bytes,
(b.bytes-sum(nvl(a.bytes,0))) used,
sum(nvl(a.bytes,0)) free,
sum(nvl(a.bytes,0))/(b.bytes)*100 Percent
from dba_free_space a,dba_data_files b
where a.file_id=b.file_id
group by b.tablespace_name,b.file_id,b.bytes
order by b.file_id) group by tablespace_name order by sum(free)*100/sum(bytes);
--查看表空间是否自动扩展--
select file_name,autoextensible,increment_by from dba_data_files;
--表空间自动扩展--
alter database datafile 'path:\datafile name' autoextend on next 1M maxsize 100M;
--自动扩展实例--
alter database datafile 'E:\ORACLE\PRODUCT\10.2.0\ORADATA\HZDB\CODRP_ATTACHMENT.ORA'
autoextend on next 50M maxsize 1000M;
--表空间大小--
select tablespace_name,count(*),sum(blocks),sum(bytes)/1024/1024
from dba_data_files
group by tablespace_name;
--表空间使用情况--
select df.tablespace_name "表空间名",totalspace "总空间M",freespace "剩余空间M"
,round((1-freespace/totalspace)*100,2) "使用率%"
from
(select tablespace_name,round(sum(bytes)/1024/1024) totalspace
from dba_data_files group by tablespace_name) df,
(select tablespace_name,round(sum(bytes)/1024/1024) freespace
from dba_free_space group by tablespace_name) fs
where df.tablespace_name=fs.tablespace_name;
--查看表空间使用状况--
oracle表空间的事情状况要经常查看,一般空闲比例过低的时候就应该考虑增大表看空间了。
查看方法如下SQL:
方法一:
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
当发现有的表空间不够的错误时,处理如下:
1:找出该表空间对应的数据文件及路径
select * from dba_data_files t where t.tablespace_name = 'ARD'
2:增大数据文件
alter database datafile '全路径的数据文件名称' resize ***M
3:增加数据文件
alter tablespace 表空间名称
add datafile '全路径的数据文件名称' ***M
注解:表空间尽量让free百分比保持在10%以上,如果低于10%就增加datafile或者resizedatafile,一般数据文件不要超过2G