加载中…
个人资料
只吃小鱼干的猫先生
只吃小鱼干的猫先生
  • 博客等级:
  • 博客积分:0
  • 博客访问:17,461
  • 关注人气:3
  • 获赠金笔:0支
  • 赠出金笔:0支
  • 荣誉徽章:
相关博文
推荐博文
谁看过这篇博文
加载中…
正文 字体大小:

查看Oracle数据库表空间大小的方法合集

(2017-07-06 14:23:00)
标签:

oracle

表空间大小

dba_data_files

分类: IT技术

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 = v.usn(+)

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$

UNDOTBS1

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

0

阅读 评论 收藏 转载 喜欢 打印举报/Report
  • 评论加载中,请稍候...
发评论

    发评论

    以上网友发言只代表其个人观点,不代表新浪网的观点或立场。

      

    新浪BLOG意见反馈留言板 电话:4000520066 提示音后按1键(按当地市话标准计费) 欢迎批评指正

    新浪简介 | About Sina | 广告服务 | 联系我们 | 招聘信息 | 网站律师 | SINA English | 会员注册 | 产品答疑

    新浪公司 版权所有