Temp Usage

Temp Usage by Temp Tablespace:-

SELECT A.tablespace_name TABLESPACE,
  D.mb_total,
  SUM (A.used_blocks * D.block_size) / 1024 / 1024 mb_used,
  D.mb_total         - SUM (A.used_blocks * D.block_size) / 1024 / 1024 mb_free
FROM v$sort_segment A,
  (SELECT B.name,
    C.block_size,
    SUM (C.bytes) / 1024 / 1024 mb_total
  FROM v$tablespace B,
    v$tempfile C
  WHERE B.ts#= C.ts#
  GROUP BY B.name,
    C.block_size
  ) D
WHERE A.tablespace_name = D.name
GROUP BY A.tablespace_name,
  D.mb_total;

Temp Usage by Users:-


SELECT b.tablespace,
       ROUND(((b.blocks*p.value)/1024/1024),2)||'M' AS temp_size,
       a.inst_id as Instance,
       a.sid||','||a.serial# AS sid_serial,
       NVL(a.username, '(oracle)') AS username,
       a.program,
       a.status,
       a.sql_id
FROM   gv$session a,
       gv$sort_usage b,
       gv$parameter p
WHERE  p.name  = 'db_block_size'
AND    a.saddr = b.session_addr
AND    a.inst_id=b.inst_id
AND    a.inst_id=p.inst_id
ORDER BY b.tablespace, b.blocks;

Hope this Helps..

ORA-39346: data loss in character set conversion for object DATABASE_EXPORT/SCHEMA/TABLE/COMMENT

Recently, we have seen this error while importing application schemas from one environment to another(which are of same Platforms). the import log had this below error.
we are on 12.1.0.2.4(both environments on same version). we see below error because we have soem undefined or some characters that not able to convert while importing.. check and see if you see any text with different characters in comments that look ODD.

ORA-39346: data loss in character set conversion for object DATABASE_EXPORT/SCHEMA/TABLE/COMMENT

Oracle has a patch for Bug# 21342624 ...

Note :- download the one that you need for ..because oracle has this patch for different versions..

once you aplly this patch.. 21342624. do the import again(only comments) and see what all columns with tables are reported. and try to alter those comments...

Hope this Helps.......

unrecoverable error ORA-29701 raised in ASM I/O path;

ERROR: unrecoverable error ORA-29701 raised in ASM I/O path; terminating process 9830432 

Thu Jul 28 17:02:07 2016 ERROR: unrecoverable error ORA-29701 raised in ASM I/O path; terminating process 

This error was recently noticed when one of our unix admin accidentally cleaned the files in /tmp folder on AIX server.. he had deleted/removed the .oracle  folder and its files(hidden files).
This should not be done when oracle is up and running....

Soln:-
when you see this error .. make sure you shutdown oracle and asm/agent as you can.... And ask the Unix admin to bounce the server/reboot....once it is up.. you can try to start the oracle resources.. it should start with no issues...


Hope this Helps....

Unix Variable to sqlplus and sqplus to UNIX different scenarios

1) passing variable to sqlplus
Variable_IN="'ABC_VAR_1','ABC_VAR_2'"

2) how to store sqlplus result to a variable back to unix variable.

v_count1=$(sqlplus -L -S sys/$passd@$PDB as sysdba <<EOF   ---(for 12c)
set feed off heading off echo off verify off termout off
select count(*) from dba_tab_statistics where stale_stats = 'YES';
exit
EOF
)

3) how to pass your own variables to use in the sqlplus...

Variable_IN="'ABC_VAR_1','ABC_VAR_2'"

declare
type_p_abc_dba integer;
begin
  select count(*) into type_p_abc_dba from dba_types where type_name='STORE_VAL_abc_DBA';
  if (type_p_abc_dba = 0) then
    execute immediate 'create type store_val_abc_dba  as table of varchar2(100);';
  end if;
end;
/

declare

    type pa_tablename is table of all_tables.table_name%type

        index by binary_integer;

    va_tablename pa_tablename;

cursor c_f is (select column_value from table(store_val_abc_dba($Variable_IN)));

BEGIN

FOR ind IN  c_f

LOOP

begin

     .............

     .............

end;

end loop;

end;

Hope this helps.......

script to find stale objects and run the stats

You can use below script to automate running stats when stale objects are found in the database...
here we are ignoring all the objects were their stats are locked.

In order to get updated  stale objects... it is better you flush the monitoring info... as this updates the changes occurred recently....and tells whether any objects got staled or not. we are doing this manually because oracle has it own way/time of updating... if you have hard hitting tables then they might be stale but the monitoring info will not capturing it...  by doing so we can have better stats for  the tables needed.

you can easily automate the below code with you environment set.

exec dbms_stats.FLUSH_DATABASE_MONITORING_INFO;

Script:-

CHANNELS=`cat /proc/cpuinfo | grep processor | wc -l`


===================
sqlplus  -S sys /change@ABCD_SID as sysdba <<EOF >> $LOG
set linesize 200
set serveroutput on size 1000000
set feedback on echo off verify off

Prompt Running stats for Stale Table objects

declare
sql_stmt varchar2(256);
va_tab dba_tab_statistics.table_name%type;

cursor c_f is (select owner,table_name,global_stats,stale_stats from dba_tab_statistics
where stale_stats = 'YES' and table_name not like 'BIN$%' and STATTYPE_LOCKED is null);

begin
    for i in c_f
    loop
        begin
           dbms_stats.gather_table_stats(''||i.owner||'',''||i.table_name||'',
                estimate_percent => 100,
                degree => $CHANNELS,
                method_opt => 'for all columns size auto', cascade => true);
                dbms_output.put_line(sysdate || ': stats done for ' || ''||i.owner||'' ||'.' || ''||i.table_name||'');
            exception
            when others then
            continue;
            dbms_output.put_line(sysdate || ': failed to gather stats for ' || ''||i.owner||'' ||'.' || ''||i.table_name||'' || chr(10) || sqlerrm);
         end;
    end loop;
           dbms_output.put_line(sysdate || ': #######################' || ': Stats done for Above Found Stale objects ' || ': #######################');
END;
/

Prompt Running stats for Stale index objects

declare
sql_stmt varchar2(256);
va_tab dba_ind_statistics.table_name%type;

cursor c_f is (select owner,index_name,global_stats,stale_stats from dba_ind_statistics
where stale_stats = 'YES' and table_name not like 'BIN$%' and STATTYPE_LOCKED is null);

begin
    for i in c_f
    loop
        begin
           dbms_stats.gather_index_stats(''||i.owner||'',''||i.index_name||'',
                estimate_percent => 100,
                degree => $CHANNELS,
                no_invalidate => false);
                dbms_output.put_line(sysdate || ': stats done for ' || ''||i.owner||'' ||'.' || ''||i.index_name||'');
            exception
            when others then
            continue;
            dbms_output.put_line(sysdate || ': failed to gather stats for ' || ''||i.owner||'' ||'.' || ''||i.index_name||'' || chr(10) || sqlerrm);
         end;
    end loop;
           dbms_output.put_line(sysdate || ': #######################' || ': Stats done for Above Found Stale objects ' || ': #######################');
END;
/


EOF
echo "Done" >> $LOG

Hope this Helps......

how to mount the raw disks or nfs mounts that are listed in fstab

we can use this simple method to find what disks or mounts are missing before we bring the database up....

To understand this better.... initially the admins will put an entry in fstab for the disks and mounts that need to be mounted... but sometimes we dont see all disks come up.... it figure it out and make them available you can use this small script....

list=`awk '{print $2}' /etc/fstab |grep -i "/" | grep -v '^/etc/fstab' | grep -v '^/director' | grep -v '/abcdef_data' | egrep '/.'`         ----(you can exclude some more using grep -V)
for i in $list
do
grep -iw $i /proc/mounts > /dev/null
status=$?
if [ $status -eq 0 ]; then
echo " $i is mounted"
else
echo "$i is not mounted"                      ---------you can mount the missing one by adding (mount $i)                                                                      ... if you want it to mount directly)
fi
done

how to attach files to email in UNIX in shell scripts or command line

Use below command to send the files as attachment in Email. doing this also helps you to zip the files and email. also this reduces the email size too.
as we all know we send logfiles

Linux:-

echo body_text_you_want | mailx -a file_name.zip -s "Subject_line_you  want to specify" abcef@domain.com

for AIX:-

to attach a file

(echo "Subject:Test email\nTo:adbcef@domain.com\nFrom: adbcef@domain.com\n\nAttached log file";uuencode file_name file_name) |  /usr/sbin/sendmail abced@domain.com

to attach multiple files....

(echo "Subject:Test email\nTo:adbcef@domain.com\nFrom: adbcef@domain.com\n\nAttached log file";(uuencode file_name file_name; uuencode file_name_2 file_name_2)) |  /usr/sbin/sendmail abced@domain.com

Hope this helps.