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