Using telnet/nc to check if a specific port is open

 Example:

curl -v telnet://172.33.542.199:1521

nc -v 172.23.242.199 5696

How to turn subquery into JOIN?

 

Current long running query:

 

 

UPDATE /*+ full(b) parallel(b,8) */ SRV B SET SRV_STS_CD =

'CE' WHERE PARTITION_ID = 8 AND (SRV_STS_CD = 'CS' OR SRV_STS_CD =

'HA') AND SYS_CREATION_DATE > SYSDATE - 730 AND SRV_TRX_S_NO IN (SELECT

SRV_TRX_S_NO FROM DVC WHERE PARTITION_ID = 8 AND

DVC_TRX_STS_CD != 'CS');

 

 

Modified query:

 

UPDATE /*+ full(b) parallel(b,4) use_hash(DVC) leading(DVC_) unnest */ SRV B SET SRV_STS_CD =

'CE' WHERE PARTITION_ID = 8 AND (SRV_STS_CD = 'CS' OR SRV_STS_CD =

'HA') AND SYS_CREATION_DATE > SYSDATE - 730 AND SRV_TRX_S_NO IN (SELECT /*+ use_hash(DVC) full(DVC) parallel(DVC,4) */

SRV_TRX_S_NO FROM DVC_TRX_REPOS WHERE PARTITION_ID = 8 AND

DVC_TRX_STS_CD != 'CS')

;

How to run sqlplus as user root?

 [root@o71 ~]# sudo -u oracle bash -c '. ~/.bash_profile; sqlplus / as sysdba'

SQL*Plus: Release 18.0.0.0.0 - Production on Sun Oct 28 10:52:43 2018
Version 18.4.0.0.0

Copyright (c) 1982, 2018, Oracle.  All rights reserved.


Connected to:
Oracle Database 18c Enterprise Edition Release 18.0.0.0.0 - Production
Version 18.4.0.0.0

SQL> 

How to monitor the impdp progress?

 Below queries are very useful: 

select owner_name, job_name, operation, job_mode

from dba_datapump_jobs

where state='EXECUTING' ;

select v.status, v.sid,v.serial#,io.block_changes,event
from v$sess_io io, v$session v
where io.sid = v.sid
and v.saddr in (
    select saddr
    from dba_datapump_sessions
) order by sid;

select s.sid, s.module, s.state,
       substr(s.event, 1, 21) as event,
       s.seconds_in_wait as secs,
       substr(sql.sql_text, 1, 30) as sql_text
from v$session s
join v$sql sql on sql.sql_id = s.sql_id
where s.module like 'Data Pump%'
order by s.module, s.sid;

Using the UNNESThint to join 2 tables in a non exist subquery

 

create table NEW_TABLE NOLOGGING

as

select  * from Table1 d 

where not exists

(select /*+ UNNEST */

* from  sa where d.cfr_ban= sa.ban and d.cfr_subscriber_no= sa.subscriber_no and sa.soc= d.cfr_p2 and sa.soc_seq_no = d.cfr_p3);

 The plan is:

------------------------------------------------------------------------------------------------------------------------

| Id  | Operation                                     | Name                   | Rows  | Bytes | Cost  | Pstart| Pstop |

------------------------------------------------------------------------------------------------------------------------

|   0 | CREATE TABLE STATEMENT                        |                        |       |       |     4 |       |       |

|   1 |  LOAD AS SELECT                               | NEW_TABLE   |       |       |       |       |       |

|   2 |   OPTIMIZER STATISTICS GATHERING              |                        |     3 |   735 |     3 |       |       |

|   3 |    NESTED LOOPS ANTI                          |                        |     3 |   735 |     3 |       |       |

|   4 |     TABLE ACCESS FULL                         | TABLE1            |     3 |   633 |     2 |       |       |

|*  5 |     TABLE ACCESS BY GLOBAL INDEX ROWID BATCHED| SERVICE_AGREEMENT      |     1 |    34 |     1 | ROWID | ROWID |

|*  6 |      INDEX RANGE SCAN                         | SA_99IX |   433K|       |     1 |       |       |

------------------------------------------------------------------------------------------------------------------------

How to allocate initial extent for a partitioned table?

 

In case the parameter deferred_segment_creation is defined as TRUE, the initial extent will only be allocated whenever the data is inserted into the table.

 But how do we do this manually, if needed?

SQL> alter table MY_TABLE allocate extent;

or if the table is partitioned:

SQL> alter table MY_PART_TABLE modify partition   PR001  allocate extent;

Oracle keeps writing to a trace file and is becoming huge; how do we take care of this?

 The trace file is   MYDB_tt01_61001.trc and it's 8 GB in size.

>cp /dev/null MYDB_tt01_61001.trc

>du -sk MYDB_tt01_61001.trc

4

 Magic :-)

Oracle 12c/18 : How to find out if a PSU has been applied? DBMS_QOPATCH

PBMLOL>set serveroutput on
PBMLOL>exec dbms_qopatch.get_sqlpatch_status;

Patch Id : 28090523
        Action : APPLY
        Action Time : 12-MAY-2019 00:22:22
        Description : Database Release Update : 18.3.0.0.180717 (28090523)
        Logfile :
/u01/app/oracle/cfgtoollogs/sqlpatch/28090523/22329768/28090523_apply_MYDB_2019May12_00_21_46.log
        Status : SUCCESS

Patch Id : 27923415
        Action : APPLY
        Action Time : 12-MAY-2019 00:29:39
        Description : OJVM RELEASE UPDATE: 18.3.0.0.180717 (27923415)
        Logfile :
/u01/app/oracle/cfgtoollogs/sqlpatch/27923415/22239273/27923415_apply_MYDB_2019May12_00_29_39.log
        Status : SUCCESS

PL/SQL procedure successfully completed.

How to enable tracing for ORA-00942: table or view does not exist

Enable the alert : 

SQL> alter system set events '00942 trace name errorstack level 1';

Disable the alert:

SQL> alter system set events '00942 trace name context off';

The rollback is very slow, how do I speed it up?

 This is a very common problem, we kill an update/merge and is taking forever to rollback.

 There are 2 solutions for this:

1) Kill the spid oracle shadow process and set the parameter below:

SQL> alter system set fast_start_parallel_rollback=HIGH;

2) Bounce the DB and set the same parameter as above.

SQL> alter system set fast_start_parallel_rollback=HIGH;

 The only issue now, to monitor the rollback, we'll need a different query:

SQL>   SELECT usn, state, undoblockstotal "Total",undoblocksdone "Done",undoblockstotal-undoblocksdone "ToDo", DECODE(cputime,0,'unknown',SYSDATE+(((undoblockstotal-undoblocksdone) / (undoblocksdone / cputime)) / 86400)) "Finish at" FROM v$fast_start_transactions;

Linux: ps command for processes older processes

 To get the exact start time of an old process, use below:

[steaua@TESTDB]/u01/app/oracle >ps -eo pid,lstart,cmd|grep 20734

20734 Wed Nov 17 01:59:02 2021 ora_pmon_TESTDB

Otherwise we'll get the year only:

[steaua@TESTDB]/u01/app/oracle >ps -ef|grep 20734 |grep -v grep

oracle   20734     1  0  2021 ?        00:41:46 ora_pmon_TESTDB