–Ref: http://asktom.oracle.com/pls/asktom/f?p=100:11:0::::P11_QUESTION_ID:1198012113596 column dur format a20 set lines 120 pages 200 select a.sid, a.serial#, a.username, to_char(sysdate-a.last_call_et/24/60/60,’hh24:mi:ss’) started, trunc(a.last_call_et/60) || ‘ mins, ‘ || mod(a.last_call_et,60) ||’ secs’ dur, b.sql_text from v$sql b, v$session a where a.username is not null and a.last_call_et > 10 and a.status = ‘ACTIVE’ and a.sql_address = b.address;
Ok, given the clarification I can show you a way to get this in 815 and before (slow on a big table) and in 816 and up (fast with analytic functions!) ops$tkyte@ORA817.US.ORACLE.COM> select * from t; DEPTNO SAL ———- ———- 10 100 10 100 10 200 10 300 10 400 10 500 20 100 20…
Read More “Display the top 5 salaries for each department using single SQL” »
exp userid=fdba file=dmp1.dmp,dmp2.dmp,dmp3.dmp log=myfinaldmp.log feedback=20000 filesize=1800M tables=Sp.SP_FLWS_ORDER_STATUS,Sp.SP_FLWS_ORDER_STATUS_DETAIL,sp.SP_MONITOR statistics=none Default: expdat.dmp Specifies the names of the export files to import. The default extension is .dmp. Because Export supports multiple export files (see the following description of the FILESIZE parameter), you may need to specify multiple filenames to be imported. For example: imp scott/tiger IGNORE=y FILE =…
http://www.akadia.com/services/ora_statspack_survival_guide.html
Comments ——– === ODM Action Plan === To recreate dbcontrol, please follow the instructions below: 1. Make sure that the following is true or have already been done: – you’re logged in to the OS as the user who owns the database installation – the ORACLE_HOME and ORACLE_SID environment variables are set properly in the…
Oracle Error :: ORA-01220 file based sort illegal before database is open Cause A query issued against a fixed table or view required a temporary segment for sorting before the database was open. Only in-memory sorts are supported before the database is open. Action Re-phrase the query to avoid a large sort, increase the values…
11.1.0.6.0 11 : Major Release Number 1 : Maintenance Release Number 0 : AppServer Release Number 6 : Component-Specific Release Number 0 : Platform specific release number.
Scenario: I wish to configure two locations for log archives in the way that archives should get stored on location “A” and when it gets filled or become unavailable due to any reason then logs should automatically start using location “B” for onward. In short failure of location A should start storing archives on location…
Read More “How to specify 2 arch location to avoid any kind of DB hanging.” »
SET MARKUP HTML ON SPOOL ON generates an HTML page for each subsequently spooled file. The HTML tags in a spool file are closed when SPOOL OFF is executed or SQL*Plus exits. You can use SET MARKUP HTML ON SPOOL OFF to generate HTML output suitable for embedding in an existing web page. HTML output…
set pages 0 set linesize 150 set long 9999999 set head off SQL> select dbms_xmlgen.getxml(‘select employee_id, first_name, 2 last_name, phone_number from employees where rownum < 6') xml 3 from dual 100 Steven King 515.123.4567 101 Neena Kochhar 515.123.4568 102 Lex De Haan 515.123.4569 103 Alexander Hunold 590.423.4567 104 Bruce Ernst 590.423.4568
