Forum Stats

  • 3,874,182 Users
  • 2,266,678 Discussions
  • 7,911,760 Comments

Discussions

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

User_MVPZ4
User_MVPZ4 Member Posts: 11 Employee
edited Nov 9, 2022 9:09AM in General Database Discussions

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

Best Answer

  • Jonathan Lewis
    Jonathan Lewis Member Posts: 10,116 Blue Diamond
    Answer ✓

    General rule: Oracle stores object names in upper case unless you've created them with quoted names.

    enc_test is stored in the database as ENC_TEST

    you've asked for an export of 'enc_test', which doesn't exist.

    Change

    value => '= ''enc_test''');
    

    to

    value => '= ''ENC_TEST''');
    


    By the way - you shouldn't be testing / experimenting in the SYS schema, it's not safe, and it doesn't always behave in exactly the same way as ordinary schemas

    Regards

    Jonathan Lewis

Answers

  • Jonathan Lewis
    Jonathan Lewis Member Posts: 10,116 Blue Diamond
    Answer ✓

    General rule: Oracle stores object names in upper case unless you've created them with quoted names.

    enc_test is stored in the database as ENC_TEST

    you've asked for an export of 'enc_test', which doesn't exist.

    Change

    value => '= ''enc_test''');
    

    to

    value => '= ''ENC_TEST''');
    


    By the way - you shouldn't be testing / experimenting in the SYS schema, it's not safe, and it doesn't always behave in exactly the same way as ordinary schemas

    Regards

    Jonathan Lewis

  • User_MVPZ4
    User_MVPZ4 Member Posts: 11 Employee

    Thank you Jonathan for your answer.

    Yes, the issue was with lower case and SYS schema. I've changed to upper case and created the table in other schema, it worked.

    Regards

    T

  • Jonathan Lewis
    Jonathan Lewis Member Posts: 10,116 Blue Diamond


    Thanks for the feedback.

    Regards

    Jonathan Lewis