Comments as ever...
HJR
--
Resources for OracleT: www.geocities.com/howardjr2000
=========================================
"Rick Denoire" <100.17706_at_germanynet.de> wrote in message
news:5044tt88h9eqjok4d5pmpsv2gfrj8s7kj0_at_4ax.com...
> "Howard J. Rogers" <howardjr_at_www.com> wrote:
>
> >> I have read that if the paths differ, the tablespaces will have to
> >> exist prior to importing the dump. Let me know if you think this is
> >> true, because it would mean that there is no sure way to import a dump
> >> if some additional information about the exporting DB is missing.
> >>
> >
> >Nope. It creates objects in the same tablespace where possible, or the
> >User's default tablespace if not.
>
> You are right, but we don't seem to agree about what we call a
> "successfull" import. If the objects go to a different tablespace from
> that where they came from, we have no duplicate DB, and we don't
> consider the import to be successfull.
>
Fine. Just make sure that all the required tablespaces are created with all
the right names, manually, beforehand. You're right that import won't
create them for you if any are missing, because the paths to the files are
hard-coded, so the 'create tablespace' commands will probably fail. That
requires just a little bit of knowledge about the source database: a list of
the exact tablespace names that exist.
> >> Well, how could I import a dump as a different user from SYS or SYSTEM
> >> if all other users have to be dropped before doing the import?
>
> >You've kerflummoxed me now with the mother of all non sequiteurs!
> >Why on earth are you dropping Users from the target at all?
>
> An English dictionary wouldn't help, but I got the point anyway :-)
> I thought that dropping all users would be an effective way to clean
> the DB while the tablespaces themselves remain.
>
OK, I see what you're after -but truncate is there for that reason.
> >That's what the ignore=y parameter is for. Truncate them if need be, but
> >there's no reason to drop them or get rid of their users.
>
> Good point. How can I truncate all objects of a DB without having to
> investigate its structure in detail and without writing complex
> scripts? Which objects should be excluded from being truncated? You
> see, I am learning a lot!
>
Well, a blunderbuss technique would be:
spool masstrunc.txt
select 'truncate table ' || table_name || ';' || from dba_tables;
spool off
@masstrunc.txt
> By the way: If I don't drop the users, and the imported DB does not
> contain some of the users left in the target DB, the resulting DB
> won't be a duplicate. We are going to keep some users from the old
> version of the target DB. I consider that even a security issue.
>
Why? alter user fred account lock; seems reasonable to me.
> I can't still see a straight, reliable way to import a DB in Oracle
> and get a copy. Ingres was far easier.
>
Well, I'm just going to have to say that you're using the wrong tool with
too little information. If you wan't an *exact* copy of a database (I mean
a logical duplicate, but where file paths etc are allowed to change) then
you don't use import blind of the source like this, you use cloning
techniques.
If you're using import, it really helps to know something about how the
export was done, what the source database looks like and so on.
Import/Export are really backup tools, not cloning ones. They can be made
to do the deed, but not if the nature of the source is kept hidden from you,
and not if you don't know precisely what's in the dump file.
> >> The command was:
> >> imp sys/syspw file=file.dmp ignore=y log=file.log full=y
>
> >Well, that seems fine. You're certainly doing a full import, and you've
got
> >ignore on (so no, there is definitely no need to drop previous objects:
if
> >they already exist, import will simply move on to the 'insert into...'
> >statement).
>
> All right, import will move on to the 'insert into..' statement, and
> it will find records which it sees as duplicates and would eventually
> violate constraints, so the insert will fail, me being not so sure if
> the data in the records are really identical. Or the same data will be
> inserted again - not a solution either.
>
Whatever happened to the idea of truncating the table? How many duplicates
and violating records is it going to find after you've removed every record
from a table by doing a truncate??
> I don't know if I should emphasize again: I need an exact copy of the
> exporting DB!
>
Then don't use export/import, use cloning techniques.
Regards
HJR
> Thanks a lot for this instructing discussion.
>
> Rick
>
Received on Sun Oct 21 2001 - 15:17:55 CDT
