To be continued ...... To be continued ...... To be continued ...... To be continued ...... 1. query the space occupied by a table.
To be continued ...... To be continued ...... To be continued ...... To be continued ...... 1. query the space occupied by a table 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
To be continued ...... To be continued ...... To be continued ...... To be continued ......
1. query the space occupied by a table
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 % ';
The result is as follows:
SEGMENT_NAME SEGMENT_TYPE TABLESPACE_NAME EXTENTS KB
-----------------------------------------------------------------
Test table users 1 64
TEST1 table users 1 64
TEST1 table users 168 794624
TEST5 table rmantest 1 64
TEST9 table users 169 800768
3. The first three spaces occupied by tables under a user:
Select * from (select segment_name, bytes/1024 KB from dba_segments where owner = 'bys 'order by bytes desc) where rownum <= 3;
SEGMENT_NAME KB
-------------------------
TEST9 800768
TEST1 794624
EMP 64
Use SQL to calculate the size of all objects under a user and provide SQL statements and results.
SQL> show user
User is "bys"
SQL> select sum (a. m) from (select segment_name, segment_type, bytes/1024/1024 M from user_segments);
SUM (A.M)
----------
4
2. query the table space, idle space, and usage.
The main views used are: 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;
The result is as follows:
TABLESPACE_NAME SPACE_M USED_M FREE_SPACE used _ % unused _ %
------------------------------------------------------------------------------
SYSAUX 625.625 595.625 30 95 5
UNDOTBS1 200 137.4375 62.5625 68 32
USERS 219.8125 121.875 97.9375 55 45
SYSTEM 500 346.1875 153.8125 69 31
TEST1 110 2 108 1 99
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 ';
The result is as follows:
TABLESPACE_NAME SPACE_M USED_M FREE_SPACE used _ %
-----------------------------------------------------
USERS 1703.75 1562.5 141.25 91
. Use SQL to calculate the size of objects contained in a tablespace. 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 ';
NAME SIZE_M
-------------------------
SIZE_TABELSPACE 5.25
SIZE_OBJECT 4
3. query the data file size and file name
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.dbf 4 USERS 1703.75
/U01/oradata/bys1/undotbs01.dbf 3 UNDOTBS1 125
/U01/oradata/bys1/sysaux01.dbf 2 SYSAUX 670
/U01/oradata/bys1/system01.dbf 1 SYSTEM 700
/U01/oradata/bys1/example01.dbf 5 EXAMPLE 100
/U01/oradata/bys1/rmantest. dbf 6 RMANTEST 10
4. query the capacity of the entire database
Data File Size
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 );
Redo log file size
Select sum (a. members * a. m) as sum_r from (select group #, members, bytes/1024/1024 m from v $ log);
Control File Size
SQL> select sum (bytes_m) as sum_c from (select name, block_size x file_size_blks/1024/1024 as bytes_m from v $ controlfile );
Total database capacity:
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.75 2615.25 90 28.5