oracle表空间扩展
oracle表空间扩展
创建表表空间：
create tablespace test1204
datafile 'd:\testSpace.dbf' size 100M
autoextend on;--自动增
查看表空间的名字及文件所在位置：
select tablespace_name, file_id, file_name, round(bytes/(1024*1024),0) total_space from dba_data_files order by tablespace_name;
创建表表空间：
create tablespace test1204
datafile 'd:\testSpace.dbf' size 100M
autoextend on;--自动增
查看表空间的名字及文件所在位置：
select tablespace_name, file_id, file_name, round(bytes/(1024*1024),0) total_space from dba_data_files order by tablespace_name;
增大所需表空间大小：
alter database datafile '表空间位置'resize 新的尺寸
alter database datafile '表空间位置'resize 新的尺寸
例如：
alter database datafile '\oracle\oradata\anita_2008.dbf' resize 4000m
增加数据文件个数
alter tablespace 表空间名称add datafile '新的数据文件地址' size 数据文件大小
eg：alter tablespace ESPS_2008 add datafile '\oracle\oradata\anita_2010.dbf' size 1000m
设置表空间自增长：
alter database datafile '数据文件位置' autoextend on next 自动扩展大小maxsize 最大扩展大小
eg：alter database datafile '\oracle\oradata\anita_2008.dbf' autoextend on next 100m maxsize 10000m ；
--查询表空间的总容量
select tablespace_name, sum(bytes) / 1024 / 1024 as MB
from dba_data_files
group by tablespace_name;
select tablespace_name, sum(bytes) / 1024 / 1024 as MB
from dba_data_files
group by tablespace_name;
--查询表空间使用率
select total.tablespace_name,
round(total.MB, 2) as Total_MB,考试大论坛
round(total.MB - free.MB, 2) as Used_MB,
round((1 - free.MB / total.MB) * 100, 2) || '%' as Used_Pct
from (select tablespace_name, sum(bytes) / 1024 / 1024 as MB
from dba_free_space
group by tablespace_name) free,
(select tablespace_name, sum(bytes) / 1024 / 1024 as MB
from dba_data_files
group by tablespace_name) total
where free.tablespace_name = total.tablespace_name;
select total.tablespace_name,
round(total.MB, 2) as Total_MB,考试大论坛
round(total.MB - free.MB, 2) as Used_MB,
round((1 - free.MB / total.MB) * 100, 2) || '%' as Used_Pct
from (select tablespace_name, sum(bytes) / 1024 / 1024 as MB
from dba_free_space
group by tablespace_name) free,
(select tablespace_name, sum(bytes) / 1024 / 1024 as MB
from dba_data_files
group by tablespace_name) total
where free.tablespace_name = total.tablespace_name;
原文链接：http://www.jxszl.com/biancheng/shujuku/445425.html
