Re: Import/Export Question.

Hello Sybrand,

    I have already revoked resource from the user SYS_DBA1 but the resulting tables are still in tablespace SYSTEM. SYS_DBA1's default tablespace is pointed another tablespace DATA1.

    However, when I imp show=y log=log_file, I found that the log_file contains:

"GRANT GRANT ANY ROLE to "SYS_DBA1" "
"GRANT DROP ANY ROLE to "SYS_DBA1" "
"GRANT UNLIMITED TABLESPACE to "SYS_DBA1" "
"GRANT "RESOURCE" TO "SYS_DBA1""

    I think during importing, these roles have been assigned to SYS_DBA1 despite the fact that I revoke these roles before improting.

    How can I disable these statements during importing? (I have already tried grants=n, show=y, however, I still found the above statements in the resulting log_file)

Thanks,
Alan

Sybrand Bakker wrote:

> The problem is not the dba role but the resource role, which implies
> unlimited tablespace privilige.
> Try to revoke unlimited tablespace privilege and make sure the default
> tablespace is not SYSTEM and it should work.
> The alternative is running imp show=y log=<any filename>
> this will dump all create statement to <any filename>.
>
> Hth,
>
> Sybrand Bakker, Oracle DBA
>
> Alan <alan_psb_at_yahoo.com> wrote in message
> news:38DE957F.CC6B7417_at_yahoo.com...
> > Hello all,
> >
> > I have a question related with Imp/Exp. I want to move all the
> > tables from one database A to B (by Entire Database). User SYS_DBA1 on
> > database A have DBA role and he exports entire database. However, on
> > database A, some tables (data tables, owned by SYS_DBA1) are on
> > tablespace SYSTEM.
> >
> > User SYS_DBA1 created in database B. Since I don't want to create
> > the tables in tablespace SYSTEM, I revoke DBA role from SYS_DBA1 and
> > issue "alter user SYS_DBA1 quota 0 on system" in database B. I import
> > the file by user SYSTEM. However, I found that those tables on
> > tablespace SYSTEM on database A are still imported into tablespace
> > SYSTEM on database B.
> >
> > Is there any way such that I can import the tables (in tablespace
> > SYSTEM, database A) to another tablespace (such as DATA1) in database B?
> > The exported file are exported by user SYS_DBA1 (via entire database)
> > and he has the DBA role. (I cannot create the export file again since
> > the database has been removed!)
> >
> >
> > Thanks,
> > Alan
> >
> >
> >
Received on Tue Mar 28 2000 - 10:56:36 CEST