Ora-01653: table a cannot be extended by 1024 (in table space ABC)

JavaScript implementation: how to write beautiful conditional expression>>>

Oracle ora-01653: error of unable to extend table

Today, when PL SQL developer was used to import data into Oracle database, an error was suddenly reported, which can only be terminated. The specific contents of the error are as follows:

ORA-01653: unable to extend table USER_ DATA.JKHDFXJL by 128 in tablespace MSMS

It means user_ The data table space is insufficient, so Google has a look, and it has some features. There are two kinds of problems: one is that the automatic expansion function of the table space is not on; The other is that the space is really not enough, which has reached the upper limit of automatic expansion

So the step to solve the problem is to check the table space size of Oracle database first, and then check whether the automatic expansion function of all data files under the table space is turned on; If the table space is not enough, then we need to expand the table space

The specific steps are as follows:

execute the following SQL statements in PL SQL developer

SELECTa.tablespace_ Name “table space name”,

a.bytes/1024/1024 “table space size (m)”,

(a.bytes – b.bytes)/1024/1024 “used space (m)”,

b.bytes/1024/1024 “free space (m)”,

round (((a.bytes – b.bytes)/a.bytes) * 100,2) “usage ratio”

from (select table space)_ name,sum(bytes) bytes

FROMdba_ data_ files

GROUPBYtablespace_ name) a,

(SELECTtablespace_ name,sum(bytes) bytes,max(bytes) largest

FROMdba_ free_ space

GROUPBYtablespace_ name) b

WHEREa.tablespace_ name = b.tablespace_ name

ORDERBY((a.bytes – b.bytes)/a.bytes)DESC

“table space size (m)” indicates the total disk space occupied by all data files in the table space in the operating system

For example: User_ Data table space has two data files, datafile1 is 300MB, datafile2 is 400MB, then User_ The “table space size” of data table space is 700MB

“used space (m)” indicates how much table space has been used

“free space (m)” indicates how much table space is left

“usage ratio” indicates the percentage that has been used

for example, view user from step 1_ Data table space has been used by more than 90% . You can view the total number of data files in the table space, whether each data file is automatically expanded, and the maximum value of automatic expansion

SELECTfile_ name,

tablespace_ name,

bytes /1024/1024″bytes MB”,

maxbytes /1024/1024″maxbytes MB”

FROMdba_ data_ files

WHEREtablespace_ name =’USER_ DATA‘;

check whether the MSMS table space is automatically expanded

SELECTfile_ id, file_ name, tablespace_ name, autoextensible, increment_ by

FROMdba_ data_ files

WHEREtablespace_ name =’USER_ DATA‘

ORDERBYfile_ iddesc;

Check whether the corresponding value of “ autoextensible ” is yes or no. if it is no, it means that the automatic expansion function of MSMS table space is not on. Just change it to yes

for example, the current size of MSMS table space is 0.9gb, but the maximum size of each data file can only be 1GB, and the data file is almost full, so we need to expand the table space

there are two ways to expand the table space: one is to increase the size of data files, the other is to increase the number of data files

first, find out the data file and path corresponding to the table space

SELECT*FROMdba_ data_ files tWHEREt.tablespace_ Name =’table space name ‘

–Find the full path of the data file in the corresponding table space, which corresponds to file_ Name field

solution 1: increase data file

alter database datafile ‘full path data file name’ resize * * m

–Increase the size of a data file in the corresponding table space to * * M

solution 2: add data file

Get the statement that creates the table space:

SELECTdbms_ metadata.get_ ddl(‘TABLESPACE’,’USER_ DATA‘)FROMdual;

confirm that the disk space is sufficient, and add a data file [use DF – G command in UNIX and Linux operating system (check the available disk space)]

alter table space name adddatafile ‘full path data file name’ size * * m

autoextension maxsize20g

–Add a new data file, and the full path data file name is the full path file name of the new data file. The size is * * m, the automatic expansion function is on, and the maximum expansion value of the data file is 20g

verify the added data files

SELECTfile_ name, file_ id, tablespace_ name

FROMdba_ data_ files

WHEREtablespace_ Name =’table space name ‘

0

if you delete a table space data file

alter table space name

drop datafile ‘/ u01/APP/Oracle/product/10.2.0/oradata/orcl/ User_ DATA.dbf’

ps : table space generally keeps the idle percentage above 10%, and the data file size should not exceed 2G. When the table space is insufficient, you can either reset the datafile or add the datafile

Similar Posts: