从08年的压力测试开始,零星整理了很多Oracle的命令,东一坨西一坨的也不知道放到了那里,懒惰之人必有可恨之处啊。
-- 查询表空间的使用率
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 F.TABLESPACE_NAME;
-- 查看其他表空间的数据文件
select a.TABLESPACE_NAME "表空间名",
a.FILE_NAME "物理文件",
a.BYTES / 1024 / 1024 "表空间大小(M)",
a.AUTOEXTENSIBLE "自动扩展"
from DBA_DATA_FILES a
order by a.TABLESPACE_NAME;
-- 创建表空间
create tablespace CRM_APP logging datafile 'd:\oradata\crm_app_1.dbf' size 1024m autoextend on next 50m maxsize 5120m extent management local;
-- 给表空间添加数据文件
alter tablespace CRM_APP add datafile 'd:\oradata\crm_app_2.dbf' size 1024m reuse autoextend on next 50m maxsize 5120m;
-- 调整表空间数据文件大小
-- ps:调整的值不能小于实际使用值,否则会报【ORA-03297: 文件包含在请求的RESIZE 值以外使用的数据……】的错误
alter database datafile 'd:\oradata\crm_app_2.dbf' resize 2048M;
-- 查看临时表空间的数据文件
select tablespace_name "表空间名",
file_name "物理文件",
bytes / 1024 / 1024 "表空间大小(M)",
autoextensible "自动扩展"
from dba_temp_files
order by tablespace_name;
-- 创建临时表空间
create temporary tablespace CRM_TEMP tempfile 'D:\oradata\crm_temp_0.dbf' size 1024m autoextend on next 50m maxsize 2048m extent management local;
-- 给临时表空间添加数据文件
alter tablespace CRM_TEMP add tempfile 'D:\oradata\crm_temp_1.dbf' size 1024m reuse autoextend on next 50m maxsize 5120m;
-- 调整临时表空间数据文件大小
alter database tempfile 'D:\oradata\crm_temp_1.dbf' resize 2048M;
【缩小临时表空间的方法】
-- 1.给临时表空间增加一个数据文件,大小自己定义【命令见上面!】
-- 2.将之前的临时表空间数据文件脱机
alter database tempfile 'D:\oradata\crm_temp_0.dbf' offline;
-- 3.删除脱机的数据文件,达到缩小临时表空间的效果
alter database tempfile 'D:\oradata\crm_temp_0.dbf' drop including datafiles;
【UNDO表空间爆满的处理方法】
-- 1.创建新的UNDO表空间
create undo tablespace undotbs2 datafile 'C:\app\Administrator\oradata\orcl\undo2.dbf' size 1024m reuse autoextend on next 50m maxsize 5120m;
-- 2.给UNDO表空间添加数据文件
alter tablespace undotbs2 add datafile 'C:\app\Administrator\oradata\orcl\undo2_2.dbf' size 1024m reuse autoextend on next 50m maxsize 5120m;
-- 3.设置Oracle的默认UNDO表空间为新建的UNDO表空间
alter system set undo_tablespace=undotbs2 scope=both;
-- 4.做一个备份文件【可略过】
create pfile from spfile;
-- 5.删除原有的UNDO表空间及数据文件
drop tablespace undotbs1 including contents; -- 只删除表空间
drop tablespace undotbs1 including contents and datafiles; -- 删除表空间及数据文件
-- 6.删除物理文件【如果数据文件没有删除的话,手动做一下】
-- 查看表占用的物理空间
select segment_name "表名", bytes / 1024 / 1024 "大小(M)"
from dba_segments
where segment_type = 'TABLE'
and owner = 'CRMT'
order by segment_name;
-- 查看索引占用的物理空间【索引有时候占用的物理空间比数据表还大,第一次做数据库规划的时候差点被害死】
select segment_name "索引名", bytes / 1024 / 1024 "大小(M)"
from dba_segments a
where segment_type = 'INDEX'
and owner = 'CRMT'
order by segment_name;
-- 查看数据文件的使用率
select a.name "物理路径",
a.bytes / 1024 / 1024 "文件大小(M)",
ceil(HWM * a.block_size) / 1024 / 1024 "已使用(M)",
(a.bytes - HWM * a.block_size) / 1024 / 1024 "可释放(M)"
from v$datafile a,
(select file_id, max(block_id + blocks - 1) HWM
from dba_extents
group by file_id) b
where a.file# = b.file_id(+)
and (a.bytes - HWM * block_size) > 0
order by a.name;
一个用户最好使用独立的用户表空间和用户临时表空间,索引需要部署在独立的表空间上,数据表也可以根据用途划分部署在不同的表空间。
windows环境下的数据库不建议创建太大的表空间数据文件(10G),反正多建几个数据文件也不是什么麻烦事。
-- 查询表空间的使用率
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 F.TABLESPACE_NAME;
-- 查看其他表空间的数据文件
select a.TABLESPACE_NAME "表空间名",
a.FILE_NAME "物理文件",
a.BYTES / 1024 / 1024 "表空间大小(M)",
a.AUTOEXTENSIBLE "自动扩展"
from DBA_DATA_FILES a
order by a.TABLESPACE_NAME;
-- 创建表空间
create tablespace CRM_APP logging datafile 'd:\oradata\crm_app_1.dbf' size 1024m autoextend on next 50m maxsize 5120m extent management local;
-- 给表空间添加数据文件
alter tablespace CRM_APP add datafile 'd:\oradata\crm_app_2.dbf' size 1024m reuse autoextend on next 50m maxsize 5120m;
-- 调整表空间数据文件大小
-- ps:调整的值不能小于实际使用值,否则会报【ORA-03297: 文件包含在请求的RESIZE 值以外使用的数据……】的错误
alter database datafile 'd:\oradata\crm_app_2.dbf' resize 2048M;
-- 查看临时表空间的数据文件
select tablespace_name "表空间名",
file_name "物理文件",
bytes / 1024 / 1024 "表空间大小(M)",
autoextensible "自动扩展"
from dba_temp_files
order by tablespace_name;
-- 创建临时表空间
create temporary tablespace CRM_TEMP tempfile 'D:\oradata\crm_temp_0.dbf' size 1024m autoextend on next 50m maxsize 2048m extent management local;
-- 给临时表空间添加数据文件
alter tablespace CRM_TEMP add tempfile 'D:\oradata\crm_temp_1.dbf' size 1024m reuse autoextend on next 50m maxsize 5120m;
-- 调整临时表空间数据文件大小
alter database tempfile 'D:\oradata\crm_temp_1.dbf' resize 2048M;
【缩小临时表空间的方法】
-- 1.给临时表空间增加一个数据文件,大小自己定义【命令见上面!】
-- 2.将之前的临时表空间数据文件脱机
alter database tempfile 'D:\oradata\crm_temp_0.dbf' offline;
-- 3.删除脱机的数据文件,达到缩小临时表空间的效果
alter database tempfile 'D:\oradata\crm_temp_0.dbf' drop including datafiles;
【UNDO表空间爆满的处理方法】
-- 1.创建新的UNDO表空间
create undo tablespace undotbs2 datafile 'C:\app\Administrator\oradata\orcl\undo2.dbf' size 1024m reuse autoextend on next 50m maxsize 5120m;
-- 2.给UNDO表空间添加数据文件
alter tablespace undotbs2 add datafile 'C:\app\Administrator\oradata\orcl\undo2_2.dbf' size 1024m reuse autoextend on next 50m maxsize 5120m;
-- 3.设置Oracle的默认UNDO表空间为新建的UNDO表空间
alter system set undo_tablespace=undotbs2 scope=both;
-- 4.做一个备份文件【可略过】
create pfile from spfile;
-- 5.删除原有的UNDO表空间及数据文件
drop tablespace undotbs1 including contents; -- 只删除表空间
drop tablespace undotbs1 including contents and datafiles; -- 删除表空间及数据文件
-- 6.删除物理文件【如果数据文件没有删除的话,手动做一下】
-- 查看表占用的物理空间
select segment_name "表名", bytes / 1024 / 1024 "大小(M)"
from dba_segments
where segment_type = 'TABLE'
and owner = 'CRMT'
order by segment_name;
-- 查看索引占用的物理空间【索引有时候占用的物理空间比数据表还大,第一次做数据库规划的时候差点被害死】
select segment_name "索引名", bytes / 1024 / 1024 "大小(M)"
from dba_segments a
where segment_type = 'INDEX'
and owner = 'CRMT'
order by segment_name;
-- 查看数据文件的使用率
select a.name "物理路径",
a.bytes / 1024 / 1024 "文件大小(M)",
ceil(HWM * a.block_size) / 1024 / 1024 "已使用(M)",
(a.bytes - HWM * a.block_size) / 1024 / 1024 "可释放(M)"
from v$datafile a,
(select file_id, max(block_id + blocks - 1) HWM
from dba_extents
group by file_id) b
where a.file# = b.file_id(+)
and (a.bytes - HWM * block_size) > 0
order by a.name;
一个用户最好使用独立的用户表空间和用户临时表空间,索引需要部署在独立的表空间上,数据表也可以根据用途划分部署在不同的表空间。
windows环境下的数据库不建议创建太大的表空间数据文件(10G),反正多建几个数据文件也不是什么麻烦事。