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?

On Wed, 29 Jul 2009 19:12:21 -0700 (PDT), The Magnet <art (AT) unsu (DOT) com>
wrote:


Did you try?

-----------
Sybrand Bakker
Senior Oracle DBA

On Jul 30, 1:02*am, sybra... (AT) hccnet (DOT) nl wrote:
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...

On Jul 30, 8:14*am, The Magnet <a... (AT) unsu (DOT) com> wrote:
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

On Jul 30, 9:30*am, ddf <orat... (AT) msn (DOT) com> wrote:
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 --

On Jul 30, 1:07*pm, Mark D Powell <Mark.Pow... (AT) eds (DOT) com> wrote:
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
--
@home.com is bogus.
Now there's a misleading url:
http://www3.signonsandiego.com/stori...-me/?uniontrib

On Jul 30, 5:48*pm, joel garry <joel-ga... (AT) home (DOT) com> wrote:
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 --

On Aug 1, 7:28*am, Mark D Powell <Mark.Pow... (AT) eds (DOT) com> wrote:
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.

jg
--
@home.com is bogus.
http://money.cnn.com/2009/08/03/tech...ney_topstories