Database Software

Announcement

For appeals, questions and feedback about Oracle Forums, please email oracle-forums-moderators_us@oracle.com. Technical questions should be asked in the appropriate category. Thank you!

DP Export - ORA-01652: unable to extend temp segment by 128 in tablespace TEMP

Karthick2003May 27 2015 — edited May 29 2015

Even though its related to Exp/Imp I prefer to post it here as I believe the chance of getting this answered is more in this forum

BANNER

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

Oracle Database 10g Enterprise Edition Release 10.2.0.5.0 - 64bi

PL/SQL Release 10.2.0.5.0 - Production

CORE 10.2.0.5.0 Production

TNS for Solaris: Version 10.2.0.5.0 - Production

NLSRTL Version 10.2.0.5.0 - Production

I have following procedure as part of a package. Its a procedure to export a table with filter condition.

  procedure export_table
  (
      pOwner        in    varchar2,
      pTableType    in    varchar2,
      pTableName    in    varchar2,
      pDirectory    in    varchar2,
      pFilterStr    in    varchar2,
      pDBLink       in    varchar2 default null
  )
  is
      lDPJobName   varchar2(100);
      lFileName    varchar2(100);
      lJobState    varchar2(50) := 'EXECUTING';
      lHandle      number;
  begin
      lDPJobName := 'TABLE_EXPORT_' || to_char(sysdate, 'YYYYMMDDHH24MISS');
      lFileName  := upper(pTableType) || '_' || upper(pTableName) || '_' || to_char(sysdate, 'YYYYMMDDHH24MISS');

      if pDBLink is null then
           lHandle := dbms_datapump.open('EXPORT', 'TABLE', job_name => lDPJobName);
      else
           lHandle := dbms_datapump.open('EXPORT', 'TABLE', remote_link => pDbLink, job_name => lDPJobName);
      end if;
      dbms_datapump.add_file (handle => lHandle, filename => lFileName, directory => pDirectory ||'_DMP', filetype => dbms_datapump.ku\$_file_type_dump_file);
      dbms_datapump.add_file (handle => lHandle, filename => lFileName, directory => pDirectory ||'_LOG', filetype => dbms_datapump.ku\$_file_type_log_file);
      
      dbms_datapump.set_parameter (handle => lHandle, name => 'KEEP_MASTER',    value => 0);
      dbms_datapump.set_parameter (handle => lHandle, name => 'ESTIMATE', value => 'STATISTICS');
      
      dbms_datapump.metadata_filter (handle => lHandle, name => 'SCHEMA_EXPR', value => '= ''' || upper(trim(pOwner)) || '''');
      dbms_datapump.metadata_filter (handle => lHandle, name => 'NAME_EXPR', value => '= ''' || upper(trim(pTableName)) || '''');
      dbms_datapump.metadata_filter (handle => lHandle, name => 'INCLUDE_PATH_EXPR', value => 'in (''TABLE_EXPORT/TABLE/TABLE'', ''TABLE_EXPORT/TABLE/TABLE_DATA'')');  
      if pFilterStr is not null then
          dbms_datapump.data_filter (handle => lHandle, name => 'SUBQUERY', value => pFilterStr, table_name => pTableName, schema_name => pOwner);
      end if;
      dbms_datapump.log_entry(handle => lHandle, message => 'START TIME = ' || to_char(sysdate, 'DD-MON-YYYY HH24:MI:SS'));
      dbms_datapump.start_job (handle => lHandle);
      dbms_datapump.wait_for_job (handle => lHandle, job_state => lJobState);

      lHandle := null;
  end;

I have passed filter condition like this (column name and table name masked)

where (col1, col2) in (select  col1, col2 from <table_name>)

One of the table gave this error while export. The error is related to TEMP table space being not sufficient.

So my question is why oracle is using TEMP table space for export (I mean in what situations it does that).

;;; START TIME = 26-MAY-2015 11:28:07

Starting "*******"."TABLE_EXPORT_20150526112804":

Estimate in progress using STATISTICS method...

Processing object type TABLE_EXPORT/TABLE/TABLE_DATA

. estimated "*******"."<TABLE_NAME_REMOVED>" 626.5 GB

Total estimation using STATISTICS method: 626.5 GB

Processing object type TABLE_EXPORT/TABLE/TABLE

ORA-39171: Job is experiencing a resumable wait.