oracle 换字段顺序,怎样修改列的名称和调整列的顺序

偶在QUEST的TOAD中有重建表的工具，可以调整列的顺序。(TOOls  -> Rebuild table )

具体过程是这样的：

--  Table Rebuild script generated by TOAD

--

--  Original table: DEPT

--  Backup of table: DEPT_X

--  Date: 2000-10-24 8:26:57

--

--  Lock original table before rename

LOCK TABLE DEPT IN EXCLUSIVE  MODE ;

--  Make backup copy of original table

RENAME  DEPT TO DEPT_X ;

--  drop fKey constraint from SCOTT.EMP

ALTER TABLE SCOTT.EMP DROP CONSTRAINT FK_DEPTNO ;

--  Remove all other NAMED Table Constraints because

--  they will cause errors when re-creating the table

--  Remove original Primary Key now that FKeys are dropped

ALTER TABLE SCOTT."DEPT_X" DROP CONSTRAINT PK_DEPT ;

--  Recreate original table

CREATE TABLE SCOTT.DEPT (

DEPTNO  NUMBER (2)    NOT NULL,

DNAME   VARCHAR2 (14),

LOC     VARCHAR2 (13) )

TABLESPACE SYSTEM PCTFREE 10

STORAGE(INITIAL 64K NEXT 64K PCTINCREASE 50 ) ;

--   Copy the data from the renamed table

Insert into SCOTT."DEPT" (

DEPTNO, DNAME, LOC )

SELECT

DEPTNO, DNAME, LOC

FROM SCOTT."DEPT_X" ;

Commit ;

--    Recreate indexes EXCLUDING those created via Unique Constraints

--    Recreate indexes EXCLUDING those created via Unique Constraints

--   Recreate the PKey Constraint

ALTER TABLE SCOTT."DEPT" ADD

CONSTRAINT PK_DEPT

PRIMARY KEY ( DEPTNO )

USING INDEX  PCTFREE 10

STORAGE(INITIAL 65536 NEXT 65536 PCTINCREASE 50 )

TABLESPACE SYSTEM  ;

--   Recreate the FKey Constraints from the NEW table

--   Grant any privs associated with the old table

--   Recreate the FKey Constraints that reference the NEW table

ALTER TABLE SCOTT.EMP ADD

CONSTRAINT FK_DEPTNO

FOREIGN KEY (DEPTNO)

REFERENCES SCOTT.DEPT (DEPTNO)  ;

--   Recompile any dependent objects

--  *** Recompile triggers for the new table