Re: Table belongs to Original tablespace after export with DataPump

Beside the original script that I posted in my email, here is the only thing
I can share:

SQL> COLUMN TABLE_NAME FORMAT a10;
SQL> COLUMN TABLESPACE_NAME FORMAT a20;
SQL> COLUMN OWNER FORMAT a15;
SQL> SELECT OWNER, TABLE_NAME, TABLESPACE_NAME FROM DBA_TABLES WHERE
TABLE_NAME = 'PAYRATE';

OWNER           TABLE_NAME TABLESPACE_NAME
--------------- ---------- --------------------
USR_3_5_UAT     PAYRATE    TBS_3_5_STAGING
USR_3_5_STAGING PAYRATE    TBS_3_5_STAGING
OLDUSR          PAYRATE    OLD_TBS


SQL> COLUMN TABLESPACE_NAME FORMAT a15;
SQL> COLUMN USERNAME FORMAT a15;
SQL> COLUMN MAX_BYTES FORMAT 999999;
SQL> COLUMN MAX_BLOCKS FORMAT 999999;
SQL> SELECT TABLESPACE_NAME, USERNAME, MAX_BYTES, MAX_BLOCKS FROM
DBA_TS_QUOTAS;

TABLESPACE_NAME USERNAME        MAX_BYTES MAX_BLOCKS
--------------- --------------- --------- ----------
SYSAUX          OLAPSYS                -1         -1
SYSAUX          SYSMAN                 -1         -1
SYSAUX          DMSYS             #######      25600


SQL> COLUMN GRANTEE FORMAT a15;
SQL> COLUMN GRANTED_ROLE FORMAT a10;
SQL> COLUMN DEFAULT_ROLE FORMAT a3;
SQL> SELECT GRANTEE, GRANTED_ROLE, DEFAULT_ROLE FROM dba_role_privs WHERE
Grantee IN ('USR_3_5_UAT', 'USR_3_5_STAGING');

GRANTEE         GRANTED_RO DEF
--------------- ---------- ---
USR_3_5_UAT     CONNECT    YES
USR_3_5_STAGING RESOURCE   YES
USR_3_5_STAGING CONNECT    YES
USR_3_5_UAT     RESOURCE   YES

SQL> COLUMN PRIVILEGE FORMAT a30;
SQL> SELECT GRANTEE, PRIVILEGE FROM dba_sys_privs WHERE Grantee IN
('USR_3_5_UAT', 'USR_3_5_STAGING');

GRANTEE         PRIVILEGE
--------------- ------------------------------
USR_3_5_STAGING CREATE ANY DIRECTORY
USR_3_5_UAT     UNLIMITED TABLESPACE
USR_3_5_STAGING CREATE VIEW
USR_3_5_STAGING UNLIMITED TABLESPACE
USR_3_5_UAT     CREATE ANY DIRECTORY
USR_3_5_UAT     CREATE VIEW

6 rows selected.

SQL> SPOOL OFF


as it can be seen from the first query, the "PAYRATE" table owned by
"USR_3_5_UAT" resides in "TBS_3_5_STAGING" tablespace. The original copy of
the table owned by "USR_3_5_STAGING" user also resides in "TBS_3_5_STAGING"
tablespace.

I think the fact the both users have "UNLIMITED TABLESPACE" privilege and
there is no quota restriction defined for them has caused that. But I'm not
sure.


On Thu, Feb 5, 2009 at 1:54 PM, Amir Gheibi <gheibia@xxxxxxxxx> wrote:

> Beside the original script that I posted in my email, here is the only
> thing I can share:
>
> SQL> COLUMN TABLE_NAME FORMAT a10;
> SQL> COLUMN TABLESPACE_NAME FORMAT a20;
> SQL> COLUMN OWNER FORMAT a15;
> SQL> SELECT OWNER, TABLE_NAME, TABLESPACE_NAME FROM DBA_TABLES WHERE
> TABLE_NAME = 'PAYRATE';
>
> OWNER           TABLE_NAME TABLESPACE_NAME
> --------------- ---------- --------------------
> USR_3_5_UAT     PAYRATE    TBS_3_5_STAGING
> USR_3_5_STAGING PAYRATE    TBS_3_5_STAGING
> OLDUSR          PAYRATE    OLD_TBS
>
>
> SQL> COLUMN TABLESPACE_NAME FORMAT a15;
> SQL> COLUMN USERNAME FORMAT a15;
> SQL> COLUMN MAX_BYTES FORMAT 999999;
> SQL> COLUMN MAX_BLOCKS FORMAT 999999;
> SQL> SELECT TABLESPACE_NAME, USERNAME, MAX_BYTES, MAX_BLOCKS FROM
> DBA_TS_QUOTAS;
>
> TABLESPACE_NAME USERNAME        MAX_BYTES MAX_BLOCKS
> --------------- --------------- --------- ----------
> SYSAUX          OLAPSYS                -1         -1
> SYSAUX          SYSMAN                 -1         -1
> SYSAUX          DMSYS             #######      25600
>
>
> SQL> COLUMN GRANTEE FORMAT a15;
> SQL> COLUMN GRANTED_ROLE FORMAT a10;
> SQL> COLUMN DEFAULT_ROLE FORMAT a3;
> SQL> SELECT GRANTEE, GRANTED_ROLE, DEFAULT_ROLE FROM dba_role_privs WHERE
> Grantee IN ('USR_3_5_UAT', 'USR_3_5_STAGING');
>
> GRANTEE         GRANTED_RO DEF
> --------------- ---------- ---
> USR_3_5_UAT     CONNECT    YES
> USR_3_5_STAGING RESOURCE   YES
> USR_3_5_STAGING CONNECT    YES
> USR_3_5_UAT     RESOURCE   YES
>
> SQL> COLUMN PRIVILEGE FORMAT a30;
> SQL> SELECT GRANTEE, PRIVILEGE FROM dba_sys_privs WHERE Grantee IN
> ('USR_3_5_UAT', 'USR_3_5_STAGING');
>
> GRANTEE         PRIVILEGE
> --------------- ------------------------------
> USR_3_5_STAGING CREATE ANY DIRECTORY
> USR_3_5_UAT     UNLIMITED TABLESPACE
> USR_3_5_STAGING CREATE VIEW
> USR_3_5_STAGING UNLIMITED TABLESPACE
> USR_3_5_UAT     CREATE ANY DIRECTORY
> USR_3_5_UAT     CREATE VIEW
>
> 6 rows selected.
>
> SQL> SPOOL OFF
>
>
> as it can be seen from the first query, the "PAYRATE" table owned by "
> USR_3_5_UAT" resides in "TBS_3_5_STAGING" tablespace. The original copy of
> the table owned by "USR_3_5_STAGING" user also resides in "TBS_3_5_STAGING"
> tablespace.
>
> I think the fact the both users have "UNLIMITED TABLESPACE" privilege and
> there is no quota restriction defined for them has caused that. But I'm
> not sure.
>
>


>
> From the quotas I can see both users have no restrictions anywhere.
>
> On Thu, Feb 5, 2009 at 5:18 AM, Yong Huang <yong321@xxxxxxxxx> wrote:
>
>> Amir,
>>
>> I looked at your problem. I can't reproduce. I tested it on 10.2.0.4
>> (Linux). Both the user and tablespace are remapped successfully in my case.
>>
>> Can you show me a complete screen shot?
>>
>> Yong
>>
>> > Hi listers,
>> >
>> > I have a 10g DB on HP-UX. I used data pump to make a copy of one of the
>> > tablespaces. So I exported from one and imported into another one:
>> >
>> > $ expdp user1/pass1    schemas=user1    directory=dump_dir
>> > dumpfile=exp.dmp    logfile=expLog.log
>> >
>> > $ impdp user2/pass2    directory=dump_dir    dumpfile=exp.dmp
>> > logfile=impLog.log    REMAP_SCHEMA=user1:user2
>> > REMAP_TABLESPACE=tblspc1:tblspc2
>> >
>> > User1's default tablespace is tblspc1 and User2's default tablespace is
>> > tblspc2.
>> >
>> > What happens is that the "tablespace" property of the imported tables
>> owned
>> > by "User2" don't change as they are just pointing back to the original
>> > tables in "tblspc1". Are'nt the imported tables supposed to be copied to
>> the
>> > destination tablespace?
>> >
>> > I logged in as "User2" and ran:
>> >
>> > $ select table_name, tablespace_name from user_tables where table_name =
>> > 'MYTBL'
>> >
>> > TABLE_NAME   TABLESPACE_NAME
>> > ---------------------------------------------------------
>> > MYTBL              tblspc1
>> >
>> > Shouldn't the tablespace_name be "tblspc2"? I thought the data pump
>> creates
>> > the table in the destination tablespace and then import the data into
>> it.
>> >
>> > How should I prevent that problem at the import time?
>> >
>> > Regards,
>> > Amir Gheibi
>>
>>
>>
>>
>

Other related posts: