UNDO Tablespace. Move from one location to another; Add Datafile.

=======================
General
=======================
Due to space shortage on disk need to move UNDO Tablespace to another location.

=======================
Steps
=======================

To add datafile to UNDO TBS:

ALTER TABLESPACE UNDOTBS1 ADD DATAFILE '/oracle_db/db1/db_igt/data/ora_undotbs_02.dbf' SIZE 1000M AUTOEXTEND ON NEXT 1M MAXSIZE 32000M; 

Increase size of existing datafile

ALTER DATABASE DATAFILE '/oracle_db/db1/db_igt/ora_undotbs_01.dbf' AUTOEXTEND ON MAXSIZE 4000M;

=======================
Steps
=======================

See current status

show parameter undo
NAME                                 TYPE                 VALUE
------------------------------------ -------------------- ----------
undo_management                      string               AUTO
undo_retention                       integer              3600
undo_tablespace                      string               UNDOTBS

Create a new UNDO Tablespace, alter system to use it,and from the old one
CREATE UNDO TABLESPACE UNDOTBS2 DATAFILE '/oracle_db/db2/db_igt/igt/ora_undotbs2_01.dbf' SIZE 10000M;

ALTER DATABASE DATAFILE '/oracle_db/db2/db_igt/igt/ora_undotbs2_01.dbf' AUTOEXTEND ON MAXSIZE 30000M;

ALTER SYSTEM SET UNDO_TABLESPACE=UNDOTBS2;

DROP TABLESPACE UNDOTBS INCLUDING CONTENTS AND DATAFILES;

Now, either update SPFILE to use the new name UNDOTBS2 via:
ALTER SYSTEM SET UNDO_TABLESPACE=UNDOTBS2 SCOPE=BOTH;

Or move UNDO tablespace back to the old name

CREATE UNDO TABLESPACE UNDOTBS DATAFILE '/oracle_db/db2/db_igt/tbs/ora_undotbs_01.dbf' SIZE 10000M;

ALTER SYSTEM SET UNDO_TABLESPACE=UNDOTBS;

DROP TABLESPACE UNDOTBS2 INCLUDING CONTENTS AND DATAFILES;

=======================
Additional Info
=======================
Inside spfile, the UNDO Tablespace name is stored, but not the actual file location.

oracle@some_server:/software/oracle/admin/igt/pfile>% strings spfileigt.ora
igt.__db_cache_size=3489660928
igt.__java_pool_size=67108864
igt.__large_pool_size=67108864
igt.__oracle_base='/software/oracle'#ORACLE_BASE set from environment
igt.__pga_aggregate_target=2952790016
igt.__sga_target=5637144576
igt.__shared_io_pool_size=0
igt.__shared_pool_size=2013265920
igt.__streams_pool_size=67108864
*.archive_lag_target=1800
*.audit_file_dest='/software/oracle/admin/igt/adump'
*.audit_trail='none'
*.compatible='11.1.0.0.0'
*.control_files='/oracle_db/db1/db_igt/
ora_control_01.ctl','/oracle_db/db1/db_igt/ora_control_02.ctl','/oracle_db/db1/db_igt/ora_control_03.ctl'
*.db_block_size=8192
*.db_domain=''
*.db_name='igt'
*.diagnostic_dest='/software/oracle'
*.dispatchers='(PROTOCOL=TCP) (SERVICE=igtXDB)'
*.fast_start_mttr_target=180
*.filesystemio_options='asynch'
*.log_archive_dest_1='location=/oracle_db/db2/db_igt/arch'
*.log_archive_format='arch%T_%s_%r.arc'
*.memory_target=8589934592
*.nls_length_semantics='char'
*.open_cursors=300
*.open_li
nks=12
*.os_authent_prefix=''
*.processes=600
*.recyclebin='OFF'
*.remote_login_passwordfile='EXCLUSIVE'
*.SESSION_CACHED_CURSORS=100
*.undo_retention=3600

*.undo_tablespace='UNDOTBS'

Error - UNDO Segment cannot be dropped

SELECT a.name,b.status 

FROM   v$rollname a,v$rollstat b

WHERE  a.usn = b.usn

AND    a.name IN ( 

          SELECT segment_name

          FROM dba_segments 

          WHERE tablespace_name = 'UNDOTBS1'

         ); 

 

NAME                           STATUS

------------------------------ ------------------------------

_SYSSMU7_3211463042$           PENDING OFFLINE

_SYSSMU18_1767712819$          PENDING OFFLINE

_SYSSMU21_4188676727$          PENDING OFFLINE

SELECT name, xacts active_transactions

FROM  v$rollname, v$rollstat 

WHERE status = 'PENDING OFFLINE' 

  AND v$rollname.usn = v$rollstat.usn; 

NAME                           ACTIVE_TRANSACTIONS

------------------------------ -------------------

_SYSSMU7_3211463042$                             0

_SYSSMU18_1767712819$                            0

_SYSSMU21_4188676727$                            0

In case there were active transactions - this SL would have shown the active sid+serial# to be killed.

SELECT a.name, b.status, d.username, d.sid, d.serial#

FROM   v$rollname a,v$rollstat b, v$transaction c , v$session d

WHERE  a.usn = b.usn

AND    a.usn = c.xidusn

AND    c.ses_addr = d.saddr

AND    a.name IN ( 

          SELECT segment_name

          FROM dba_segments 

          WHERE tablespace_name = 'UNDOTBS1'

         ); 

NAME                  STATUS          USERNAME       SID    SERIAL#

--------------------- --------------- ----------- ------ ----------

_SYSSMU46_132405334$  PENDING OFFLINE LAB_USER      3988      18531

ALTER SYSTEM KILL SESSION '3988,18531' IMMEDIATE;

>System altered.

DROP TABLESPACE UNDOTBS INCLUDING CONTENTS AND DATAFILES;

>Tablespace dropped.

See current UNDO usage

SELECT * FROM V$UNDOSTAT;

SELECT * FROM V$TRANSACTION;

SELECT * FROM DBA_UNDO_EXTENTS;

SELECT * FROM DBA_HIST_UNDOSTAT;

See current UNDO info

SELECT tablespace_name, contents 

FROM DBA_TABLESPACES 

WHERE contents = 'UNDO';

SELECT tablespace_name, SUBSTR(file_name,1,60) 

  FROM DBA_DATA_FILES 

WHERE tablespace_name LIKE 'UNDO%' order by 1,2;

SELECT

  DDF.tablespace_name,

  SUM(DDF.bytes)/(1024*1024) total_space_MB,

  round(FREE_SPACE.free,2) free_space_mb,

  round(FREE_SPACE.free/(sum(DDF.bytes)/(1024*1024))* 100,2) pct_free

 FROM DBA_DATA_FILES DDF,

  (select tablespace_name,SUM(bytes)/(1024*1024) free  

     FROM DBA_FREE_SPACE GROUP BY tablespace_name) FREE_SPACE

 WHERE DDF.tablespace_name = FREE_SPACE.tablespace_name(+)

   AND DDF.tablespace_name LIKE 'UNDO%'

GROUP BY DDF.tablespace_name,FREE_SPACE.free;

SELECT tablespace_name, status, COUNT(segment_name), SUM(bytes/1024/1024)

FROM DBA_UNDO_EXTENTS 

GROUP BY tablespace_name, status;

Space usage, in Database, In Linux

=========================
General
=========================
Useful way to find space usage + add more space in Database and in Linux

=========================
Database
=========================

Tablespace

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;

Segments
SET LINESIZE 120
SET PAGESIZE 200
COL tablespace_name FOR A30
COL owner FOR A30
COL segment_name FOR A30
COL USED_MB FOR 9999999999999


SELECT owner, tablespace_name, segment_name, USED_MB 
   FROM ( 
  SELECT owner, tablespace_name, segment_name, ROUND(SUM(bytes)/1024/1024)AS USED_MB FROM DBA_SEGMENTS
   WHERE tablespace_name LIKE '%TABLE%' 
  GROUP BY owner, tablespace_name,segment_name 
  ORDER BY ROUND(SUM(bytes)/1024/1024) DESC 
) WHERE ROWNUM < 11;


SELECT owner, tablespace_name, segment_name, USED_MB 
   FROM ( 
  SELECT owner, tablespace_name, segment_name, ROUND(SUM(bytes)/1024/1024)AS USED_MB FROM DBA_SEGMENTS
   WHERE tablespace_name LIKE '%INDEX%' 
  GROUP BY owner, tablespace_name,segment_name 
  ORDER BY ROUND(SUM(bytes)/1024/1024) DESC 
) WHERE ROWNUM < 11;


Datafiles
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;  

Add space

ALTER TABLESPACE IGT_TABLE ADD DATAFILE '/oracle_db/db1/db_igt/ora_igt_table_02.dbf' SIZE 1000M AUTOEXTEND ON MAXSIZE 10000M;

--Increase MAXSIZE

ALTER DATABASE DATAFILE '/oracle_db/db1/db_igt/ora_igt_index_01.dbf' AUTOEXTEND ON MAXSIZE 20000M;

--Resize

ALTER DATABASE DATAFILE '/oracle_db/db1/db_igt/ora_igt_index_01.dbf' RESIZE 20000M;

=========================
Linux
=========================

Get top files

find /software/oracle -type f -printf '%s %p\n'|sort -nr | head -10

Get top folders

du  -sh * |sort -nr | head -10

Add space

df -hP | grep oracle 

/dev/mapper/Volume00-LogVol07   7.8G  7.2G  194M  98% /software/oracle

cat /etc/fstab | grep LogVol07

/dev/Volume00/LogVol07  /software/oracle        ext3    defaults,acl        1 2

root@my_server:~>% vgs

  VG       #PV #LV #SN Attr   VSize   VFree 

  Volume00   1  17   0 wz--n- 135.75G 13.19G

  Volume01   1   1   0 wz--n- 136.70G 33.70G

lvextend -L +4G /dev/Volume00/LogVol07 && resize2fs /dev/Volume00/LogVol07

  Extending logical volume LogVol07 to 12.00 GB

  Logical volume LogVol07 successfully resized

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