Add a column SQL query in Oracle database
声明：本页面是StackOverFlow热门问题的中英对照翻译，遵循CC BY-SA 4.0协议，如果您需要使用它，必须同样遵循CC BY-SA许可，注明原文地址和作者信息，同时你必须将它归于原作者（不是我）：StackOverFlow
原文地址: http://stackoverflow.com/questions/11516696/
Warning: these are provided under cc-by-sa 4.0 license. You are free to use/share it, But you must attribute it to the original authors (not me):
StackOverFlow
Add a column SQL query in Oracle database
提问by Murtaza
I am using Oracle Database (version is 9i) and I want to add a column to a current table in oracle database.
I am using Oracle Database (version is 9i) and I want to add a column to a current table in oracle database.
I want to add an integer column to keep track of invalid tries per user, so default value should be 5.
I want to add an integer column to keep track of invalid tries per user, so default value should be 5.
When I try to execute this query in Sql*Plus it gives an error table or view doesn't exist ( I have double checked table name is correct.
When I try to execute this query in Sql*Plus it gives an error table or view doesn't exist ( I have double checked table name is correct.
ALTER TABLE CustApps_user ADD VALID_TRIES INT DEFAULT 5 NOT NULL;
回答by APC
I guess the error you're getting is ORA-00942. This can mean a number of things, but basically it means the object does not exist in the current scope and context of what you're doing. So for instance it is the error thrown when we attempt to build a view on a table in another schema when we have been granted privileges through a role and not directly.
I guess the error you're getting is ORA-00942. This can mean a number of things, but basically it means the object does not exist in the current scope and context of what you're doing. So for instance it is the error thrown when we attempt to build a view on a table in another schema when we have been granted privileges through a role and not directly.
In your case it probably mean that the table is in another schema. You normally may be accessing it through a view or synonym. You can easily check this by querying the data dictionary:
In your case it probably mean that the table is in another schema. You normally may be accessing it through a view or synonym. You can easily check this by querying the data dictionary:
select owner, object_type
from all_objects
where object_name = 'CUSTAPPS_USER'
回答by user3301153
alter table
table_name
add
(
column1_name column1_datatype column1_constraint,
column2_name column2_datatype column2_constraint,
column3_name column3_datatype column3_constraint
);
Here are some examples of Oracle "alter table" syntax to add data columns.
Here are some examples of Oracle "alter table" syntax to add data columns.
alter table
cust_table
add
cust_sex varchar2(1) NOT NULL;
Here is an example of Oracle "alter table" syntax to add multiple data columns.
Here is an example of Oracle "alter table" syntax to add multiple data columns.
ALTER TABLE
cust_table
ADD
(
cust_sex char(1) NOT NULL,
cust_credit_rating number
);
回答by tomi
You have to add bracket in query:
You have to add bracket in query:
ALTER TABLE CustApps_user ADD (VALID_TRIES INT DEFAULT 5 NOT NULL);
INT is legal, but it will be converted to NUMBER, so you can also use:
INT is legal, but it will be converted to NUMBER, so you can also use:
ALTER TABLE CustApps_user ADD (VALID_TRIES NUMBER(38,0) DEFAULT 5 NOT NULL);
or change (decrease) NUMBER precision.
or change (decrease) NUMBER precision.
