ERROR "ora-01950: no privileges on tablespace" during Import Oracle Dump in AWS Oracle RDS

Question

I am trying to move one Oracle 12 database from one Oracle RDS to another using datapump full

Reason for this is to have the ability to move an Oracle RDS (pinned with an Option Group) to another AWS account.

nohup expdp myUser/myPass@myDB \
full=y job_name=DRPRD1_EXPORT_DAILY \
dumpfile=file.dmp \ 
directory=data_pump_dir \
exclude=statistics \
METRICS=Y LOGTIME=ALL

I move the dmp file to the new empty Oracle RDS(Same version)

My Import script :

    set serveroutput on;
set timing on;
DECLARE
  ind NUMBER;              -- Loop index
  h1 NUMBER;               -- Data Pump job handler
  percent_done NUMBER;     -- Percentage of job complete
  job_state VARCHAR2(30);  -- To keep track of job state
  le ku$_LogEntry;         -- For WIP and error messages
  js ku$_JobStatus;        -- The job status from get_status
  jd ku$_JobDesc;          -- The job description from get_status
  sts ku$_Status;          -- The status object returned by get_status
BEGIN
  h1 := DBMS_DATAPUMP.OPEN('IMPORT','FULL',NULL,'My_Import');
  DBMS_DATAPUMP.ADD_FILE(h1,'file.dmp','DATA_PUMP_DIR');
  DBMS_DATAPUMP.SET_PARAMETER(h1,'TABLE_EXISTS_ACTION','REPLACE');
  DBMS_DATAPUMP.START_JOB(h1);

 percent_done := 0;
  job_state := 'UNDEFINED';
  while (job_state != 'COMPLETED') and (job_state != 'STOPPED') loop
    dbms_datapump.get_status(h1,
           dbms_datapump.ku$_status_job_error +
           dbms_datapump.ku$_status_job_status +
           dbms_datapump.ku$_status_wip,-1,job_state,sts);
    js := sts.job_status;

-- If the percentage done changed, display the new value.

     if js.percent_done != percent_done
    then
      dbms_output.put_line('*** Job percent done = ' ||
                           to_char(js.percent_done));
      percent_done := js.percent_done;
    end if;

-- If any work-in-progress (WIP) or Error messages were received for the job,
-- display them.

       if (bitand(sts.mask,dbms_datapump.ku$_status_wip) != 0)
    then
      le := sts.wip;
    else
      if (bitand(sts.mask,dbms_datapump.ku$_status_job_error) != 0)
      then
        le := sts.error;
      else
        le := null;
      end if;
    end if;
    if le is not null
    then
      ind := le.FIRST;
      while ind is not null loop
        dbms_output.put_line(le(ind).LogText);
        ind := le.NEXT(ind);
      end loop;
    end if;
  end loop;

-- Indicate that the job finished and gracefully detach from it. 

  dbms_output.put_line('Job has completed');
  dbms_output.put_line('Final job state = ' || job_state);
  dbms_datapump.detach(h1);
END;
/

I am running the import with the master user that i created the RDS with so i assume i have the right privileges

None of the tables get created and I get below ERROR

ora-01950: no privileges on tablespace

Anybody encountered such an issue ?

Dmitry Demin Dmitry Demin · Accepted Answer · 2019-08-14T05:49:19

Do not import in full mode.

Because Amazon RDS for Oracle does not allow access to SYS or SYSDBA administrative users, importing in full mode, or importing schemas for Oracle-maintained components, might damage the Oracle data dictionary and affect the stability of your database. Importing Data into Oracle on Amazon RDS

Step 1: Grant Privileges to the User on the Amazon RDS Target Instance To grant privileges to the user on the RDS target instance, take the following steps:

Use SQL Plus or Oracle SQL Developer to connect to the Amazon RDS target Oracle DB instance into which the data will be imported. Connect as the Amazon RDS master user. For information about connecting to the DB instance, see Connecting to a DB Instance Running the Oracle Database Engine.

Create the required tablespaces before you import the data. For more information, see Creating and Sizing Tablespaces.

If the user account into which the data is imported doesn't exist, create the user account and grant the necessary permissions and roles. If you plan to import data into multiple user schemas, create each user account and grant the necessary privileges and roles to it.

For example, the following commands create a new user and grant the necessary permissions and roles to import the data into the user's schema.

create user schema_1 identified by <password>;
grant create session, resource to schema_1;
alter user schema_1 quota 100M on users;

ERROR "ora-01950: no privileges on tablespace" during Import Oracle Dump in AWS Oracle RDS

2 Answers