Rename Statement Oracle

---COLUMN 
alter table category rename COLUMN  Old_COL  to New_COL 

--- Table
alter table category_old rename to category_new;
or
RENAME  old_tbl TO new_tbl; 

 Drop

alter table receipt_detail drop constraint receipt_detail_itemcode_fk cascade 

alter table receipt_detail drop constraint receipt_detail_pk cascade
/ 

user


select user#, name from user$;
update sys.user$ set name='QUIUM1' where user#=96 and name='TEST';

You can rename a user but it is not supported by Oracle.

To properly rename the user, follow this guide:

1. login sqlplus using sys user as sysdba


D:\>sqlplus /nolog

SQL*Plus: Release 10.1.0.2.0 - Production on Fri Jun 13 16:59:05 2008

Copyright (c) 1982, 2004, Oracle. All rights reserved.

SQL>conn sys/oracle as sysdba
Connected.



2. select the user id and which name from user$


SQL>select user#, name from user$;

USER# NAME
---------- ------------------------------
0 SYS
1 PUBLIC
2 CONNECT
3 RESOURCE
4 DBA
5 SYSTEM
...........
...........
64 DEMO
...........
...........

70 rows selected.



3. Change your old user name into new user name. Be careful when u update user$


---update user$ set name=''; where user#=;

SQL>update user$ set name='SHOW' where user#=64;

1 row updated.

SQL>commit;

Commit complete.




4. Shutdown the database and start up, or restart database service.


SQL>shutdown immediate;
Database closed.
Database dismounted.
ORACLE instance shut down.

SQL>startup
ORACLE instance started.

Total System Global Area 171966464 bytes
Fixed Size 787988 bytes
Variable Size 145488364 bytes
Database Buffers 25165824 bytes
Redo Buffers 524288 bytes
Database mounted.
Database opened.
SQL>



5. alter the renamed user password using system user or sys or dba user user.


SQL>alter user SHOW identified by SHOW;

User altered.



6. Now try to connect database with renamed user and its password


SQL>conn SHOW/SHOW
Connected.
SQL>exit

Question: I need to re-name a schema owner user that has 100 tables and 150 indexes.  How can I issue the "alter user xxx rename " command in Oracle?  There appears to be no rename command for an Oracle user.

Answer:  There is no supported method for renaming a user in Oracle.  Here is a  supported method for cloning an Oracle user ID. 

In an Oracle schema, user user ID is the anchor, and although that there is a dictionary "hack" that claims to successfully re-name an Oracle user, it is better to export/import data pump to rename an Oracle user ID.  The "hack" performs an "alter user" by going into the dictionary DBA view source (sys.user$).

NOTE: This command below is NOT a supported method for renaming a user un Oracle.  It does not account for ownership of PL/SQL, references from other schemas (such as database links), security , TNS connectivity and other internal consistency mechanisms:

update
   sys.user$
set
   name='new_name'
where
   user#=N
and
   name='old_name';

  

 ---rename column name 

 ---alter table sales rename column OLD_column to NEW_column;
 SQL> desc sales

Name             Null?    Type
---------------- -------- ---------------
STORE_KEY                 VARCHAR2(4)
BOOK_KEY                  VARCHAR2(6)
ORDER_NUMBER              VARCHAR2(20)
ORDER_DATE                DATE

SQL> alter table sales rename column order_date to date_of_order;

SQL> desc sales

Name             Null?    Type
---------------- -------- ---------------
STORE_KEY                 VARCHAR2(4)
BOOK_KEY                  VARCHAR2(6)
ORDER_NUMBER              VARCHAR2(20)
DATE_OF_ORDER             DATE


 ---rename table name
 --RENAME OLD_table  TO New_table;;

 SQL> select * from tab;

TNAME                          TABTYPE  CLUSTERID
------------------------------ ------- ----------
COUNTRIES                      TABLE
DEPARTMENTS                    TABLE

11 rows selected.

SQL> RENAME COUNTRIES  TO CONT;

Table renamed.

SQL> select * from tab;

TNAME                          TABTYPE  CLUSTERID
------------------------------ ------- ----------
CONT                           TABLE
DEPARTMENTS                    TABLE

11 rows selected.

SQL> RENAME CONT  TO COUNTRIES;

Table renamed.