The default password verification function provided by the Oracle which is kept in location $ORACLE_HOME/rdbms/admin/utlpwdmg.sql. If we have a newly upgraded 11g database, this script is not loaded automatically. Therefore, we have run this script manually for it to take effect. Till then the verify function of the previous version would be in …
Most of our Oracle applications are being upgraded to 11g version nowadays. At this time it is important to discuss some features in the Oracle 11g Default Profile: We know that any user created in a database needs to be assigned to a profile. If not, the user will be assigned …
INDEX RANGE SCAN An index range scan is used to access a selected range of data. In this case not only the index but also the data from actual table is also fetched. Here, Oracle traverses through the branches of the index tree to reach the leaf block where it …
The Query parameter of exp/expdp utility allows to export the subset of a table based on any condition. The value for the parameter should be specified as a string with a WHERE clause. Consider that we have to export data from employee table where salary > 5000. exp scott/tiger TABLES=employee …
We don’t have a single command in Oracle by which we can alter the name of a user. But, there is a workaround which serve the purpose. Say, we have to rename user A to B. Take an export of user A. Create the user B. Import the dump file taken in step …
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. …
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 …
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 …
Most of the times, we would have the export dump file in hand. And would be required to import it to another database or schema. But before actually importing, we would like to just have a look at the contents of the dump file, to make sure we are importing …
Managing UNDO Tablespace by properly setting the size of UNDO tablespace and UNDO retention is a very important DBA task. To avoid the head ache of doing this manually, AUTOMATIC UNDO MANAGEMENT can be turned on, so that Oracle itself would manage this task. To do this, initialization parameter UNDO_MANAGEMENT …
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 …
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 …
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 …
Being the DOCTOR of DATABASES, doing a routine checkup on your databases is mandatory. Here are a few steps and associated queries to check the health of your database. OS Level Checks 1. If the instance and associated background processes are up and running. $ps -ef |grep <instance_name> 2. If …
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 …
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 …