Re: Import/Export Question.

Hi,

An alternative is to create the offending tables in the desired tablespace prior to import. Then import with ignore=y.

HTH Peter Whyte

Alan <alan_psb_at_yahoo.com> wrote in message news:38E073C3.831C5B8D_at_yahoo.com...
> 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 Mon Mar 27 2000 - 00:00:00 CST