查看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 = 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