Re: External Tables
From: Mark D Powell <Mark.Powell_at_eds.com>
Date: Sat, 1 Aug 2009 07:28:10 -0700 (PDT)
Message-ID: <61aece9f-a32d-449e-913c-3ecbdeb28428_at_o6g2000yqj.googlegroups.com>
On Jul 30, 5:48 pm, joel garry <joel-ga..._at_home.com> wrote:
> On Jul 30, 1:07 pm, Mark D Powell <Mark.Pow..._at_eds.com> wrote:
>
>
>
>
>
> > 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 rows
> > exported
> > <snip>
> > -- notice no row count
>
> > HTH -- Mark D Powell --
>
> Too bad the indexfile option doesn't work with imp here (at least on
> my 10.2.0.4). Haven't tried expdp.
>
> However, if you have unix like tools, you can grep the export file for
> the "CREATE EXTERNAL TABLE" statement. Well, almost, the access
> parameters are in a clob, as a desc dba_external_tables will show.
> Looks like that winds up in the exp file on the line after the create
> with a couple of special characters followed by the parameters on
> several lines. Maybe somehow with perl, awk or sed grab all the lines
> up to the one with REJECT LIMIT in it, and clean.
>
> Or just use some tool that understands clobs on the [dba|user]
> _external_tables (set long 32000 for sqlplus).
>
> I agree with David, find the original scripts. Maybe they'll have
> additional explanations of strange things that need to be accounted
> for in messy real world data.
>
> jg
> --
> _at_home.com is bogus.
> Now there's a misleading url:http://www3.signonsandiego.com/stories/2009/jul/30/1n30fraud21513-doz...- Hide quoted text -
>
> - Show quoted text -
Date: Sat, 1 Aug 2009 07:28:10 -0700 (PDT)
Message-ID: <61aece9f-a32d-449e-913c-3ecbdeb28428_at_o6g2000yqj.googlegroups.com>
On Jul 30, 5:48 pm, joel garry <joel-ga..._at_home.com> wrote:
> On Jul 30, 1:07 pm, Mark D Powell <Mark.Pow..._at_eds.com> wrote:
>
>
>
>
>
> > 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 rows
> > exported
> > <snip>
> > -- notice no row count
>
> > HTH -- Mark D Powell --
>
> Too bad the indexfile option doesn't work with imp here (at least on
> my 10.2.0.4). Haven't tried expdp.
>
> However, if you have unix like tools, you can grep the export file for
> the "CREATE EXTERNAL TABLE" statement. Well, almost, the access
> parameters are in a clob, as a desc dba_external_tables will show.
> Looks like that winds up in the exp file on the line after the create
> with a couple of special characters followed by the parameters on
> several lines. Maybe somehow with perl, awk or sed grab all the lines
> up to the one with REJECT LIMIT in it, and clean.
>
> Or just use some tool that understands clobs on the [dba|user]
> _external_tables (set long 32000 for sqlplus).
>
> I agree with David, find the original scripts. Maybe they'll have
> additional explanations of strange things that need to be accounted
> for in messy real world data.
>
> jg
> --
> _at_home.com is bogus.
> Now there's a misleading url:http://www3.signonsandiego.com/stories/2009/jul/30/1n30fraud21513-doz...- Hide quoted text -
>
> - Show quoted text -
Being that both objects export and import I am not sure what real issue the OP has, but as far as imp goes you should be able to use the show= option to generate the DDL for both objects. Some editing would be required.
On a recent version of Oracle the dbms_metadata package is also available to extract the source with.
HTH -- Mark D Powell -- Received on Sat Aug 01 2009 - 09:28:10 CDT