Configuring new disks for ASM

RAC Speedup

If you want to make the query response speed up, then alter table with parallel option

alter table tablename parallel;

VIP Concept

1- The system shown here is 2 node RAC

Create and configure a listener

The listener forwards client requests to supported services. These services can be configured statically in the listener.ora file or they can be dynamically registered with the listener. This dynamic registration feature is called service registration. The registration is performed by the PMON process.

Create and manage multiple network configuration files

LISTENER.ORA

If you have a server that is running multiple versions of Oracle software (multiple Oracle homes, each home has one or more database instances). You can use multiple listener.ora files.

Transparent Data Encryption (TDE) & LogMiner

1- Change in sqlnet.ora
######################SQLNET.ORA###############################

NAMES.DIRECTORY_PATH=(TNSNAMES, ONAMES, HOSTNAME)
SQLNET.EXPIRE_TIME= 10
sqlnet.authentication_services= (NTS)

Create and Manage a tablespace that uses NFS mounted file system file

Direct NFS
Direct NFS is a new feature introduced with Oracle 11g and  is an optimized NFS (Network File System) client that provides faster and more scalable access to NFS storage located on NAS storage devices (accessible over TCP/IP). Direct NFS is built directly into the database kernel - just like ASM which is mainly used when using DAS or SAN storage.

Create and manage bigfile tablespaces

Bigfile Tablespaces
- a tablespace with a single, but very large (up to 4G blocks) datafile.
-A bigfile tablespace with 8K blocks can contain a 32 terabyte datafile.A bigfile tablespace with 32K blocks can contain a 128 terabyte datafile.

Create and manage database configuration files

1- PFILE

Related Topic: Click here to view
Related Topic: Click here to view

Write alert to alert log

execute sys.dbms_system.ksdwrt(2,to_char(sysdate)|| ' Test alert ');

Configure the database environment to support optimal data access performance

The Symptoms and the Problems
A common pitfall in performance tuning is to mistake the symptoms of a problem for the actual problem itself. It is important to recognize that many performance statistics indicate the symptoms, and that identifying the symptom is not sufficient data to implement a remedy. For example:

Stripe data files across multiple physical devices and locations

Goal of striping data is simple:-
To avoid I/O bottlenecks during parallel processing, all tablespaces  accessed by parallel operations should be striped.  Many current OS's support disk striping hence DBA's need not stripe data  across the disk.

Create and manage temporary, permanent, and undo tablespaces

Using Multiple Tablespaces
Using multiple tablespaces allows you more flexibility in performing database operations.Some operating systems set a limit on the number of files that can be open simultaneously. Such limits can affect the number of tablespaces that can be simultaneously online. To avoid exceeding your operating system limit, plan your tablespaces efficiently.

Determine and set sizing parameters for database structures

You can determine/set parameter sizes using Enterprise Manager's Server Tab-> Storage section. This is first section of configuration of database structure, and second is parameters form parameter file.

Create the database

Note: Practice performed on 11g Rel 1 (APPS DB)

Considerations Before Creating the Database
Database Planning Tasks
- Plan the database tables and indexes and estimate the amount of space they will require.
- Plan the layout of the underlying operating system files your database will comprise. To greatly simplify this planning task, consider using Oracle
Managed Files and Automatic Storage Management to create and manage the operating system files that comprise your database storage.

Error: Procedure entry point longjmp could not be located in dynamic link library orauts.dll

If you get "Procedure entry point longjmp could not be located in dynamic link library orauts.dll" while connecting the SQL Plus, your environment variables are not set properly.

Spell the numbers - Examples

Using JSP format

SELECT TO_CHAR(TO_DATE(123.50,'J'),'JSP') to_words FROM   dual;

SELECT    TO_CHAR (TO_DATE (TRUNC (&num), 'J'), 'JSP')
       || ' Point '
       || TO_CHAR (TO_DATE (TO_NUMBER (SUBSTR (&num, INSTR (&num, '.') + 1)),'J'),'JSP')
  FROM DUAL;

Collect Full DML Statements for specific user without AUDIT

Use a logon trigger for the specific user to enable SQL tracing : it  generates a  trace file in the USER_DUMP_DEST location.The trace file contains all the SQL statements executed by the user after logon.
Create the following trigger in SYS schema:

create or replace trigger SCOTTLOG
after logon
on scott.schema
begin
   -- optionally add a tag to the trace file name to make the files discernable:
   execute immediate ' ALTER SESSION SET TRACEFILE_IDENTIFIER =''SCOTT''';
   -- enable sql_trace for this session:
   execute immediate 'ALTER SESSION SET SQL_TRACE TRUE';
end;
/

Note that all the SQLs of SCOTT user after logon will be traced and if the required collection is sufficient, disable the trigger to stop the collection and generation of trace files.

SQL> alter trigger scottlog disable;

Note
 i) Overhead involved is trace file generation in user_dump_dest.
 ii) Grant the CREATE SESSION system privilege to the user account rather than granting the CONNECT role. This is necessary  because in the versions older than 10gR2 the CONNECT role includes the ALTER SESSION system privilege and the user can disable sql tracing after logon. To avoid this, do not grant the CONNECT and RESOURCE roles , but grant the necessary privileges explicitly.

Ref: 309798.1

How to check if the IO of the Database is Slow

We will outline some of the thresholds whereby RDBMS Support may consider IO to be slow and thus a potential reason for a performance problem. If the underlying cause for slow performance is found to be a result of slow IO at the OS level, then the appropriate vendor responsible for the IO subsystem (hardware and software) should be engaged to diagnose and correct the situation

How To Configure Anti-Virus On Windows Server Running Oracle Database

When an Anti-virus performs a scan on a file it holds a lock on it. This lock interrupts the normal functioning of the database. To prevent any disaster situation such as database crash/hang, we recommend the following files to be excluded from online anti-virus scanning.

DCD & TCP Keep Alive (Windows)

KNOWN PROBLEMS OR LIMITATIONS  for DCD
1- Of the few reported problems, perhaps the most significant is DCD's poor performance on Windows NT. Dead connections are cleaned up only when the server is rebooted and the database is restarted. Exactly how well DCD works on NT depends on the client's proto implementation.

All Users with Client side Info

Create Or Replace Force View Sys.Av_users_all (Logon_time,
                                               Sid,
                                               Serial#,

Calling External Procedure (DLL) from PL/SQL

If Oracle has been configured for the external procedures , you can call any DLL from your PL/SQL. How to configure for EXTPROC please click on the link below

Configuring SQL*Net for External Procedures

External procedures are functions written in a third-generation language  
(3GL) such as C, and callable from within PL/SQL or SQL as if they were a 
PL/SQL procedure or function.  External procedures enable you to take  
advantage of the strengths and capabilities of a 3GL programming language  
in a PL/SQL environment.

Restricting user to change the password

If for some reason you want to restrict the user to change the password ,

Use the event trigger AFTER ALTER with the attribute function ora_des_encrypted_password 

specific for ALTER USER events:

CREATE or REPLACE TRIGGER pass_change 
    AFTER ALTER on database
          BEGIN
               IF ora_sysevent='ALTER' and ora_dict_obj_type = 'USER' and ora_des_encrypted_password is not null
               THEN
                  RAISE_APPLICATION_ERROR(-20003,  'You are not allowed to alter password user.');
               END IF;
          END;
/

Note:
In the trigger, instead of raising the error, you may want to insert a row into a custom audit table.

How To Audit Application Username Using Trigger

With default auditing Oracle stores Oracle username but not the application username. In order to store application username you need to set CLIENT IDENTIFIER for the application session which is connecting to the database.

Copying Files Over Remote Desktop

Start up a remote desktop dialog and set the local resources as desired using options button. After this you will be able to use the clipboard of local system on remote machine.

How To Use PROFILES To Limit User Resources

You have a group of users that do not always disconnect from the database when they are done, or, they leave their connection idle for long periods of time. You want to eliminate these connections to make more connections available to other users.

How to Audit Connect AS SYSDBA Using Oracle Server

Windows Systems
---------------
On Windows Systems, you can monitor audited connects to Oracle as an administrative
user (former connect INTERNAL as of 8.1.7 connections ' as sysdba ') in the
event viewer. 

Application Tuning using Explain

The EXPLAIN facility can be used quickly and easily to
determine how the data is accessed (known as the access path) for
any given SQL statement, namely queries.  The ability to modify
this access path can yield an incredible performance benefit.

SCRIPT TO CHECK FOR FOREIGN KEY LOCKING ISSUES

Description

This script checks the current users Foreign Keys to make sure of the
following:

1) All the FK columns have indexes to prevent a possible locking

How to Collect Diagnostics for Database Hanging Issues

If you are encountering a database hanging situation, you need to take system
state dumps/hanganalyze so that Oracle Support can begin to diagnose the cause of the problem.
Whenever you take such dumps for a hang it is important to take at least 3 of
them a few minutes apart, on all instances of your database.

How To Connect Using A Sqlplus Preliminary Connection

There is an another  way to connect to a database through Sqlplus when all other connection methods are hanging.  Using a Sqlplus preliminary connection you will be able to connect to the database since no session is actually created,

Using DBMS_MONITOR

Using DBMS_MONITOR, Tracing has been enabled for diagnose and workload management based on, a specified client identifier or a hierarchical combination of service name, module name and action name. Also we have the facility to trace on session level.

Using TKPROF

The TKPROF facility accepts as input an SQL trace file and produces a formatted output file.  Note that TKPROF can be run on individual or
appended trace files to produce the formatted output file.

How to Enable SQL_TRACE for Another Session or in MTS Using Oradebug

The ORADEBUG utility can enable/disable setting the SQL tracing for another 
user's session or an MTS session.  To enable tracing for another session, the 
Oracle process identifier (PID) or the Operating System processes identifier 
(SPID) must be identified from v$process.  This is an effective way of capturing 
a SQL trace from a process which is already running.  The output can be used to 
analyze SQL related performance issues.

How To Collect 10046 Trace (SQL_TRACE) Diagnostics for Performance Issues

Event 10046 is the standard method of gathering extended sql_trace information for Oracle sessions.

This is a special EVENT code. It can be used to signal Oracle to perform SQL_TRACE type 

actions. The 10046 trace is the equivalent of setting SQL_TRACE=TRUE.The advantage of using 

the event is that extra details may be output to the trace file depending on the level

specified with the event. 

Investigating a Database Performance Issue

To investigate a slow performance problem, begin by deciding what diagnostics will be gathered. To do this, consider the following questions and take the appropriate action:-
Is the performance problem constant or does it occur at certain times of the day ?

Optimizing Joins

The first thing to remember about optimising the performance of joins is that the performance can decrease considerably as a 3rd or 4th or 5th table is added to the query. The more tables that are added, the more important it is to ensure that the query is properly tuned and tested.

Oracle database Performance Tuning

The Oracle RDBMS is highly tunable and allows the database to be monitored and adjusted to increase its performance. ne should do performance tuning for the following reasons:

• The speed of computing might be wasting valuable human time (users waiting for response);

Monitoring Index Usage

Monitoring Index Usage

Oracle Database provides a means of monitoring indexes to determine whether they are being used. If an index is not being used, then it can be dropped, eliminating unnecessary statement overhead.

Desktop Heap Overview

Desktop heap is probably not something that you spend a lot of time thinking about, which is a good thing.  However, from time to time you may run into an issue that is caused by desktop heap exhaustion, and then it helps to know about this resource.  Let me state up front that things have changed significantly in Vista around kernel address space, and much of what I’m talking about today does not apply to Vista.

Schedule Killing sessions dynamically

You can kill session dynamically as below in a single instance but for the RAC you may face issues , so approach below can be used.

WUC-14 Object Cache Error: Specified object handle {0} not found in the cache.

WUC-14 was occurring when developer was trying to write a file with more than 10000 lines as below.

500 Internal Server Error

Client was getting following error while accessing the form application.

500 Internal Server Error
java.io.IOException: Too many open files at java.io.FileInputStream.open(Native Method) at java.io.FileInputStream.(FileInputStream.java:106) at java.io.FileInputStream.(FileInputStream.java:66) at java.io.FileReader.(FileReader.java:41) at oracle.forms.servlet.ConfigFileParser.(Unknown Source) at oracle.forms.servlet.FormsServlet.doRequest(Unknown Source) at

Finding FK for a Parent table

select owner,constraint_name,constraint_type,table_name,r_owner,r_constraint_name

     from all_constraints

    where constraint_type='R'

    and r_constraint_name in (select constraint_name from all_constraints

    where constraint_type in ('P','U') and table_name='T_LOOKUP');

ORA-19815: WARNING: db_recovery_file_dest_size

We were getting warning in Alertlog for a database although we were not using FRA.

ORA-19815: WARNING: db_recovery_file_dest_size of 6442450944 bytes is 99.78% used, and has 14326272 remaining bytes available.

How to Delete a Windows Service in Windows

If you are a fan of tweaking your system and disabling services, you might find that over time your Windows Services list becomes huge and unwieldy with a large number of services in the list that will never be enabled.

TNS-12531: TNS:cannot allocate memory

Symptoms
On windows platform, new client connections to the database through listener fails, "TNS-12531: TNS:cannot allocate memory" error found in listener.log.

How to Install Oracle 10.2.0.5 on MS Windows 7 / Windows 2008R2

Goal

Instructions to Install Oracle 10.2.0.5 on MS Windows 7 and MS Windows 2008R2.
These instructions can be used for either the Oracle Database software, or the Oracle Client software.

TG4MSQL and slow response

If you see the "HS Message to agent" wait event for a long time while querying the SQL Server, first of all check that tracing is enabled and if it is enabled , disable it and rerun the statement again as tracing may cause the poor response.
I met this situation with one of the environment today where a query was taking the 4 minutes and SQL Server team confirmed that query does not take the 2 seconds to execute on the SQL Server itself. I investigated and found that tracing was On with debug option , just I remarked the related line in the TG init file (eg; E:\oracle\product\10.2.0\tg_1\tg4msql\admin\inittg4msql.ora) as below
#HS_FDS_TRACE_LEVEL=debug
and query returned the results in few seconds rather than 4 minutes.

Notify Report success/failure by mail

You can change the report configuration to notify the report failure or success by mail as below. Set Notification tag and pluginparam in report server's configuration file . Failnote.txt and succnote.txt files are found under
MT_OH\reports\templates

About Netbackup

NetBackup integrates the database backup and recovery capabilities of the Oracle Recovery Manager (RMAN) with the backup and recovery management capabilities of NetBackup. NetBackup for Oracle also lets you export and import Oracle data in XML format for long-term archival and retrieval.

Wait Event Enhancements in Oracle 10g

Oracle Database 10g Enhanced wait model [ID 245055.1]
		Modified 26-JUN-2007     Type BULLETIN     Status PUBLISHED

PURPOSE

This bulletin outlines Enhanced wait model introduced in Oracle Database 10G.

SCOPE & APPLICATION

The Oracle Database 10g has many improvements for the wait reporting mechanism. These include:

1. Blocking Session
2. Classification of Events
3. Break out important latch events
4. Histogram
5. Session and System Level Event Class Stats
6. Combine V$SESSION_WAIT into V$SESSION
7. Added V$SESSION_WAIT_HISTORY
8. NEW "WAIT_CLASS" columns in V$EVENT_NAME

1. Blocking Session

Two new columns blocking_session and blocking_session_status have been added to V$SESSION view.

Column "blocking_session" would contain the session id of the resource holder for which a session is waiting for. Else it would contain Null.

Column "blocking_session_status" would contain the status of the value of the blocking_session column.

The Value of the columns could be:

• VALID:-                         A valid Session ID is present in the Blocking Session column
• NO HOLDER:-              which implies that there are no holders of this resource.
• UNKNOWN:-               which implies that we could not figure out the holder.
• UNIMPLEMENTED:-   which implies that the callback for the event has not been implemented
• GLOBAL:-                     which implies that the holder is a session on another instance.

Following query would be useful to find the blocking session.

SELECT sid, blocking_session, username, blocking_session_status

FROM v$session

ORDER BY blocking_session;

2. Classification of Events 

All wait events have been classified into categories like contention wait/OS service wait/DB Service ,Wait/idle wait, etc.. This will enable the user to immediately find out whether the system is performing poorly due to excessive contention or a background not performing well or whether the Operating System does not have enough resources.

A category has many wait events assigned to it, so the below example shown displays the total waits reported for each class. The wait event class gives an overall view of a particular area. For example, the I/O wait category contains all wait events associated with disk I/O. As a rule, the wait event categories with the highest wait times and counts become the focus of tuning effort.

Example :

col wait_class format a30

SELECT e.wait_class#, e.wait_class,sum(s.total_waits), sum(s.time_waited)

FROM  v$event_name e, v$system_event s

WHERE  e.name = s.event

GROUP BY e.wait_class#,e.wait_class;

Classify the wait events into:

• Idle Waits: Whenever an Oracle process has no work to do this is an idle wait. For most processes this is because they are waiting on the user to provide a new SQL statement to execute.
• Application: These are waits caused by the way the application is designed. These include row lock waits, and table or other locks that are requested by the application either explicitly or implicitly (possibly due to DDL).
• Configuration: These are waits which occur in a badly configured system and weill be reduced dramatically as a result of proper tuning.
• Administrative: These are waits imposed by a privileged users by some action.
• Concurrency: These are waits that can not be tuned and will occur on a system with High Concurrency.
• Commit: This class only has log file sync. It deserves a special class because it is a necessary event and will be high and is supposed to be high on a system doing queries.
• Network: All waits due to network messaging delays belong here. They are supposed to point out network congestion or latency. They should not include think or processing time, only the time spent in the networking code and hardware.
• User I/O Waits: All waits for Disk I/O done by User queries or even SMON, MMON
• System I/O Waits: All waits for Disk I/O done by backgrnd processes like LGWR, DBWR, ARCH, RFS. But not SMON and MMON
• Scheduler: These are waits due to the resource manager
• Cluster: waits which will occur only in RAC mode.
• Other: All the wait events, which do not fit into one of the above classes clearly, or are not important to classify. By not important I mean those that wait for an insignificant amount of time or really do not fit into any one class.

3. Break out important latch events

Before Oracle Database 10g, it is not possible to see the latch waits on a session basis. When a session is waiting it waits on the latch free event, which does not tell much. One needs to see p1, which gives address, and find out which latch is it. Also, One does not know which are the sessions and which are getting the latch and resulting in contention.

Thus, the high contention in the database are breaking out separate wait events for certain latches which have known to be points of past. Some latches events will also have different wait classes. All the event names will have prefix of “latch: “ so that users can get all latch events by writing like ‘latch: %’.

Example:

SELECT event,p1,p2,p3 

FROM v$session_wait

WHERE event like 'latch%';

4. Histogram

The following views have been introduced from Oracle Database 10G which would be useful to generate histogram for system events and I/Os at file level.

• V$EVENT_HISTOGRAM
• V$FILE_HISTOGRAM
• V$TEMP_HISTOGRAM

To collect histogram data in these views database parameter timed_statistics should be set to TRUE.

4.a V$EVENT_HISTOGRAM

The v$system_event view displays the number of waits, the maximum wait time, and the total time waited per wait event. This view that show wait event information do not offer information on the time of each wait event occurrence. A single very long wait can skew the information offered by this view, these waits might not be indicative of the system as a whole.

The v$event_histogram view remedies this. Using this view, you create a histogram showing the frequency of wait events for a range of durations. To provide you with more in depth data the v$event_histogram view shows the number of waits for a wait event over a range of time values. You use the v$event_histogram view to determine if the bottleneck is a regular or a unique problem.

This view has the following columns:

• event# :- The number assigned to the wait event. This corresponds to the event# column in the v$event_name table.
• Event:- The name of the wait event. This corresponds to the name column in the v$event_name table.
• wait_time_milli:- The time this bucket represents in ms. If DURATION= k then it represents waits of duration <=k not included in any smaller bucket. The values of DURATION will be 1, 2, 4, 8, …, 2^21, 2^22, UB4MAXVAL
• wait_count:- The number of waits of duration belonging to this bucket of the histogram

4.b V$FILE_HISTOGRAM

The v$filestat view displays statistics per data file. This information can be skewed due to a couple of very large I/O waits, thus these waits might not be indicative of the system as a whole.

V$FILE_HISTOGRAM displays a histogram of all single block reads on a per-file basis. The histogram has buckets of time intervals from < 1 ms, < 2 ms, < 4 ms, < 8 ms, ... < 2^21 ms, < 2^22 ms, >= 2^22 ms. The histogram can be used to determine if the bottleneck is a regular or a unique problem.The frequency of each wait event versus the length of the wait events would be seen using the information in the v$file_histogram view.

The following columns make up the v$file_histogram view:

• FILE#:- File number
• SINGLEBLKRDTIM_MILLI :-The time this bucket represents in ms. If DURATION= k then it represents waits of duration <=k not included in any smaller bucket. The values of DURATION will be 1, 2, 4, 8, …, 2^21, 2^22, UB4MAXVAL
• SINGLEBLKRDS :- Number of waits of the duration belonging to the bucket of the histogram

4.c V$TEMP_HISTOGRAM

V$TEMP_HISTOGRAM for temporary files. V$TEMP_HISTOGRAM displays a histogram of all single block reads on a per-tempfile basis. The histogram has buckets of time intervals from < 1 ms, < 2 ms, < 4 ms, < 8 ms, ... < 2^21 ms, < 2^22 ms, >= 2^22 ms.

• FILE# :- File number
• SINGLEBLKRDTIM_MILLI :- The time this bucket represents in ms. If DURATION= k then it represents waits of duration <=k not included in any smaller bucket. The values of DURATION will be 1, 2, 4, 8, …, 2^21, 2^22, UB4MAXVAL
• SINGLEBLKRDS :- Number of waits of the duration belonging to the bucket of the histogram.

5. Session and System Level Event Class Stats

The following views have been introduced from Oracle Database 10G, which display total wait wide instance and session.

•   v$system_wait_class
•   v$session_wait_class

5.a v$system_wait_class

The v$system_wait_class view displays the instance wide totals of the time waited and the number of times wait events of this class have occurred. Both of these totals are running totals started when the instance started.

The view consists of the following columns:

• wait_class# :- The wait class number
• wait_class :- The name given to the wait class when registered
• time_waited :- The amount of time spent in this wait by all sessions in the instance
• total_waits :- The number of times waits of this class occurred

From the output of the below query you can determine which classes of wait event you should investigate further.

SELECT wait_class#, wait_class, time_waited, total_waits

FROM v$system_wait_class

ORDR BY time_waited;

5.b v$session_wait_class

The v$session_wait_class view shows the time spent in various classes of wait event operations on a per session basis. The view includes the following columns:

• sid :- The session id (same as in v$sesstat)
• serial# :- The serial number
• wait_class# :- The wait class number
• wait_class :- The name given to the wait class when registered.
• time_waited :- The amount of time spent in this wait class by this session
• total_waits :- The number of times waits of this class occurred for this session

After determining that a wait event class is affecting performance (from the v$system_wait_class view) use v$session to determine the sessions that are experiencing the specific wait event class.

Use the below SQL statement to determine the sessions that have wait events associated with this class.

SELECT  sid,serial#,time_waited,total_waits

FROM v$session_wait_class

WHERE  wait_class# =

ORDER BY time_waited;

Note: Do not expect the wait_time and wait_count columns to add up to the total in v$system_wait_class since it is likely that some sessions would have already ended and therefore would not appear in the v$session_wait_class view.

6. Combine V$SESSION_WAIT into V$SESSION

In the previous releases to determine the sessions experiencing waits, we need to join the v$session_wait view with the v$session view.

In the Oracle Database 10g all wait event columns from v$session_wait have been added to v$session thus increasing performance by eliminating the overhead of joins.

7. Added V$SESSION_WAIT_HISTORY

In the previous releases, we cannot see the last few waits of a session. From Oracle Database 10g a new view V$Session_wait_history will allow us to see the last few wait events a session waited on.

The last 10 wait events that a session experienced can be displayed using the v$session_wait_history view. The session has to be currently active. Once the session ends this information is not available.

We can use the seq# column to sort the wait events into the order in which the wait events occurred for the session.

SELECT sid,seq#,event

FROM v$session_wait_history

WHERE sid = ;

8. NEW "WAIT_CLASS" columns in V$EVENT_NAME

The following columns have been added to the v$event_name view:

• wait_class# :- The wait class number
• wait_class :- The name of the class of wait events

Following query can check waits caused by I/O:

SELECT name, wait_class#, wait_class

FROM v$event_name

WHERE wait_class# in (10,11);

Related Products Oracle Database Products > Oracle Database > Oracle Database > Oracle Server - Enterprise Edition Oracle Database Products > Oracle Database > Oracle Database > Oracle Server - Enterprise Edition

Back to top

Rate this document 

Article Rating Rate this document Excellent Good Poor Did this document help you? Yes No Just browsing How easy was it to find this document? Very easy Somewhat easy Not easy		Comments Provide feedback for this article. Please use 'Contact Us' for other feedback. Important Note: this feedback is anonymously visible to other customers until processed by Oracle Support.
Cancel

DB Segments Used (Type wise)

Create Or Replace Force View Sys.Av_db_used_space_seg_type ("Total Used",
                                                            "Data part",
                                                            "Index part",
                                                            "LOB part",
                                                            "RBS part",
                                                            "TEMP part"
                                                           )
As
   Select Sum (Bytes) / 1024 / 1024 "Total Used",
          Sum (Decode (Substr (Segment_type, 1, 5),
                       'TABLE', Bytes / 1024 / 1024,
                       0
                      )
              ) "Data part",
          Sum (Decode (Substr (Segment_type, 1, 5),
                       'INDEX', Bytes / 1024 / 1024,
                       0
                      )
              ) "Index part",
          Sum (Decode (Substr (Segment_type, 1, 3),
                       'LOB', Bytes / 1024 / 1024,
                       0
                      )
              ) "LOB part",
          Sum (Decode (Segment_type, 'ROLLBACK', Bytes / 1024 / 1024, 0)
              ) "RBS part",
          Sum (Decode (Segment_type, 'TEMPORARY', Bytes / 1024 / 1024, 0)
              ) "TEMP part"
     From Sys.Dba_segments;

DB History

Create Or Replace Force View Sys.Av_db_update_history (Action_time,
                                                        Action,
                                                        Namespace,
                                                        Version,
                                                        Id,
                                                        Comments
                                                       )
As
   Select "ACTION_TIME", "ACTION", "NAMESPACE", "VERSION", "ID", "COMMENTS"
     From Registry$history;

How much DB undo generated

Create Or Replace Force View Sys.Av_db_undo_generated (Rundate,
                                                       Day,
                                                       Logswitch,
                                                       "REDO PER DAY (MB)"
                                                      )
As
   Select   Trunc (Completion_time) Rundate,
            To_char (Trunc (Completion_time), 'DAY') Day, Count (*) Logswitch,
            Round ((Sum (Blocks * Block_size) / 1024 / 1024)
                  ) "REDO PER DAY (MB)"
       From V$archived_log
   Group By Trunc (Completion_time)
   Order By 1 Desc;

Session blocking quiesce

Create Or Replace Force View Sys.Av_db_sess_blking_quiesc (Sid,
                                                           Sess_user,
                                                           Osuser,
                                                           Type,
                                                           Program,
                                                           Killstmt
                                                          )
As
   Select Bl.Sid, User Sess_user, Osuser, Type, Program,
             'Alter SYSTEM DISCONNECT Session '
          || ''''
          || Se.Sid
          || ','
          || Se.Serial#
          || ''' '
          || 'IMMEDIATE;' Killstmt
     From V$blocking_quiesce Bl, V$session Se
    Where Bl.Sid = Se.Sid;

DB overall efficiency

Create Or Replace Force View Sys.Av_db_overall_eff (Metric_name,
                                                    Mininum,
                                                    Maximum,
                                                    Average
                                                   )
As
   Select   Case Metric_name
               When 'SQL Service Response Time'
                  Then 'SQL Service Response Time (secs)'
               When 'Response Time Per Txn'
                  Then 'Response Time Per Txn (secs)'
               Else Metric_name
            End Metric_name,
            Case Metric_name
               When 'SQL Service Response Time'
                  Then Round ((Minval / 100), 2)
               When 'Response Time Per Txn'
                  Then Round ((Minval / 100), 2)
               Else Minval
            End Mininum,
            Case Metric_name
               When 'SQL Service Response Time'
                  Then Round ((Maxval / 100), 2)
               When 'Response Time Per Txn'
                  Then Round ((Maxval / 100), 2)
               Else Maxval
            End Maximum,
            Case Metric_name
               When 'SQL Service Response Time'
                  Then Round ((Average / 100), 2)
               When 'Response Time Per Txn'
                  Then Round ((Average / 100), 2)
               Else Average
            End Average
       From Sys.V_$sysmetric_summary
      Where Metric_name In
               ('CPU Usage Per Sec', 'CPU Usage Per Txn',
                'Database CPU Time Ratio', 'Database Wait Time Ratio',
                'Executions Per Sec', 'Executions Per Txn',
                'Response Time Per Txn', 'SQL Service Response Time',
                'User Transaction Per Sec')
   Order By 1;

Hard activities in DB

Create Or Replace Force View Sys.Av_db_hard_activities (Db_stat_name,
                                                        Time_secs,
                                                        Pct_time
                                                       )
As
   Select   Case Db_stat_name
               When 'parse time elapsed'
                  Then 'soft parse time'
               Else Db_stat_name
            End Db_stat_name,
            Case Db_stat_name
               When 'sql execute elapsed time'
                  Then Time_secs - Plsql_time
               When 'parse time elapsed'
                  Then Time_secs - Hard_parse_time
               Else Time_secs
            End Time_secs,
            Case Db_stat_name
               When 'sql execute elapsed time'
                  Then Round (100 * (Time_secs - Plsql_time) / Db_time,
                              2
                             )
               When 'parse time elapsed'
                  Then Round (100 * (Time_secs - Hard_parse_time) / Db_time,
                              2)
               Else Round (100 * Time_secs / Db_time, 2)
            End Pct_time
       From (Select Stat_name Db_stat_name,
                    Round ((Value / 1000000), 3) Time_secs
               From Sys.V_$sys_time_model
              Where Stat_name Not In
                       ('DB time', 'background elapsed time',
                        'background cpu time', 'DB CPU')),
            (Select Round ((Value / 1000000), 3) Db_time
               From Sys.V_$sys_time_model
              Where Stat_name = 'DB time'),
            (Select Round ((Value / 1000000), 3) Plsql_time
               From Sys.V_$sys_time_model
              Where Stat_name = 'PL/SQL execution elapsed time'),
            (Select Round ((Value / 1000000), 3) Hard_parse_time
               From Sys.V_$sys_time_model
              Where Stat_name = 'hard parse elapsed time')
   Order By 2 Desc;

DB Global waits

Create Or Replace Force View Sys.Av_db_global_waits (Wait_class,
                                                     Total_waits,
                                                     Pct_waits,
                                                     Time_waited_secs,
                                                     Pct_time
                                                    )

DB General Info

Create Or Replace Force View Sys.Av_db_gen_info (Db_info)
As
   Select '01- ' || Banner Db_info
     From Sys.V_$version

Data Files free space

Create Or Replace Force View Sys.Av_db_df_free_space (Tablespace_name,
                                                      File_name,
                                                      Allocated_mb,
                                                      Used_mb,
                                                      Free_space_mb
                                                     )

Current session wait activity in DB

Create Or Replace Force View Sys.Av_db_cur_sess_wait_activity (Sid,
                                                               Username,
                                                               Wait_class,
                                                               Total_waits,
                                                               Time_waited_secs,
                                                               Killstmt
                                                              )
As
   Select   A.Sid, B.Username, A.Wait_class, A.Total_waits,
            Round ((A.Time_waited / 100), 2) Time_waited_secs,
               'Alter SYSTEM Kill Session '
            || ''''
            || B.Sid
            || ','
            || B.Serial#
            || ''' '
            || 'IMMEDIATE;' Killstmt
       From Sys.V_$session_wait_class A, Sys.V_$session B
      Where B.Sid = A.Sid
        And B.Username Is Not Null
        And A.Wait_class != 'Idle'
   Order By 5 Desc;

Active Process in DB

Create Or Replace Force View Sys.Av_db_active_process (Username,
                                                       Osuser,
                                                       Disk_reads,
                                                       Buffer_gets,
                                                       Lockwait,
                                                       Pid,
                                                       Event,
                                                       Sql
                                                      )

Blocking Waiting Sessions

Create Or Replace Force View Sys.Av_blocking_waiting_sess (Waiting_session,
                                                           Holding_session,
                                                           Lock_or_pin,
                                                           Address,
                                                           Mode_held,
                                                           Mode_requested
                                                          )

Locked Object

Create Or Replace Force View Sys.Av_locked_obj (Sid,
                                                Oracle_username,
                                                Os_user_name,
                                                Locked_mode,
                                                Object_name,

Object Cache Locks

Create Or Replace Force View Sys.Av_lock_object_cache (Owner,
                                                       Name,
                                                       Db_link,
                                                       Namespace,

Library Cache Locks

Create Or Replace Force View Sys.Av_lock_lib_cache (Sid,
                                                    Lockmode,
                                                    Lockrequest,

DDL Locks on Objects

Create Or Replace Force View Sys.Av_lock_ddl_on_obj (Sid,
                                                     Serial#,
                                                     Owner,
                                                     Obj_name,

ASM Diskgroup Info

Create Or Replace Force View Sys.Av_asm_diskgroup (Group_number,
                                                   Name,
                                                   Sector_size,
                                                   Block_size,
                                                   Allocation_unit_size,
                                                   State,
                                                   Type,
                                                   Total_mb,
                                                   Free_mb,
                                                   Required_mirror_free_mb,
                                                   Usable_file_mb,
                                                   Offline_disks,
                                                   Unbalanced,
                                                   Compatibility,
                                                   Database_compatibility
                                                  )
As
   Select "GROUP_NUMBER", "NAME", "SECTOR_SIZE", "BLOCK_SIZE",
          "ALLOCATION_UNIT_SIZE", "STATE", "TYPE", "TOTAL_MB", "FREE_MB",
          "REQUIRED_MIRROR_FREE_MB", "USABLE_FILE_MB", "OFFLINE_DISKS",
          "UNBALANCED", "COMPATIBILITY", "DATABASE_COMPATIBILITY"
     From V$asm_diskgroup;

ASM Disk Info

Create Or Replace Force View Sys.Av_asm_disk (Name,
                                              Path,
                                              Header_status,
                                              Free_mb,

How can I tell if a procedure/package is running?

Sometimes the installation of a new version of a database package "hangs" and eventually times out with a ORA-04021: timeout occurred while waiting to lock object". This is caused by another session that is currently executing the same package.

Listing privileges recursively for Oracle users

This is a script that shows the hierarchical relationship between system privileges, roles and users.

FRM-92100 when running a long running report

Users were experiencing the FRM-92100 with IllegalArgumentException while running a long running report, after investigation it was found that Request Timeout (seconds) value was 600 only on OHS.  So changing to 1500 resolved the issue.

RMAN Restore (Netbackup)

RMAN> show all;

RMAN configuration parameters are:
CONFIGURE RETENTION POLICY TO REDUNDANCY 1;
CONFIGURE BACKUP OPTIMIZATION OFF; # default
CONFIGURE DEFAULT DEVICE TYPE TO DISK; # default
CONFIGURE CONTROLFILE AUTOBACKUP ON;

RMAN Backup using Netbackup

After Netbackup configuration on the Oracle Server, you can use rman to take backup on tape.

How To Change ASM SYS PASSWORD ?

Things tried:
SQL> password
Changing password for SYS
Old password:
New password:
Retype new password:
ERROR:
ORA-00600: internal error code, arguments: [15051], [], [], [], [], [], [], []

Moving table(s) to a different tablespace

Q: What happens when u move a table to a different Tablespace?
Q: How can u move table to a different Tablespace which have long datatype?
Q: How can u move table to a different Tablespace with indexes?

ORA-02449 during tablespace drop

SQL> drop tablespace users including contents and datafiles ;
drop tablespace users including contents and datafiles
*
ERROR at line 1:
ORA-02449: unique/primary keys in table referenced by foreign keys