Issue With EXPDP Full Tablespace

expdporacle

I am trying to export schema with below command using expdp in Oracle 18c XE , the schema uses tablespace named "testtablespace"

expdp myschema/*******@//localhost:1521/XEPDB1 schemas=myschema directory=DATA_PUMP_DIR dumpfile=myschema.dmp logfile=myschema.log

the tablespace "testtablespace" has maximum size of 100MB and currently 70MB of it is utilized , upon running above command I get error

ORA-01654: unable to extend index TESTTABLESPACE.SYS_MTABLE_0000357BA_IND_2 by 8 in tablespace testtablespace

When I increase size of tablespace size the issue is fixed , it seems like during export the tablespace of schema is being used for some temporary data storage

Is there a way that I can specify a temporary tablespace to be used instead of my schema tablespace so that even if my schema tablespace is completely full it can still export schema using expdp without any issue?

Best Answer

Tracking Progress Within a Job

While the data and metadata are being transferred, a master table is used to track the progress within a job.

...

The master table is created in the schema of the current user performing the export or import operation. Therefore, that user must have the CREATE TABLE system privilege and a sufficient tablespace quota for creation of the master table.

So alter the user performing the export to have another default tablespace. That way the master table will be created in another tablespace.

Related Solutions

How to find the schema name of a data pump dmp file

Use the sqlfile= parameter of impdp to generate a file containing all of the DDL/DML in the dump.

For example:

[oracle@oel61 ~]$ impdp phil/phil directory=oracledmp dumpfile=phil.dmp logfile=phil.log sqlfile=philddl.txt

Import: Release 11.2.0.2.0 - Production on Wed Mar 13 15:15:03 2013

Copyright (c) 1982, 2009, Oracle and/or its affiliates.  All rights reserved.

Connected to: Oracle Database 11g Enterprise Edition Release 11.2.0.2.0 - 64bit Production
With the Partitioning, Automatic Storage Management, OLAP, Data Mining
and Real Application Testing options
Master table "PHIL"."SYS_SQL_FILE_FULL_01" successfully loaded/unloaded
Starting "PHIL"."SYS_SQL_FILE_FULL_01":  phil/******** directory=oracledmp dumpfile=phil.dmp logfile=phil.log sqlfile=philddl.txt 
Processing object type SCHEMA_EXPORT/USER
Processing object type SCHEMA_EXPORT/SYSTEM_GRANT
Processing object type SCHEMA_EXPORT/ROLE_GRANT
Processing object type SCHEMA_EXPORT/DEFAULT_ROLE
Processing object type SCHEMA_EXPORT/TABLESPACE_QUOTA
Processing object type SCHEMA_EXPORT/PRE_SCHEMA/PROCACT_SCHEMA
Processing object type SCHEMA_EXPORT/SEQUENCE/SEQUENCE
Processing object type SCHEMA_EXPORT/TABLE/TABLE
Processing object type SCHEMA_EXPORT/TABLE/STATISTICS/TABLE_STATISTICS
Job "PHIL"."SYS_SQL_FILE_FULL_01" successfully completed at 15:15:05

[oracle@oel61 ~]$

Looking for CREATE USER DDL statements in the file should show you what schemas are needed in order to perform an import.

For example, from my test dump:

-- new object type path: SCHEMA_EXPORT/USER
-- CONNECT SYSTEM
 CREATE USER "PHIL" IDENTIFIED BY VALUES 'S:924B2E756404611021428644B4DF06A4A7BAB886837FCCFA510151E0FC44;181446AE258EE2F6'
      DEFAULT TABLESPACE "PHILDATA"
      TEMPORARY TABLESPACE "TEMP";
-- new object type path: SCHEMA_EXPORT/SYSTEM_GRANT
GRANT UNLIMITED TABLESPACE TO "PHIL";
GRANT CREATE SESSION TO "PHIL";
-- new object type path: SCHEMA_EXPORT/ROLE_GRANT
 GRANT "DBA" TO "PHIL";
-- new object type path: SCHEMA_EXPORT/DEFAULT_ROLE
 ALTER USER "PHIL" DEFAULT ROLE ALL;

The only drawback is that if the .dmp file is large the resulting SQL dump will be huge.

Expdp error UDE-22303

SQL> show parameter aq

NAME                                 TYPE        VALUE
------------------------------------ ----------- ------------------------------
aq_tm_processes                      integer     0

SQL> show parameter streams_pool_size

NAME                                 TYPE        VALUE
------------------------------------ ----------- ------------------------------
streams_pool_size                    big integer 0

SQL> show parameter db_cache_size

NAME                                 TYPE        VALUE
------------------------------------ ----------- ------------------------------
db_cache_size                        big integer 24G

Solution:

SQL> alter system set aq_tm_processes=1 scope=both sid='*';
SQL> alter system set streams_pool_size = 256M scope = both sid='*';

If you get any error like this:

ERROR at line 1:
ORA-02097: parameter cannot be modified because specified value is invalid
ORA-04033: Insufficient memory to grow pool

Reduce the db_cache_size:

SQL> alter system set db_cache_size = 12G scope = both sid='*';

and re-run:

SQL> alter system set streams_pool_size = 256M scope = both sid='*';

Now, you can run the expdp again without issues. Once done, don't forget to roll back all changes.

Best Answer

Related Solutions

How to find the schema name of a data pump dmp file

Expdp error UDE-22303

Related Question