Category: SQL scripts

GoldenGate Tutorial: Simplest

Issue: Inbound connection timed out (ORA-3136) In 10gR2, SQLNET.INBOUND_CONNECT_TIMEOUT the parameters were set to have a default of 60 (seconds). Set the parameters SQLNET.INBOUND_CONNECT_TIMEOUT and INBOUND_CONNECT_TIMEOUT_listenername to 0 (indefinite). What the error is telling you is that a connection attempt was made, but the session authentication was not provided before SQLNET.INBOUND_CONNECT_TIMEOUT seconds. As far as…

Read More “ORA-3136” »

–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 =…

Read More “import-export with multiple files” »

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…

Read More “DB Console Mainenance.” »

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…

Read More “ORA-01220” »

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.” »