ravi, July 11, 2008 - 2:11 am UTC
We are facing the same issue while moving the table structure from UAT to prod it change the order of column in the database and i have written a pl/sql code which will do SCD(slowly changing dimension ) based on the order of columns in the table.So we have again changing the order by deleting the table and creating new one its very difficult to do it always.
Tom do we have any option to change the order without deleting the columns or without deleting the relationship
thanks
July 11, 2008 - 8:16 am UTC
rename table to table_tab
create view table as select columns,in,any,order,that,makes,you,happy from table_tab;
use view.
or do the right thing which would be to maintain your OWN METADATA in a format you own, control, can fix, can change, maintain and so on. And have your process be driven off of YOUR metadata.
Internal column order
Wolfgang, July 11, 2008 - 10:32 am UTC
The internal order of the column is also not garanteed when you have a LONG Column in your CREATE TABLE Script.
Oracle will put a LONG column to the end of the table, but will still keep the original triage when you execute a select *
Example:
create table t1 (col1 long, col2 number,col3 varchar2(10));
create table t2 (col1 number, col2 number,col3 varchar2(10));
SQL> select table_name,column_name,column_id, segment_column_id
from user_tab_cols
where table_name IN('T1','T2') order by 1,2;
TABLE_NAME COLUMN_NAME COLUMN_ID SEGMENT_COLUMN_ID
------------ ------------- ----------- -----------------
T1 COL1 1 3
T1 COL2 2 2
T1 COL3 3 1
T2 COL1 1 1
T2 COL2 2 2
T2 COL3 3 3So a LONG column will change the order of how the row is stored on the block.
Wolfgang