Performance

How to Find Most Expensive SQL

Posted on
With a demanding database, you may want to know how to find out the most expensive sqls. Running on Oracle, we have Cost-Based Optimizer, the optimizer will choose the lowest cost of sql plan to run. So, if we could list the highest optimizer cost, we can map them to the most consuming sql statements. […]
Unix

How to Monitor Unix Memory Usage by Processes

Posted on
Monitor Memory Usage In Unix, you have many tools to monitor the overall memory usage, such as vmstat, sar, top and free. Using vmstat Command you can monitor the field free to see how much memory left now, for example: $ vmstat 2 5  procs     memory            page            disk          faults      cpu  r b w   swap  free  […]
User and Schema

What is the Difference Between SYSDBA and SYSOPER Privileges

Posted on
SYSDBA vs SYSOPER According to Oracle documentation about administrative accounts and privileges, I compared their privileges between SYSDBA and SYSOPER as below: Allowed Actions SYSDBA SYSOPER Perform STARTUP and SHUTDOWN operations ALTER DATABASE: open, mount, back up, or change character set Only open, mount and back up CREATE DATABASE DROP DATABASE CREATE SPFILE ALTER DATABASE […]
User and Schema

Who has SYSDBA Privilege?

Posted on
SYSDBA Privilege SYSDBA is a system privilege. By default, only SYS has the privilege, but it can be changed by granting to other user. Although it’s a system privilege, and very special, you will hardly find any other user has SYSDBA privilege by looking up dictionary view, so we also call it administrative privilege. Similarly, […]
PFILE and SPFILE

Why SPFILE Does Not Work in RAC

Posted on
Shared SPFILE You might have noticed that the newly created SPFILE for a RAC database was not taking effective after restarts. And I think you might create the SPFILE in the wrong places. Oracle 11g RAC stores all the shared files in ASM, including control files, SPFILE, data files. Thus, it starts all the instances […]
RMAN

How to Set NLS_DATE_FORMAT in RMAN

Posted on
After you set RMAN log location, the next problem that you want to solve is the format of timestamp in the log, which was too coarse to judge the actual time like this: 08-OCT-12 Set NLS_DATE_FORMAT for RMAN If you are using sqlplus, you can ALTER SESSION SET NLS_DATE_FORMAT to modify the date format, but […]