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

How to color (add) SQLs to the AWR reports?

I just found a very good blog explaining this:

http://oracleprof.blogspot.ca/2011/06/how-to-color-mark-sql-for-awr-snapshots.html


Sometimes, we have a problematic query which runs not that often or not that long to be included in the AWR reports, but we are still interested in see the statistics of this query. So the answer is to "color" this specific query, by providing the SQL_ID.

How to color the query:

exec dbms_workload_repository.add_colored_sql('6zfggtprazcvb');

How to check that it was added to the list:

select * from DBA_HIST_COLORED_SQL;

How to remove the query (uncolor):


exec dbms_workload_repository.remove_colored_sql('6zfggtprazcvb');





How to get a list of the tables whose statistics are locked?

The below query will do it:

SELECT TABLE_NAME FROM DBA_TAB_STATISTICS WHERE STATTYPE_LOCKED = 'ALL';