Querying table size, table occupancy, and table space size in Oracle

Reprinted from http://blog.csdn.net/cuker919/article/details/8514253

Select Segment_name, bytes as size
From User_segments
where Segment_type = ' TABLE ' and segment_name in (' view_jlzdh_mp_dl_day_01 ', ' view_jlzdh_mp_dl_day_02 ', ' View_jlzdh_mp_ Dl_day_03 ',
' View_jlzdh_mp_dl_day_04 ', ' view_jlzdh_mp_dl_day_05 ', ' view_jlzdh_mp_dl_day_06 ',
' view_jlzdh_mp_dl_day_07 ', ' view_jlzdh_mp_dl_day_08 ', ' view_jlzdh_mp_dl_day_09 ',
' View_jlzdh_mp_dl_day_10 ', ' view_jlzdh_mp_dl_day_11 ', ' View_jlzdh_mp_dl_day_12 ')


Select T.table_name,t.num_rows from User_tables t where table_name in (' view_jlzdh_mp_dl_day_01 ', ' View_jlzdh_mp_dl_day _02 ', ' view_jlzdh_mp_dl_day_03 ',
' View_jlzdh_mp_dl_day_04 ', ' view_jlzdh_mp_dl_day_05 ', ' view_jlzdh_mp_dl_day_06 ',
' view_jlzdh_mp_dl_day_07 ', ' view_jlzdh_mp_dl_day_08 ', ' view_jlzdh_mp_dl_day_09 ',
' View_jlzdh_mp_dl_day_10 ', ' view_jlzdh_mp_dl_day_11 ', ' View_jlzdh_mp_dl_day_12 ') Order by T.table_name

The table size has two meanings. One is the amount of physical space assigned to a table, regardless of whether the space is used or not. You can get the number of bytes in this query:

Select Segment_name, bytes
From User_segments
where Segment_type = ' TABLE ';
Or
Select segment_name,sum (bytes)/1024/1024 from User_extents Group by segment_name

The space that the other table actually uses. This query:

Analyze table EMP Compute statistics;
Select Num_rows * Avg_row_len
From User_tables
WHERE table_name = ' EMP ';

To view the size of each table space
Select tablespace_name,sum (bytes)/1024/1024 from Dba_segments Group by Tablespace_name

1. To view the remaining table space sizeSELECT tablespace_name tablespace, sum (blocks*8192/1000000) remaining space m from Dba_free_space GROUP by Tablespace_name; 2. Check the total space of all table spaces in the systemSelect B.name,sum (a.bytes/1000000) total space from V$datafile A,v$tablespace b where a.ts#=b.ts# group by B.name;

1. Tool methods for viewing tablespace information in Oracle databases:

Using the Oracle Enterprise Manager console tool, this is an Oracle client tool that installs this tool automatically when an Oracle server or client is installed, and after completing the Oracle installation on the Windows operating system, Log in to the tool by using the following method: Start Menu--Program--oracle-orahome92--enterprise Manager console (click)--oracle Enterprise Manager Console Login--select ' Standalone start ' Radio '--' OK '--' Oracle Enterprise Manager console, standalone '--select ' Instance name ' to login--Popup ' Database connection information '--enter ' username/password ' (generally using SYS user), ' Connection identity ' Select sysdba--' OK, this time has been successfully logged in the tool, select ' Storage '--table space, you will see the following interface, the interface shows the table space name, table space type, the type of zone management, the size of the table in "megabytes", the size of the table space used and table space utilization.

Figure 1 Table space size and utilization

2. Command method for viewing tablespace information in an Oracle database:

By querying the data dictionary table in the database system (dictionary tables) to get information about the tablespace, first connect to the database using the client tools, which can be sqlplus character tools, TOAD, PL/SQL, etc. After connecting to the database, execute the following query statement:

Select

A.A1 table space Name,

C.C2 type,

C.C3 District Management,

b.b2/1024/1024 table space size M,

(B.B2-A.A2)/1024/1024 has used M,

SUBSTR ((B.B2-A.A2)/b.b2*100,1,5) utilization

From

(select Tablespace_name A1, sum (NVL (bytes,0)) A2 from Dba_free_space Group by Tablespace_name) A,

(Select Tablespace_name b1,sum (bytes) B2 from Dba_data_files Group by Tablespace_name) B,

(select Tablespace_name c1,contents c2,extent_management c3 from Dba_tablespaces) c

where A.a1=b.b1 and C.C1=B.B1;

The statement dba_free_space,dba_data_files,dba_tablespaces the three data dictionary tables by querying the table space name, table space type, zone management type, table space size in megabytes, Table space size used and tablespace utilization. The Dba_free_space table describes the table space's idle size, and the Dba_data_files table describes the data files in the database, and the Dba_tablespaces table describes the table spaces in the database.

After the FROM clause in the above statement there are three SELECT statements, each SELECT statement is equivalent to a view, the name of the view is a, B, c, through the association between them, we get information about the table space.

The result of the statement execution is as follows:

　　

Table Space Name	Type	District Management	Table Space Size M	has used M	Utilization
Cwmlite	PERMANENT	LOCAL	20	9.375	46.87
Drsys	PERMANENT	LOCAL	20	9.6875	48.43
EXAMPLE	PERMANENT	LOCAL	149.375	149.25	99.91
INDX	PERMANENT	LOCAL	25	0.0625	0.25
Odm	PERMANENT	LOCAL	20	9.375	46.87
SYSTEM	PERMANENT	LOCAL	400	397.375	99.34
TOOLS	PERMANENT	LOCAL	10	6.0625	60.62
UNDOTBS1	UNDO	LOCAL	200	5.9375	2.968
USERS	PERMANENT	LOCAL	25	0.0625	0.25
XDB	PERMANENT	LOCAL	38.125	37.9375	99.5

The tool methods and command methods for viewing tablespace information in an Oracle database are described in the description above.

1. Tool method for viewing data file information in Oracle database:

Using the method described above, log in to the Oracle Enterprise Manager console tool, select ' Storage '--data file, you will see the following interface, which shows the data file name, tablespace name, in "trillion" units of data file size, The size of the data file used and the utilization of the data file.

　　

Figure 2 Data file size and utilization

2. Command method to view data file information in Oracle database:

Get information about data files by querying the data dictionary tables in the database system, first using client tools to connect to the database, which can be sqlplus character tools, TOAD, PL/SQL, etc. After connecting to the database, execute the following query statement:

Select

B.file_name Physical file name,

B.tablespace_name table Space,

b.bytes/1024/1024 size M,

(B.bytes-sum (NVL (a.bytes,0)))/1024/1024 has used M,

SUBSTR ((B.bytes-sum (NVL (a.bytes,0))/(b.bytes) *100,1,5) utilization

From Dba_free_space A,dba_data_files b

where a.file_id=b.file_id

GROUP BY B.tablespace_name,b.file_name,b.bytes

ORDER BY B.tablespace_name

The result of the statement execution is as follows:

　　

Physical file name	Table Space	Size M	has used M	Utilization
G:/oracle/oradata/ora92/cwmlite01. Dbf	Cwmlite	20	9.375	46.87
G:/oracle/oradata/ora92/drsys01. Dbf	Drsys	20	9.6875	48.43
G:/oracle/oradata/ora92/example01. Dbf	EXAMPLE	149.375	149.25	99.91
g:/oracle/oradata/ora92/indx01. Dbf	INDX	25	0.0625	0.25
G:/oracle/oradata/ora92/odm01. Dbf	Odm	20	9.375	46.87
G:/oracle/oradata/ora92/system01. Dbf	SYSTEM	400	397.375	99.34
G:/oracle/oradata/ora92/tools01. Dbf	TOOLS	10	6.0625	60.62
G:/oracle/oradata/ora92/undotbs01. Dbf	UNDOTBS1	200	5.9375	2.968
G:/oracle/oradata/ora92/users01. Dbf	USERS	25	0.0625	0.25
G:/oracle/oradata/ora92/xdb01. Dbf	XDB	38.125	37.9375	99.5

The tools and command methods for viewing data file information in an Oracle database are described separately in the Oberming description.

In an Oracle database, the temporal tablespace is primarily used for temporary workspaces that users need to sort and summarize by using the order by and group by statements. To query the name, size and data file of the temporary tablespace in the database, you can query the data dictionary dba_tablespaces and Dba_data_files. The command is as follows:

Select

A.talbespace_name table space Name,

B.bytes size bytes,

B.file_name Data file name

From Dba_tablespaces A, dba_data_files b

Where a.talbespace_name=b.talbespace_name and a.contents= ' temporary ';

The query results are as follows:

Table space name Size bytes data file name

Temponlineg:/oracle/oradata/ora92/temp01. Dbf

Starting with Oracle 9i, you can create temporary tablespace class table spaces, which are "temporary" tablespaces that use temporary files. The information for the temporary file is stored in the data dictionary v$tempfile. The command is as follows:

Select file#,status,name from V$tempfile;

Query data dictionary v$tempfile results are as follows:

　　

file#	Status	NAME
1	ONLINE	G:/oracle/oradata/ora92/temp01. Dbf

In the method described above, it is recommended to master the command method, because your environment may not have graphical tools, and sqlplus generally can be used, with command scripts, it is easy to get information about tablespaces and data files. In addition, the database administrator should defragment the command script and execute the script directly when needed to improve productivity.

In the daily work of the database administrator, we should frequently query the utilization of table space, estimate the growth of table space according to the specific situation of the database system, when the utilization ratio of table space exceeds 90%, take measures such as clean history table, historical data to free space, add new data file to table space. Expanding the size of existing data files and so on to reduce the utilization of table space, avoid table space utilization near 100%, will produce enough space error.

1. Querying the usage of Oracle Tablespace

Select b.file_id file ID,
B.tablespace_name table Space,
B.file_name Physical file name,
Total number of bytes b.bytes,
(B.bytes-sum (NVL (a.bytes,0))) has been used,
Sum (NVL (a.bytes,0)) remaining,
Sum (NVL (a.bytes,0))/(b.bytes) *100 remaining percentage
From Dba_free_space A,dba_data_files b
where a.file_id=b.file_id
GROUP BY B.tablespace_name,b.file_name,b.file_id,b.bytes
ORDER BY B.tablespace_name

2. Querying the default tablespace and temporary tablespace for Oracle system users

Select Default_tablespace,temporary_tablespace from Dba_users

3. Query the use of single-sheet

Select Segment_name,bytes from dba_segments where segment_name = ' re_stdevt_fact_day ' and owner = USER

Re_stdevt_fact_day is the name of the table you want to query

4. Query the top 30 of all user tables using size

SELECT * FROM (select Segment_name,bytes from dba_segments where owner = USER order BY bytes desc) where RowNum <= 30

5. Query the usage of the default tablespace for the current user

Select Tablespacename,sum (totalcontent), sum (usecontent), sum (sparecontent), avg (sparepercent)
From
(
SELECT b.file_id as id,b.tablespace_name as tablespacename,b.bytes as Totalcontent, (B.bytes-sum (NVL)) as Usecontent,sum (NVL (a.bytes,0)) as Sparecontent,sum (NVL (a.bytes,0))/(B.bytes) *100 as Sparepercent
From Dba_free_space A,dba_data_files b
where a.file_id=b.file_id and B.tablespace_name = (select Default_tablespace from dba_users where username = user)
GROUP BY B.tablespace_name,b.file_name,b.file_id,b.bytes
)
GROUP by Tablespacename

6. Querying the table for the user table space

SELECT * FROM User_tables

Querying table size, table occupancy, and table space size in Oracle