ORA-01688 tablespace cannot be expanded

Error Report:

SQL error: ORA-01688: Table JINRILOG. USEROPERATELOG partition P1 cannot be extended through table space JINRILOG

01688. 00000-"unable to extend table % s. % s partition % s by % s in tablespace % s"

* Cause: Failed to allocate an extent for table segment in tablespace.

* Action: Use alter tablespace add datafile statement to add one or more

Files to the tablespace indicated.

----- Solution Process:

Step 1: Check whether automatic expansion is enabled

SELECT file_id, file_name, tablespace_name, autoextensible, increment_by

FROM dba_data_files

WHERE tablespace_name = 'json'

Order by file_id desc;

Step 2: Try to resize the file size

SQL> alter database datafile's: \ APP \ ADMINISTRATOR \ ORADATA \ ORCL \ jinrilog01.dbf' resize 100000 M;

Alter database datafile's: \ APP \ ADMINISTRATOR \ ORADATA \ ORCL \ jinrilog01.dbf' resize 100000 M

*

Row 3 has an error:

ORA-01144: file size (12800000 blocks) exceeds the maximum number of 4194303 Blocks

Select value from v $ parameter where name = 'db _ block_size ';

/****

FIX 1:

Specify a smaller file size.

The maximum file size is

Db_block_size * 4194303

For example, if db_block_size is 2kB then maximum file size is limited

To 2 k * 4194303, 8 Gb-2 k.

FIX 2:

Change db_block_size. This can only be done by recreating

Database.

 

Db_block_size Maximum data file size

-----------------------------------

2kb 8Gb-2kb

4kb 16Gb-4kb

8kb 32Gb-8kb

16kb 64Gb-16kb

32kb 128Gb-32kb

It can be seen that db_block_size still plays a decisive role in maximum data file size.

We can see that the system file can only be 32 GB because db_block_size is set to 8 KB.

****/

Step 2: add data files

Alter tablespace jinrilog add datafile

'S: \ APP \ ADMINISTRATOR \ ORADATA \ ORCL \ JINRILOG02.DBF'

Size 30 GB;

Alter tablespace jinrilog add datafile

'S: \ APP \ ADMINISTRATOR \ ORADATA \ ORCL \ JINRILOG03.DBF'

Size 30 GB;