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
TABLE_NAME | TABLESPACE_NAME |
---|---|
SAMPLE_TABLE | SYSTEM |
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
TABLE_NAME | TABLESPACE_NAME |
---|---|
SAMPLE_TABLE | SAMPLE_SPACE |
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.
コメント