Check the tablespace to which a table belongs in Oracle, how to change the tablespace, and check the utilization of the tablespace.

Check the tablespace to which a table belongs in Oracle, how to change the tablespace, and check the utilization of the tablespace.

The following SQL checks which tablespace the table belongs to in Oracle.

select table_name,tablespace_name from user_tables
order by tablespace_name;

The results are as follows

You can see that SAMPLE_TABLE belongs to the SYSTEM table area.

To change this to another tablespace, use ALTER TABLE.

ALTER TABLE SAMPLE_TABLE MOVE TABLESPACE SAMPLE_SPACE;

SAMPLE_SPACE is another table area.

Search again.

select table_name,tablespace_name from user_tables
order by tablespace_name;

The results are as follows

Incidentally, the tablespace can also be checked from dba_tables.

select table_name,tablespace_name
from dba_tables
where owner = 'USER002';

If OWNER is not specified, tables owned by SYS, etc. will be displayed, so it is better to narrow down the conditions with OWNER.

Check table space utilization

To check tablespace utilization, issue the following SQL

select
tablespace_name,
to_char(nvl(total_bytes / 1024 / 1024,0),'999,999,999') as "size(MB)",
to_char(nvl((total_bytes - free_total_bytes) / 1024 / 1024,0),'999,999,999') as "used(MB)",
to_char(nvl(free_total_bytes/1024 / 1024,0),'999,999,999') as "free(MB)",
round(nvl((total_bytes - free_total_bytes) / total_bytes * 100,100),2) as "rate(%)"
from
( select
tablespace_name,
sum(bytes) total_bytes
from
dba_data_files
group by
tablespace_name
),
( select
tablespace_name free_tablespace_name,
sum(bytes) free_total_bytes
from
dba_free_space
group by tablespace_name
)
where
tablespace_name = free_tablespace_name(+)

The execution results are as follows

TABLESPACE_NAME SIZE(MB) USED(MB) FREE(MB) USED(%)
--------------- -------- -------- -------- -------
SYSTEM               360      354        6   98.32
SYSAUX               670      632       38   94.37
UNDOTBS1              25        9       16   36.75
USERS                100        7       94     6.5
SAMPLE_SPACE          10        1        9   10.63

I inserted about 100,000 records into a table belonging to the tablespace SAMPLE_SPACE.
ORA-01653: Unable to extend table USER002.SAMPLE_TABLE (128 minutes, table area SAMPLE\SPACE).” was displayed.

Possible solutions to this error include resizing the data file.

高橋