DBA常用的SQL语句

数据库的大小

数据库的大小主要是数据文件(dba_data_files)和临时文件(dba_temp_files;)的大小之和。

 1
 2
 3
 4
 5
 6
 7
 8
 9
10
11
12
13
14
15
16
17
18
19
20
21
22
23
24
25
26
27
28
29
--查询数据文件大小
SQL> select sum(bytes) from dba_data_files;

SUM(BYTES)
----------
1515192320

--查询临时文件大小
SQL> select sum(bytes) from dba_temp_files;

SUM(BYTES)
----------
  30408704

--查询数据库的大小:两项相加
SQL> select (select sum(bytes) from dba_data_files)+(select sum(bytes) from dba_
temp_files) from dual;

(SELECTSUM(BYTES)FROMDBA_DATA_FILES)+(SELECTSUM(BYTES)FROMDBA_TEMP_FILES)
-------------------------------------------------------------------------
                                                               1545601024

--取一个别名total_size
SQL> select (select sum(bytes) from dba_data_files)+(select sum(bytes) from dba_
temp_files) as total_size from dual;

TOTAL_SIZE
----------
1545601024

查询某个段对象(表,索引)的大小

dba_segments: DBA_SEGMENTSdescribes the storage allocated for all segments in the database

查看有哪些表空间

 1
 2
 3
 4
 5
 6
 7
 8
 9
10
11
12
SQL> select tablespace_name from dba_tablespaces;

TABLESPACE_NAME
------------------------------
SYSTEM
SYSAUX
UNDOTBS1
TEMP
USERS
EXAMPLE

已选择6行。