oracle 表空间剩余大小,oracle 查看表空间以及剩余量

oracle 查看表空间以及剩余量

–1、查看表空间的名称及大小

SELECT t.tablespace_name, round(SUM(bytes / (1024 * 1024)), 0) ts_size

FROM dba_tablespaces t, dba_data_files d

WHERE t.tablespace_name = d.tablespace_name

GROUP BY t.tablespace_name;

–2、查看表空间物理文件的名称及大小

SELECT tablespace_name,

file_id,

file_name,

round(bytes / (1024 * 1024), 0) total_space

FROM dba_data_files

ORDER BY tablespace_name;

–3、查看回滚段名称及大小

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

r

o

l

l

s

t

a

t

v

W

H

E

R

E

r

.

s

e

g

m

e

n

t

i

d

=

v

.

u

s

n

(

+

)

O

R

D

E

R

B

Y

s

e

g

m

e

n

t

n

a

m

e

;

−

−

4

、

查

看

控

制

文

件

S

E

L

E

C

T

N

A

M

E

F

R

O

M

v

rollstat v WHERE r.segment_id = v.usn(+) ORDER BY segment_name; --4、查看控制文件 SELECT NAME FROM v

rollstatvWHEREr.segmenti​d=v.usn(+)ORDERBYsegmentn​ame;−−4、查看控制文件SELECTNAMEFROMvcontrolfile;

–5、查看日志文件

SELECT MEMBER FROM v

l

o

g

f

i

l

e

;

−

−

6

、

查

看

表

空

间

的

使

用

情

况

S

E

L

E

C

T

S

U

M

(

b

y

t

e

s

)

/

(

1024

∗

1024

)

A

S

f

r

e

e

s

p

a

c

e

,

t

a

b

l

e

s

p

a

c

e

n

a

m

e

F

R

O

M

d

b

a

f

r

e

e

s

p

a

c

e

G

R

O

U

P

B

Y

t

a

b

l

e

s

p

a

c

e

n

a

m

e

;

S

E

L

E

C

T

a

.

t

a

b

l

e

s

p

a

c

e

n

a

m

e

,

a

.

b

y

t

e

s

t

o

t

a

l

,

b

.

b

y

t

e

s

u

s

e

d

,

c

.

b

y

t

e

s

f

r

e

e

,

(

b

.

b

y

t

e

s

∗

100

)

/

a

.

b

y

t

e

s

"

(

c

.

b

y

t

e

s

∗

100

)

/

a

.

b

y

t

e

s

"

F

R

O

M

s

y

s

.

s

m

logfile; --6、查看表空间的使用情况 SELECT SUM(bytes) / (1024 * 1024) AS free_space, tablespace_name FROM dba_free_space GROUP BY tablespace_name; SELECT a.tablespace_name, a.bytes total, b.bytes used, c.bytes free, (b.bytes * 100) / a.bytes "% USED ", (c.bytes * 100) / a.bytes "% FREE " FROM sys.sm

logfile;−−6、查看表空间的使用情况SELECTSUM(bytes)/(1024∗1024)ASfrees​pace,tablespacen​ameFROMdbaf​rees​paceGROUPBYtablespacen​ame;SELECTa.tablespacen​ame,a.bytestotal,b.bytesused,c.bytesfree,(b.bytes∗100)/a.bytes"(c.bytes∗100)/a.bytes"FROMsys.smts_avail a, sys.sm

t

s

u

s

e

d

b

,

s

y

s

.

s

m

ts_used b, sys.sm

tsu​sedb,sys.smts_free c

WHERE a.tablespace_name = b.tablespace_name

AND a.tablespace_name = c.tablespace_name;

–7、查看数据库库对象

SELECT owner, object_type, status, COUNT(*) count#

FROM all_objects

GROUP BY owner, object_type, status;

–8、查看数据库的版本

SELECT version

FROM product_component_version

WHERE substr(product, 1, 6) = ‘Oracle’;

–9、查看数据库的创建日期和归档方式

SELECT created, log_mode, log_mode FROM v$database;

–1G=1024MB

–1M=1024KB

–1K=1024Bytes

–1M=11048576Bytes

–1G=1024*11048576Bytes=11313741824Bytes

SELECT a.tablespace_name “表空间名”,

total “表空间大小”,

free “表空间剩余大小”,

(total - free) “表空间使用大小”,

total / (1024 * 1024 * 1024) “表空间大小(G)”,

free / (1024 * 1024 * 1024) “表空间剩余大小(G)”,

(total - free) / (1024 * 1024 * 1024) “表空间使用大小(G)”,

round((total - free) / total, 4) * 100 “使用率 %”

FROM (SELECT tablespace_name, SUM(bytes) free

FROM dba_free_space

GROUP BY tablespace_name) a,

(SELECT tablespace_name, SUM(bytes) total

FROM dba_data_files

GROUP BY tablespace_name) b

WHERE a.tablespace_name = b.tablespace_name

标签：剩余,1024,name,查看,bytes,free,tablespace,oracle,SELECT

来源： https://blog.csdn.net/lzh_2_4/article/details/113753521