HomeSQLHow To Rename & Modify A Column Using SQL Alter Table
This is the third tutorial in the ALTER table series. Grand central 2013 full movie download hd. Till now we have learned in the previous tutorials:
- How to rename table using ALTER TABLE and
- How To add / delete a column from an existing table using ALTER TABLE.
In this tutorial we will learn how to rename column/s of a table as well as how to modify one or more columns of a table.
How To Rename A Column Of A Table
Syntax
ALTER TABLE table_name RENAME COLUMN old_name_of_column TO new_name_of_column;
For example
In the previous tutorial we have created a simple table by the name of TEST so we will be using that for this example as well.
Table test has only one column test_name. Structure of table Test is as follows:
DESC test
Name Null Type
——- —- ———
TEST_ID NUMBER(3)
Name Null Type
——- —- ———
TEST_ID NUMBER(3)
Say you want to rename the column test_id to col1
ALTER TABLE test RENAME COLUMN test_id TO col1;
On executing the above query the column test_id of table test will be renamed to col1. You can check it out by describing the structure of TEST table.
DESC test
Name Null Type
——- —- ———
COL1 NUMBER(3)
Name Null Type
——- —- ———
COL1 NUMBER(3)
How To Modify The Column Definition Using ALTER TABLE
Above we saw how to rename a column of a table. Now we will learn how to modify the column definition of a table. What I mean by modifying the column definition is to change the Data type and column width of the column.
Syntax
ALTER TABLE table_name MODIFY colun_name data_type (size);
Example:
Above we renamed column test_id to col1. Column col1 has data type NUMBER and column width 3. Now let’s say we want to change the data type of column col1 from NUMBER to VARCHAR2 and data width from 3 to 30. We can do that by writing the following code:
ALTER TABLE test MODIFY col1 VARCHAR2(30);
You can verify the change by describing the structure of the test table using DESCRIBE command.
That’s it. This is the basic concept behind renaming and modifying a column of a Table. Keep checking for more such simple and easy to understand concepts. Also please do like and share this on your social network and help me spread the word. Thanks & have a great day!
What is the syntax to rename a table or view in Oracle?
Steven
StevenSteven6,3482424 gold badges8484 silver badges133133 bronze badges
5 Answers
QuassnoiQuassnoi341k7272 gold badges536536 silver badges566566 bronze badges
To rename a table you can use:
or
Oracle Rename A Table
or, if owned by another schema:
Interestingly, ALTER VIEW does not support renaming a view. You can, however:
The RENAME command works for tables, views, sequences and private synonyms, for your own schema only.
If the view is not in your schema, you can recompile the view with the new name and then drop the old view.
(tested in Oracle 10g)
Jeffrey KempJeffrey Kemp49.6k1212 gold badges9393 silver badges136136 bronze badges
In order to rename a table in a different schema, try:
The rename command (as in '
PopPoprename mytable to othertable
') only supports renaming a table in the same schema.3,15211 gold badge1313 silver badges1212 bronze badges
user2727357,89066 gold badges4848 silver badges7777 bronze badges
MaurícioMaurício
Past 10g the current answer no longer works for renaming views.The only method that still works is dropping and recreating the view.The best way I can think of to do this would be:
SELECT TEXT FROM ALL_VIEWS WHERE owner='some_schema' and VIEW_NAME='some_view';
Add this in front of the SQL returned
Create or replace view some_schema.new_view_name as ..
Drop the old view
Drop view some_schema.some_view;
WouterWouter
Not the answer you're looking for? Browse other questions tagged oracletable-rename or ask your own question.
Summary: in this tutorial, you will learn how to use the Oracle
RENAME
table statement to rename an existing table in the database.To rename a table, you use the following Oracle
RENAME
table statement as follows:In the
RENAME
table statement:- First, specify the name of the existing table which you want to rename.
- Second, specify the new table name. The new name must not be the same as another table in the same schema.
Note that you cannot roll back a
RENAME
statement once you executed it.When you rename a table, Oracle automatically transfers indexes, constraints, and grants on the old table to the new one. In addition, it invalidates all objects that depend on the renamed table such as views, stored procedures, function, and synonyms.
Oracle RENAME
table example
Let’s create a table named
promotions
for the demonstration.2 4 6 8 | promotion_idNUMBERGENERATEDBYDEFAULTASIDENTITY, start_dateDATENOTNULL, PRIMARYKEY(promotion_id), ); |
The following PL/SQL function returns the number of promotions by querying data from the
promotions
table:2 4 6 8 10 12 | RETURNNUMBER v_countNUMBER; SELECT INTO FROM RETURNv_count; |
To rename the
promotions
table to campaigns
table, you use the following statement:As mentioned earlier, Oracle transferred all indexes, constraints, and grants from the promotions table to the campaigns table. The following statement shows constraints of the new
campaigns
table transferred from the promotions
table:2 4 6 8 | constraint_name, FROM WHERE ANDconstraint_type='C'; |
Because the
COUNT_PROMOTIONS
function references to promotions
table, when you renamed the promotions
table, the COUNT_PROMOTIONS
function became invalid.To find the invalid objects in the current schema, you query data from the
all_objects
view as follows:2 4 6 8 10 | owner, object_name all_objects status='INVALID' object_type, |
As you can see, the statement returns
COUNT_PROMOTIONS
function as an invalid object.In this tutorial, you have learned how to use the Oracle
RENAME
table statement to rename an existing table in the database.- Was this tutorial helpful?