Rebuilding Indexes and Recreating Partitions. From Old Tbl to New Tbl. Sql Queries!
DBAsupport.com Forums - Powered by vBulletin
Results 1 to 10 of 10

Thread: Rebuilding Indexes and Recreating Partitions. From Old Tbl to New Tbl. Sql Queries!

  1. #1
    Join Date
    Jan 2007
    Posts
    12

    Question Rebuilding Indexes and Recreating Partitions. From Old Tbl to New Tbl. (RESOLVED)

    I need to know how, by using SQL Queries, Do i do the following:

    1-Create a new empty table based on another tables structure:

    I have worked this part out.

    Create Table NewTable as Select * From OldTable Where 1 = 0

    -----------------------

    2-Rebuild the Indexes on the new table so they start where the old table ended off

    Not a clue

    -----------------------

    3-ReCreate the exact partitions of the old table, to the new table

    Not a clue


    Please note that it must be done using SQL Queries!

    Also when i try to export a script of a table to see if i could get a clue of syntax etc from there, TOAD crashes everytime. Oracle help and most of the help on the net will only tell me how to do it using the interface and not Queries. The idea of this is to do it without TOAD just using Queries. Thanks in advance

    Using Toad for Oracle version 8.6
    Last edited by Pixie22; 01-24-2007 at 08:22 AM.

  2. #2
    Join Date
    Sep 2002
    Location
    England
    Posts
    7,331
    if you do search for how to rebuild and index on google, you will get many many hits - same for partitions - give it a try

  3. #3
    Join Date
    Jan 2007
    Posts
    12
    But thats rebuilding indexes and partitions in the same table, not between 2. And i have been looking for about 4 hours and cant find anything of practical use. Nothing with Queries atleast.

  4. #4
    Join Date
    Apr 2006
    Posts
    50
    Depending on the version of oracle you are using you may be able to use dbms_metadata.get_ddl to retrieve the ddl used to created the table, partitions and indexes.

    e.g.

    SQL> create table test_tab (col1 varchar2(20), col2 number);

    Table created.

    SQL> select dbms_metadata.get_ddl('TABLE','TEST_TAB',user)
    2 from dual;

    DBMS_METADATA.GET_DDL('TABLE','TEST_TAB',USER)
    --------------------------------------------------------------------------------

    CREATE TABLE "GB"."TEST_TAB"
    ( "COL1" VARCHAR2(20),
    "COL2" NUMBER
    )

  5. #5
    Join Date
    Jan 2007
    Posts
    12
    Yeah thats what im looking for. Only prob is i get an error:

    Using Toad For Oracle Ver 8.6

    ORA-31603: object "Testertable" of type TABLE not found in schema "MNP"
    ORA-06512: at "SYS.DBMS_SYS_ERROR", line 105
    ORA-06512: at "SYS.DBMS_METADATA_INT", line 2857
    ORA-06512: at "SYS.DBMS_METADATA_INT", line 3192
    ORA-06512: at "SYS.DBMS_METADATA_INT", line 4078
    ORA-06512: at "SYS.DBMS_METADATA", line 326
    ORA-06512: at "SYS.DBMS_METADATA", line 410
    ORA-06512: at "SYS.DBMS_METADATA", line 449
    ORA-06512: at "SYS.DBMS_METADATA", line 615
    ORA-06512: at "SYS.DBMS_METADATA", line 1221
    ORA-06512: at line 1

    There is a table called Testertable and it is included in the list under schema MNP when i check under the Schema browser? Also tried with other tables.

    Tried:

    SELECT dbms_metadata.get_ddl('TABLE', 'TesterTable', 'MNP') From DUAL

    also tried:

    SELECT dbms_metadata.get_ddl('TABLE', 'TesterTable', User) from DUAL

    ?

  6. #6
    Join Date
    Jan 2007
    Posts
    12
    Oh it was Case Sensitive. Ok I got it to run without errors but it returns empty??

  7. #7
    Join Date
    Apr 2006
    Posts
    50
    are you running this in toad or sqlplus?

  8. #8
    Join Date
    Jan 2007
    Posts
    12
    Toad SQL Editor


    However when I pick execute as SQL*Plus in the menu it freezes for 20 seconds and then opens the SQL PLUS text editor?

    When i run it as a script i get the following results which is only the first column of the table. It has 16 columns

    DBMS_METADATA.GET_DDL('TABLE','TESTERTABLE','MNP')
    --------------------------------------------------------------------------------

    CREATE TABLE "MNP"."TESTERTABLE"
    ( "NODE" VARCHAR2(7) NOT NULL ENABLE,



    1 row selected.

  9. #9
    Join Date
    Apr 2006
    Posts
    50
    you need to run a sqlplus command first in the sqlplus text editor.

    SET LONG 999999

    then run the select dbms_metadata.... command.

  10. #10
    Join Date
    Jan 2007
    Posts
    12
    ok when i run the query thru .net it works fine. Thanks for the help people!

Posting Permissions

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



Click Here to Expand Forum to Full Width