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

Materialized View (mview) in oracle

Materialized View (mview) in oracle
A materialized view is a replica of a target master from a single point in time. The master can be either a master table at a master site or a master materialized view at a materialized view site. Whereas in multimaster replication tables are continuously updated by other master sites, materialized views are updated from one or more masters through individual batch updates, known as a refreshes, from a single master site or master materialized view site.

How to create materialized view in oracle

Create materialized view refresh on demand with force method

CREATE MATERIALIZED VIEW [OWNER].[MVIEW_NAME]
TABLESPACE
NOCACHE
LOGGING
NOPARALLEL
BUILD IMMEDIATE
REFRESH FORCE
WITH ROWID
AS
SELECT * FROM ;

Create materialized view refresh on demand with force method and create schedule job for refresh materialized view.

CREATE MATERIALIZED VIEW [OWNER].[MVIEW_NAME]
TABLESPACE
NOCACHE
LOGGING
NOPARALLEL
BUILD IMMEDIATE
REFRESH FORCE
START WITH TO_DATE('21-04-2009 00:00:00','dd-mm-yyyy hh24:mi:ss')
NEXT TRUNC(SYSDATE+1,'DD')+0/24+00/1440
WITH ROWID
AS
SELECT * FROM ;

Create materialized view refresh on commit with force method

CREATE MATERIALIZED VIEW [OWNER].[MVIEW_NAME]
TABLESPACE
NOCACHE
LOGGING
NOPARALLEL
BUILD IMMEDIATE
REFRESH FORCE ON COMMIT
WITH ROWID
AS
SELECT * FROM ;

Create materialized view refresh on demand with fast method

1. Create materialized view log

CREATE MATERIALIZED VIEW LOG ON [OWNER].[MVIEW_NAME]
TABLESPACE
WITH ROWID;

2. Create materialized view with fast method

CREATE MATERIALIZED VIEW [OWNER].[MVIEW_NAME]
TABLESPACE
NOCACHE
LOGGING
NOPARALLEL
BUILD IMMEDIATE
REFRESH FAST
WITH ROWID
AS
SELECT * FROM ;

How to refresh materialized view on demand

Refresh mview on demand method have 3 clauses
1. FAST Clause
Specify FAST to indicate the incremental refresh method, which performs the refresh according to the changes that have occurred to the master tables. The changes for conventional DML changes are stored in the materialized view log associated with the master table.The changes for direct-path INSERT operations are stored in the direct loader log.
2. COMPLETE Clause
Specify COMPLETE to indicate the complete refresh method, which is implemented by executing the defining query of the materialized view. If you request a complete refresh, then Oracle Database performs a complete refresh even if a fast refresh is possible.
3. FORCE Clause
Specify FORCE to indicate that when a refresh occurs, Oracle Database will perform a fast refresh if one is possible or a complete refresh otherwise. If you do not specify a refresh method (FAST, COMPLETE, or FORCE), then FORCE is the default.

Refresh mview command

EXEC DBMS_MVIEW.REFRESH('[OWNER].[MVIEW_NAME]','FAST');

EXEC DBMS_MVIEW.REFRESH('[OWNER].[MVIEW_NAME]','COMPLETE');

EXEC DBMS_MVIEW.REFRESH('[OWNER].[MVIEW_NAME]','FORCE');


How to alter refresh method from force to on commit
1. Execute an on-demand refresh on the materialized view to synchronize the data between the materialized view and the detail tables
EXEC DBMS_MVIEW.REFRESH('[OWNER].[MVIEW_NAME]','FORCE');

2. Alter mview from on-demand method to on-commit
ALTER MATERIALIZED VIEW . REFRESH ON COMMIT;

Oracle Tips : How to rename oracle database name (DBNAME) and SID

Oracle Tips : How to rename oracle database
Rename SID and DBNAME (Not change DBID)
Example case -- > I want to rename sid from OLD to NEW

1. Shutdown database and startup on mount mode
SQL> shutdown immediate
SQL> startup mount

2. If use spfile should create pfile from spfile
SQL> create pfile from spfile;

3. Use DBNEWID Utility for set new database name (Use on OS prompt)
% nid target=sys/ dbname= setname=Y

4. Edit parameter db_name and add parameter instance_name in pfile
*.db_name='NEW'
*.instance_name='NEW'

5. Rename on prameter file from "initOLD.ora" to "initNEW.ora"

6. Create new password file
% orapwd file=orapw password=password entries=5

7. Set ORACLE_SID = NEW
% export ORACLE_SID= --> Unix
> set ORACLE_SID= --> Windows

8. Edit database name in oratab file
oratab file on solaris in /var/opt/oracle/oratab
on AIX and Linux /etc/oratab
:/oracle/product/10.2.0:N

9. Startup database in open mode (No resetlogs)
SQL> startup pfile=init.ora

10. Create spfile from pfile
SQL> create spfile from pfile;

11. Verify dbname and instance name
SQL> select name from v$database;
SQL> select instance_name from v$instance;