Active Session History (ASH) is a very important feature to analyze sessions in Oracle to troubleshoot transient problems that last for just few minutes. ASH reports gives the details needed to analyze the sessions. Below example shows how to generate ASH(Active Session History) reports from SQLPLUS. The same can be done using …
DBMS_JOB package is used to manage and schedule job in Oracle. Given below is a simple example with detailed steps which illustrates DBMS_JOB. The requirement is to insert employee details into the table EMPLOYEES. When the job is invoked the details would be inserted. The details needs to passed as …
Join is a method used to combine two or more tables,views or materialized views based on a common condition. There is wide variety of classification. Below given in one such classification. Different types of Joins 1. Cross Join/Cartesian Product 2. Natural Join 3. Inner Join 4. Outer join a.Left Outer …
Among the many enhancements in Oracle 11g, very important one is the default auditing.In Oracle 11g, parameter audit_trail is set to the value DB by default. And many privileges audited by access are enabled by default.See the list below. ALTER ANY PROCEDURE ALTER ANY TABLE ALTER DATABASE ALTER PROFILE AUDIT ROLE …
Temporary tablepspace is a very dynamic tablespace which gets filled up quickly, due to SQL operations that inludes sorting like CREATE INDEX, ANALYZE, Select DISTINCT, ORDER BY, GROUP BY, UNION, INTERSECT, MINUS. Though this tablespace is not backed up or used for recovery, this is a very important tablespace for …
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. …