Oracle startup and shutdown script: very famous (dbora) ;)

This is the most complete dbora script i came up with, i used linux subsystem and pid file for complete status workings… please have a look and n point out if there is anything to be done further in it…   #!/bin/sh # chkconfig: 345 55 25 # /etc/init.d/dbora # Description: Starts and stops the Oracle database and listener # Author: ConArtist # Date: 21/04/14 # source function library .

Which query takes the most CPU in oracle database?

Here is a simple sql query which will show which query is misbehaving and consuming the most CPU usage SELECT ss.username,        ss.OSUSER,        ss.TERMINAL,        se.SID,        VALUE / 100 cpu,        sq.SQL_TEXT   FROM v$session ss, v$sesstat se, v$statname sn, v$sql sq  WHERE se.STATISTIC# = sn.STATISTIC#    AND NAME LIKE '%CPU used by this session%'    AND se.SID = ss.SID    AND ss.STATUS = 'ACTIVE'    AND ss.username IS NOT NULL    AND ss.SQL_ID = sq.SQL_ID    AND VALUE / 100 >= 1  ORDER BY VALUE DESC; And when you see the top CPU using process, monitor its activity through this query: SELECT nvl(ses.username,'ORACLE PROC')||' ('||ses.sid||')' USERNAME,        SID,          MACHINE,        REPLACE(SQL.SQL_TEXT,CHR(10),'') STMT,       ltrim(to_char(FLOOR(SES.LAST_CALL_ET/3600), '09')) || ':'        || ltrim(to_char(FLOOR(MOD(SES.LAST_CALL_ET, 3600)/60), '09')) || ':'  

How to get 10046 Trace for Oracle Export and Import Utility

PURPOSE
β€”β€”-

If you want to find out what happens when you run Oracle export or Oracle import utility is to set ON the Oracle trace event 10046. This will generate a trace file that can be used to find out the actual happenings behind the scene.

This is how you can get 10046 Trace for Export and Import Utilities

1] Run the Oracle export command export and let the program prompt you for the options.

$ exp

Enter user and password as below when prompted
Username: system
Password:

2] Open another window to the database server and login using sqlplus.

$ sqlplus system/manager

3] Now find out the SID of exp session

sql> select sid,program from
v$session where username = β€˜SYSTEM’;

SID PROGRAM
β€”β€”β€”- β€”β€”β€”β€”β€”β€”β€”β€”β€”β€”β€”β€”β€”β€”β€”β€”
788 exp@SERVER01 (TNS V1-V3)

4] Now find the PID and SPID for that session

sql> select s.sid, p.pid, p.spid
from v$session s, v$process p
where s.paddr = p.addr and s.sid = 10;

SID PID SPID
β€”β€”β€”- β€”β€”β€”- β€”β€”β€”
788 189 1076

SPID from the previous query is equivalent to OSPID (operating System process). This is the process that will be traced

5] Now exit from this session

Sql>exit

6] Generate a trace file for Procces ID 1076. To do that login as sys using sqlplus and run the commands (in bold)

$ sqlplus / as sysdba

SQL> oradebug setospid 1076
Oracle pid: 189, Unix process pid: 1076, image: oracle@SERVER01 (TNS V1-V3)

SQL> oradebug unlimit
Statement processed.

SQL> oradebug tracefile_name
/u01/app/oracle/diag/rdbms/dev/DEV1/trace/DEV1_ora_1076.trc

This gives the name of the trace file

SQL> oradebug Event 10046 trace name context forever, level 12;
Statement processed.

7] From the window where β€œexp” command was run, now export a table

8] From the SQL prompt of the window where logged in as β€œsys” user
Set the Trace off once you get the Required information or the error.

SQL> oradebug Event 10046 trace name context off;
ORA-00072: process β€œUnix process pid: 17370, image: oracle@ SERVER01β€³ is not active

SQL>exit

Now you have got the trace file which is
/u01/app/oracle/diag/rdbms/dev/DEV1/trace/DEV1_ora_1076.trc