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';

Dataguard : Switch over step

Dataguard : Switch over step

1. Initiate the switchover operation on the primary database (On the original primary database)

- Verify database role

SQL> SELECT DATABASE_ROLE FROM V$DATABASE;

DATABASE_ROLE
----------------
PRIMARY

- Switch database role from primary to standby database

SQL> ALTER DATABASE COMMIT TO SWITCHOVER TO PHYSICAL STANDBY WITH SESSION SHUTDOWN WAIT;

Database altered.

- Shutdown database

SQL> SHUTDOWN IMMEDIATE

- Startup database

SQL> STARTUP

- Verify database role

SQL> SELECT DATABASE_ROLE FROM V$DATABASE;

DATABASE_ROLE
----------------
PHYSICAL STANDBY

- Start apply log process for standby database

SQL> ALTER DATABASE RECOVER MANAGE STANDBY DATABASE DISCONNECT;

2. Switch standby database to primary database (On the original standby database)
- Verify database role

SQL> SELECT DATABASE_ROLE FROM V$DATABASE;

DATABASE_ROLE
----------------
PHYSICAL STANDBY

- Switch database role from standby to primary database

SQL> ALTER DATABASE COMMIT TO SWITCHOVER TO PRIMARY;

Database altered.

- Shutdown database

SQL> SHUTDOWN IMMEDIATE

- Startup database

SQL> STARTUP

- Verify database role

SQL> SELECT DATABASE_ROLE FROM V$DATABASE;

DATABASE_ROLE
----------------
PRIMARY

Oracle Tips : How to use Oracle Function-Based Index (Example)

Oracle Tips : How to use Oracle Function-Based Index (Example)


Create test table for testing Function-Based Index


CREATE TABLE TEST_TABLE (
ID NUMBER(10) NOT NULL,
FNAME VARCHAR2(250) NOT NULL
);


Insert sample data into test table


INSERT INTO TEST_TABLE VALUES (1,'Ant');
INSERT INTO TEST_TABLE VALUES (2,'Ball');
INSERT INTO TEST_TABLE VALUES (3,'Cat');
INSERT INTO TEST_TABLE VALUES (4,'Dog');
INSERT INTO TEST_TABLE VALUES (5,'Egg');
INSERT INTO TEST_TABLE VALUES (6,'Fan');
COMMIT;

Create a regular index on the FNAME column


CREATE INDEX TEST_TABLE_FNAME_IDX ON TEST_TABLE (FNAME);

Check by Execution Plan . Index is still not used.


SET AUTOTRACE ON;
SELECT ID,FNAME
FROM TEST_TABLE
WHERE UPPER(FNAME) = 'DOG';



----------------------------------------
Id Operation Name
----------------------------------------
0 SELECT STATEMENT
* 1 TABLE ACCESS FULL TEST_TABLE
----------------------------------------

Drop and create function-based index on the FNAME column


DROP INDEX TEST_TABLE_FNAME_IDX;
CREATE INDEX TEST_TABLE_FNAME_IDX ON TEST_TABLE (UPPER(FNAME));

These two alter session queries ensures that the new index is used


ALTER SESSION SET QUERY_REWRITE_INTEGRITY = TRUSTED;
ALTER SESSION SET QUERY_REWRITE_ENABLED = TRUE;


SET AUTOTRACE ON;
SELECT ID,FNAME
FROM TEST_TABLE
WHERE UPPER(FNAME) = 'DOG';

------------------------------------------------------------------------------------
Id Operation Name Rows Bytes Cost
------------------------------------------------------------------------------------
0 SELECT STATEMENT 1 8 2
1 TABLE ACCESS BY INDEX ROWID TEST_TABLE 1 8 2
* 2 INDEX RANGE SCAN TEST_TABLE_FNAME_IDX 1 1
------------------------------------------------------------------------------------

Oracle Tips : How you know memory component size when use automatic storage memory managenent(ASMM)

Oracle Tips : How you know memory component size when use automatic storage memory managenent(ASMM)

Oracle Database 10g includes the Automatic Shared Memory Management feature which simplifies the SGA memory management significantly.

When automatic SGA memory management is enabled, the sizes of the different SGA components are flexible and can adapt to the needs of a workload without requiring any additional configuration. The database automatically distributes the available memory among the various components as required, allowing the system to maximize the use of all available SGA memory.

DBA can check all memory component size with dynamic view V$SGA_DYNAMIC_COMPONENT

Oracle Tips : How to change database character set

Oracle Tips : How to change database character set

This article gives a overview of methods to change the database character set .

1. Check NLS_CHARACTERSET
Connect to user SYS or SYSTEM

SELECT *
FROM V$NLS_PARAMETERS
WHERE PARAMETER = 'NLS_CHARACTERSET';

Parameter Value
NLS_CHARACTERSET AL32UTF8

If you want to change NLS_CHARACTERSET from AL32UTF8 to TH8TISASCII

2. Connect to user SYS

3. Update value

UPDATE PROPS$
SET VALUE$ = 'TH8TISASCII'
WHERE NAME = 'NLS_CHARACTERSET';

COMMIT;

4. Restart database

SHUTDOWN IMMEDIATE;

STARTUP;

5. Check NLS_CHARACTERSET
Connect to user SYS or SYSTEM

SELECT * FROM
V$NLS_PARAMETERS
WHERE PARAMETER = 'NLS_CHARACTERSET';

Parameter Value
NLS_CHARACTERSET TH8TISASCII