Viewing table space Information
Statement one:
SELECT DBF. Tablespace_name "Table space name",
Dbf. file_name "Table Space file",
Dbf. Totalspace "Total (M)",
Dbf. Totalblocks as total block number,
Dfs. FREESPACE "Total surplus (M)",
Dfs. Freeblocks "Number of remaining blocks",
(DFS. Freespace/dbf. Totalspace) * 100 "free ratio"
From (SELECT T.tablespace_name,
SUM (t.bytes)/1024/1024 Totalspace,
SUM (t.blocks) Totalblocks,
file_name
From Dba_data_files T
GROUP by T.tablespace_name,file_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)
Statement two:
Select A.tablespace_name "Table space name",
B.file_name as "table space file",
total/1024/1024 | | ' MB ' table space size,
free/1024/1024 | | The remaining size of the ' MB ' table space,
(total-free)/1024/1024 | | The ' MB ' table space uses size,
ROUND ((total-free)/Total, 4) * 100 "Usage%"
From (SELECT tablespace_name, SUM (BYTES) free
From Dba_free_space
GROUP by Tablespace_name) A,
(SELECT tablespace_name, SUM (BYTES) Total, file_name
From Dba_data_files
GROUP by Tablespace_name, file_name) B
WHERE A.tablespace_name = B.tablespace_name
Order by ROUND ((total-free)/Total, 4) * DESC;
To change the size of a table space file:
ALTER DATABASE datafile ' d:/webgisdb/history. ORA ' RESIZE 5000M;
Note: When expanding, be sure to write the path to the file, or you will not be prompted to find the file