| sunil |
Posted: Mar 23, 2007 03:18:37 AM |
Total Post: 20
Joined: Nov, 2006
|
Hi All,
This is Sunil. I have a doubt, When ever i add new column to a table using ALTER TABLE the column gets added as a last one in the table. Is ther any way to get rid of this. This is actually what i want
if I have 4 columns in a table and adding a new col will make it 5 and the new one will be the 5th one but i want it to be 2nd one how can i do it is ther any other way to order cols in a table. Please suggest me.
Thanks in advance,
Regards,
Sunil. |
|
|
 Jayanta |
| Posted: Mar 23, 2007 04:36:51 AM |  |
|
Total Post: 479
Joined: Feb, 2006
|
new columns are always added at the last u cant change this.
but onething u can do after adding the column u can create a new table based on the old one with desired column order, and then delete the old table.
cheers!
|
|
|
|
|
| Jayanta |
| Posted: Mar 23, 2007 04:41:15 AM | |
|
Total Post: 479
Joined: Feb, 2006
|
another thing is i dont find the use of it to put columns in particular order, particularly when ur adding new column(s) to an existing table.
whatever the column positions r u can order the columns in select statement such that they r displayed in particular order. so will u plz clarify why do u want to keep cols in particular order in a table? is there any performance improvement?
regards
|
|
|
|
|
| Rahul |
| Posted: Mar 23, 2007 05:13:25 AM | |
|
Total Post: 1
Joined: Jul, 2006
|
We cannot add column in between of the existing column and as every one says it really doent matter what the column position is in the database cozz u can always change it using your select statement.
|
|
|
|
|
| sunil |
| Posted: Mar 23, 2007 05:31:55 AM | |
|
Total Post: 20
Joined: Nov, 2006
|
thnq u all for info. I know that i can use select statement in any order i want. I am calling a procedure from a JSP and that has some related columns like id, size etc... which requires a change in java code which i thought would be avoided by changing the order of cols.
any ways thnx 4 help.
regards,
Sunil.
|
|
|
|
|
| Jayanta |
| Posted: Mar 23, 2007 05:43:38 AM | |
|
Total Post: 479
Joined: Feb, 2006
|
no sunil u better change ur java code.
|
|
|
|
|
| Jolly |
| Posted: Apr 02, 2007 12:27:51 AM | |
|
Total Post: 24
Joined: Mar, 2007
|
Hi
You can try the following method. This is an example
1 - Original table with some data
SQL> select * from test;
ID NAME ADDRESS
---------- ---------- --------------------
1 Name 1 Address 1
2 Name 2 Address 2
3 Name 3 Address 3
4 Name 4 Address 4
5 Name 5 Address 5
6 Name 6 Address 6
7 Name 7 Address 7
8 Name 8 Address 8
9 Name 9 Address 9
10 Name 10 Address 10
10 rows selected.
2 - Create an interim table
CREATE TABLE TESTNEW
(
ID NUMBER,
NAME VARCHAR2(50),
SEX VARCHAR(1) DEFAULT 'M',
ADDRESS VARCHAR2(100)
)
TABLESPACE TOOLS;
ALTER TABLE TESTNEW ADD (
PRIMARY KEY
(ID)
USING INDEX
TABLESPACE TOOLS
);
3 - Check that the table can be redefined online
exec dbms_redefinition.can_redef_table('oracle','test'); <=== The 1st parameter is the schema owner
4 - Start the redefinition process
exeexec dbms_redefinition.start_redef_table('oracle','test','testnew',dbms_redefinition.cons_use_pdbms_redefie','test','testnew',dbms_redefinition.cons_use_pk);exec dbms_redefinition.start_redef_table('oracle','test','testnew',dbms_redefinition.cons_use_pk);
exec dbms_redefinition.start_redef_table('oracle','test','testnew','id id,name name,address address',dbms_redefinition.cons_use_pk);
You shoud get the following results
SQL> desc testnew
Name Null? Type
------------------------------------------------------------------------ -------- -----------------
ID NOT NULL NUMBER
NAME VARCHAR2(50)
SEX VARCHAR2(1)
ADDRESS VARCHAR2(100)
SQL> select * from testnew;
ID NAME S ADDRESS
---------- ---------- - --------------------
1 Name 1 M Address 1
2 Name 2 M Address 2
3 Name 3 M Address 3
4 Name 4 M Address 4
5 Name 5 M Address 5
6 Name 6 M Address 6
7 Name 7 M Address 7
8 Name 8 M Address 8
9 Name 9 M Address 9
10 Name 10 M Address 10
10 rows selected.
5 - Complete the redefinition
exec dbms_redefinition.finish_redef_table('oracle','test','testnew');
Then you should expect to see the following
SQL> desc test
Name
-----------------------------------------------------
ID
NAME
SEX
ADDRESS
SQL> select * from test;
ID NAME S ADDRESS
---------- ---------- - ------------------------------
1 Name 1 M Address 1
2 Name 2 M Address 2
3 Name 3 M Address 3
4 Name 4 M Address 4
5 Name 5 M Address 5
6 Name 6 M Address 6
7 Name 7 M Address 7
8 Name 8 M Address 8
9 Name 9 M Address 9
10 Name 10 M Address 10
10 rows selected.
and
SQL> desc testnew
Name Null? Type
------------------------------------------------------------------------ -------- -----------------
ID NOT NULL NUMBER
NAME VARCHAR2(50)
ADDRESS VARCHAR2(100)
SQL> select * from testnew
2 /
ID NAME ADDRESS
---------- ---------- ----------------------------------------
1 Name 1 Address 1
2 Name 2 Address 2
3 Name 3 Address 3
4 Name 4 Address 4
5 Name 5 Address 5
6 Name 6 Address 6
7 Name 7 Address 7
8 Name 8 Address 8
9 Name 9 Address 9
10 Name 10 Address 10
10 rows selected.
GOOD LUCK
Regards,
Jolly
|
|
|
|
|
| Time Zone: EDT |
Send this thread to your friend |
