Useful UNIX Commands For Oracle DBAs

Basic knowledge in UNIX is very essential for any DBA. Given below are some few useful UNIX commands for Oracle DBAs in their daily work. 1.  To Cleanup trace files more than n days old $ find $DBA/$ORACLE_SID/bdump/*.trc -mtime +n -exec rm {} \; 2. To get the value of all environment variables. …

How To Resolve Database Blocking In Oracle ?

Database blocking is a situation where the statement run by one user locks a record or set of records and another statement run by the same user or different user requires a conflicting lock type on the record or records, locked by the first user. Database blocking issue is a …

Why V$PARAMETER And V$SPPARAMETER Gives Different Parameter Values?

We may sometimes face a situation where we get two different values for the same initialization  parameter from the views v$PARAMETER and V$SPPARAMETER. Lets see an example here: The parameter processes gives two different values 150 and 200 when queried from v$PARAMETER and V$SPPARAMETER respectively. SQL> show parameter processes NAME                    …

What Is Row Migration And Row Chaining?

Row migration and row chaining are two known problems in Oracle that can degrade the database performance. Row migration refers to the scenario where an entire row moves from one block to another, as it becomes unfit to the present block. Usually UPDATE statements results in row migration. Let’s see …

How To Compare Two AWR Reports?

Often we generate AWR reports for performance tuning our system. Mostly we have a baseline AWR when the system was performing fine and an AWR when the system was showing degraded performance. Comparing these two reports would give us an idea of where the problem could be and we can …

What Is DUAL Table In Oracle?

Most of us might have at least once queried the DUAL table of our Oracle database. But, what is this DUAL table? Who owns it? What privilege is required to select from dual table?  Well . This article explains the concept of dual table. Dual is a one row table …

How To Enable Automatic Memory Management (AMM) in Oracle 11g?

Oracle has taken a step ahead in making the memory management simple by introducing Automatic Memory Management (AMM) in 11g , by which Oracle dynamically manages both SGA and PGA. MEMORY_TARGET and MEMORY_MAX_TARGET are the two initialization parameters related to AMM. MEMORY_TARGET refers to the shared memory available to Oracle …

Not Able To Truncate Table – Why? What to do then?

It is a strange fact that Oracle will not permit a user to truncate a table even after having “ Delete any table “ privilege. The privilege required to truncate a table is Drop any table or Drop on that particular table . With respect to the security perspective it …

How To Move OR Rename An Oracle Datafile?

How to move a datafile from one location to another in Oracle: At times , to fix the space crunch in a filesystem or to follow the filesystem standards or proper naming conventions , a DBA might have to move/rename an oracle datafile. The datafile movement can be performed without …

15 Basic SQL Queries For A Beginner DBA

SQL Queries for Dummies! Yes – Some of the basic Oracle queries/commands which might turn out extremely helpful to the beginners of Oracle database. 1. To check the name of the database: SQL> select name from v$database; 2. To check the version of a database: SQL> select * from v$version; 3. …

How To Make a Table READ ONLY in Oracle 11g?

Read Only tables in Oracle 11g: Read-only table is a  good enhancement introduced in Oracle 11g , using which we can safeguard the tables from DML operations. This new feature helps to switch a table to READ ONLY mode by executing a single ALTER TABLE command. SQL > Alter table table_name …