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
===============================
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 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.
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
===============================
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
===============================
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
===============================
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.
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
===============================
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:
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