Re: External Tables
From: Mark D Powell <Mark.Powell_at_eds.com>
Date: Thu, 30 Jul 2009 13:07:21 -0700 (PDT)
Message-ID: <f9a03cfa-6bc8-41a3-b4f7-fc98a7149f29_at_b14g2000yqd.googlegroups.com>
On Jul 30, 9:30 am, ddf <orat..._at_msn.com> wrote:
> On Jul 30, 8:14 am, The Magnet <a..._at_unsu.com> wrote:
>
>
>
>
>
> > On Jul 30, 1:02 am, sybra..._at_hccnet.nl wrote:
>
> > > On Wed, 29 Jul 2009 19:12:21 -0700 (PDT), The Magnet <a..._at_unsu.com>
> > > wrote:
>
> > > >Hi,
>
> > > >When you export a schema, are external tables and directory
> > > >definitions exported also? So, when I import, it will create the
> > > >directory definition and the external table?
>
> > > Did you try?
>
> > > -----------
> > > Sybrand Bakker
> > > Senior Oracle DBA
>
> > Did try and did not get it to work. So, thought there might be some
> > convoluted way of doing it. I mean, if a company has 300 external
> > table definitions, it would be meaningless if you cannot re-import
> > those definitions.....and have to re-create those tables again...- Hide quoted text -
>
> > - Show quoted text -
>
> External tables are built upon flat files and neither exp nor expdp
> export such files. Exporting the definitions, then, would be a
> useless undertaking. This is why one uses scripts to create such
> objects, so they can be recreated in another database (or in this same
> database) provided the source files exist. It should not be a major
> effort to run a series of scripts to recreate such tables.
>
> David Fitzjarrell- Hide quoted text -
>
> - Show quoted text -
<snip>
-- notice no row count
Date: Thu, 30 Jul 2009 13:07:21 -0700 (PDT)
Message-ID: <f9a03cfa-6bc8-41a3-b4f7-fc98a7149f29_at_b14g2000yqd.googlegroups.com>
On Jul 30, 9:30 am, ddf <orat..._at_msn.com> wrote:
> On Jul 30, 8:14 am, The Magnet <a..._at_unsu.com> wrote:
>
>
>
>
>
> > On Jul 30, 1:02 am, sybra..._at_hccnet.nl wrote:
>
> > > On Wed, 29 Jul 2009 19:12:21 -0700 (PDT), The Magnet <a..._at_unsu.com>
> > > wrote:
>
> > > >Hi,
>
> > > >When you export a schema, are external tables and directory
> > > >definitions exported also? So, when I import, it will create the
> > > >directory definition and the external table?
>
> > > Did you try?
>
> > > -----------
> > > Sybrand Bakker
> > > Senior Oracle DBA
>
> > Did try and did not get it to work. So, thought there might be some
> > convoluted way of doing it. I mean, if a company has 300 external
> > table definitions, it would be meaningless if you cannot re-import
> > those definitions.....and have to re-create those tables again...- Hide quoted text -
>
> > - Show quoted text -
>
> External tables are built upon flat files and neither exp nor expdp
> export such files. Exporting the definitions, then, would be a
> useless undertaking. This is why one uses scripts to create such
> objects, so they can be recreated in another database (or in this same
> database) provided the source files exist. It should not be a major
> effort to run a series of scripts to recreate such tables.
>
> David Fitzjarrell- Hide quoted text -
>
> - Show quoted text -
The definitions for extenal tables and directorys export and import; however, the definitions may need repair if the file system does not match what was specified in the definitions.
UT1 > select owner, table_name from dba_external_tables;
OWNER TABLE_NAME ------------------------------ ------------------------------ MPOWEL01 LOADTEXT2
From an export log
<snip>
exporting sequence numbers
. exporting directory aliases
. exporting context namespaces
<snip>
. . exporting table INV_PRICE_HEADER 0 rows
exported
. . exporting table
LOADTEXT2
. . exporting table MARKEST2 3 rowsexported
<snip>
-- notice no row count
HTH -- Mark D Powell -- Received on Thu Jul 30 2009 - 15:07:21 CDT