Pages

Tuesday, July 4, 2017

SQL*Loader is failing with error: ORA-01653: unable to extend table by 128 in tablespace

===============================
General
===============================
SQL*Loader is failing with error: ORA-01653: unable to extend table 
CUSTOMER_REPORTS.CUSTOMER_USAGE_DAILY by 128 in tablespace DWH_TABLE

This is strange, because when checking used space inside tablespace DWH_TABLE, there is plenty of space. 
What could be wrong?

===============================
Evidence from SQL*Loader
===============================

Table CUSTOMER_USAGE_DAILY, loaded from every logical record.
Insert option in effect for this table: APPEND
TRAILING NULLCOLS option in effect

   Column Name                  Position   Len  Term Encl Datatype
------------------------------ ---------- ----- ---- ---- ---------------------
KEY1                                FIRST     *   ,       CHARACTER            
DATE_OF_RECORD                       NEXT     *   ,       CHARACTER            
NETWORK_ID                           NEXT     *   ,       CHARACTER            
IMSI                                 NEXT     *   ,       CHARACTER            
VOICE_MO                             NEXT     *   ,       CHARACTER            
VOICE_MT                             NEXT     *   ,       CHARACTER            
DATA                                 NEXT     *   ,       CHARACTER            
SMS_MO                               NEXT     *   ,       CHARACTER            
TS_LAST_MODIFIED                                          SYSDATE

ORA-01653: unable to extend table CUSTOMER_REPORTS.CUSTOMER_USAGE_DAILY by 128 in tablespace DWH_TABLE

===============================
Checking current status
===============================

Checking usage in DBA_DATA_FILES

SET LINESIZE 140
SET PAGESIZE 200
COL tablespace_name FOR A30
COL file_name FOR A60
COL Mb FOR 9999999999999
COL MAX_MB FOR 9999999999999

SELECT tablespace_name, 
       file_name, 
       ROUND(bytes/1024/1024) AS Mb, ROUND(maxbytes/1024/1024) AS MAX_MB
  FROM DBA_DATA_FILES
ORDER BY tablespace_name, file_name;  

TABLESPACE_NAME FILE_NAME                                         MB     MAX_MB
--------------- -------------------------------------------- ------- ----------
DWH_INDEX       /oracle_db/db1/db_igt/ora_dwh_index_01.dbf      2200       6000
DWH_TABLE       /oracle_db/db1/db_igt/ora_dwh_table_01.dbf     12000      12000
GIN             /oracle_db/db1/db_igt/ora_gin_01.dbf             500       6000
GININDEX        /oracle_db/db1/db_igt/ora_ginindex_01.dbf        200       3000
IGT_INDEX       /oracle_db/db1/db_igt/ora_igt_index_01.dbf      2900      30000
IGT_TABLE       /oracle_db/db1/db_igt/ora_igt_table_01.dbf      2300      30000
IGT_TABLE       /oracle_db/db1/db_igt/ora_igt_table_02.dbf       600      30000
IGT_TABLE       /oracle_db/db1/db_igt/ora_igt_table_03.dbf       400      30000
SYSAUX          /oracle_db/db1/db_igt/ora_sysaux_01.dbf          900       2000
SYSTEM          /oracle_db/db1/db_igt/ora_system_01.dbf          500       2000
UNDOTBS         /oracle_db/db1/db_igt/ora_undotbs_01.dbf        6000       6000
WORKAREA        /oracle_db/db1/db_igt/ora_workarea_01.dbf        300       3000



SET LINESIZE 120
SET PAGESIZE 200

SELECT TABLESPACE_NAME, 
       (MAX(MAX_SPACE)-MAX(USED_SPACE)) AS DBA_FREE_SPACE_MB,
       MAX(USED_SPACE) AS USED_SPACE_MB, 
       MAX(MAX_SPACE) AS MAX_SPACE_MB, 
       ROUND(((MAX(MAX_SPACE)-MAX(USED_SPACE))/MAX(MAX_SPACE))*100) as FREE_PCT, 
       CASE WHEN (ROUND(( (MAX(MAX_SPACE)-MAX(USED_SPACE)  ) /MAX(MAX_SPACE) ) *100)<15)  THEN 'Y' ELSE 'N' END AS ADD_MORE_SPACE 
       FROM ( 
          SELECT tablespace_name,  
         ROUND(SUM(bytes)/1024/2014) AS FREE_SPACE, 
               0 AS MAX_SPACE, 
            0 AS USED_SPACE   
            FROM DBA_FREE_SPACE
           WHERE 1=1
                 --AND tablespace_name LIKE '%IGT%' OR tablespace_name LIKE '%DWH%' OR tablespace_name LIKE '%REPORTS%' OR tablespace_name = 'SYSTEM'  OR tablespace_name = 'SYSAUX' 
           GROUP BY tablespace_name  
           UNION ALL 
          SELECT tablespace_name, 
         0 AS FREE_SPACE, 
           ROUND( SUM(CASE WHEN (bytes>maxbytes)  THEN bytes ELSE maxbytes END)/1024/1024) AS MAX_SPACE, 
                 0 AS USED_SPACE 
            FROM DBA_DATA_FILES
           WHERE 1=1
                 --AND tablespace_name LIKE '%IGT%' OR tablespace_name LIKE '%DWH%' OR tablespace_name LIKE '%REPORTS%' OR tablespace_name = 'SYSTEM'  OR tablespace_name = 'SYSAUX' 
           GROUP BY tablespace_name 
           UNION ALL 
           SELECT tablespace_name, 
                  0 AS FREE_SPACE, 
                  0 AS MAX_SPACE, 
                  ROUND(SUM(bytes/1024/1024)) AS USED_SPACE 
             FROM DBA_SEGMENTS
            WHERE 1=1
            --AND tablespace_name LIKE '%IGT%' OR tablespace_name LIKE '%DWH%' OR                     tablespace_name LIKE '%REPORTS%' OR tablespace_name = 'SYSTEM'                     OR tablespace_name = 'SYSAUX' 
            GROUP BY tablespace_name 
       ) 
GROUP BY tablespace_name;

TABLESPACE_NAME DBA_FREE_SPACE_MB USED_SPACE_MB MAX_SPACE_MB   FREE_PCT ADD_SPACE
--------------- ----------------- ------------- ------------ ---------- ---------
DWH_INDEX                    3935          2065         6000         66 N
SYSAUX                       1462           538         2000         73 N
IGT_TABLE                   86981          3019        90000         97 N
GININDEX                     3000             0         3000        100 N
UNDOTBS                      5834           166         6000         97 N
SYSTEM                       1593           407         2000         80 N
WORKAREA                     3000             0         3000        100 N
GIN                          6000             0         6000        100 N
IGT_INDEX                   27319          2681        30000         91 N
DWH_TABLE                   10234          1766        12000         85 N


Checking DBA_TABLESPACE_USAGE_METRICS

SELECT name, value from  V$PARAMETER WHERE name = 'db_block_size';

NAME              VALUE
----------------  ----------
db_block_size     8192

SELECT tablespace_name, 
       ROUND(used_space*8192/1024/1024) AS USED_MB,
       ROUND(tablespace_size*8192/1024/1024) AS MAX_MB, 
       ROUND(used_percent) AS USED_PCT , 
       100-ROUND(used_percent) AS FREE_PCT
 FROM  DBA_TABLESPACE_USAGE_METRICS
ORDER BY tablespace_name;


TABLESPACE_NAME                   USED_MB         MAX_MB   USED_PCT   FREE_PCT
------------------------------ ---------- -------------- ---------- ----------
DWH_INDEX                            2066           6000         34         66
DWH_TABLE                            1767          12000         15         85
GIN                                     1           6000          0        100
GININDEX                                1           3000          0        100
IGT_INDEX                            2682          30000          9         91
IGT_TABLE                            3022          90000          3         97
SYSAUX                                539           2000         27         73
SYSTEM                                408           2000         20         80
TEMPORARY                               0          12000          0        100
UNDOTBS                                78           6000          1         99
WORKAREA                                1           3000          0        100

Checking top DBA_SEGMENTS on DWH_TABLE tablespace, CUSTOMER_USAGE_DAILY is not one of the top heavy segments. 

OWNER            TABLESPACE_NAME SEGMENT_NAME                USED_MB
---------------- --------------- -------------------------- --------
CUSTOMER_REPORTS DWH_TABLE       FACT_ROAMER_CAMPAIGNS          699
CUSTOMER_REPORTS DWH_TABLE       AGG_ROAMING_PROF_1M            524
CUSTOMER_REPORTS DWH_TABLE       FACT_ROAMER_SCENARIO           146
CUSTOMER_REPORTS DWH_TABLE       FACT_ROAMER_USAGE               45
CUSTOMER_REPORTS DWH_TABLE       AGG_ROAMING_PROF_12M            32
CUSTOMER_REPORTS DWH_TABLE       AGG_ROAMING_PROF_YTD            31
CUSTOMER_REPORTS DWH_TABLE       AGG_CAMPAIGN_SENT_DAILY         29
CUSTOMER_REPORTS DWH_TABLE       AUDIT_TRACE                     26
CUSTOMER_REPORTS DWH_TABLE       AGG_SCENARIO_6M                 23
CUSTOMER_REPORTS DWH_TABLE       AGG_SCENARIO_CN_18M             23



SELECT COUNT(*) FROM DBA_EXTENTS WHERE TABLESPACE_NAME = 'DWH_TABLE';

  COUNT(*)
----------
     13753

Checking for pct_increase value, there is no fixed value set. 
So Oracle is using it internal defaults.

SELECT table_name, PCT_INCREASE FROM DBA_TABLES WHERE table_name = 'CUSTOMER_USAGE_DAILY';

TABLE_NAME                     PCT_INCREASE
------------------------------ ------------
SFI_CUSTOMER_USAGE_DAILY


SELECT TABLESPACE_NAME, PCT_INCREASE FROM DBA_TABLESPACES WHERE TABLESPACE_NAME = 'DWH_TABLE';

TABLESPACE_NAME PCT_INCREASE
--------------- ------------
DWH_TABLE


Checking CUSTOMER_USAGE_DAILY extents.

SELECT OWNER, SEGMENT_NAME, TABLESPACE_NAME AS TBS_NAME, EXTENT_ID, BLOCK_ID , BYTES 
  FROM DBA_EXTENTS 
 WHERE SEGMENT_NAME = 'CUSTOMER_USAGE_DAILY';

OWNER            SEGMENT_NAME         TBS_NAME  EXTENT_ID   BLOCK_ID      BYTES
---------------- -------------------- --------- ---------- ---------- ----------
CUSTOMER_REPORTS CUSTOMER_USAGE_DAILY DWH_TABLE          0       3384      65536
CUSTOMER_REPORTS CUSTOMER_USAGE_DAILY DWH_TABLE          1    1523848      65536
CUSTOMER_REPORTS CUSTOMER_USAGE_DAILY DWH_TABLE          2    1523856      65536
CUSTOMER_REPORTS CUSTOMER_USAGE_DAILY DWH_TABLE          3    1523864      65536
CUSTOMER_REPORTS CUSTOMER_USAGE_DAILY DWH_TABLE          4    1523872      65536
CUSTOMER_REPORTS CUSTOMER_USAGE_DAILY DWH_TABLE          5    1523880      65536
CUSTOMER_REPORTS CUSTOMER_USAGE_DAILY DWH_TABLE          6    1523888      65536
CUSTOMER_REPORTS CUSTOMER_USAGE_DAILY DWH_TABLE          7    1523896      65536
CUSTOMER_REPORTS CUSTOMER_USAGE_DAILY DWH_TABLE          8    1523904      65536
CUSTOMER_REPORTS CUSTOMER_USAGE_DAILY DWH_TABLE          9    1523912      65536
CUSTOMER_REPORTS CUSTOMER_USAGE_DAILY DWH_TABLE         10    1523920      65536
CUSTOMER_REPORTS CUSTOMER_USAGE_DAILY DWH_TABLE         11    1523928      65536
CUSTOMER_REPORTS CUSTOMER_USAGE_DAILY DWH_TABLE         12    1523936      65536
CUSTOMER_REPORTS CUSTOMER_USAGE_DAILY DWH_TABLE         13    1523944      65536
CUSTOMER_REPORTS CUSTOMER_USAGE_DAILY DWH_TABLE         14    1523952      65536
CUSTOMER_REPORTS CUSTOMER_USAGE_DAILY DWH_TABLE         15    1523960      65536
CUSTOMER_REPORTS CUSTOMER_USAGE_DAILY DWH_TABLE         16     217088    1048576
CUSTOMER_REPORTS CUSTOMER_USAGE_DAILY DWH_TABLE         17     217216    1048576
CUSTOMER_REPORTS CUSTOMER_USAGE_DAILY DWH_TABLE         18    1536000    1048576


Checking DBA_RECYCLEBIN contents.
SELECT COUNT(*) FROM DBA_RECYCLEBIN;
COUNT(*)
----------
0
===============================
Oracle Metalink
===============================

Per Oracle Metalink 
SQL*Loader Fils on Load with ORA-1653 (Doc ID 205296.1)
Fact(s)
~~~~~~~~
Oracle RDBMS Server
Oracle SQL*Loader


Symptom(s)
~~~~~~~~~~
SQL*Loader fails on small load with 
  ORA-1653: unable to extend table <schema>.<table_name> by <size> in tablespace <tbs>

Loads on other tables work without problem. There is sufficient space within the
tablespace to accomodate the load.


Change(s)
~~~~~~~~~~
N/A


Cause
~~~~~~~
NEXT extent requested by the table is larger than the largest available extent
within the tablespace.

It is possible that PCTINCREASE is set to some non-zero value and each request
for extents getslarger and larger.


Fix
~~~~
Possible resolutions include:

1. Coalesce the tablespace:

ALTER TABLESPACE <TBS> COALESCE;

2. Add a datafile to the tablespace:

ALTER TABLESPACE <TBS> ADD DATAFILE 'PATH/NAME' SIZE <SIZE>;

3. Alter the NEXT attribute for the table that you are trying to load into
   to a value smaller than the largest available free extent in the tablespace:

ALTER TABLE <TABLE_NAME> STORAGE (NEXT <SIZE M/K>);
   where M/K = Megabytes or Kilobytes.

   To get the Next extent that will be requested by the table do:
   
SELECT TO_CHAR (NEXT_EXTENT,'999G999G999G999D00')||' MB' AS "NEXT"
  FROM   USER_TABLES 
 WHERE  TABLE_NAME = '<TABLE_NAME>';


   To get the largest free extent available in the tablespace run:
   
SELECT TABLESPACE_NAME,
       TO_CHAR (MAX(BYTES),'999G999G999G999D00')||' MB' AS "LARGEST FREE EXTENT"
  FROM   DBA_FREE_SPACE
 WHERE  TABLESPACE_NAME = '<TBS>'
 GROUP  BY TABLESPACE_NAME;



===============================
Add more space, to fix the error
===============================
D. In order to resolve the issue, additional 2Gb of space were allocated to /oracle_db/db1/db_igt/ora_dwh_table_01.dbf datafile.

ALTER DATABASE DATAFILE '/oracle_db/db1/db_igt/ora_dwh_table_01.dbf' AUTOEXTEND OF MAXSIZE 14000M;

Now when running the SQL*Load, the process completes without error.

Checking usage in DBA_DATA_FILES, the above datafile has increase by 200M, although there is plenty of free space!!

DBA_DATA_FILES
TABLESPACE_NAME FILE_NAME                                         MB     MAX_MB
--------------- -------------------------------------------- ------- ----------
DWH_INDEX       /oracle_db/db1/db_igt/ora_dwh_index_01.dbf      2200       6000
DWH_TABLE       /oracle_db/db1/db_igt/ora_dwh_table_01.dbf     12200      14000
GIN             /oracle_db/db1/db_igt/ora_gin_01.dbf             500       6000
GININDEX        /oracle_db/db1/db_igt/ora_ginindex_01.dbf        200       3000
IGT_INDEX       /oracle_db/db1/db_igt/ora_igt_index_01.dbf      2900      30000
IGT_TABLE       /oracle_db/db1/db_igt/ora_igt_table_01.dbf      2300      30000
IGT_TABLE       /oracle_db/db1/db_igt/ora_igt_table_02.dbf       600      30000
IGT_TABLE       /oracle_db/db1/db_igt/ora_igt_table_03.dbf       400      30000
SYSAUX          /oracle_db/db1/db_igt/ora_sysaux_01.dbf          900       2000
SYSTEM          /oracle_db/db1/db_igt/ora_system_01.dbf          500       2000
UNDOTBS         /oracle_db/db1/db_igt/ora_undotbs_01.dbf        6000       6000
WORKAREA        /oracle_db/db1/db_igt/ora_workarea_01.dbf        300       3000


DBA_TABLESPACES
TABLESPACE_NAME DBA_FREE_SPACE_MB USED_SPACE_MB MAX_SPACE_MB   FREE_PCT ADD_SPACE
--------------- ----------------- ------------- ------------ ---------- ---------
DWH_INDEX                    3935          2065         6000         66 N
SYSAUX                       1462           538         2000         73 N
IGT_TABLE                   86981          3019        90000         97 N
GININDEX                     3000             0         3000        100 N
UNDOTBS                      5834           166         6000         97 N
SYSTEM                       1593           407         2000         80 N
WORKAREA                     3000             0         3000        100 N
GIN                          6000             0         6000        100 N
IGT_INDEX                   27319          2681        30000         91 N
DWH_TABLE                   12234          1766        14000         87 N


===============================
Conclusion
===============================
When checking all the above information, two findings stand out.

A. There are 13753 extents in tablespace DWH_TRACE.
This Tablespace size is 12000 Mb.
That means that many of the extents are less than 1Mb.


B. Before addition of 2Gb, the data from DBA_DATA_FILES shows:
DWH_TABLE       /oracle_db/db1/db_igt/ora_dwh_table_01.dbf     12000      12000
So the DWH_TRACE was populated in the past with segments, which are now deleted.

C. The next extent of table CUSTOMER_USAGE_DAILY is 1048576 bytes.
   So when loading data via SQL*Loader, the next allocated extent should be of size 1Mb.

D. If extent of size 1Mb cannot be allocated, an error ORA-01653: unable to extend table by 128 in tablespace would be thrown.

E. After adding additional space to datafile, the DBA_DATA_FILES.bytes value has increased from 12000M to 12200MB.

This is surprising!

Tablespace DWH_TABLE is only 13% used, but still the datafile has increased by 200M.

SQL*Loader is using Direct Access method data load.

It seems that the SQL*Loader process cannot re-use existing free extents withing the tablespace, and is allocating new space.

===============================
Solution
===============================
The long term solution would be to move table CUSTOMER_USAGE_DAILY table to a dedicated tablespace.
That way, this tablespace can be maintained without affecting other tables in the application.
ALTER TABLE CUSTOMER_USAGE_DAILY MOVE TABLESPACE NEW_TABLESPACE;



View Indexes Fragmentation.
Collect Statistics for Index Fragmentation:
as sys
GRANT SELECT ON index_stats to COL_CLARO_SPXQQ

as user
CREATE TABLE frag_stats_all AS SELECT * FROM index_stats WHERE 1=2;

DECLARE
    cursor ind_cur IS
       select owner , index_name
       from   dba_indexes where tablespace_name='IGT_INDEX' AND owner = 'COL_CLARO_SPXQQ'
       order by owner , index_name; 
    l_sql_text         varchar2(1000);
BEGIN    
    for ind_rec in ind_cur LOOP
  l_sql_text := 'analyze index '||ind_rec.owner||'.'||ind_rec.index_name||' validate structure';
  execute immediate l_sql_text;
      l_sql_text := 'insert into frag_stats_all select * from index_stats';
      execute immediate l_sql_text;
    end loop;
    commit;
END;
/

To See results: 
SELECT name,height,lf_rows,lf_blks,lf_rows_len,lf_blk_len,del_lf_rows,distinct_keys,used_space/1024/1024 used_space
FROM INDEX_STATS
SELECT * FROM dba_indexes
SELECT USER FROM DUAL

No comments:

Post a Comment