Re: Table belongs to Original tablespace after export with DataPump

From: Amir Gheibi <gheibia_at_gmail.com>
Date: Thu, 5 Feb 2009 14:00:06 +0800
Message-ID: <c906cd460902042200m4d38dec5w234dd4e5181b8d6a_at_mail.gmail.com>



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_at_gmail.com> 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_at_yahoo.com> 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
>>
>>
>>
>>
>

--
http://www.freelists.org/webpage/oracle-l
Received on Thu Feb 05 2009 - 00:00:06 CST

Original text of this message