查看表空间使用情况
column tablespace_name format a20;
column avail format 999999999;
column used format 999999999;
column free format 999999999;
SELECT dts.tablespace_name, NVL(ddf.bytes / 1024 / 1024, 0) avail,
NVL(ddf.bytes - NVL(dfs.bytes, 0), 0)/1024/1024 used,
NVL(dfs.bytes / 1024 / 1024, 0) free,
TO_CHAR(NVL((ddf.bytes - NVL(dfs.bytes, 0)) / ddf.bytes * 100, 0), '990.00') "Used %"
FROM
dba_tablespaces dts,
(select tablespace_name, sum(bytes) bytes from dba_data_files group by tablespace_name) ddf,
(select tablespace_name, sum(bytes) bytes from dba_free_space group by tablespace_name) dfs
WHERE dts.tablespace_name = ddf.tablespace_name(+)
AND dts.tablespace_name = dfs.tablespace_name(+)
AND NOT (dts.extent_management like 'LOCAL' AND dts.contents like 'TEMPORARY')
order by 5;
查看数据文件大小
select file_name,tablespace_name,autoextensible,bytes/1024/1024/1024 from dba_data_files;
扩大表空间,添加数据文件
ALTER TABLESPACE "XXX"
ADD
DATAFILE '+DATA/XXX1.ora' SIZE 33554424K REUSE AUTOEXTEND
ON NEXT 1048576K MAXSIZE UNLIMITED;
可以一次同时增加多个数据文件
alter tablespace "XXX"
ADD
DATAFILE '+DATA/XXX1.ora' SIZE 33554424K REUSE AUTOEXTEND ON NEXT 1048576K MAXSIZE UNLIMITED,
'+DATA/XXX2.ora'' SIZE 33554424K REUSE AUTOEXTEND ON NEXT 1048576K MAXSIZE UNLIMITED;
回退方案:
删除所加数据文件
Alter tablespace XXX drop datafile '+DATA/XXX2.ora''
大文件表空间扩容:
ALTER TABLESPACE bigtbs RESIZE 10821G;
ASM磁盘组大小及剩余空间
set lines 150 pages 200
col path for a40
col HEADER_STATUS for a9
col disk_name for a12
col type for a16
col bytes for 999,999,999,999
col name for a52
col name2 for a15
col group_number heading 'Group|_NO' format 99
col file_number heading 'FILE|NO' format 9999
col redundancy format a6 noprint
col striped format a6 noprint
col FAILGROUP for a10
col disk_number heading 'Disk|_NO' format 9999
col MOUNT_STATUS heading 'Mount|_Status'
col FAILGROUP heading 'FAIL|_GROUP'
col TOTAL_MB heading 'Total|_MB' format 99,999,999
select name name2,state,type,free_mb,total_mb,usable_file_mb from v$asm_diskgroup;
ASM使用磁盘信息
select GROUP_NUMBER,free_mb,total_mb,FAILGROUP,disk_number,
MOUNT_STATUS,mode_status,STATE,HEADER_STATUS,name name2,PATH from v$asm_disk order by 4,5;
查看数据库内存分配情况
select component,current_size,min_size,max_size from v$memory_dynamic_components;
0 0