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)