Re: External Tables

From: joel garry <joel-garry_at_home.com>
Date: Mon, 3 Aug 2009 10:13:08 -0700 (PDT)
Message-ID: <737d7288-85cd-4a54-b220-e152ea9215e3_at_d4g2000prc.googlegroups.com>



On Aug 1, 7:28 am, Mark D Powell <Mark.Pow..._at_eds.com> wrote:
> 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.

D'oh! Of course, no index, no indexfile, nothing shows. It works with the show=y option and no indexfile parameter. I plead brain overload.

>
> On a recent version of Oracle the dbms_metadata package is also
> available to extract the source with.
>
> HTH -- Mark D Powell --

jg

--
_at_home.com is bogus.
http://money.cnn.com/2009/08/03/technology/schmidt_google_apple_board/index.htm?section=money_topstories
Received on Mon Aug 03 2009 - 12:13:08 CDT

Original text of this message