How to change the database username(i.e SCHEMA name)

If you want to modify a schema's name,you should have the privileged on USER$

1. Get Id of a schema's name

SQL> select user#,NAME from SYS.user$ WHERE NAME='E';

     USER# NAME
---------- ------------------------------
       104 E

Let's check what object has schema contains..so that after the change of the name of the schema,we can confirm,we have sucessfully did the changes by having every objects of the old schemaname.

2. modify the schema's name
 
SQL> UPDATE USER$ SET NAME='NEW_SCHEMA_NAME' WHERE USER#=104;

SQL> UPDATE USER$ SET NAME='F' WHERE USER#=104;

 

3. Shutdown the database and restart the database


 

4. Check whether the schema name has changed or not.

SQL> select user#,NAME from SYS.user$ WHERE user#=104;

     USER# NAME
---------- ------------------------------
       104 F

 5. Change the password of newly created schemname.

Alter user F identified by f;

6. Check whether new schema contains the old schema objects or not.

 
Done !!!

References

http://stackoverflow.com/questions/18730850/how-to-change-schema-name

How to Instal SQLTXPLAIN (SQLT)

SQLTXPLAIN (SQLT)->  Tool that helps to diagnose SQL statements performing poorly.

Download SQLTXPLAIN (SQLT)

10.2, 11.1, 11.2 and 12.1 download
9.2 and 10.1 download

Installation Steps:
********************

After downloading the SQLTXPLAN from the above link..please follow the below steps to install.

SQLT, also known as SQLTXPLAIN is a tool provided by Oracle Server Technologies Center of Expertise that can be used to diagnose why a particular SQL statement is performing poorly.  It is not like AWR or Statspack which provide a system-wide view of performance.  SQLT is very focused and works on one SQL statement at a time.  SQLT inputs one SQL statement and outputs a set of diagnostics files. These files can be used to diagnose SQL statements performing poorly.  In this blog, I will give an overview of SQLT and how to get started with it. 


Installing SQLT SQLT installs under its own schemas SQLTXPLAIN and SQLTXADMIN. It does not install any objects into the application schema(s). You can install this version of SQLT in Oracle databases 10.2, 11.1, 11.2 and higher, on UNIX, Linux or Windows platforms.
Installation steps:

1. Uninstall a prior version (optional).
This optional step removes all obsolete SQLTXPLAIN/SQLTXADMIN schema objects and prepares the environment for a fresh install. Skip this step if you want to preserve the existing content of the SQLT repository (recommended).



# cd sqlt/install
# sqlplus / as sysdba
SQL> START sqdrop.sql

2. Execute installation script sqlt/install/sqcreate.sql connected as SYS.

# cd sqlt/install
# sqlplus / as sysdba
SQL> START sqcreate.sql

 

 

 

>> Download SQLT tool from here: Doc ID 215187.1
>> Install SQLT as per SQLT Usage Instructions: Doc ID 1614107.1
>> Identify your SQL ID as per doc How to Determine the SQL_ID for a SQL Statement ( Doc ID 1627387.1 ) <<<<<<<<<<<< identify your query as per document
>> Execute sqlt XTRXEC method for your SQL_ID


Quick example of usage:
# cd sqlt/run
# sqlplus apps
SQL> START sqltxtrxec.sql [SQL_ID]|[HASH_VALUE] [sqltxplain_password]
SQL> START sqltxtrxec.sql 0w6uydn50g8cx sqltxplain_password
SQL> START sqltxtrxec.sql 2524255098 sqltxplain_password 
Please refer for more:

https://support.oracle.com/epmos/faces/DocumentDisplay?_afrLoop=373648185377378&id=1627387.1&_afrWindowMode=0&_adf.ctrl-state=ng46hiduj_641

https://support.oracle.com/epmos/faces/DocumentDisplay?_afrLoop=373651036195714&id=1614107.1&_afrWindowMode=0&_adf.ctrl-state=ng46hiduj_682

https://support.oracle.com/epmos/faces/DocumentDisplay?_afrLoop=373653549527768&id=215187.1&_afrWindowMode=0&_adf.ctrl-state=ng46hiduj_731

http://www.dbspecialists.com/use-sqlt-to-diagnose-difficult-sql-performance-issues/