Results 1 to 8 of 8
  1. #1
    Join Date
    Aug 2003
    Posts
    20

    Question Unanswered: External table create as select

    Hi,

    I have usual table. I'd like to create external table with the same structure as normal table.
    But create table ... organization external ... as select ... - does not work
    Is it possibility to create external table based on normal table structure.
    Usually I can create it by constructing it from views as column name, column type etc.
    I guess it has simple way I don't know.
    Could you please suggest this simple way to create external table as select from normal one?

    Thanks,
    Sergiy
    Oracle 9i OCP DBA

  2. #2
    Join Date
    Sep 2003
    Location
    Virginia, USA
    Posts
    246
    The CTAS method of creating external tables is a new Oracle 10g Feature. In Oracle 9i all you can do is create external tables from flat files.

    The CTAS method requires access to the DBMS_DATAPUMP package new to 10g.
    MarkRem
    Author, Oracle Database 10g: From Nuts to Soup
    http://www.remidata.com/book_nuts2soup.htm

  3. #3
    Join Date
    Jan 2004
    Location
    Croatia, Europe
    Posts
    4,094
    Provided Answers: 4
    The External Table feature allows for flat files, which reside outside the database, to be accessed just like relational tables within the database: the flat-file data can be queried and joined to other tables using standard SQL. Data access can be serial or parallel for maximum scalability. From a user’s point of view, the main difference between an external table and a regular table is that the external table is read-only.
    Therefore, I'd say you can't create them as you'd like to.

    Also, you said that you create external tables from views? What do you mean by that? "View" as a database object, or "view" as "look how a table looks like and then write CREATE TABLE statement"?

  4. #4
    Join Date
    Aug 2003
    Posts
    20

    Thumbs up

    Quote Originally Posted by markrem
    The CTAS method of creating external tables is a new Oracle 10g Feature. In Oracle 9i all you can do is create external tables from flat files.

    The CTAS method requires access to the DBMS_DATAPUMP package new to 10g.
    Thanks for your answer, but production is 9i - so, I can not apply 10g feature
    Oracle 9i OCP DBA

  5. #5
    Join Date
    Aug 2003
    Posts
    20

    Smile

    Quote Originally Posted by Littlefoot
    Also, you said that you create external tables from views? What do you mean by that? "View" as a database object, or "view" as "look how a table looks like and then write CREATE TABLE statement"?
    The last one I have a table view some structure. I have a view based on that table with subset of fields. I can construct create table organixation external from dynamic views but i'd like to do it with simple query.
    Oracle 9i OCP DBA

  6. #6
    Join Date
    Aug 2003
    Posts
    20
    At last I solve it by constructing query
    Thanks for replies.
    Oracle 9i OCP DBA

  7. #7
    Join Date
    Dec 2003
    Posts
    1,074
    So when you use the CTAS method for creating an external table in 10g, does a file actually get created at the location designated by your directory object?

    -cf

  8. #8
    Join Date
    Aug 2003
    Posts
    20
    Quote Originally Posted by chuck_forbes
    So when you use the CTAS method for creating an external table in 10g, does a file actually get created at the location designated by your directory object?

    -cf
    I did not play with external tables in 10g
    Oracle 9i OCP DBA

Posting Permissions

  • You may not post new threads
  • You may not post replies
  • You may not post attachments
  • You may not edit your posts
  •