Re: Table belongs to Original tablespace after export with DataPump
- From: Amir Gheibi <gheibia@xxxxxxxxx>
- To: yong321@xxxxxxxxx
- Date: Thu, 5 Feb 2009 14:00:06 +0800
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:
