查看Oracle数据库表空间大小的方法
查看Oracle数据库表空间大小的方法
2013-07-30 00:58:06| 分类： Oracle数据库 | 标签：表空间 oracle |举报|字号 订阅
1.查看所表空间大小
SQL>select tablespace_name,sum(bytes)/1024/1024 from dba_data_files
group by tablespace_name;
TABLESPACE_NAME SUM(BYTES)/1024/1024
------------------------------ --------------------
ZXIN_DATA 2048
ZXUMA_DATA 4096
UNDOTBS1 8192
SYSAUX 512
ZXDBP_156 2048
USERS 512
SYSTEM 512
ZXDBP_166 20480
ZXUMA2_DATA 4096
9 rows selected.
2. 已经空闲的表空间大小
SQL>select tablespace_name,sum(bytes)/1024/1024 from dba_free_space
group by tablespace_name;
TABLESPACE_NAME SUM(BYTES)/1024/1024
------------------------------ --------------------
ZXIN_DATA 2047.875
ZXUMA_DATA 3504.0625
UNDOTBS1 2890.6875
SYSAUX 332.0625
ZXDBP_156 2030.1875
USERS 511.9375
SYSTEM 172.25
ZXDBP_166 20325.8125
ZXUMA2_DATA 4076.0625
3.查看Oracle表空间大小--已经使用的百分比
select a.tablespace_name,a.bytes/1024/1024
"Sum MB",(a.bytes-b.bytes)/1024/1024
"used MB",b.bytes/1024/1024 "free MB",
round(((a.bytes-b.bytes)/a.bytes)*100,2) "percent_used"
from
(select tablespace_name,sum(bytes) bytes
from dba_data_files group by tablespace_name) a,
(select tablespace_name,sum(bytes) bytes,max(bytes) largest
from dba_free_space group by tablespace_name) b
where a.tablespace_name=b.tablespace_name
order by ((a.bytes-b.bytes)/a.bytes) desc;
TABLESPACE_NAME Sum MB used MB free MB percent_used
------------------------------ ---------- ---------- ---------- ------------
SYSTEM 512 339.75 172.25 66.36
UNDOTBS1 8192 5301.3125 2890.6875 64.71
SYSAUX 512 179.9375 332.0625 35.14
ZXUMA_DATA 4096 591.9375 3504.0625 14.45
ZXDBP_156 2048 17.8125 2030.1875 .87
ZXDBP_166 20480 154.1875 20325.8125 .75
ZXUMA2_DATA 4096 19.9375 4076.0625 .49
USERS 512 .0625 511.9375 .01
ZXIN_DATA 2048 .125 2047.875 .01
9 rows selected.
4.查看所有segment的大小
SQL>Select Segment_Name,Sum(bytes)/1024/1024 From User_Extents Group By Segment_Name;
5.查看回滚段名称及大小
SELECT segment_name,
tablespace_name,
r.status,
(initial_extent / 1024) initialextent,
(next_extent / 1024) nextextent,
max_extents,
v.curext curextent
FROM dba_rollback_segs r, v$rollstat v
WHERE r.segment_id = https://www.360docs.net/doc/c39446618.html,n(+)
ORDER BY segment_name;
SEGMENT_NAME
TABLESPACE_NAME
STATUS
INITIALEXTENT
NEXTEXTENT
MAX_EXTENTS
CUREXTENT
1
SYSTEM
SYSTEM
ONLINE
112
56
32765
1
2
_SYSSMU1$
UNDOTBS1
ONLINE
128
64
32765
4
3
_SYSSMU10$
UNDOTBS1
ONLINE
128
64
32765
107
4
_SYSSMU2$
UNDOTBS1
ONLINE
128
64
32765
14
5
_SYSSMU3$
UNDOTBS1
ONLINE
128
64
32765
11
6
_SYSSMU4$
UNDOTBS1
ONLINE
128
64
32765
16
7
_SYSSMU5$
U
NDOTBS1
ONLINE
128
64
32765
13
8
_SYSSMU6$
UNDOTBS1
ONLINE
128
64
32765
13
9
_SYSSMU7$
UNDOTBS1
ONLINE
128
64
32765
10
10
_SYSSMU8$
UNDOTBS1
ONLINE
128
64
32765
9
11
_SYSSMU9$
UNDOTBS1
ONLINE
128
64
32765
10
6.查看表空间物理文件的名称及大小
SELECT tablespace_name,
file_id,
file_name,
round(bytes / (1024 * 1024), 0) total_space
FROM dba_data_files
ORDER BY tablespace_name;
TABLESPACE_NAME
FILE_ID
FILE_NAME
TOTAL_SPACE
1
SYSAUX
3
/zxindata/oracle/data/system/sysaux.dbf
512
2
SYSTEM
1
/zxindata/oracle/data/system/system.dbf
512
3
UNDOTBS1
2
/zxindata/oracle/data/system/undo.dbf
8192
4
USERS
4
/zxindata/oracle/data/system/users.dbf
512
5
ZXDBP_156
8
/zxindata/oracle/data/zxdbp_156
2048
6
ZXDBP_166
6
/zxindata/oracle/data/zxdbp_166
20480
7
ZXIN_DATA
5
/zxindata/oracle/data/zxin_data
2048
8
ZXUMA2_DATA
9
/zxindata/oracle/data/zxuma2_data
4096
9
ZXUMA_DATA
7
/zxindata/oracle/data/zxuma_data
4096
7.查看控制文件
SQL> SELECT NAME FROM v$controlfile;
NAME
--------------------------------------------
/zxindata/oracle/data/control/control01.ctl
/zxindata/oracle/data/control/control02.ctl
/zxindata/oracle/data/control/control03.ctl
8.查看日志文件
SQL> SELECT MEMBER FROM v$logfile;
MEMBER
-------------------------------------------
/zxindata/oracle/data/redolog/redo01.dbf
/zxindata/oracle/data/redolog/redo02.dbf
/zxindata/oracle/data/redolog/redo03.dbf
9.查看数据库库对象
SELECT owner, object_type, status, COUNT(*) count#
FROM all_objects
GROUP BY owner, object_type, status;
10.查看数据库的版本
SQL> SELECT version FROM product_component_version WHERE substr(product, 1, 6) = 'Oracle';
VERSION
--------------------------------------------------------------------------------
10.2.0.5.0
11.查看数据库的创建日期和归档方式
SQL> SELECT created, log_mode, log_mode FROM v$database;
CREATED LOG_MODE LOG_MODE
------------------- ------------ ------------
2012-09-11 16:09:55 NOARCHIVELOG NOARCHIVELOG
12.查看 xxx 表空间是否为自动扩展
select file_id,file_name,tablespace_name,autoextensible,increment_by from dba_data_files order by file_id desc;
FILE_ID
FILE_NAME
TABLESPACE_NAME
AUTOEXTENSIBLE
INCREMENT_BY
1
9
/zxindata/oracle/data/zxuma2_data
ZXUMA2_DATA
YES
16384
2
8
/zxindata/oracle/data/zxdbp_156
ZXDBP_156
YES
16384
3
7
/zxindata/oracle/data/zxuma_data
ZXUMA_DATA
YES
16384
4
6
/zxindata/oracle/data/zxdbp_166
ZXDBP_166
YES
16384
5
5
/zxindata/oracle/data/zxin_data
ZXIN_DATA
YES
16384
6
4
/zxindata/oracle/data/system/users.dbf
USERS
NO
0
7
3
/zxindata/oracle/data/system/sysaux.dbf
SYSAUX
NO
0
8
2
/zxindata/oracle/data/system/undo.dbf
UNDOTBS1
NO
0
9
1
/zxindata/oracle/data/system/system.dbf
SYSTEM
NO
0