ORACLE数据库 表空间 表的容量相关查询

数据库|mysql教程

ORACLE,数据库,空间,容量,相关,查询,未完待续,查询

数据库-mysql教程

两格团购模块源码,ubuntu查看总空间,设置tomcat7内存打下,楼板下有爬虫,php8安装教程详解,海外seo渠道lzw

未完待续……未完待续……未完待续……未完待续…… 1.查询某个表所占空间大小 col tablespace_name for a15 col segment_name for a15 col segment_type for a15 select segment_name,segment_type,tablespace_name,extents,bytes/1024 KB from dba_segment

ios应用分发平台源码,ubuntu打开主机文件,一个tomcat一闪,宝鸡数据爬虫ip,扣丁学堂php在哪里下载,西昌网店seolzw

ibeacon 源码,go vscode 代理设置,ubuntu系统选,采集tomcat请求日志,python爬虫并行,php xml-rpc,seo站长工具测试广告,php英文网站源码免费,html5网站正在建设中模板下载lzw

未完待续……未完待续……未完待续……未完待续……

1.查询某个表所占空间大小

col tablespace_name for a15

col segment_name for a15

col segment_type for a15

select segment_name,segment_type,tablespace_name,extents,bytes/1024 KB from dba_segments where segment_name like ‘TEST%’;

结果如下：

SEGMENT_NAME SEGMENT_TYPE TABLESPACE_NAME EXTENTS KB

————— ————— ————— ———- ———-

TEST TABLE USERS 1 64

TEST1 TABLE USERS 1 64

TEST1 TABLE USERS 168794624

TEST5 TABLE RMANTEST 1 64

TEST9 TABLE USERS 169800768

3.某个用户下的表所占空间前三位:

select * from (select segment_name,bytes/1024 KB from dba_segments where owner = ‘BYS’ order by bytes desc ) where rownum <= 3;

SEGMENT_NAME KB

————— ———-

TEST9800768

TEST1794624

EMP64

用SQL计算出某个用户下所有对象的大小，给出SQL语句和结果。

SQL> show user

User is “bys”

SQL> select sum(a.m) from (select segment_name,segment_type,bytes/1024/1024 M from user_segments) a;

SUM(A.M)

———-

4

2.查询表空间大小及空闲空间大小，使用率等

主要使用的视图有：dba_data_files，dba_free_space

col used_% for a8

col TABLESPACE_NAME for a15

select df.tablespace_name,df.sum_df_m as space_m,df.sum_df_m-fs.sum_fs_m as used_m,fs.sum_fs_m as free_space,to_char(trunc((df.sum_df_m-fs.sum_fs_m)/df.sum_df_m,2)*100) as “used_%”,100-to_char(trunc((df.sum_df_m-fs.sum_fs_m)/df.sum_df_m,2)*100) “unused_%”

from (select tablespace_name,sum(bytes/1024/1024) as sum_df_m from dba_data_files group by tablespace_name) df,(select tablespace_name,sum(bytes/1024/1024) as sum_fs_m from dba_free_space group by tablespace_name) fs where df.tablespace_name=fs.tablespace_name;

结果如下：

TABLESPACE_NAME SPACE_MUSED_M FREE_SPACE used_%unused_%

—————————— ———- ———- ———- ——– ———-

SYSAUX 625.625 595.625 30 955

UNDOTBS1 200 137.4375 62.5625 6832

USERS 219.8125 121.875 97.9375 5545

SYSTEM500 346.1875 153.8125 6931

TEST1 1102 108 199

select df.tablespace_name,df.sum_df_m as space_m,df.sum_df_m-fs.sum_fs_m as used_m,fs.sum_fs_m as free_space,to_char(trunc((df.sum_df_m-fs.sum_fs_m)/df.sum_df_m,2)*100) as “used_%” from (select tablespace_name,sum(bytes/1024/1024) as sum_df_m from dba_data_files

group by tablespace_name) df,(select tablespace_name,sum(bytes/1024/1024) as sum_fs_m from dba_free_space group by tablespace_name) fs where df.tablespace_name=fs.tablespace_name and df.tablespace_name=’USERS’;

结果如下：

TABLESPACE_NAME SPACE_MUSED_M FREE_SPACE used_%

————— ———- ———- ———- ——–

USERS 1703.751562.5141.25 91

.用SQL计算某个表空间所包含对象的大小

SQL> show user

User is “bys”

SQL> select ‘SIZE_TABELSPACE’ NAME,sum(user_bytes)/1024/1024 SIZE_M from dba_data_files where tablespace_name=’USERS’ UNION ALL select ‘SIZE_OBJECT’ NAME,sum(nvl(bytes,0))/1024/1024 SIZE_M from user_segments where tablespace_name=’USERS’;

NAMESIZE_M

————— ———-

SIZE_TABELSPACE 5.25

SIZE_OBJECT 4

3.查询数据文件大小及文件名

col file_name for a35

select file_name,file_id,tablespace_name,bytes/1024/1024 MB from dba_data_files;

FILE_NAME FILE_ID TABLESPACE_NAME MB

———————————– ———- ————— ———-

/u01/oradata/bys1/users01.dbf4 USERS 1703.75

/u01/oradata/bys1/undotbs01.dbf 3 UNDOTBS1125

/u01/oradata/bys1/sysaux01.dbf2 SYSAUX 670

/u01/oradata/bys1/system01.dbf1 SYSTEM 700

/u01/oradata/bys1/example01.dbf 5 EXAMPLE100

/u01/oradata/bys1/rmantest.dbf6 RMANTEST10

4.查询整个数据库的容量

数据文件大小

select sum(m) as sum_d from (select file_name,tablespace_name,bytes/1024/1024 m from dba_data_files union select file_name,tablespace_name,bytes/1024/1024 m from dba_temp_files);

重做日志文件大小

select sum(a.members*a.m) as sum_r from (select group#,members,bytes/1024/1024 m from v$log) a;

控制文件大小

SQL> select sum(bytes_m) as sum_c from (select name,block_size*file_size_blks/1024/1024 as bytes_m from v$controlfile);

数据库总容量：

SQL> select sum_d+sum_r+sum_c as sum_database_M,sum_d as sum_datafile,sum_r as sum_redo,sum_c as sum_ctl from (select sum(m) as sum_d from (select file_name,tablespace_name,bytes/1024/1024 m from dba_data_files union select file_name,tablespace_name,bytes/1024/1024

m from dba_temp_files)) a,(select sum(members*m) as sum_r from (select group#,members,bytes/1024/1024 m from v$log)) b,(select sum(bytes_m) as sum_c from (select name,block_size*file_size_blks/1024/1024 as bytes_m from v$controlfile)) c;

SUM_DATABASE_M SUM_DATAFILE SUM_REDO SUM_CTL

————– ———— ———- ———-

2733.752615.25 90 28.5