Re: Tablespace information from export file (NT)

To avoid these caveats you must create the tablespaces manually BEFORE importing the data. Then it should work ok. :)
Instead of extracting tablespace info from the export file. (works) why not run a simple
sql when you export the stuff. You get this info from sys.dba_data_files and sys.dba_tablespaces.

Petter Henrik Hansen

mjain_at_my-dejanews.com wrote:

> Just a small addendum to add my thoughts..
>
> As suggested by Charles the import can very well create the
> tablesapces and the datafiles.
>
> Small caveats:-
> 1. This should work fine if the directories in which the datafiles
> reside are present. Otherwise the tablespace creation may result
> in an error and subsequent export will run into errors.
>
> 2. If the operating system of the source database has a different
> file and naming conventions than destination database then the
> directory and filenames will not be valid, and so during import
> process the tablespace creation may fail.
>
> For example suppose source database is on UNIX and the destination
> database is Windows NT then the directory and file naming changes.
> So a valid filename "/usr/oracle/datafile01.dbf" in Unix when
> used during import on NT will give an error as the filename
> is not a valid filename.
>
> I guess you got the idea..
>
> 3. If you are creating the database on the same machine on which
> the source database exists and you want to create a brand new
> database on the same database server machine and want to keep
> the source also, then we should have different filenames than
> source database as source and destination databases should have
> different filename on the same box.
>
> I just wanted to add my comments to the discussion to assist
> you further.
>
> Whatever Charles has mentioned is fine, but you should also
> consider your situation.
>
> hope this helps,
>
> Manoj Jain
> Oracle Certified Professional OCP DBA Oracle7 & Oracle8
>
> Email: m_jain_at_go.com
>
> In article <7f33si$7v9_at_sjx-ixn10.ix.netcom.com>,
> "Charles Wolfe" <cwolfe_at_ix.netcom.com> wrote:
> > Actually, import is quite capable of creating tablespaces. Please refer to
> > the attached excerpt:
> >
> > hth
> >
> > Regards,
> > Chuck
> >
> > Connected to: Oracle8 Personal Edition Release 8.0.4.0.0 - Production
> > PL/SQL Release 8.0.4.0.0 - Production
> >
> > Export file created by EXPORT:V08.00.04 via conventional path
> > .. importing SYSTEM's objects into SYSTEM
> > "ALTER SCHEMA = "SYSTEM""
> > "CREATE TABLESPACE "USER_DATA" DATAFILE
> > 'C:\ORAWIN95\DATABASE\USR1ORCL.ORA'"
> > " SIZE 3145728 DEFAULT STORAGE (INITIAL 10240 NEXT 10240 MINEXTENTS 1
> > "
> > "MAXEXTENTS 121 PCTINCREASE 50) ONLINE PERMANENT"
> > "CREATE TABLESPACE "ROLLBACK_DATA" DATAFILE
> > 'C:\ORAWIN95\DATABASE\RBS1ORCL."
> > "ORA' SIZE 5242880 DEFAULT STORAGE (INITIAL 10240 NEXT 10240
> > MINEXTENT"
> > "S 1 MAXEXTENTS 121 PCTINCREASE 50) ONLINE PERMANENT"
> > "CREATE TABLESPACE "TEMPORARY_DATA" DATAFILE
> > 'C:\ORAWIN95\DATABASE\TMP1ORCL"
> > ".ORA' SIZE 2097152 DEFAULT STORAGE (INITIAL 10240 NEXT 10240
> > MINEXTEN"
> > "TS 1 MAXEXTENTS 121 PCTINCREASE 50) ONLINE PERMANENT"
> >
> > Kenny Gump <kgump_at_mylanlabs.com> wrote in message
> > news:3714ff1f.0_at_news.mountain.net...
> > > The import will create users but will not create the Tablespaces.
> > >
> > > To see exactly what the import will do, execute the following statement,
> > it
> > > will not import the file, but will show you what would happen if you did:
> > >
> > > imp user/password_at_TNSName myfile.dat show=Y log=mylogfile.txt
> > >
> > > This will output mylogfile.txt with all the create commands and sizing
> > > information for each of the segments to be created.
> > >
> > > Kenny Gump
> > >
> > >
> > > Andy Hardy wrote in message ...
> > > >Is there any way of getting the tablespace details from an export file
> > > >as I'd like to know how big the tablespaces should be when I create my
> > > >new database.
> > > >
> > > >In fact, I almost expected an import to attempt to create the
> > > >tablespaces and users from an export file as required?
> > > >
> > > >Andy
> > > >--
> > > >Andy Hardy. PGP key available on request
> > > >===============================================================
> > >
> > >
> >
> >
>
> -----------== Posted via Deja News, The Discussion Network ==----------
> http://www.dejanews.com/ Search, Read, Discuss, or Start Your Own
Received on Fri Apr 16 1999 - 15:55:18 CDT