Forum Stats

  • 3,734,243 Users
  • 2,246,920 Discussions
  • 7,857,198 Comments

Discussions

Can I ipmdp another user's schema into his/her own schema when I'm another user?

Hi,

I received a pump file from a colleague which he exported using expdp.

This file contains data for a PDB's user ("A").

I want to import this user's data into this user's schema on my database (ie. "A:A"), but I don't want to give this user all the rights required to do so.

In my pluggable database I'm having a user ("B") with DBA privileges in the PDB. I want this user to perform the import. But it fails:

Connected to: Oracle Database 18c Express Edition Release 18.0.0.0.0 - Production
ORA-31626: job does not exist
ORA-31633: unable to create master table "B.SYS_IMPORT_SCHEMA_05"
ORA-06512: at "SYS.DBMS_SYS_ERROR", line 95
ORA-06512: at "SYS.KUPV$FT", line 1142
ORA-01950: no privileges on tablespace 'SYSTEM'
ORA-06512: at "SYS.KUPV$FT", line 1035
ORA-06512: at "SYS.KUPV$FT", line 1023


What do I need to do to be able to import data from "A" into "A"'s scheme on my database while being logged in as user "B"?

Your help is very much appreciated.

Best Answers

Answers

  • Solomon Yakobson
    Solomon Yakobson Member Posts: 18,395 Black Diamond
    ORA-01950: no privileges on tablespace 'SYSTEM'
    

    Doesn't ring a bell? Also, creating non-oracle maintained users with SYSTEM tablespace as default tablespace is bad idea.

    SY.

  • AxD
    AxD Member Posts: 27 Green Ribbon
    edited Jun 1, 2021 11:40PM

    Pardon me, but I don't have any clue what this error is about, I'm afraid.

    I read that blog post before, but it was no help to me.

    I'm (basically) running this bash script:

    /opt/oracle/product/18c/dbhomeXE/bin/impdp B/[email protected]_NAME parfile=./import.par
    

    ... using this parameter file:

    directory=importdirectory
    dumpfile=A.pump
    schemas=A
    

    ... after preparing the database like this:

    CONNECT sys/[email protected]//localhost:1521/DB_NAME AS SYSDBA
    
    EXECUTE IMMEDIATE 'CREATE OR REPLACE DIRECTORY ImportDirectory AS ''/db-install/pump''';
    EXECUTE IMMEDIATE 'GRANT READ, WRITE ON DIRECTORY ImportDirectory TO B';
    EXECUTE IMMEDIATE 'GRANT "DATAPUMP_IMP_FULL_DATABASE" TO B';
    


  • Solomon Yakobson
    Solomon Yakobson Member Posts: 18,395 Black Diamond
    Accepted Answer

    When you run datapump Oracle creates auxiliary table in schema datapump connects to the database - in your case user B. Therefore user B needs CREATE TABLE privilege. So

    EXECUTE IMMEDIATE 'GRANT "DATAPUMP_IMP_FULL_DATABASE" TO B';
    

    to

    EXECUTE IMMEDIATE 'GRANT DATAPUMP_IMP_FULL_DATABASE,CREATE TABLE TO B';
    

    SY.

    AxD
  • AxD
    AxD Member Posts: 27 Green Ribbon

    Unfortunately, that didn't work out.

    I changed the corresponding line to EXECUTE IMMEDIATE 'GRANT "DATAPUMP_IMP_FULL_DATABASE", CREATE TABLE TO &DB_ADMIN';

    .. and, after running my scripts, received the same set of errors again:

    ORA-31626: job does not exist
    ORA-31633: unable to create master table "B.SYS_IMPORT_SCHEMA_05"
    ORA-06512: at "SYS.DBMS_SYS_ERROR", line 95
    ORA-06512: at "SYS.KUPV$FT", line 1142
    ORA-01950: no privileges on tablespace 'SYSTEM'
    ORA-06512: at "SYS.KUPV$FT", line 1035
    ORA-06512: at "SYS.KUPV$FT", line 1023
    


  • Brett Calhoun
    Brett Calhoun Member Posts: 42 Bronze Badge

    I agree with SY. It looks like your user "B" has SYSTEM as it's default tablespace, but has no quota on SYSTEM tablespace.

    ORA-01950: no privileges on tablespace "string"

    Cause: User does not have privileges to allocate an extent in the specified tablespace.

    Action: Grant the user the appropriate system privileges or grant the user space resource on the tablespace

    Can you post the results of this query? select username, default_tablespace from dba_users where username = 'B';

    Thanks,

    Brett

  • Solomon Yakobson
    Solomon Yakobson Member Posts: 18,395 Black Diamond
    edited Jun 2, 2021 12:43PM Accepted Answer

    You need to give user B quota on default tablespace:

    ALTER USER B QUOTA UNLIMITED ON SYSTEM;
    

    But again, you shouldn't create non-oracle users with default tablespace SYSTEM. I suggest changing it to, say, USERS:

    ALTER USER B DEFAULT TABLESPACE USERS QUOTA UNLIMITED ON USERS;
    

    SY.

    AxD
  • AxD
    AxD Member Posts: 27 Green Ribbon

    Thank you very much for your so utterly valuable input. I feel deep in your debt.

    So I changed the user's default tablespace:

    CREATE PLUGGABLE DATABASE &DB_NAME ADMIN USER &DB_ADMIN IDENTIFIED BY "&DB_PWD" ROLES=(dba) DEFAULT TABLESPACE USERS
    

    ... and granted this user quota in that tablespace:

    ALTER USER &DB_ADMIN QUOTA UNLIMITED ON USERS
    

    Now everything works like a charm!


    Just to explain the approach I took:

    My task is to take over an existing Oracle database from my retiring collegue and to move his local installation to a Docker container.

    The idea behind my script is to:

    1. create an appropriate entry for the pluggable database in TNSNAMES.ORA
    2. remove all pluggable databases
    3. enable managed files
    4. create my client pluggable database with default DBA user on tablespace USERS
    5. create all necessary user tablespaces
    6. create all users
    7. import all current database data from PUMP file
    8. create synonyms and grants
    9. lock (or drop) the previously created DBA user


    After importing the PUMP file I just added a "finish_import.sql" file where I'm revoking all the previously granted rights from my default DBA user ("B"). To do so, I'll gave it a quota of 0. Runs perfectly.

    My retiring colleague is of no great help, I'm afraid, since after all these years he appears to have only basic knowledge (like me at this time as I'm coming from MS SQL Server). Anyway, never mind.


    You've been such a great source of information, aid and help. Thank you so much, Solomon!!! ❤️

Sign In or Register to comment.