Results 1 to 9 of 9
  1. #1
    Join Date
    Jul 2005
    Posts
    5

    Unanswered: insert statement script

    Hi all,

    Does anybody know any script to make CREATE TABLE and INSERT statement from Sys tables (dba_tables, etc) and tables.


    I need to script some dba VIEW and its content but it must be one executable script which has file output.

    We have performance promlems so like to compare two similar Oracle databese structures (indexes, statistics, etc)


    Thx

  2. #2
    Join Date
    Nov 2002
    Posts
    833
    in would always be a good idea to provide platform and Oracle Version
    but for newer releases
    stick with the dbms_metadata package

    performance problems are normally not caused from the sys schema

    run statspack to figure out where the bottlenecks are

  3. #3
    Join Date
    Dec 2003
    Posts
    1,074
    CTAS (Create Table AS)

    create table my_schema.v$instance_copy as
    select * from v$instance;

    -Chuck

  4. #4
    Join Date
    Jul 2005
    Posts
    5
    I must to export these oracle tables into TXT file and load to another Oracle RDBS. I am searching for easy way to do this.

    CTAS does not help me now but nice feature.

  5. #5
    Join Date
    Aug 2003
    Location
    Where the Surf Meets the Turf @Del Mar, CA
    Posts
    7,776
    Provided Answers: 1
    >INSERT statement from Sys tables (dba_tables, etc)
    Doing INSERT against SYS tables/views is a VERY bad idea & will result in an unsupported DB.
    Besides I willing to give you high odds & bet that even if you do succeed at reconstructing a 2nd DB, you won't be any closer to finding the root cause than you are right now.
    I suggest that you give up the fool's errand and simply use SQL_TRACE to find identify the "bad" SQL.
    You can lead some folks to knowledge, but you can not make them think.
    The average person thinks he's above average!
    For most folks, they don't know, what they don't know.
    Good judgement comes from experience. Experience comes from bad judgement.

  6. #6
    Join Date
    Oct 2002
    Location
    greenwich.ct.us
    Posts
    279
    What are you trying to accomplish by manipulating the data dictionary tables?

  7. #7
    Join Date
    Jul 2005
    Posts
    5
    I does'nt want to INSERT into Sys Tables/Views. I like to load into a separated user schame and compare to our sys tables.

    I intend to build some select joining the original dba_indexes (and dba_ind_columns) and the loaded dba_indexes (come from another DB with similar table structures).

    Mybe I realize some interesting difference between index properties which cause perfomance issue.

  8. #8
    Join Date
    May 2004
    Location
    BA [ARG]
    Posts
    137
    So, what you want it to generate all the DDL to create the same structure on another schema from scripts, isn't it?

    I never used it but there's a package to do that, DBMS_DDL I think or something like that.

    To compare the schemas the best tool from my point of view is Quest Central, even better than TOAD.

    Saludos,
    DKG.

  9. #9
    Join Date
    Jul 2005
    Posts
    5
    I don't want to generate all the DDL to create some structure, the only things I need that export some tables (system views) to TEXT file and import to another DB's user schema.

    EXPORT/IMPORT by script inserts statement

Posting Permissions

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