7. Use the tablespace size -- view the usage of the tablespace selecta. tablespace_name, round (a. bytes10241024) SumMB, round (a. bytes
7. table space usage size -- view the table space usage select. tablespace_name, round (. bytes/1024/1024) Sum MB, round (. bytes-B. bytes)/1024/1024) used MB, round (B. bytes/1024/1024) free MB, round (. bytes-B. bytes)/. bytes), 2) per
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 ';