Oracle Database does not provide a function of direct modification of the names in the table, but often need to modify the column name and column order of the table, and have some Oracle's user using the database table with the correct column name and order, will The data dump of the old table comes in, and finally removes the old table and name the new table to the old table to complete this feature. The biggest problem with this method is to require double storage space, a larger return segment and a longer time. If the amount of data is large, this work overhead will be large. In fact, we can modify the name and order of the table column directly from the data dictionary. The following is a specific implementation step:
1. Log in to the Oracle Database as an Internal Username and create a test list.
SQL> CREATE TABLE Scott.test As SELECT Empno, Ename from Scott.emp;
SQL> Desc Scott.test
Name Type Nullable Default Comments
-------------------------
Empno Number (4) Y
ENAME VARCHAR2 (10) Y
Below we have to change the empno and eName in the Scott.Test table, and change the ename column to EMP_NAME, and Empno is changed to EMP_NO.
2. The actual storage location or table listed in the table.
SQL> SET Long 9999
Since the TEXT column is a long type, only "set" can only be fully displayed.
SQL> Select text from all_views where view_name = 'user_tab_columns'
Data Dictionary View User_Tab_Columns stores a definition information of a table column, from the query result of the statement, the column definition information is stored in Table Sys.col $, that is, if the definition of the column in the table should be modified, it should be in SYS. COL $ form is modified.
3. Find the object Scott.test object ID from the data dictionary view all_Objects.
SQL> SELECT * from ALL_OBJECTS WHERE OWNER = 'Scott' and Object_name = 'Test'
4. Depending on the Id of the Scott.test object, the definition information of the column in the table is retrieved from Sys.col $.
SQL> Select Obj #, col #, name from sys.col $ where obj # = 13888;
Obj # col # name
---------- ---------- -------
13888 1 Empno
13888 2 ENAME
5. Use the UPDATE statement to modify.
Update sys.col $ set col # = 2, name = 'EMP_NO' WHERE OBJ # = 13888 and name = 'Empno';
Update sys.col $ set col # = 1, name = 'EMP_NAME' WHERE OBJ # = 13888 and name = 'ename';
COMMIT;
6. Restart the database service.
Since the data dictionary is loaded into SQL when the database is started, after modifying it, if you use "Select * from Scott.test;", it will find that it does not modify it. Therefore, after the modification is complete, you also need to restart the database service.
SQL> Shutdown
SQL> Startup
At this time, then view, it will be found that the modification has been successful.
SQL> SELECT * from scott.test;
EMP_NAME EMP_NO
---------- ------ Smith 7369
Allen 7499
Ward 7521
......
This method is directly modified in the database, there is a certain risk, but it is very useful for the table of the amount of data. Take advantage of the data dictionary function, often capable of completing its daily difficult work
