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.

marți, 19 ianuarie 2016

How to install Oracle APEX

sqlplus / as sysdba
sql> SELECT dbms_xdb.gethttpport FROM dual;
sql> EXEC dbms_xdb.sethttpport(0);


>COL comp_name FOR A30
>SELECT comp_name, version, status FROM dba_registry WHERE comp_id='APEX';

 
If there is already installed:

cd $ORACLE_HOME/apex
$ sqlplus / as sysdba

sql> @apxremov_con.sql


If the tablespace is not there already:

CREATE TABLESPACE APEX DATAFILE '/u02/oradata/snap11g/apex_01.dbf'
SIZE 200M REUSE AUTOEXTEND ON NEXT 10M MAXSIZE 1000M LOGGING
EXTENT MANAGEMENT LOCAL
SEGMENT SPACE MANAGEMENT AUTO;

 sql> @apexins APEX APEX TEMP /i/

 admin password :

sqlplus / as sysdba
sql> @apxchpwd

Under the old apex versions you can edit the script summoned by the apxchpwd.sql (apxxepwd.sql) such as the password can be as strong or weak as you wish. Under the newer versions, you can decide this only from the admin interface.

 embedded PL/SQL Gateway configuration (EPG)

sql> @apex_epg_config.sql /u01/app/oracle/product/11.2.0.3/db_1

 Update the APEX images for the new release

sql> @apxldimg.sql /u01/app/oracle/product/11.2.0.3/db_1

ALTER USER anonymous ACCOUNT UNLOCK;
ALTER USER xdb ACCOUNT UNLOCK;
ALTER USER apex_public_user ACCOUNT UNLOCK;
ALTER USER flows_files ACCOUNT UNLOCK;

 enable the Oracle XML DB HTTP Server port (8080):

 EXEC dbms_xdb.sethttpport(8080);

Enable remote HTTP connections (optional):
EXEC dbms_xdb.setListenerLocalAccess(l_access => FALSE);

joi, 14 ianuarie 2016

ORA-39097: Data Pump job encountered unexpected error -12801 while expdp

ORA-39097: Data Pump job encountered unexpected error -12801 
ORA-39065: unexpected master process exception in MAIN 
ORA-12801: error signaled in parallel query server P..., instance <instance_name> 
ORA-01460: unimplemented or unreasonable conversion requested Job <job_name> stopped due to fatal error at <failure_time>. 



The problem seems to be a bug and the solution can be an update to minimum 11.2.0.4 version.
For me, the workaround was:

> alter system set  PARALLEL_FORCE_LOCAL=TRUE;

miercuri, 6 ianuarie 2016

Create audit triggers for all the tables of a schema and audit tables in another schema

First, create the  owner of the audit tables (in our scenario, will be "john_audit" and the audited schema will be "john"). Be aware that the audit user should mandatory name like audited name + "_audit".

CREATE OR REPLACE PROCEDURE create_audit_tables (table_owner VARCHAR2)
IS
   CURSOR c_tables (
      table_owner VARCHAR2)
   IS
SELECT ot.owner AS owner, ot.table_name AS table_name
        FROM all_tables ot
       WHERE    lower( ot.owner) = lower(table_owner)
             ;

   aud_user  varchar2(100);
   v_sql     VARCHAR2 (8000);
   v_count1  number;
   v_count2  number;
   v_count   NUMBER := 0;
   v_aud     VARCHAR2 (30);
   n_count   number;
BEGIN
   aud_user:=table_owner||'_AUDIT';
   select count(*) into v_count1  from dba_tables where  lower(owner)=lower(table_owner);
   select count(*) into v_count2  from dba_tables where  lower(owner)=lower(aud_user);
   v_count:=v_count1-v_count2;
   if v_count>0 then
   FOR r_table IN c_tables (table_owner)
   LOOP
      BEGIN
         v_aud := r_table.table_name;
         select count(*) into n_count from dba_tables where lower(table_name)=lower(v_aud) and lower(owner)=lower(aud_user);
        
         if n_count=0 then
        
         v_sql :=
               'create table '||aud_user
            ||    '.'
            || v_aud
            || ' as select * from '
            || r_table.owner
            || '.'
            || r_table.table_name
            || ' where 0 = 1';

         DBMS_OUTPUT.put_line ('Info: ' || v_sql);


         EXECUTE IMMEDIATE v_sql;

         v_sql :=
               'alter table  '||aud_user||'.'
            || v_aud
            || ' add ( AUDIT_ACTION char(1), AUDIT_BY varchar2(50), AUDIT_AT TIMESTAMP)';

         EXECUTE IMMEDIATE v_sql;

        end if;
     
        -- v_count := c_tables%ROWCOUNT;
      EXCEPTION
         WHEN OTHERS
         THEN
            DBMS_OUTPUT.put_line (
                  'Failed to create table '
               || v_aud
               || ' due to '
               || SQLERRM);
      END;
   END LOOP;
      end if;
   IF v_count = 0
   THEN
      DBMS_OUTPUT.put_line ('No audit tables created');
   ELSE
      DBMS_OUTPUT.put_line (v_count || ' audit tables created.');
   END IF;
END;
/


Run the procedure (will create audit tables in audit schema only for the tables created after the last run):
 
begin
 create_audit_tables('john'); --it's not caps sensitive
end;
/


Now, a function which will gatter the columns together:



create or replace FUNCTION get_columns_for_table (
     table_owner   VARCHAR2,
     t_name   VARCHAR2,
     prefix  VARCHAR2
  ) RETURN  CLOB
  IS
     v_text CLOB;
  BEGIN
     FOR getrec IN (SELECT column_name
                      FROM all_tab_columns
                     WHERE table_name = t_name
        AND owner = table_owner
        AND data_type<>'BLOB')
     LOOP
       v_text := v_text
          || ','
          || prefix
          || getrec.column_name
          || CHR (10)
          || '                             ';
     END LOOP;

     RETURN ltrim(v_text,',');
  END;


And a function that will compare the old and new columns values, in order to find if an update applied.



create or replace function get_column_comparison (
     table_owner   VARCHAR2,
     t_name   VARCHAR2
  ) RETURN CLOB
  IS
    v_text CLOB;
  BEGIN
    FOR getrec IN (SELECT column_name
                     FROM all_tab_columns
                    WHERE table_name = t_name
                      AND owner = table_owner
                      AND data_type<>'BLOB')
   LOOP
      v_text := v_text
         || ' or( (:old.'
         || getrec.column_name
         || ' <> :new.'
         || getrec.column_name
         || ') or (:old.'
         || getrec.column_name
         || ' IS NULL and  :new.'
         || getrec.column_name
         || ' IS NOT NULL)  or (:old.'
         || getrec.column_name
         || ' IS NOT NULL and  :new.'
         || getrec.column_name
         || ' IS NULL))'
         || CHR (10)
         || '                ';
   END LOOP;
 

   v_text := LTRIM (v_text, ' or');
   RETURN v_text;
  END; 


And now the procedure that will produce the triggers:



CREATE OR REPLACE PROCEDURE create_audit_triggers (table_owner VARCHAR2)
IS
   CURSOR c_tab_inc (
      table_owner VARCHAR2)
   IS
      SELECT ot.owner AS owner, ot.table_name AS table_name
        FROM all_tables ot
       WHERE     lower (ot.owner) =lower ( table_owner)
            ;
   aud_user  varchar2 (100);
   v_query   VARCHAR2 (32767);
   v_count   NUMBER := 0;
BEGIN
   aud_user:=table_owner||'_AUDIT';
   -- the triggers should have privileges in the audit schema
   FOR c IN (SELECT owner, table_name FROM all_tables WHERE lower(owner)=lower(aud_user))
        LOOP
            EXECUTE IMMEDIATE 'grant select,insert,update on '||c.owner||'.'||c.table_name||' to '||table_owner;
        END LOOP;
   FOR r_tab_inc IN c_tab_inc (table_owner)
   LOOP
      BEGIN


         v_query :=
               'CREATE OR REPLACE TRIGGER '
            ||table_owner
            || '.A'
            || substr(r_tab_inc.table_name,1,29)
            || ' AFTER INSERT OR UPDATE OR DELETE ON '
            || r_tab_inc.owner
            || '.'
            || r_tab_inc.table_name
            || ' FOR EACH ROW'
            || CHR (10)
            || 'DECLARE '
            || CHR (10)
            || ' v_user varchar2(30):=null;'
            || CHR (10)
            || ' v_action varchar2(15);'
            || CHR (10)
            || 'BEGIN'
            || CHR (10)
            || '   SELECT SYS_CONTEXT (''USERENV'', ''session_user'') session_user'
            || CHR (10)
            || '   INTO v_user'
            || CHR (10)
            || '   FROM DUAL;'
            || CHR (10)
            || ' if inserting then '
            || CHR (10)
            || ' v_action:=''INSERT'';'
            || CHR (10)
            || '      insert into '
            || aud_user
            || '.'
            || r_tab_inc.table_name

            || '('
            || get_columns_for_table (r_tab_inc.owner,
                                      r_tab_inc.table_name,
                                      NULL)
            || '      ,AUDIT_ACTION,AUDIT_BY,AUDIT_AT)'
            || CHR (10)
            || '      values ('
            || get_columns_for_table (r_tab_inc.owner,
                                      r_tab_inc.table_name,
                                      ':new.')
            || '      ,''I'',v_user,SYSDATE);'
            || CHR (10)
            || ' elsif updating then '
            || CHR (10)
            || ' v_action:=''UPDATE'';'
            || CHR (10)
            || '   if '
            || get_column_comparison (r_tab_inc.owner, r_tab_inc.table_name)
            || ' then '
            || CHR (10)
            || '      insert into '
            ||aud_user
            ||'.'
            || r_tab_inc.table_name
            || '('

            || get_columns_for_table (r_tab_inc.owner,
                                      r_tab_inc.table_name,
                                      NULL)
            || '      ,AUDIT_ACTION,AUDIT_BY,AUDIT_AT)'
            || CHR (10)
            || '      values ('
            || get_columns_for_table (r_tab_inc.owner,
                                      r_tab_inc.table_name,
                                      ':new.')
            || '      ,''U'',v_user,SYSDATE);'
            || CHR (10)
            || '   end if;'
            || ' elsif deleting then'
            || CHR (10)
            || ' v_action:=''DELETING'';'
            || CHR (10)
            || '      insert into '
            ||aud_user
            ||'.'
            || r_tab_inc.table_name
            || '('
            || get_columns_for_table (r_tab_inc.owner,
                                      r_tab_inc.table_name,
                                      NULL)
            || '      ,AUDIT_ACTION,AUDIT_BY,AUDIT_AT)'
            || CHR (10)
            || '      values ('
            || get_columns_for_table (r_tab_inc.owner,
                                      r_tab_inc.table_name,
                                      ':old.')
            || '      ,''D'',v_user,SYSDATE);'
            || CHR (10)
            || '   end if;'
            || CHR (10)
            || 'END;';

         DBMS_OUTPUT.put_line (
               'CREATE TRIGGER '
            || REPLACE (r_tab_inc.table_name, 'TABLE_', 'TRIGGER_'));

         EXECUTE IMMEDIATE v_query;

         DBMS_OUTPUT.put_line (
               'Audit trigger '
            || REPLACE (r_tab_inc.table_name, 'TABLE_', 'TRIGGER_')
            || ' created.');


         v_count := c_tab_inc%ROWCOUNT;
      EXCEPTION
         WHEN OTHERS
         THEN
            DBMS_OUTPUT.put_line (
                  'Failed to create audit trigger for '
               || r_tab_inc.owner
               || '.'
               || r_tab_inc.table_name
               || ' due to '
               || SQLERRM);
      END;
   END LOOP;

   IF v_count = 0
   THEN
      DBMS_OUTPUT.put_line ('No audit triggers created');
   END IF;
END;
/

Notice the sequence which grant the audited user to modify any table from the audit schema, created before with the create_audit_tables procedure.
Now, all you have to do is to run the procedure and you will create as many triggers as tables they are. The triggers will be either created or replaced, so the procedure will run for every single table in the schema, no mother if it was created after the last run of the
create_audit_tables procedure or not.


begin
CREATE_AUDIT_TRIGGERS('john');
end;
/

Create incrementing triggers for all the PKs from all the tables of a schema

This is the procedure:

CREATE OR REPLACE PROCEDURE create_pk_triggers (table_owner VARCHAR2)
IS
   CURSOR c_tables (
      table_owner VARCHAR2)
   IS

        select * FROM ALL_CONS_COLUMNS
                WHERE
                    CONSTRAINT_NAME IN ( SELECT CONSTRAINT_NAME FROM ALL_CONSTRAINTS where CONSTRAINT_TYPE = 'P') and
                    lower(owner)=lower(table_owner);

   v_sql     VARCHAR2 (8000);
   v_count   NUMBER := 0;
   v_aud     VARCHAR2 (30);
   v_col     varchar2(50);

  
BEGIN
   FOR r_table IN c_tables (table_owner)
   LOOP
      BEGIN
         v_aud := r_table.table_name;
         v_col := r_table.column_name;
         v_sql :=
               'CREATE OR REPLACE TRIGGER inspk_'
            ||substr(v_aud,1,24)
            || CHR (10)
            ||' before insert on '
            ||table_owner
            ||'.'
            || v_aud
            || CHR (10)
            || ' for each row begin '
            || CHR (10)
            || ' if inserting and :new.'
            || v_col
            || ' is null then '
            || CHR (10)
            || ':new.'
            || v_col
            || ' :='
            || ' call4prop_seq.nextval;'
            || CHR (10)
            || '  end if;'
            || CHR (10)
            || '   end;'
            ;

         DBMS_OUTPUT.put_line ('Info: ' || v_sql);

         EXECUTE IMMEDIATE v_sql;


         v_count := c_tables%ROWCOUNT;
      EXCEPTION
         WHEN OTHERS
         THEN
            DBMS_OUTPUT.put_line (
                  'Failed to create table '
               || v_aud
               || ' due to '
               || SQLERRM);
      END;
   END LOOP;

   IF v_count = 0
   THEN
      DBMS_OUTPUT.put_line ('No audit tables created');
   ELSE
      DBMS_OUTPUT.put_line (v_count || ' audit tables created.');
   END IF;
END;
/


And here is how to run the procedure, using as a parameter the name of the schema owner (the capitals rule will not apply):

 
begin
 create_pk_triggers('username');
end;
/