Use SQL Query below at Grid sysman database to locate growth of tablespaces over time. The returned results data could be used under EXCEL to generate graphs. Read more…
I have often used following procedure to create list of roles & corresponding users mapped into it. This was then used to email business owners as well as for user accesss auditing. Read more…
Create .run_awr with following details :
"TNS-connect-string : recipient-list : hrs of AWR snapshot" [oracle@ ~]$ cat .run_awr prod:email@example.com:11 I added this script in my crontab for a daily emails:
I have One Primary database and 2 Physical Standby Databases under RHEL 5. Here is a process to patch them with JULY CPU update “Critical Patch Update Release 10.2.0.4 for UNIX Released July 13, 2010″
The following represents the Oracle recommended method for automating database startup and shutdown of Oracle 10g instances.
Count of archive files and size of the redo generated by day
SELECT A.*, ROUND (A.Count# * B.AVG# / 1024 / 1024) Daily_Avg_Mb FROM ( SELECT TO_CHAR (First_Time, 'YYYY-MM-DD') DAY, COUNT (1) Count#, MIN (RECID) Min#, MAX (RECID) Max# FROM v$log_history GROUP BY TO_CHAR (First_Time, 'YYYY-MM-DD') ORDER BY 1 DESC) A, (SELECT AVG (BYTES) AVG#, COUNT (1) Count#, MAX (BYTES) Max_Bytes, MIN (BYTES) Min_Bytes FROM v$log) B;
Patch is an Oracle supplied utility to assist you with the process of applying interim patches to Oracle’s software. OPatch is a Java-based utility which requires the Oracle Universal Installer to be installed. It is platform independent and runs on all supported operating systems. Read more…
My Statspack has following details.
Enqueue activity DB/Inst: Snaps: 1791-1808
-> only enqueues with waits are shown
-> Enqueue stats gathered prior to 10g should not be compared with 10g data
-> ordered by Wait Time desc, Waits desc
One of my 10.2.0.1 database was hanging despite allocating ample opf resources. The CPU, Memory,Disk accesses were normal but under heavy load server would freeze for no valid reason. I had to raise TAR with Oracle.
Oracle 10g’s DBMS_SCHEDULER provides significant improvements over DBMS_JOB for scheduling jobs and tasks. Let’s take an overview of new functionalities that the Oracle Scheduler provides for DBAs.
One can use UTL_FILE else OS editors/utilities. The best is using undocumented KSDWRT procedure of the DBMS_SYSTEM package.
|8||RDBMS Version Number|
|1||New Features Release Number|
|5||Maintenance Release Number|
|1||Generic Patch Set Number|
|2||Platform Patch Set Number|
I was trying to execute following SQL through client side tools and found network delays were contributing to the SQl execution times. This SQL normally returns in less than 50 millisec and now taking more than 700 Msec. I am addicted to TOAD -TORA GUI created Execution plans and was looking for a an easy way to get execution times as well as plans on sqlplus. Here is how you do it .
Displays partition information for the specified index, or all indexes. Read more…
undo_retention parameter will not guarantee a RETENTION unless you define a RETENTION GUARANTEE CLAUSE on tablespace level Read more…
It’s a tricky question. I have explained here by using a 33GB SAP BW driving table called VBAP which needed a major work. At end I had to rebuild this table using Quest Shareplex.
where substr(ksppinm,1,1) = ‘_’;
Applies to: Oracle Server – Enterprise Edition – Version: 184.108.40.206.0
This problem can occur on any platform.
When NLS_LANGUAGE is set to ENGLISH and NLS_TERRITORY is set to AUSTRALIA, the text related to Archival messages are not displayed. The following contents are shown in the Alert log :
Oracle Flashback Query allows users to see a consistent view of the database as it was at a point in the past. This functionality allows comparative reporting over time and recovery from logical corruptions.
Here is a situation, This afternoon I have seen my CPU hitting through the roof on a Live system. There are about 6 Oracle instances and one is chewing more than 90% of CPU time.
1. Enable trace at instance level
Put the following line in init.ora. It will enable trace for all sessions and the background
Reason : Active x’actions running on the table space
Locate SQL_TEXT and Session Address running alter Tablspace Command Read more…
repA>SELECT LOG_MODE FROM SYS.V$DATABASE;
repA> alter system set log_archive_dest_1=’location=/opt/oracle/oraarchive/repA/’