Tablespace (lower)

Source: Internet
Author: User
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 ';

Related Article

Contact Us

The content source of this page is from Internet, which doesn't represent Alibaba Cloud's opinion; products and services mentioned on that page don't have any relationship with Alibaba Cloud. If the content of the page makes you feel confusing, please write us an email, we will handle the problem within 5 days after receiving your email.

If you find any instances of plagiarism from the community, please send an email to: and provide relevant evidence. A staff member will contact you within 5 working days.

A Free Trial That Lets You Build Big!

Start building with 50+ products and up to 12 months usage for Elastic Compute Service

  • Sales Support

    1 on 1 presale consultation

  • After-Sales Support

    24/7 Technical Support 6 Free Tickets per Quarter Faster Response

  • Alibaba Cloud offers highly flexible support services tailored to meet your exact needs.