• Status: Solved
  • Priority: Medium
  • Security: Public
  • Views: 4273
  • Last Modified:

ORA-31655 on expdp tablespace attempt

Trying to export a tablespace using expdp but getting message:

oracle@bb6:/u01/app/oracle/product/10.2.0.1/db_1/rdbms/log$ expdp system/manager DIRECTORY=data_pump_dir TABLESPACES=BB_BB60_INDX DUMPFILE=tabsp.dmp
Export: Release 10.2.0.3.0 - 64bit Production on Wednesday, 05 November, 2008 14:48:47
Copyright (c) 2003, 2005, Oracle.  All rights reserved.
Connected to: Oracle Database 10g Enterprise Edition Release 10.2.0.3.0 - 64bit Production
With the Partitioning, OLAP and Data Mining options
Starting "SYSTEM"."SYS_EXPORT_TABLESPACE_01":  system/******** directory=data_pump_dir tablespaces=BB_BB60_INDX dumpfile=tabsp.dmp
Estimate in progress using BLOCKS method...
Processing object type TABLE_EXPORT/TABLE/TABLE_DATA
Total estimation using BLOCKS method: 0 KB
ORA-31655: no data or metadata objects selected for job
Job "SYSTEM"."SYS_EXPORT_TABLESPACE_01" completed with 1 error(s) at 14:48:56

What am i doing wrong?  The tablespace has about 20 GB of data.
0
xoxomos
Asked:
xoxomos
  • 6
  • 3
  • 2
1 Solution
 
Devinder Singh VirdiCommented:
Have you created the directory data_pump_dir  using "create directory" oracle command
0
 
DavidSenior Oracle Database AdministratorCommented:
Also, double-check your tablespace name for any typos.
0
 
Devinder Singh VirdiCommented:
I agree with dvz, check tablespace name
0
What is SQL Server and how does it work?

The purpose of this paper is to provide you background on SQL Server. It’s your self-study guide for learning fundamentals. It includes both the history of SQL and its technical basics. Concepts and definitions will form the solid foundation of your future DBA expertise.

 
xoxomosAuthor Commented:

SQL> select trim(directory_name),trim(directory_path) from dba_directories;

TRIM(DIRECTORY_NAME)
------------------------------
TRIM(DIRECTORY_PATH)
--------------------------------------------------------------------------------
DATA_PUMP_DIR
/u01/app/oracle/product/10.2.0.1/db_1/rdbms/log/
0
 
xoxomosAuthor Commented:
SQL> select tablespace_name from dba_tablespaces;

TABLESPACE_NAME
------------------------------
SYSTEM
TOOLS
TEMP
USERS
INDX
BBADMIN_DATA
BBADMIN_INDX
BB_BB60_STATS_DATA
BB_BB60_STATS_INDX
UNDOTBS_02
BB_BB60_INDX

DIRECTORY=data_pump_dir TABLESPACES=BB_BB60_INDX DUMPFILE=tabsp.dmp
0
 
Devinder Singh VirdiCommented:
Along with the data pump utilities Oracle provide an PL/SQL API. The following is an example of how this API can be used to perform a schema export
SET SERVEROUTPUT ON SIZE 1000000
DECLARE
  l_dp_handle       NUMBER;
  l_last_job_state  VARCHAR2(30) := 'UNDEFINED';
  l_job_state       VARCHAR2(30) := 'UNDEFINED';
  l_sts             KU$_STATUS;
BEGIN
  l_dp_handle := DBMS_DATAPUMP.open(
    operation   => 'EXPORT',
    job_mode    => 'SCHEMA',
    remote_link => NULL,
    job_name    => 'EMP_EXPORT',
    version     => 'LATEST');

  DBMS_DATAPUMP.add_file(
    handle    => l_dp_handle,
    filename  => 'SCOTT.dmp',
    directory => 'TEST_DIR');

  DBMS_DATAPUMP.add_file(
    handle    => l_dp_handle,
    filename  => 'SCOTT.log',
    directory => 'TEST_DIR',
    filetype  => DBMS_DATAPUMP.KU$_FILE_TYPE_LOG_FILE);

  DBMS_DATAPUMP.metadata_filter(
    handle => l_dp_handle,
    name   => 'SCHEMA_EXPR',
    value  => '= ''SCOTT''');

  DBMS_DATAPUMP.start_job(l_dp_handle);

  DBMS_DATAPUMP.detach(l_dp_handle);
END;
/

You can check the status of job using
select * from dba_datapump_jobs;
0
 
xoxomosAuthor Commented:
Thanks.  Actually I'm trying to do a tablespace export rather than a schema export although a schema export would probably work.
0
 
DavidSenior Oracle Database AdministratorCommented:
You have a bit around "estimate in progress using BLOCKS", which I presume is normal.  But since we're all questioning presumptions, what do you have in that tablespace in the way of objects and datafiles?
0
 
xoxomosAuthor Commented:
A single datafile
       name                                     directory                                  size             used  
bb_bb60_indx_file1.dbf       /u02/app/oracle/oradata/BB60/     22303.69      9458.38
0
 
xoxomosAuthor Commented:

Tablespace  BB_BB60_INDX

Status  Online

File Size (KB)  22838976
AutoExtend  Yes

Increment  5120KB

Maximum File Size  32767MB

 
 
0
 
xoxomosAuthor Commented:
From Oracle support
tablespace BB_BB60_INDX is
having only a "Index segments" and the "Base tables" belongs to these indexes
are not exsisting in the this tablespace.So this error is expected.
++ In tablespace mode (level) export oracle can not export only the index segments
without the base tables and base tables are existing in an another tablespace.
But oracle can export table segments and its belonging indexes are existing in another tablespace & exported dumpfile includes the index structure too
0

Featured Post

VIDEO: THE CONCERTO CLOUD FOR HEALTHCARE

Modern healthcare requires a modern cloud. View this brief video to understand how the Concerto Cloud for Healthcare can help your organization.

  • 6
  • 3
  • 2
Tackle projects and never again get stuck behind a technical roadblock.
Join Now