How to modify the table space size in oracle

In Oracle, you can use the alter statement to modify the table space size. This statement is often used to modify the contents of the table. The syntax is "alter database datafile 'path resize change size;".

The operating environment of this tutorial: Windows 10 system, Oracle 11g version, Dell G3 computer.

How to modify the table space size in oracle

Oracle's default table space size is 400M. When the amount of data in the database reaches this value, an error will be reported when importing data into the database. The solution is to

extend the table space. You can choose to expand the table capacity, such as to 5G, or automatically increase a certain capacity each time when the table space is not enough, such as increasing by 200M each time.

The detailed process is listed below:

1. Log in to the database through the sql plus command.

Enter sqlplus "login user name/password as login type" at the command line to log in. The commonly used user name built into the system is sys, and the password is the password set during the installation of Oracle. Be sure to clear it. Remember, if you log in with a sys account, the login type must be sysdba.

2. Check the allocation of each table space.

select tablespace_name, sum(bytes) / 1024 / 1024  from dba_data_files  
 group by tablespace_name;

3. Check the free status of each table space.

select tablespace_name, sum(bytes) / 1024 / 1024  from dba_free_space  group by tablespace_name;

4. Change the data table size (10G)

alter database datafile '/ora/oradata/radius/undo.dbf' resize 10240m；

5. Set automatic growth when the table space is insufficient

5.1 Check whether the table space automatically grows

SELECT FILE_NAME,TABLESPACE_NAME,AUTOEXTENSIBLE FROM dba_data_files;

5.2 Set table space automatic growth

ALTER DATABASE DATAFILE 'c:\SmartDB01.ora' AUTOEXTEND ON;//Turn on automatic growth

ALTER DATABASE DATAFILE 'c:\SmartDB01.ora' AUTOEXTEND ON NEXT 200M;//Automatically grow 200m each time

ALTER DATABASE DATAFILE 'c:\SmartDB01.ora' AUTOEXTEND ON NEXT 200M MAXSIZE 1024M;//Automatically grow 200m each time, the maximum data table does not exceed 1G

Recommended tutorial: "Oracle Video Tutorial"

The above is the detailed content of How to modify the table space size in oracle. For more information, please follow other related articles on the PHP Chinese website!