ORA-01653: unable to extend table [Résolu]

[wissem.ba]

Bonjour,

Suite à cette requête :

Code :

1
2
3
4
5
6
7
8
9
10
11
12
13
14
15
16
17
18
19
20
21
22
23
24
25
26
27
28
29
30
31
INSERT INTO CUSTOMFIELDVALUE_TER  
      (CUSTVALUEDWHID,
      ID,
      ISSUE,
      CUSTOMFIELD,
      PARENTKEY,
      STRINGVALUE,
      NUMBERVALUE,
      DATEVALUE,
      VALUETYPE,
      ISUPDATED,
      DATEINSERTED,
      INSERTSTATUS,
      IMPORTSTATUS,
      TEXTVALUE)
SELECT 
      DWHID_CUSTOMFIELDVALUE.NEXTVAL,
      a.ID,
      a.ISSUE,
      a.CUSTOMFIELD,
      a.PARENTKEY,
      a.STRINGVALUE,
      a.NUMBERVALUE,
      a.DATEVALUE,
      a.VALUETYPE,
       'YES' AS ISUPDATED  ,                                                                                                                                                                                                                                                                                                                                            
      sysdate AS DATEINSERTED,                                                                                                                                                                                                                                                                                                                                                 
     'NEW' AS INSERTSTATUS,
		 'FULL' AS IMPORTSTATUS,
      dbms_lob.substr(a.TEXTVALUE,4000)                                                                                                                                                                                                                                                                                                                                                   
FROM  CUSTOMFIELDVALUE a

J'ai cela comme message d'erreur

SQL Error: ORA-01653: unable to extend table JIRAQUAL.CUSTOMFIELDVALUE_TER by 128 in tablespace JIRAQUAL_DAT
01653. 00000 - "unable to extend table %s.%s by %s in tablespace %s"
*Cause:Failed to allocate an extent of the required number of blocks for a table segment in the tablespace indicated.
*Action:Use ALTER TABLESPACE ADD DATAFILE statement to add one or more
files to the tablespace indicated.

Savez-vous la raison pour laquelle ma requête ne marche pas et la solution s'il y a un problème ? je cherche depuis ce matin sans rien trouver.

Merci

[laurentschneider]

peut-être que ton tablespace est plein?

que te donnes

Code :

1
2
3
4
 
SELECT count(*),sum(bytes) 
FROM dba_free_space 
WHERE tablespace_name='JIRAQUAL_DAT'

[DBA_OCP]

ORA-01653

Explanation:
------------
This error does not necessarily indicate whether or not you have enough space
in the tablespace, it merely indicates that Oracle could not find a large enough area of free
contiguous space in which to fit the next extent.


Diagnostic Steps:
-----------------
1. In order to see the free space available for a particular tablespace, you must
use the view DBA_FREE_SPACE. Within this view, each record represents one
fragment of space. How the view DBA_FREE_SPACE can be used to determine
the space available in the database is described in:
[NOTE:121259.1] Using DBA_FREE_SPACE

2. The DBA_TABLES view describes the size of next extent (NEXT_EXTENT) and the
percentage increase (PCT_INCREASE) for all tables in the database.
The "next_extent" size is the size of extent that is trying to be allocated (and for
which you have the error).

When the extent is allocated :
next_extent = next_extent * (1 + (pct_increase/100))

Algorythm to allocate extent for segment is described in the Concept Guide
Chapter : Data Blocks, Extents, and Segments - How Extents Are Allocated

3. Look to see if any users have the tablespace in question as their temporary tablespace.
This can be checked by looking at DBA_USERS (TEMPORARY_TABLESPACE).

Possible solutions:
-------------------
- Manually Coalesce Adjacent Free Extents
ALTER TABLESPACE COALESCE;
The extents must be adjacent to each other for this to work.

- Add a Datafile:
ALTER TABLESPACE ADD DATAFILE ''
SIZE ;

- Resize the Datafile:
ALTER DATABASE DATAFILE '' RESIZE ;

- Enable autoextend:
ALTER DATABASE DATAFILE ?? AUTOEXTEND ON
MAXSIZE UNLIMITED;

- Defragment the Tablespace:

- Lower "next_extent" and/or "pct_increase" size:
ALTER STORAGE ( next
pctincrease );

- If the tablespace is being used as a temporary tablespace, temporary segments may
be still holding the space.

[wissem.ba]

Merci bien

la requête

Code :

1
2
3
SELECT count(*),sum(bytes) 
FROM dba_free_space 
WHERE tablespace_name='JIRAQUAL_DAT'

me donne rien, j'ai ce message d'erreur


ORA-00942: table or view does not exist
00942. 00000 - "table or view does not exist"

[laurentschneider]

ok, tu n'as pas les droits sur cette vue du dictionnaire.

que te donne :

Code :

1
2
3
 
SELECT * FROM user_ts_quotas;
SELECT * FROM session_privs;

[laurentschneider]

demande aussi à ton DBA si le tablespace est plein, ok?

[wissem.ba]

La requête

Code :

SELECT * FROM user_ts_quotas;

me donne ça

TABLESPACE_NAME BYTES MAX_BYTES BLOCKS MAX_BLOCKS DROPPED
JIRAQUAL_BLOB 433979392 -1 52976 -1 NO
JIRAQUAL_DAT 3752394752 -1 458056 -1 NO
JIRAQUAL_IDX 958267392 -1 116976 -1 NO

et celle là

Code :

SELECT * FROM session_privs;

Privilège
CREATE SESSION
UNLIMITED TABLESPACE
CREATE TABLE
CREATE CLUSTER
CREATE SYNONYM
CREATE VIEW
CREATE SEQUENCE
CREATE PROCEDURE
CREATE TRIGGER
CREATE TYPE
CREATE OPERATOR
CREATE INDEXTYPE
QUERY REWRITE

[wissem.ba]

La table CUSTOMFIELDVALUE_TER ne peut contenir plus que 1839565

Si j'insère une ligne supplémentaire j'aurai le message d'erreur.

Que cela signifie ?

Merci

[wissem.ba]

Problème résolu,

Comme je n'ai pas assez de droit j'ai appelé mon DBA qui a augmenté le tablespace

Merci bien pour votre aide