Re: External Tables

From: joel garry <joel-garry_at_home.com>
Date: Thu, 30 Jul 2009 14:48:59 -0700 (PDT)
Message-ID: <1758afce-4a3a-4c64-8734-e3ba8b9be61c_at_12g2000pri.googlegroups.com>

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-dozens-across-four-states-busted-me/?uniontrib

Received on Thu Jul 30 2009 - 16:48:59 CDT