From Oracle11g onwards, we can include multi-byte characters like $, _, and #  in Oracle passwords without quoting. In earlier versions, we may have to use quotes while using these special characters in passwords, else it may throw error saying password is wrong. This feature needs to tested properly before …

In Oracle 11g, we have SQL Tuning Advisor which is run automatically against high resource consuming SQL statements during the maintenance windows. Lets see how to do this. 1. DBMS_AUTO_TASK_ADMIN package is used to enable and disable this Automatic SQL Tuning Advisor. To Enable the advisor SQL> BEGIN DBMS_AUTO_TASK_ADMIN.enable( client_name …

Password case sensitivity is a new Oracle 11g feature. This is an additional password complexity as well as a security feature. sec_case_sensitive_login is an initialization parameter which controls password case sensitivity. This is a dynamic parameter. Default value is TRUE ie, once your 11g upgrade from previous versions is complete …

ORA-29280: invalid directory path This is one common error we get while running PL/SQL procedures. Primary cause of this error is that parameter UTL_FILE_DIR is not specified in initialization file. UTL_FILE_DIR is an initialization parameter where one or more directories are set that Oracle uses for PL/SQL file I/O. This is …

There can be situations where we have mistakenly added datafiles to a tablespace and need to remove it. Prior to Oracle 10gR2, Oracle did not support dropping of datafiles. The only workaround was to take a export backup of the existing objects in the tablespace. Re-create the tablespace and import …

The ADDM accepts AWR snapshots as  input and provides various findings and recommentations to resolve the performance bottlenecks.   ADDM reports include : Top SQL Activities CPU bottlenecks Undersized memory allocations Excessive parsing I/O usage Concurrency issues Object contention RAC and application issues ADDM report can be generated by running addmrpt.sql …

Cause : ORA-28221 error occurs when an user tries to reset his own password without specifying the REPLACE keyword, provided the user does not have ALTER USER privilege and the user is having a profile with password verify function. Solution : Provide the current password along with the REPLACE clause …

Oracle initialization parameters which starts with an underscore (_) are called as hidden parameters. These are undocumented parameters. We cannot find them in V$PARAMETER or by using SHOW PARAMETER command. Manipulating these parameters will alter Oracle’s internal mechanisms. It is advised NOT to modify these parameters without the consent of …

Password change history of an Oracle user can be checked from the SYS tables USER$ and USER_HISTORY$.  SQL> Select name,password_date ” Password Changed date” from sys.user$ A ,sys.user_history$ B where A.user# = B.user# and A.name=’TEST_USER’ order by password_date; NAME                     Password Changed date ——————– ————————– TEST_USER                Jun 19 2010 09:06:27 TEST_USER                …

Prior to Oracle 10g, it was a tedious task to rename a tablespace. We had to follow a series of steps. Lets say, we need to rename tablespace TEST_1 to TEST_2 1. Create a tablespace TEST_2 2. Export all the objects from TEST_1 tablespace 3. Import the data to TEST_2 …

We know that Oracle consists of many default user accounts like SCOTT, HR etc. Till Oracle 10g, these accounts had a default password, and we had no option to find if the current password used were default ones or not.This caused a security risk if the accounts were not locked. …

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 …