Skip to Main Content

Oracle Database Discussions

Export table from encrypted tablespace using dbms_datapump export returning ORA-31655

User_T-OracleNov 9 2022 — edited Nov 9 2022

Hello
I'm facing an issue with export a table form encrypted table space. I followed these steps to export.
 I’ve created an encrypted table space with AES256 

CREATE TABLESPACE encrypted_ts
DATAFILE '/u01/oracle/dbs/tbs_en.dbf' SIZE 100M
AUTOEXTEND ON NEXT 64K
ENCRYPTION USING 'AES256'
DEFAULT STORAGE(ENCRYPT);

I’ve created a table inside it.

CREATE TABLE enc_test (
 id  NUMBER,
 data VARCHAR2(100)
)
TABLESPACE encrypted_ts;

CREATE INDEX enc_test_idx ON enc_test(data) TABLESPACE encrypted_ts;
INSERT INTO enc_test (id, data) VALUES (1, 'Data'); 

Flushed the buffer cache 

ALTER SYSTEM FLUSH BUFFER_CACHE;

Now I’m exporting the table using data pump export. 

declare
 l_dp_handle    number;
begin
 l_dp_handle := dbms_datapump.open(
  operation  => 'EXPORT',
  job_mode  => 'TABLE',
  remote_link => NULL,
  job_name  => 'en_tab_export-01',
  version   => 'LATEST');

 dbms_datapump.add_file(
  handle  => l_dp_handle,
  filename => 'tab_dump.dmp',
  directory => 'DPUMP_DIR');

 dbms_datapump.add_file(
  handle  => l_dp_handle,
  filename => 'export.log',
  directory => 'DPUMP_DIR',
  filetype => DBMS_DATAPUMP.KU$_FILE_TYPE_LOG_FILE);

 dbms_datapump.metadata_filter(
  handle => l_dp_handle,
  name  => 'SCHEMA_EXPR',
  value => '= ''SYS''');

 dbms_datapump.metadata_filter(
  handle => l_dp_handle,
  name  => 'NAME_EXPR',
  value => '= ''enc_test''');
  
  dbms_datapump.set_parameter(handle => l_dp_handle, name => 'ENCRYPTION', value => 'ALL');
  dbms_datapump.set_parameter(handle => l_dp_handle, name => 'ENCRYPTION_MODE', value => 'TRANSPARENT');
  dbms_datapump.set_parameter(handle => l_dp_handle, name => 'ENCRYPTION_ALGORITHM', value => 'AES256');
dbms_datapump.start_job(l_dp_handle);

 dbms_datapump.detach(l_dp_handle);
end;
/

Table is not exporting and getting “ORA-31655: no data or metadata objects selected for job” in the log file. 
Can anybody help, why I’m getting this error ?

Thanks
T

This post has been answered by Jonathan Lewis on Nov 9 2022
Jump to Answer
Comments
Post Details
Added on Nov 9 2022
3 comments
72 views