vineri, 24 martie 2017

Grant select on all tables and views (except the invalid views) of a schema to another user

begin
 

for i in (select object_name from user_objects where object_type in ('TABLE','VIEW')) LOOP
 

  begin
   execute immediate 'grant select on '||i.object_name||' to    

   grantee_user';
   EXCEPTION

   WHEN OTHERS THEN 
    dbms_output.put_line('err_num: '||SQLCODE);
    dbms_output.put_line('err_msg: '||SUBSTR(SQLERRM, 1, 100));
    dbms_output.put_line('object_name: '||i.object_name);
    continue;
   END;
 end loop;
end;
/

joi, 29 septembrie 2016

Export SQL query to csv and send it as a file attachment

I will use the PLSQL to fill up the file with queried data, then a linux script to send the mail.


declare
   v_file     UTL_FILE.file_type;
   v_string   VARCHAR2 (4000);

BEGIN
   v_file :=
      UTL_FILE.fopen ('DUMP2_FLORIN',
                      'tt.csv',
                      'w',
                      1000);

   v_string := 'csf_code, op_code';
   UTL_FILE.put_line (v_file, v_string);





-- header above
 

   FOR cur IN (SELECT csf_code, op_code
        FROM prg_measures)
   LOOP
      v_string :=
            cur.csf_code
         || ','
         || cur.op_code
         ;
      UTL_FILE.put_line (v_file, v_string);
   END LOOP;

   UTL_FILE.fclose (v_file);
EXCEPTION
   WHEN OTHERS
   THEN
      IF UTL_FILE.is_open (v_file)
      THEN
         UTL_FILE.fclose (v_file);
      END IF;
END;



Configure the sendmail client:

vi /etc/mail/submit.cf

 find the line:

D{MTAHost}[127.0.0.1]

and change it to 

D{MTAHost}mailserver 

where "mailserver" is the mail server host

Now, the linux script:

from="florin.videa@domain.ro"
to="florin.videa@domain.ro"
subject="this is the title"
boundary="ZZ_/afg6432dfgkl.94531q"
body="This is the body"
declare -a attachments
attachments=( "tt.csv"  "tt.csv" ) #here you can put more files

# Build headers
{

printf '%s\n' "From: $from
To: $to
Subject: $subject
Mime-Version: 1.0
Content-Type: multipart/mixed; boundary=\"$boundary\"

--${boundary}
Content-Type: text/plain; charset=\"US-ASCII\"
Content-Transfer-Encoding: 7bit
Content-Disposition: inline

$body
"

# now loop over the attachments, guess the type
# and produce the corresponding part, encoded base64
for file in "${attachments[@]}"; do

  [ ! -f "$file" ] && echo "Warning: attachment $file not found, skipping" >&2 && continue

  mimetype=$(file -b --mime "$file")

  printf '%s\n' "--${boundary}
Content-Type: $mimetype
Content-Transfer-Encoding: base64
Content-Disposition: attachment; filename=\"$file\"
"

  base64 "$file"
  echo
done

# print last boundary with closing --
printf '%s\n' "--${boundary}--"

} | sendmail -t -oi   # one may also use -f here to set the envelope-from

miercuri, 21 septembrie 2016

How to free swap in Linux

First, verify with free command how much swap it is used.
If you want to free all the swap:

>swapoff -a

Meanwhile, verify from another command prompt the pace of swap freeing, running the free command on and on.

>swapon -a 


joi, 1 septembrie 2016

"ora - 02248 : invalid option for ALTER SESSION" when try to connect to database from Forms Builder 9.0.4

If you run the Builder from Windows 7, don't bother to follow any advice from any site on the net, the solution is to change the OS. It's true the problem comes from the combination builder - database, but you can't do anything but what i said above to cope with.
Take the 9.0.4 kit (if you have it, because i think it is imposible to download nowadays) and install it on, let's say, win xp!
Good luck!

joi, 14 iulie 2016

The top 10 first hungriest queries

SELECT Disk_Reads DiskReads, Executions, SQL_ID, SQL_Text SQLText,
   SQL_FullText SQLFullText
FROM
(
   SELECT Disk_Reads, Executions, SQL_ID, LTRIM(SQL_Text) SQL_Text,
      SQL_FullText, Operation, Options,
      Row_Number() OVER
         (Partition By sql_text ORDER BY Disk_Reads * Executions DESC)
         KeepHighSQL
   FROM
   (
       SELECT Avg(Disk_Reads) OVER (Partition By sql_text) Disk_Reads,
          Max(Executions) OVER (Partition By sql_text) Executions,
          t.SQL_ID, sql_text, sql_fulltext, p.operation,p.options
       FROM gv$sql t, gv$sql_plan p
       WHERE t.hash_value=p.hash_value AND p.operation='TABLE ACCESS'
       AND p.options='FULL' AND p.object_owner NOT IN ('SYS','SYSTEM')
       AND t.Executions > 1
   )
   ORDER BY DISK_READS * EXECUTIONS DESC
)
WHERE KeepHighSQL = 1
AND rownum <=10;

vineri, 8 iulie 2016

Linux: Display the usage of processors and memory without provisioning values (the real usage)

Procs (two alternatives):

top -bn1 | grep "Cpu(s)" | sed "s/.*, *\([0-9.]*\)%* id.*/\1/" | awk '{print 100 - $1"%"}'

grep 'cpu ' /proc/stat | awk '{usage=($2+$4)*100/($2+$4+$5)} END {print usage "%"}'


Memory:
 
ps aux | awk '{sum+=$6} END {print sum / 1024}'

joi, 24 martie 2016

Linux: reclaim your space after deleting some files

The problem is a simple one, but very annoying one: one or some of the deleted file (usually some big one/ones) are still opened by their parent processes. So, look for that processes, kill them, and you'll notice the df command will report propper results.