ORACLE Database, tablespace, and table capacity query-1

To be continued ...... To be continued ...... To be continued ...... To be continued ......

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

The main views used are: dba_data_files, dba_free_space

Col used _ % for a8

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;

TABLESPACE_NAME SPACE_M USED_M FREE_SPACE used _ %
-----------------------------------------------------
SYSAUX 670 637.125 32.875 95
UNDOTBS1 125 30.125 94.875 24
RMANTEST 10 1.0625 8.9375 10
USERS 1703.75 1562.5 141.25 91
SYSTEM 700 692.3125 7.6875 98
EXAMPLE 100 79.25 20.75 79

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_M USED_M FREE_SPACE used _ %
-----------------------------------------------------
USERS 1703.75 1562.5 141.25 91

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

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

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