Tablespace (bottom), tablespace (

Tablespace (bottom), tablespace (

7. Table space size

-- View the table space usage
Select a. tablespace_name,
Round (a. bytes/1024/1024) "Sum MB ",
Round (a. bytes-B. bytes)/1024/1024) "used MB ",
Round (B. bytes/1024/1024) "free MB ",
Round (a. bytes-B. bytes)/a. bytes), 2) "percent_used"
From (select tablespace_name, sum (bytes) bytes
From dba_data_files
Group by tablespace_name),
(Select tablespace_name, sum (bytes) bytes, max (bytes) largest
From dba_free_space
Group by tablespace_name) B
Where a. tablespace_name = B. tablespace_name
Order by (a. bytes-B. bytes)/a. bytes) desc;

NOTES: If you haven't gotten any results after running for a long time, view the garbage bins under each user,
It may be caused by the accumulation of too much data in the recycle bin.
(Select * from recyclebin or select * from dba_recyclebin ),
Run the following command to clear the user's garbage bin.
(Each user must execute the command once, or use the purge dba_recyclebin command directly,
However, if you use the same database with other modules, we do not recommend this operation because the garbage bin data of other users may be useful .)
SQL> purge recyclebin;
 
Select file #, name, bytes/1024/1024 from v $ tempfile; -- total temporary tablespace size
Select tablespace, (sum (blocks) * 8/1000 "MB" from v $ sort_usage group by tablespace; -- temporary tablespace used

-- Temporary tablespace usage:
Select f. tablespace_name,
Sum (f. bytes_free + f. bytes_used)/(1024*1024) "total MB ",
Sum (f. bytes_free + f. bytes_used)-nvl (p. bytes_used, 0)/(1024*1024) "Free MB ",
Sum (nvl (p. bytes_used, 0)/(1024*1024) "Used MB"
From sys. v _ $ temp_space_header f,
Dba_temp_files d,
Sys. v _ $ temp_extent_pool p
Where f. tablespace_name (+) = d. tablespace_name
And f. file_id (+) = d. file_id
And p. file_id (+) = d. file_id
Group by f. tablespace_name;

 

8. Modify the table tablespace.
Alter table a move tablespace TBS_DW_YM

9. query the default tablespace used by the imuse01 user
Select default_tablespace from dba_users where username = 'imuse01 ';

Table space data files

View the following two data dictionary views.
View the tablespaces: select * from dba_tablespaces;
Check which data files are in which tablespace: select * from dba_data_files;

How to view the tablespace path and users in the tablespace?

The tablespace to which the user belongs should be checked in the DBA_USERS view.
Path to DBA_DATA_FILES