Oracle Statspack survival Guide
http://www.akadia.com/services/ora_statspack_survival_guide.html
DB Console Mainenance.
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…
ORA-01220
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…
Oracle Release Explaination
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.
How to specify 2 arch location to avoid any kind of DB hanging.
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.” »
Sending SQLPLUS output in HTML format
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…
Generating XML from SQLPLUS
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
Query to Generate aggregate on every 30 mins.
select to_char(first_time, ‘RRRRMMDDHH24’), COUNT (CASE WHEN TO_NUMBER(TO_CHAR(FIRST_time, ‘MI’)) 30 THEN 1 ELSE NULL END) COUNT2 FROM V$LOG_HISTORY GROUP BY to_char(first_time, ‘RRRRMMDDHH24’) order by 1 / TO_CHAR(FI COUNT1 COUNT2 ———- ———- ———- 2010020906 3 1 2010020907 3 1 2010020908 3 1 2010020909 3 1 2010020910 3 1 2010020911 3 1 2010020912 3 1 2010020913 3 1…
Distributed Transaction Troubleshooting.
SQL> select * from sys.pending_trans$ where local_tran_id = ‘26.2.2712168’; no rows selected SQL> select * from sys.pending_sessions$ where local_tran_id = ‘26.2.2712168’; no rows selected SQL> select * from sys.pending_sub_sessions$ where local_tran_id = ‘26.2.2712168’; no rows selected SQL> select * from dba_2pc_neighbors; no rows selected SQL> select * from dba_2pc_pending; no rows selected SQL> commit force…
Deleting first line and lastline of a file using sed
Delete last line of a file. =========================== sed ‘$d’ < input_file > output_file Delete First line of a file. ============================ sed ‘1d’ input_file > output_file