A question about tablespace

Source: Internet
Author: User
Recently, during database server maintenance, the usage of a tablespace (named ppstt) was too high. How to solve this problem has been found on the Internet, I am still a beginner in database management, so I have to ask for advice from my colleagues. my colleague sent a document and followed the instructions to solve the problem.

The database server is Sun Microsystems Inc SunOS 5.9, and the database is Oracle9i.
The procedure is as follows:
1) execute select * From dba_data_files where tablespace_name = 'ppstt' order by file_name DESC
Obtain the file_name field of the data file sequence field empty in the system. If the value is/dev/MD/rdsk/d203, the table is empty.
D204.
2) log on to the database server as a root user
3) run/usr/sbin/metainit d204-P d100 2G here d204 is the name of the empty table file, and the 2G behind it indicates the empty table space.
Size, d100 unchanged
4) Run chown ORACLE: DBA/dev/MD/DSK/d204
Chown ORACLE: DBA/dev/MD/rdsk/d204
Grant the permissions of these two files to oracle users and ora groups.
5) switch from root user to Oracle user (SU Oracle)
6) establish a connection with the database: sqlplus "/As sysdba"
7) execute alter tablespace "ppstt" add datafile '/dev/MD/rdsk/d204' size 2000 m. Add the empty table file
Ppstt table in the air.
Appendix: The following SQL statement can be used to view the usage of each tablespace:
Select DBF. tablespace_name,
DBF. totalspace "Total (m )",
DBF. Total number of totalblocks as blocks,
DFS. freespace "total remaining amount (m )",
DFS. freeblocks "remaining blocks ",
(Dfs. freespace/DBF. totalspace) * 100 "idle percentage"
From (select T. tablespace_name,
Sum (T. bytes)/1024/1024 totalspace,
Sum (T. blocks) totalblocks
From dba_data_files t
Group by T. tablespace_name) DBF,
(Select TT. tablespace_name,
Sum (TT. bytes)/1024/1024 freespace,
Sum (TT. blocks) freeblocks
From dba_free_space TT
Group by TT. tablespace_name) DFS
Where trim (DBF. tablespace_name) = trim (Dfs. tablespace_name)

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: info-contact@alibabacloud.com 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.