Re: PreCreate Oracle Tables

On Wed, 18 Feb 2004 13:34:09 -0700, "Daniel Fink" <Daniel.Fink_at_Sun.COM> said:
> Don't forget option 5) Use a backup from production and perform a full
> recovery. It not only creates the database, it tests your production
> backups and refreshes your recovery skills.
>

Well said.

• = =

Gene Sais

Depending on the situation you may decide which method to use. I sometimes rcv exports (objs) which lie in more than 5-6 tbs. If import is to be used (especially with LOBs) it is likely to expect the same tbs to exist. For pre-creating tables, indexes i have found DBMS_METADATA to be very useful (9i onwards). The advantage of this over indexfile is that you need not worry about the initial , next extents which will tag in the index file. Just create on default tbs, set uniform size nK and create the objs. It is convenient. You can do this if your requirement is only structure / objs and not necessarily full copy of data.

Take a look at the following link where in Tom Kyte has given useful inputs on this. I too was part of it in raising some doubts.

http://asktom.oracle.com/pls/ask/f?p=4950:8:8565428585502252564::NO::F49- 50_P8_DISPLAYID,F4950_P8_CRITERIA:1464804639878, HTH GovindanK

> If there are not any changes to the db, I like 4 as it is very quick
> and painless.
>
>
> Gene Sais wrote:
>
> > When copying a database, what are the best practices?
> >
> > 1) I typically, perform a full export from db A, create db B, import
> > dump from A into B.
> >
> > 2) Same as 1, but precreate tables with indexfile and then import
> > ignore=y.
> >
> > 3) Same as 1, but precreate tables with dynamic sql (create table emp
> > as select * from emp_at_dblink where 1=2), then import ignore=y.
> >
> > 4) Copy datafiles, recreate controlfile and rename db.
> >
> > I am looking to precreate the objects before importing the data and
> > in the past, I have used #2, but #3 is more flexible and requires no
> > editing of indexfile.
> >
> > Are they any disadvantages of using #3 from #2?
> >
> > Thanks, Gene
> >

-- 
http://www.fastmail.fm - Consolidate POP email and Hotmail in one place
----------------------------------------------------------------
Please see the official ORACLE-L FAQ: http://www.orafaq.com
----------------------------------------------------------------
To unsubscribe send email to:  oracle-l-request_at_freelists.org
put 'unsubscribe' in the subject line.
--
Archives are at http://www.freelists.org/archives/oracle-l/
FAQ is at http://www.freelists.org/help/fom-serve/cache/1.html
-----------------------------------------------------------------