Category: Oracle

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

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…

Read More “Query to Generate aggregate on every 30 mins.” »

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…

Read More “Distributed Transaction Troubleshooting.” »

COL LOCAL_TRAN_ID FORMAT A13 COL IN_OUT FORMAT A6 COL DATABASE FORMAT A25 COL DBUSER_OWNER FORMAT A15 COL INTERFACE FORMAT A3 SELECT LOCAL_TRAN_ID, IN_OUT, DATABASE, DBUSER_OWNER, INTERFACE FROM DBA_2PC_NEIGHBORS / SQL> CONNECT SYS@hq.acme.com AS SYSDBA SQL> @neighbors_script LOCAL_TRAN_ID IN_OUT DATABASE DBUSER_OWNER INT ————- —— ————————- ————— — 1.15.870 out SALES.ACME.COM SYS C

COL LOCAL_TRAN_ID FORMAT A13 COL GLOBAL_TRAN_ID FORMAT A30 COL STATE FORMAT A8 COL MIXED FORMAT A3 COL HOST FORMAT A10 COL COMMIT# FORMAT A10 SELECT LOCAL_TRAN_ID, GLOBAL_TRAN_ID, STATE, MIXED, HOST, COMMIT# FROM DBA_2PC_PENDING /

http://www.toadworld.com/KNOWLEDGE/KnowledgeXpertforOracle/tabid/648/TopicID/2471975d4f31c8aa/Default.aspx

-Default order of NAMES. DIRECTORY_PATH is TNSNAMES, OID, HOSTNAME in sqlnet.ora. -EZCONNECT is available after 10g onwards. -Make sure Names.default_domain is set property to avoid any kind of client connectivity issues.

Option: Try each address, in order, until one succeeds ( Failover only). By default failover=true if you have more than one entry in address list. ============================================================================= MEHTA = (DESCRIPTION = (ADDRESS_LIST = (ADDRESS = (PROTOCOL = TCP)(HOST = NYBOX)(PORT = 1555)) (ADDRESS = (PROTOCOL = TCP)(HOST = FLBOX)(PORT = 1600)) ) (CONNECT_DATA = (SERVICE_NAME =…

Read More “TNSNAMES entries details” »

-SERVICE_NAMES = INSTANCE_NAME + DB_DOMAIN -GLOBAL DATABASE NAME should be same as SERVICE_NAMES -If system ts is LMT then temporary tablespace is required. -if system ts is lmt then no dmt can be created in db. -When system ts is LMT then undo tbs is also must. -1 tbs can have max 1022 dbfs. -1…

Read More “Oracle 11g Training on 29JAN1010” »