First, query the use of table space
select
a.tablespace_name
as
"表空间名"
,
a.bytes / 1024 / 1024
as
"表空间大小(M)"
,
(a.bytes - b.bytes) / 1024 / 1024
as
"已使用空间(M)"
,
b.bytes / 1024 / 1024
"空闲空间(M)"
,
round(((a.bytes - b.bytes) / a.bytes) * 100, 2)
"使用比"
from
(
select
tablespace_name,
sum
(bytes) bytes
from
dba_data_files
group
by
tablespace_name) a,
(
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
;
Second, expand the table space
1. View the name of the table space and the location of the file
select
tablespace_name,
file_id,
file_name,
round(bytes / (1024 * 1024), 0) total_space
from
dba_data_files
order
by
tablespace_name
2. Size of table space required for expansion
1
alter
database
datafile
‘F:\ORACLE\PRODUCT\10.2.0\ORADATA\ORCL\xxx.DBF‘
resize 1024m;
For the table space of an Oracle database, the table space size can be expanded in addition to manually increasing the size of the data file.
Method One: Increase the number of data files
alter
tablespace 表空间名称
add
datafile
‘F:\ORACLE\PRODUCT\10.2.0\ORADATA\ORCL\new_xxx.DBF‘
size
500m
Method Two: Set table Space Auto-expansion.
ALTER
DATABASE
DATAFILE
‘xxx\xxx.dbf‘
AUTOEXTEND
ON
;//打开自动增长
ALTER
DATABASE
DATAFILE
‘xxx\xxx.dbf‘
AUTOEXTEND
ON
NEXT
200M ;//每次自动增长200m
ALTER
DATABASE
DATAFILE
‘xxx\xxx.dbf‘
AUTOEXTEND
ON
NEXT
200M MAXSIZE 1024M;//每次自动增长200m,表空间最大不超过1G
How does Oracle query table space usage? Ways to extend table space for Oracle