ALTER TABLE table_name MODIFY column_name VARCHAR(200) [message #6856] |
Thu, 08 May 2003 14:36 |
Jadie
Messages: 64 Registered: January 2002
|
Member |
|
|
I went to an interview for a DBA position. There was a written test. One of the question is:
ALTER TABLE table_name MODIFY column_name VARCHAR(200)
What does this command mean? What will make this command to fail?
I never saw ALTER TABLE command can have option MODIFY?
Please help me get the answer. thanks!
Jade
|
|
|
Re: ALTER TABLE table_name MODIFY column_name VARCHAR(200) [message #6857 is a reply to message #6856] |
Thu, 08 May 2003 15:44 |
Joe
Messages: 138 Registered: November 1999
|
Senior Member |
|
|
Actually the command:
ALTER TABLE table_name MODIFY column_name VARCHAR(200)
will fail because it is missing a set of parens. The modify statement will enable you to alter the characteristics of the columns, such as length, null, not null, default values...etc. In some cases the column must be null. In order for this command to execute properly it should read:
ALTER TABLE table_name MODIFY (column_name varchar2(200));
|
|
|
|
Re: ALTER TABLE table_name MODIFY column_name VARCHAR(200) [message #6876 is a reply to message #6869] |
Fri, 09 May 2003 10:32 |
Jadie
Messages: 64 Registered: January 2002
|
Member |
|
|
Thanks Joe and Barbara. I really appreciate that you answered my quesion.
Yes, I agreed with you. And I gave the same answer.
But following this question, they gave four answers to choose:
1) table is partitioned.
2) table is not partitioned.
3) table is partitioned on this column
4) table is not partitioned on this column.
There answers made me very confused! ALTER TABLE MODIFY command is related to table partition?
Jadie
|
|
|
Re: ALTER TABLE table_name MODIFY column_name VARCHAR(200) [message #6888 is a reply to message #6876] |
Sat, 10 May 2003 01:24 |
|
Barbara Boehmer
Messages: 9077 Registered: November 2002 Location: California, USA
|
Senior Member |
|
|
The original question asked what would make the command fail. Although I identified trying to decrease the column size of a non-empty column as one thing that would make the command fail, there are other things that would also cause it to fail. It appears that, by providing the list of choices, they are asking you which of those situations would cause the command to fail. The best way to find out, is to test the possibilities. I have already demonstrated the command on a table that it is not partitioned. So, the remaining question is will the command fail if it is partitioned on any column, will it fail if it is partitioned on this column, will it fail if it is partitioned on some column other than this one.
Below I have demonstrated first that it will not fail if it is partitioned on some other column. Below that I have demonstrated that it will fail if it is partitioned on this column. So, the correct answer is:
The command will fail if:
3) table is partitioned on this column.
SQL> -- <b>create table partitioned on column you don't intend to modify:</b>
SQL> CREATE TABLE test
2 (test VARCHAR (10),
3 other_column NUMBER)
4 PARTITION BY RANGE (other_column)
5 (PARTITION p1 VALUES LESS THAN (10),
6 PARTITION p2 VALUES LESS THAN (20),
7 PARTITION p3 VALUES LESS THAN (MAXVALUE))
8 /
Table created.
SQL> INSERT INTO test (test, other_column)
2 VALUES ('ABCDEFJHIJ', 1)
3 /
1 row created.
SQL> INSERT INTO test (test, other_column)
2 VALUES ('KLMONPQRST', 10)
3 /
1 row created.
SQL> INSERT INTO test (test, other_column)
2 VALUES ('UVWXYZ', 20)
3 /
1 row created.
SQL> -- <b>modify column that table is not partitioned on:</b>
SQL> ALTER TABLE test MODIFY test VARCHAR(200)
2 /
Table altered.
SQL> DROP TABLE test
2 /
Table dropped.
.
.
.
.
.
SQL> -- <b>create table partitioned on column you intend to modify:</b>
SQL> CREATE TABLE test
2 (test VARCHAR (10),
3 other_column NUMBER)
4 PARTITION BY RANGE (test)
5 (PARTITION p1 VALUES LESS THAN ('K'),
6 PARTITION p2 VALUES LESS THAN ('U'),
7 PARTITION p3 VALUES LESS THAN (MAXVALUE))
8 /
Table created.
SQL> INSERT INTO test (test, other_column)
2 VALUES ('ABCDEFJHIJ', 1)
3 /
1 row created.
SQL> INSERT INTO test (test, other_column)
2 VALUES ('KLMONPQRST', 10)
3 /
1 row created.
SQL> INSERT INTO test (test, other_column)
2 VALUES ('UVWXYZ', 20)
3 /
1 row created.
SQL> -- <b>attempt to modify column that table is partitioned on fails:</b>
SQL> ALTER TABLE test MODIFY test VARCHAR(200)
2 /
ALTER TABLE test MODIFY test VARCHAR(200)
*
ERROR at line 1:
ORA-14060: data type or length of a table partitioning column may not be
changed
SQL> DROP TABLE test
2 /
Table dropped.
|
|
|
|