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,61223 gold badges63 silver badges113 bronze badges
3
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
295k52 gold badges303 silver badges346 bronze badges
2
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
2683 silver badges9 bronze badges
If you use a tablespace with 8K block size, then the maximum size of your datafile is 32 GB. If your datafile is already at that size, you can not increase it any further, there comes option number two:
- add a new datafile to the tablespace, for example:
alter tablespace xyz add datafile ‘/path/to/datafiles/xyz02.dbf’ size 100M autoextend on next 100M maxsize unlimited;
answered Apr 5 at 14:55
I recently ran into the following Oracle error
ORA-01658: unable to create INITIAL extent for segment in tablespace USERS
So I extended the USERS tablespace with another 5 GByte file.
Unfortunately after some time the error came back and I can see that the newly created file is already full. The USERS tablespace is already more than 30 GByte big.
How can I reuse some of the existing space that should be free since I have dropped some users?
asked Sep 2, 2015 at 14:40
Harold L. BrownHarold L. Brown
7,99311 gold badges56 silver badges106 bronze badges
3
(Moving comment to an answer)
If you drop users, which by extension drops all the associated objects, Oracle will be able to automatically use the space that was freed.
It would be a different story if you were trying to free space by deleting rows. In that case, you would have to shrink the space used by the table so that you can reuse the space elsewhere. But this is not your case.
answered Sep 3, 2015 at 11:58
Solution or Workaround
After loading a feature dataset, trying to register the feature dataset as versioned produces this error:
«Underlying DBMS error(ORA-01658): unable to create INITIAL extent for segment in tablespace xxxx (Dnn) [xxxx.N_2_JStatus]»
After closing the error dialog box, a right-click on the feature dataset shows the Register As Versioned option grayed out as if the registration had worked.
Cause:
When registering a feature class as versioned, it creates two new tables and a few new indexes. These need table space. If there is not enough space, not all the feature classes are versioned. The Catalog finds the first class already versioned and report as if the whole dataset has been versioned.
Solution:
1. Check the tablespace.
a. Check the configuration keyword for the business table with:
sdetable -o describe_reg -t tablename ...ArcSDE 8.0.1 Build 276 Tue Dec 7 11:01:59 PST 1999
Attribute Administration Utility
-----------------------------------------------------
.
.
.
Config. Keyword : FOOTPRINT
.
.
.
b. Look up that keyword in %sdehome%etcdbtune.sde. The A_* parameters control the creation of the versioning tables, Ann and Dnn. Specifically, look at the value for A_INIT and A_IX1_INIT. There must be enough space in the tablespace to fit all of the initial extents for both tables and their indexes. If registering a feature dataset, there needs to be enough space for all of the feature classes it contains.
c. Verify that there is enough space in the tablespace, and add more if necessary.
2. Once there is enough space, unregister the feature dataset. The default GUI does not have an unregister control, so it must be added to a toolbar.
a. Right-click on a toolbar in ArcCatalog
b. Open the Customize dialog
c. Click the Commands tab
d. For Categories, click Geodatabase tools
e. For Commands, drag Unregister as Versioned to any toolbar
f. Close the dialog
g. Select the feature dataset and click the ‘Unregister As Versioned button’.
3. Register As Versioned again.
Note: ArcSDE will use the dbtune keyword for the business table from the TABLE_REGISTRY, which may be different than the one in LAYERS.
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?