Code Example: batch, sqlplus

=========================
General
=========================
Code example
Overall - read data from Database
Generate files using oracle UTL_FILE
FTP files from oracle server to customer serer
Update records in Database

The main is main_send_files.bat

=========================
Code
=========================

=======================================

main_send_files.bat
=======================================

REM set Paths
SET HOME_DIR=D:\CUST_APU_Reports\Files
SET OLD_DIR=D:\CUST_APU_Reports\OldFiles

REM Delete old temp file
IF EXIST DELnewCMD.ini DEL DELnewCMD.ini
IF EXIST del_files.txt DEL del_files.txt
IF EXIST sftp_output.log DEL sftp_output.log 

REM get date
for /f "tokens=2,3,4 delims=/ " %%i in ('date/t') do (set mm=%%i) & (set dd=%%j) & (set yyyy=%%k)
set RUN_DATE=%yyyy%%mm%%dd%

REM Override default Time
REM SET RUN_DATE='20170714'

echo %RUN_DATE% >> cust_log.txt

for /f "tokens=1,2 delims=: " %%i in ('time /t') do (set hh=%%i) & (set mm=%%j)
REM get time
set RUN_TIME=%hh%:%mm%
echo %RUN_TIME% >> cust_log.txt

echo #### >> cust_log.txt
echo Activate database package >> cust_log.txt
echo #### >> cust_log.txt
sqlplus user/pass@oraint @activate_report_new.sql %RUN_DATE%
IF NOT ERRORLEVEL 0 goto :ERROR_DB

:BRING_THE_FILES
echo before FTP >> cust_log.txt
CALL SFTPCMD2 > sftp_output.log 
type sftp_output.log >> cust_log.txt
echo Finished FTP successfully >> cust_log.txt

dir /B D:\cust_APU_Reports\Files\output*.csv /O:d >> del_files.txt
echo cd /software/oracle/admin/igt/utl_file >> DELNEWCMD.ini
for /F %%i in (del_files.txt) do (@echo rm %%i) >> delnewcmd.ini
echo bye >> DELnewCMD.ini
CALL SFTPCMD3 > sftp_output.log 
type sftp_output.log >> cust_log.txt
if NOT ERRORLEVEL 0 GOTO :ERROR_DELETING

echo send the files by sftp to the customer (10.92.40.20)>> cust_log.txt
CALL SFTPCMD SFTPCMD.ini > sftp_output.log 
type sftp_output.log >> cust_log.txt

REM echo send the files by sftp to the customer >> cust_log.txt
REM CALL SFTPCMD4 SFTPCMD4.ini > sftp_output.log 
REM type sftp_output.log >> cust_log.txt

IF NOT ERRORLEVEL 0 GOTO :ERROR_SFTP
echo Rename the sent CSV files to old directory >> cust_log.txt
for /F  %%i in (del_files.txt) do (move %HOME_DIR%\%%i %OLD_DIR%\%%i)
IF NOT ERRORLEVEL 0 GOTO :ERROR_RENAME
echo process finish succefully >> cust_log.txt
goto FINISH
:ERROR_DB
 echo Error in the DAILY_REPORTS package. >> cust_log.txt
REM REM send mail
:ERROR_DELETING
 echo Errors deleting from the cgw! >> cust_log.txt
REM REM send mail
 goto FINISH
:ERROR_SFTP
 echo Errors SFTP the files to CUST >> cust_log.txt
REM REM send mail
 goto FINISH
:ERROR_RENAME
 echo Error moving the files to the oldDir directory >> cust_log.txt
REM send mail
 goto FINISH
:FINISH

=======================================

SFTPCMD.bat

=======================================

sftp -b SFTPCMD.ini user@10.10.10.10

SFTPCMD.ini

lcd D:\CUST_APU_Reports\Files

cd ../VHE

mput CUST_APU*.csv

bye

=======================================

SFTPCMD2.bat

=======================================

sftp -b DWHNEWCMD.ini user@20.20.20.20

dwhnewcmd.ini

lcd D:\CUST_APU_Reports\Files

cd /software/oracle/admin/igt/utl_file

mget output*.csv

bye

=======================================

SFTPCMD3.bat

=======================================

sftp -b DELnewCMD.ini user@30.30.30.30

DELNEWCMD.ini

cd /software/oracle/admin/igt/utl_file 

rm CUST_APU_BHCA_20170716.csv

rm CUST_APU_BHSM_20170716.csv

bye  

=======================================

activate_report_new.sql

=======================================

exec DAILY_REPORTS.CUST_MAIN(&1);

exit;

UTL_MAIL error: utl_mail must be declared

=================================
General
=================================
Below error is coming when executing procedure inside UTL_MAIL package:
UTL_MAIL error: utl_mail must be declared

Most likely, the permissions are missing

=================================
UTL_MAIL
=================================

SELECT owner, object_name, object_type 

FROM DBA_OBJECTS 

WHERE object_name = 'UTL_MAIL';

OWNER      OBJECT_NAME                    OBJECT_TYPE
---------- ------------------------------ --------------------
SYS        UTL_MAIL                       PACKAGE BODY
SYS        UTL_MAIL                       PACKAGE
PUBLIC     UTL_MAIL                       SYNONYM

connect as sysdba, and grant missing permission to users:

 sqlplus / as sysdba

SQL> GRANT EXECUTE ON UTL_MAIL TO NEW_USER;

Grant succeeded.

=================================
UTL_MAIL permissions 
=================================

Current status:

SELECT * FROM DBA_TAB_PRIVS WHERE owner = 'SYS' AND table_name LIKE '%MAIL%'

GRANTEE             OWNER      TABLE_NAME        GRANTOR    PRIVILEGE  GRANTABLE HIERARCHY

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

PUBLIC              SYS        UTL_MAIL_INTERNAL SYS        EXECUTE    NO        NO

VIPCREATOR          SYS        UTL_MAIL          SYS        EXECUTE    YES       NO

GBR_VIRGI_XXXXX     SYS        UTL_MAIL          VIPCREATOR EXECUTE    NO        NO

NGA_MTNNG_XXXXX     SYS        UTL_MAIL          VIPCREATOR EXECUTE    NO        NO

RWA_TIGOR_XXXXX     SYS        UTL_MAIL          VIPCREATOR EXECUTE    NO        NO

BGR_MOBIL_XXXXX     SYS        UTL_MAIL          VIPCREATOR EXECUTE    NO        NO

GNB_MTNGN_XXXXX     SYS        UTL_MAIL          VIPCREATOR EXECUTE    NO        NO

GHA_TIGOG_XXXXX     SYS        UTL_MAIL          VIPCREATOR EXECUTE    NO        NO

BFA_TELEC_XXXXX     SYS        UTL_MAIL          VIPCREATOR EXECUTE    NO        NO

BWA_BTCQQ_XXXXX     SYS        UTL_MAIL          VIPCREATOR EXECUTE    NO        NO

COG_MTNCO_XXXXX     SYS        UTL_MAIL          VIPCREATOR EXECUTE    NO        NO

COD_TIGOC_XXXXX     SYS        UTL_MAIL          VIPCREATOR EXECUTE    NO        NO

GNQ_HITSE_XXXXX     SYS        UTL_MAIL          VIPCREATOR EXECUTE    NO        NO

COG_AIRTE_XXXXX     SYS        UTL_MAIL          VIPCREATOR EXECUTE    NO        NO

LBR_LONES_XXXXX     SYS        UTL_MAIL          VIPCREATOR EXECUTE    NO        NO

MWI_AIRTE_XXXXX     SYS        UTL_MAIL          VIPCREATOR EXECUTE    NO        NO

KOR_LGTEL_XXXXX     SYS        UTL_MAIL          VIPCREATOR EXECUTE    NO        NO

BEN_MOOVB_XXXXX     SYS        UTL_MAIL          VIPCREATOR EXECUTE    NO        NO

BEN_MTNBE_XXXXX     SYS        UTL_MAIL          VIPCREATOR EXECUTE    NO        NO

COL_ETBCO_XXXXX     SYS        UTL_MAIL          VIPCREATOR EXECUTE    NO        NO

Generate SQL command to grant this permission to all user who do not have it

SELECT 'GRANT EXECUTE ON UTL_MAIL TO '|| username||';' FROM (

SELECT username as username FROM all_users

MINUS

SELECT grantee as username FROM DBA_TAB_PRIVS 

 WHERE owner = 'SYS' AND table_name LIKE '%MAIL%'

)

GRANT EXECUTE ON UTL_MAIL TO AUT_THREE_XXXXX;

GRANT EXECUTE ON UTL_MAIL TO AUT_THREE_XXXXX;

GRANT EXECUTE ON UTL_MAIL TO AUT_THREE_XXXXX;

GRANT EXECUTE ON UTL_MAIL TO AUT_THREE_XXXXX;

GRANT EXECUTE ON UTL_MAIL TO BDI_ECONE_XXXXX;

GRANT EXECUTE ON UTL_MAIL TO BDI_ECONE_XXXXX;

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