Oracle Tips : How to change an Oracle password temporarily

Oracle Tips : How to change an Oracle password temporarily

Example case information :
- Username : TEST

In Oracle it is possible to change a password temporarily.
This can be useful for DBA which act as a different user.

In 9i ,10g
SQL> SELECT USERNAME,PASSWORD FROM DBA_USERS WHERE USERNAME='TEST';

SQL> SELECT USERNAME,PASSWORD FROM SYS.USER$ WHERE USERNAME='TEST';

USERNAME PASSWORD
TEST F894844C34402B67

SQL> ALTER USER TEST IDENTIFIED BY password1234;

Now login with the following credentials: scott/tigerAfter doing your work you can change the password back by using an undocumented feature called "by values"

SQL> ALTER USER TEST IDENTIFIED BY VALUES 'F894844C34402B67';

Oracle Tips : How you know password hashkey in 11g

Oracle Tips : How you know password hashkey in 11g

In 9i 10g we can know password hashkey via dba_users

SQL> SELECT USERNAME,PASSWORD FROM DBA_USERS;

But in 11g the password hash is no longer accessible via dba_users
But we can show password hashkey via sys.user$

Connect to sys as sysdba
and use this query

SQL> SELECT NAME,PASSWORD FROM SYS.USER$;

Oracle Tips : How to rename or relocation(move) datafiles

Oracle Tips : How to rename or relocation(move) datafiles.

Example case information :
- Move datafile /oracle/oradata/test01.dbf to /oracle/oradata_1/test01.dbf
- Tablespace name is 'TEST'

To rename datafiles, complete the following steps:

Take the tablespace that contains the datafiles offline. The database must be open mode.

SQL> ALTER TABLESPACE TEST OFFLINE;

Rename the datafiles using the operating system command.

cp -p /oracle/oradata/test01.dbf /oracle/oradata_1/test01.dbf

Use the ALTER TABLESPACE statement with the RENAME DATAFILE clause to change the filenames within the database.

SQL> ALTER DATABASE RENAME FILE '/oracle/oradata/test01.dbf' TO '/oracle/oradata_1/test01.dbf';

Take the tablespace that contains the datafiles online.

SQL> ALTER TABLESPACE TEST ONLINE;

Verify location and datafiles name.

SQL> SELECT FILE_NAME FROM DBA_DATA_FILES WHERE TABLESPACE_NAME = 'TEST';