Error code 1658 - Исправление ошибок и поиск оптимальных решений проблем

When i tried to create a table in my User_DB schema i am getting an error as ORA-01658: unable to create INITIAL extent for segment in tablespace TS_DATA. I run the following query to get all the TABLESPACE_NAME:

SELECT * FROM DBA_DATA_FILES;

But i really dont know which tablespace i am using and how to extend the tablespace to solve this issue.

asked May 6, 2016 at 11:23

AndrewAndrew

3,52021 gold badges62 silver badges111 bronze badges

As the error message indicates, you’re using the TS_DATA tablespace. You can extend it by either enlarging one of the existing data files:

ALTER DATABASE 
DATAFILE 'C:ORACLEXEAPPORACLEORADATAXETS_DATA.DBF' 
RESIZE 3000M;

Or by adding a second datafile to the tablespace:

ALTER TABLESPACE ts_data 
ADD DATAFILE 'C:ORACLEXEAPPORACLEORADATAXETS_DATA2.DBF' 
SIZE 1000M;

Or just allow the datafile to auto extend:

ALTER DATABASE 
DATAFILE 'C:ORACLEXEAPPORACLEORADATAXETS_DATA2.DBF'
AUTOEXTEND ON
MAXSIZE UNLIMITED; -- Or some reasonable cap

answered May 6, 2016 at 11:36

MureinikMureinik

290k52 gold badges300 silver badges337 bronze badges

To check existing table spaces data file and size by following sql

select a.file_id,b.file_name,b.autoextensible,b.bytes/1024/1024,sum(a.bytes)/1024/1024
from dba_extents a , dba_data_files b
where a.file_id=b.file_id
group by a.file_id,b.file_name,autoextensible,b.bytes/1024/1024

Then run following sql, it would to make auto extend data file size.

ALTER DATABASE 
DATAFILE '/u01/app/oracle/oradata/XE/TS_DATA.dbf'
AUTOEXTEND ON
MAXSIZE UNLIMITED;

answered Aug 31, 2021 at 20:03

JulfikerJulfiker

2583 silver badges9 bronze badges

Источник

Problem

Administrator is upgrading Controller version. As part of this process, installer clicks on ‘Run Steps’.

Error message received.

Symptom

Conversion of databases is in progress…
Creating menu…
Adding predefined workflow…
Running General Database Upgrade to Version: 678
Running Step 599
Running Step 600
Running Step 601
Running Step 602
Running Step 603
Running Step 604
Running Step 605
Running Step 606
Running Step 607
Running Step 608
Running Step 609
Running Step 610
Running Step 611
Database Upgrade is aborted due to error in processing Step 611 — Script: nc_xdb00_journals.index.
Error — -2147217900 ORA-20001: ORA-01658: unable to create INITIAL extent for segment in tablespace XDB_INDEX
ORA-06512: at line 15

Cause

Oracle tablespace ‘XDB_INDEX’ has run out of storage space.

Resolving The Problem

Perform the following, as per third party (Oracle’s) best practice methods:

(1) Ensure that the tablespace’s sizing property is set to ‘auto-extend’ (or manually increase the size of the affected tablespace for example ‘XDB_INDEX’)

(2) Ensure that the tablespace size does not have a ‘maxsize’ configured (or manually increaes the size of this maxsize value)

TIP: The following SQL statement will display the amount of freespace available for each tablespace:

Launch SQLPlus Worksheet
Logon to Oracle database as user ‘system’
Run the following script:

SELECT TABLESPACE_NAME, SUM(bytes)/1024/1024 «Free (MB)»
FROM dba_free_space
GROUP BY tablespace_name;

This will display something
similar to:

TABLESPACE_NAME Free (MB)
—————————— ———-
CONDB 18967.75
FRANGO_INDEX 1.0625
FRANGO_LOB 2.875
FRANGO_TABLE .5
INDX 24.9375
SYSTEM 2.75
TOOLS 9.9375
TS_DCOGNOSD_D1 4740.0625
UNDOTBS1 796.4375
USERS 196.625
XDB_TABLE 1.875

11 rows selected.

Steps:One method (for Oracle 9i servers) to increase the MAXSIZE of a tableis shown in attached file ‘KB_1038596_Printscreen.gif’.

[{«Product»:{«code»:»SS9S6B»,»label»:»IBM Cognos Controller»},»Business Unit»:{«code»:»BU059″,»label»:»IBM Software w/o TPS»},»Component»:»Controller»,»Platform»:[{«code»:»PF033″,»label»:»Windows»}],»Version»:»Controller (Frango) 8.2″,»Edition»:»»,»Line of Business»:{«code»:»LOB10″,»label»:»Data and AI»}}]

Historical Number

1038596

Источник

ORA-01658: невозможно создать начальную область для абзацев в пространстве табличного пространства;

Или же:

ORA-01658: unable to create INITIAL extent for segment in tablespace string。

или же

ORA-01659: невозможно выделить Mynctents (в табличном пространстве XXX).

То есть табличное пространство недостаточно, добавьте табличное пространство.

1 Просмотреть общий размер табличного пространства:

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

2 Просмотр размера и распределения табличного пространства

select SEGMENT_TYPE,owner,sum(bytes)/1024/1024 from  dba_segments  where tablespace_name='XITONG' group by segment_type,owner;

3 Посмотреть путь к таблице

select  * from dba_data_files;


----------

4 Увеличьте размер табличного пространства

Вы можете добавить новые файлы данных:

alter tablespace YWGOV add datafile 'F:SOFT-INSTALL-SOFTORACLEPRODUCT10.2.0ORADATAORCLHOUTAI1.ORA ' size 100M;

Расширение исходного размера файла данных

ALTER DATABASE DATAFILE 'F:SOFT-INSTALL-SOFTORACLEPRODUCT10.2.0ORADATAORCLHOUTAI.ORA' RESIZE 1024M;

Модифицировать табличное пространство автоматически увеличивается

ALTER DATABASE DATAFILE 'F:SOFT-INSTALL-SOFTORACLEPRODUCT10.2.0ORADATAORCLHOUTAI.ORA' AUTOEXTEND ON NEXT 200M MAXSIZE UNLIMITED;

или же

ALTER TABLESPACE SDE AUTOEXTEND ON NEXT 100M MAXSIZE UNLIMITED;

Источник

We are getting ORA-01658 (Error :ORA-01658: unable to create INITIAL extent for segment in tablespace MYDATA_LDATA_INDEX) in our application logs. This happens when application is trying to insert data for the first time into tables that are empty. It is only happening to this particular index. The tablespace free space decreases by multiple of 50M.

The parameters used to create the index tablespace:

CREATE TABLESPACE MYDATA_LDATA_INDEX DATAFILE
  '/index/oradata/mydb/mydata_ldata_index_01.dbf'
SIZE 1300M AUTOEXTEND ON NEXT 10M MAXSIZE 2048M
LOGGING
ONLINE
PERMANENT
EXTENT MANAGEMENT LOCAL UNIFORM SIZE 50M
BLOCKSIZE 8K
SEGMENT SPACE MANAGEMENT AUTO
FLASHBACK ON;

We are sure application is not trying to insert massive amount of data into the tables, an operation that only insert one row also causes this.

After increasing tablespace for the index manually, regular operation can be continued and tablespace usage for the index is not increasing dramatically.

We are using Oracle Database 11g Enterprise Edition Release 11.2.0.3.0 — 64bit Production on Solaris 11.1.

How do we pin point source of the problem? Is there an issue with the parameter of the index tablespace?

Источник

ORA-01658: unable to create INITIAL extent

hi,
I’ve exported my instance:

EXP SYSTEM/MANAGER@INSTANCE FULL=Y FILE=C:EXP_FILE.DMP LOG=C:ERROR_EXP.LOG

after I’ve created new tablespace and new user:
CREATE TABLESPACE MAX DATAFILE ‘c:MAX01.dbf’ SIZE 3800M REUSE DEFAULT STORAGE
(INITIAL 10240 NEXT 10240 MINEXTENTS 1 MAXEXTENTS 256 PCTINCREASE 0) ONLINE;

CREATE USER MAX IDENTIFIED BY MAX DEFAULT TABLESPACE MAX TEMPORARY TABLESPACE TEMP;

finally I’ve imported tom’s objects in MAX:

IMP SYSTEM/MANAGER@INSTANCE FROMUSER=TOM TOUSER=MAX FILE=C:EXP_FILE.DMP LOG=C:ERROR_IMP.log

but in some tables I get these errors:
IMP-00017: following statement failed with ORACLE error 1658:
«CREATE TABLE «AREA» («OFL» VARCHAR2(4), «OBL» VARCHAR2(32), «OSITE» V»
«ARCHAR2(32), «OLS» VARCHAR2(32), «OAREA» NUMBER, «DV_ID» VARCHAR2(32), «DP_»
«ID» VARCHAR2(32), «ODISMESSO» VARCHAR2(2), «OTYPE» VARCHAR2(64), «OCAT» VAR»
«CHAR2(64), «OOCCUP» NUMBER, «SFL» VARCHAR2(32), «SBL» VARCHAR2(32), «SSITE»»
» VARCHAR2(32), «PRORATE» VARCHAR2(8), «STYPE» VARCHAR2(64), «SCAT» VARCHAR2″
«(64), «SAREA» NUMBER, «SOCCUP» NUMBER, «AREA_SERV» NUMBER) PCTFREE 10 PCTU»
«SED 40 INITRANS 1 MAXTRANS 255 LOGGING STORAGE(INITIAL 314572800 NEXT 52428»
«800 MINEXTENTS 1 MAXEXTENTS 1000 PCTINCREASE 0 FREELISTS 1 FREELIST GROUPS «
«1 BUFFER_POOL DEFAULT) TABLESPACE «TOM»»
IMP-00003: ORACLE error 1658 encountered
ORA-01658: unable to create INITIAL extent for segment in tablespace TOM

I tried to increase tablespace MAX but I get always these errors:

How can I import without errors?

Thanks
Raf

Even though you have created a seperate tablespace for MAX the object will still be created in it’s original tablespace if it exists, in this case it will try and create the object in the TOM tablespace.

This is why increasing the size of the MAX tablespace has no effect.

Look at the error

ORA-01658: unable to create INITIAL extent for segment in tablespace TOM

Remove all quotas on TOM tablespace from user MAX or take the tablespace offline

Regards

Jim
Oracle Certified Professional
«Build your reputation by helping other people build theirs.»

«Sarcasm may be the lowest form of wit but its still funny»

Click HERE to vist my website!

Do you have a TOM tablespace in the new instance?

Jim
Oracle Certified Professional
«Build your reputation by helping other people build theirs.»

«Sarcasm may be the lowest form of wit but its still funny»

Click HERE to vist my website!

The import is complaining about the TOM tablespace in the new instance, as I said before if the original tablespace exists the import utility will attempt to create the objects there.

Simply remove MAX’s quota on the TOM tablespace in the new instance or take the tablespace offline in the new instance.

Jim
Oracle Certified Professional
«Build your reputation by helping other people build theirs.»

«Sarcasm may be the lowest form of wit but its still funny»

Click HERE to vist my website!

Источник

Oracle ORA-01658 Unable to Create INITIAL Extent for Segment in Tablespace Error

When inserting records into Oracle database by SQL statements, creating new tables, importing backup dump into Oracle database or when manipulating tables or data in the Oracle database, the following error may occurs:

ORA-01658: unable to create INITIAL extent for segment in tablespace tablespace_name

The problem is caused by the Oracle unable or fails to find sufficient contiguous space to allocate INITIAL extent for segment being created, due to the data files for a tablespace specified in tablespace_name has become full, or there is not enough space in the datafile.

You can check if the datafiles available and used by Oracle’s tablespaces is autoextensible, and if there is any free space in the datafile by using the following query.

select a.file_id,b.file_name,b.autoextensible,b.bytes/1024/1024,sum(a.bytes)/1024/1024
from dba_extents a , dba_data_files b
where a.file_id=b.file_id
group by a.file_id,b.file_name,autoextensible,b.bytes/1024/1024

The solutions or workarounds for the ORA-01658 Oracle error include:

Add a new datafile into the tablespace to increase additional space by using SQL query as shown:
```
ALTER TABLESPACE <tablespace_name> ADD DATAFILE <datafile_name7gt; size <size>
```
Retry the transaction or process with a smaller value for INITIAL.
Set AUTOEXTEND on for the data file of the tablespace.
Increase the size of the existing datafile by using the following SQL command:
```
ALTER DATABASE DATAFILE <datafile_name> RESIZE newsize;
```

About the Author: LK

LK is a technology writer for Tech Journey with background of system and network administrator. He has be documenting his experiences in digital and technology world for over 15 years.Connect with LK through Tech Journey on Facebook, Twitter or Google+.

Page load link

Go to Top

Источник

когда я пытался создать таблицу в моей схеме User_DB, я получаю ошибку как ORA-01658: unable to create INITIAL extent for segment in tablespace TS_DATA. Я выполните следующий запрос, чтобы получить все TABLESPACE_NAME:

SELECT * FROM DBA_DATA_FILES;

но я действительно не знаю, какое табличное пространство я использую и как расширить табличное пространство для решения этой проблемы.

2 ответов

как указывает сообщение об ошибке, вы используете TS_DATA табличное пространство. Вы можете расширить его, увеличив один из существующих файлов данных:

ALTER DATABASE 
DATAFILE 'C:ORACLEXEAPPORACLEORADATAXETS_DATA.DBF' 
RESIZE 3000M;

или добавив второй файл данных в табличное пространство:

ALTER TABLESPACE ts_data 
ADD DATAFILE 'C:ORACLEXEAPPORACLEORADATAXETS_DATA2.DBF' 
SIZE 1000M;

или просто позвольте файлу данных автоматически расширяться:

ALTER DATABASE 
DATAFILE 'C:ORACLEXEAPPORACLEORADATAXETS_DATA2.DBF'
AUTOEXTEND ON
MAXSIZE UNLIMITED; -- Or some reasonable cap

ИЗМЕНИТЬ БАЗУ ДАННЫХ
ДАННЫХ ‘C:ORACLEXEAPPORACLEORADATAXETS_DATA.ДБФ’
РАЗМЕР 3000М;

это сработало для меня спасибо

Источник

ORA-01658: unable to create INITIAL extent for segment in tablespace USERS

When do we get this error?

When tablespace doesn’t have good enough space,will receive ORA-01658 error.

Where do we trace this error?

We can trace the error from alert log. Read the alert log using tail unix command.

How to fix it ?

Solution:

Find the disk usage for free space and add datafile for the tablespace.

SQL>alter tablespace USERS add datafile ‘/oradata/prod/users02.dbf’ size 100m autoextend on maxsize unlimited;

Following error found when importing data from dumpfile

DW00 started with pid=51, OS id=5961, wid=1, job SYSTEM.SYS_IMPORT_SCHEMA_01
Tue Sep 01 09:02:47 2020
statement in resumable session ‘SYSTEM.SYS_IMPORT_SCHEMA_01.1’ was suspended due to
ORA-01658: unable to create INITIAL extent for segment in tablespace USERS

After Adding datafile,the job auto resumed as follows.

Tue Aug 01 09:11:15 2019
alter tablespace users add datafile ‘/u01/app/oracle/oradata/users02.dbf’ size 100m autoextend on maxsize unlimited
Completed: alter tablespace users add datafile ‘/u01/app/oracle/oradata/users02.dbf’ size 100m autoextend on maxsize unlimited
Tue Sep 01 09:11:18 2020
statement in resumable session ‘SYSTEM.SYS_IMPORT_SCHEMA_01.1’ was resumed

Источник