Linux: how to open a tar file remotely, directly, using ssh

Sometimes we need to open a tar file on a server, but we don't have enough space for the tar file.
Solution: open the file directly on the target server:

host1!oracle:
 $ cat Sanity.tar | ssh oracle@host2 "(cd /u01/app/oracle/users/Florin/; tar xf -)"

How to find the top queries TEMP space consumers, historical data?

The query below identifies the queries which used more then 5 GB of temp space, during the last 2 days:

select sql_id,max(TEMP_SPACE_ALLOCATED)/(1024*1024*1024) gig
from DBA_HIST_ACTIVE_SESS_HISTORY
where
sample_time > sysdate-2 and
TEMP_SPACE_ALLOCATED > (5*1024*1024*1024)
group by sql_id order by 2
/

Which oracle session is generating a lot of redo logs?

A simple and fast query to run:

set line 200 pages 80

SELECT s.sid, s.serial#, s.username, s.program, i.block_changes
FROM v$session s, v$sess_io i
WHERE s.sid = i.sid
ORDER BY 5;

By the way, the view v$sess_io is very useful, to identify the top I/O consumers also.

Why my delete is not running in parallel?

Sometimes is really faster of the delete statement is running in parallel; not only to identify the rows to be deleted, but the delete itself.

 2 observations related to this:

1) Deleting from a table partition, it seems that the alias is not being translated properly, so instead I added a where clause, to access the required partition.

2) Adding 2 where clauses didn't help, but adding a between clause, did the trick.

Example of a query which will NOT do the delete in parallel:


delete /*+ parallel(a,16) full(a)  */
from MYTAB  partition (PR001) a
where expiration_date <  add_months( trunc(sysdate), -85 );

Example of a fixed query, which will run in parallel:

delete /*+ parallel(a,16) full(a)  */

from MYTAB a 

where expiration_date <  add_months( trunc(sysdate), -85 )

and ID between 2500 and 37500;

Notes:

1. ALTER SESSION ENABLE PARALLEL DML; is still required.
2. To check that the delete is running in parallel, the below query should return more than 1 row:


select a.SID,a.USERNAME,b.xidusn,b.used_urec,b.used_ublk
from  v$session a,v$transaction b
where a.SADDR=b.ses_addr
/

ORA-28040: No matching authentication protocol, when connecting to a 12c DB

The issue:

 >sqlplus myuser/mypass@MY12CDB

SQL*Plus: Release 11.2.0.2.0 Production on Fri May 11 10:22:44 2018

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

ERROR:
ORA-28040: No matching authentication protocol


 Cause: connecting to a 12c DB, with a client lower than 11.2.0.3

 Solution:

 Add the following to $ORACLE_HOME/network/admin/sqlnet.ora , for both versions:

 SQLNET.ALLOWED_LOGON_VERSION_SERVER=8
 SQLNET.ALLOWED_LOGON_VERSION_CLIENT=8

Note: it's important to modify the sqlnet.ora in the above mentioned location.

Which Red Hat version my server is?

cat /etc/redhat-release

A nice detailed link:

https://www.cyberciti.biz/faq/what-version-of-redhat-linux-am-i-running/

How to sort lines in Linux, starting with a specific character position, using "sort"

Let's say we have an file with a list of DB names:

$cat my_file.txt
BMLCRM12
BMLCRM98
BMLCRM99
BMSCRM11
BMSCRM13
BMSCRM15
BMSCRM19
BMSCRM23
BMSCRM24
BMSCRM25

The goal is to sort them out in order, ignoring the first 6 characters.

Solution:

$cat my_file.txt |sort -n -k 1.6,1.8

BMSCRM11

BMLCRM12

BMSCRM13

BMSCRM15

BMSCRM19

BMSCRM23

BMSCRM24

BMSCRM25

BMLCRM98

BMLCRM99