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

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

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

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.

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
)

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

?

Oh it was Case Sensitive. Ok I got it to run without errors but it returns empty??

are you running this in toad or sqlplus?

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.

you need to run a sqlplus command first in the sqlplus text editor.

SET LONG 999999

then run the select dbms_metadata.... command.

ok when i run the query thru .net it works fine. Thanks for the help people!