Cannot enter data into an Oracle table in production through stored procedure

Question

The row count of the table is 671839. The sequence is in order. Transactions have been made to the table yesterday like it had been made on for the last 7 months, i.e. since the last application deployment. Nothing has been changed in the application code or database in the last 7 months and everything was working fine. Suddenly after some successful transactions, insertions have been failing to the table since middle of yesterday! No error messages in browser. I have exported the whole database, but cannot import the concerned table using oracle import script in cmd prompt. The following error is shown:

IMP-00003: ORACLE error 1659 encountered ORA-01659: unable to allocate MINEXTENTS beyond 8 in tablespace USERS

Here is the DDL for the table:

CREATE TABLE MDP.TBL_DAILY_DATA
(
  DAILY_DATA_ID        NUMBER(38),
  METER_ID             NUMBER(38),
  D_DATE               DATE,
  FREQUENCY_POWER_SUM  FLOAT(126),
  WH_REG_VALUE         FLOAT(126),
  VAR_REG_HIGH_VALUE   FLOAT(126),
  VAR_REG_LOW_VALUE    FLOAT(126),
  RECEIVED_DATE        DATE,
  DETAIL               SYS.XMLTYPE,
  DETAIL_TEXT          VARCHAR2(4000 BYTE),
  MWH_DETAIL               SYS.XMLTYPE,
  MWH_DETAIL_TEXT          VARCHAR2(4000 BYTE)
)
XMLTYPE DETAIL STORE AS CLOB (
  TABLESPACE USERS
  ENABLE       STORAGE IN ROW
  CHUNK       8192
  RETENTION
  NOCACHE
  LOGGING
  INDEX       (
        TABLESPACE USERS
        STORAGE    (
                    INITIAL          64K
                    NEXT             1M
                    MINEXTENTS       1
                    MAXEXTENTS       UNLIMITED
                    PCTINCREASE      0
                    BUFFER_POOL      DEFAULT
                   ))
      STORAGE    (
                  INITIAL          64K
                  NEXT             1M
                  MINEXTENTS       1
                  MAXEXTENTS       UNLIMITED
                  PCTINCREASE      0
                  BUFFER_POOL      DEFAULT
                 ))
TABLESPACE USERS
PCTUSED    0
PCTFREE    10
INITRANS   1
MAXTRANS   255
STORAGE    (
            INITIAL          272M
            NEXT             1M
            MINEXTENTS       1
            MAXEXTENTS       UNLIMITED
            PCTINCREASE      0
            BUFFER_POOL      DEFAULT
           )
LOGGING 
NOCOMPRESS 
NOCACHE
NOPARALLEL
MONITORING;


CREATE UNIQUE INDEX MDP.UK_TBL_DAILY_DATA ON MDP.TBL_DAILY_DATA
(METER_ID, D_DATE)
LOGGING
TABLESPACE USERS
PCTFREE    10
INITRANS   2
MAXTRANS   255
STORAGE    (
            INITIAL          3M
            NEXT             1M
            MINEXTENTS       1
            MAXEXTENTS       UNLIMITED
            PCTINCREASE      0
            BUFFER_POOL      DEFAULT
           )
NOPARALLEL;


ALTER TABLE MDP.TBL_DAILY_DATA ADD (
  PRIMARY KEY
  (DAILY_DATA_ID)
  USING INDEX
    TABLESPACE USERS
    PCTFREE    10
    INITRANS   2
    MAXTRANS   255
    STORAGE    (
                INITIAL          1M
                NEXT             1M
                MINEXTENTS       1
                MAXEXTENTS       UNLIMITED
                PCTINCREASE      0
               ),
  CONSTRAINT UK_TBL_DAILY_DATA
  UNIQUE (METER_ID, D_DATE)
  USING INDEX MDP.UK_TBL_DAILY_DATA);

score 5 · Answer 1 · answered Aug 19 '15 at 12:01

5

your USERS tablespace is full, find the datafiles for the tablespace

SELECT * FROM DBA_DATA_FILES WHERE tablespace_name = 'USERS';

once you have done this, extend the maxsize of the datafiles (the following extends it to 100g, but choose a sensible size for your environment)

ALTER DATABASE DATAFILE '<filename>' MAXSIZE 100g;

answered Aug 19 '15 at 12:01

davegreen100

2,055
3
13
24

2

Thanks a lot! But instead of ALTER DATABASE DATAFILE '' MAXSIZE 100g; I used "alter tablespace USERS add datafile '' size 512M autoextend on next 128M maxsize 8192M; – Pratip GD Aug 20 '15 at 07:22

Tharunkumar Reddy · Answer 2 · 2015-08-19T12:53:28.860

3

WITH THE USE OF BELOW QUERY FIND THE FREE MB IN YOUR TABLE SPACE.

select b.tablespace_name, tbs_size SizeMb, a.free_space FreeMb
from  (select tablespace_name, round(sum(bytes)/1024/1024 ,2) as free_space
from dba_free_space
group by tablespace_name) a,
(select tablespace_name, sum(bytes)/1024/1024 as tbs_size
from dba_data_files

       group by tablespace_name) b
where a.tablespace_name(+)=b.tablespace_name
and b.tablespace_name='USERS' ;

if free space is zero then alter your data file size. Find your datafile name with the help of below query

select *
from dba_data_files c where tablespace_name='USERS'

And alter the size of your data file.

edited Aug 19 '15 at 12:53

answered Aug 19 '15 at 12:15

Tharunkumar Reddy

2,773
18
32

Thanks a lot! That was very useful. The free_space in the tablespace was 1.19mb. – Pratip GD Aug 20 '15 at 07:14
So you need to extend the size of data file i guess. Welcome @PratipGD – Tharunkumar Reddy Aug 20 '15 at 07:16

Cannot enter data into an Oracle table in production through stored procedure

2 Answers2