miercuri, 26 iulie 2017

Display all the tables in an Oracle schema, with fk relations and number of records, for the parent and the child tables

 select   a.owner powner, a.table_name tableswithoutpk,
          b.table_name ptable, 
          to_number(extractvalue(xmltype(dbms_xmlgen.getxml('SELECT 
          count(*) c FROM ' || a.owner || '.' || 
           '"'||a.table_name||'"')),'/ROWSET/ROW/C')) pcount,
          b.constraint_name pkname, b.status pkstatus, 
          c.table_name chtable, c.owner chowner, c.constraint_name 
          fkname, c.status fkstatus,
          decode(c.table_name, null, null, 
          to_number(extractvalue(xmltype(dbms_xmlgen.getxml('SELECT 
          count(*) c FROM ' || c.owner || '.' ||                
          '"'||c.table_name||'"')),'/ROWSET/ROW/C'))) chCount 
                 from all_tables a   
                 left outer join all_constraints b on 
                   b.owner='SCOTT' and b.constraint_type='P' and 
                   a.table_name=b.table_name
                 left outer join all_constraints c on 
                   c.R_constraint_name=b.constraint_name  
                 where a.owner='SCOTT' ;


powner and tableswithoutpk - the owner and the name of the table, no matter if it has a primary key

ptable - the name of the parent table, is not null only if the table has a primary key

pcount - the number of records of the parent table

pkname - the name of the primary key constraint

pkstatus - the status of the primary key constraint

chtable, chowner - the name and the owner of the child table. They will be null if the parent table has no childs (foreign keys that references it). The owner is not necessarily the same with the owner of the parent.

fkname - the name of the foreign key constraint name

chcount - the number of records of the child table


How to empty all the tables in an Oracle schema, disregarding any foreign key

The main problem when you want to empty all the tables in a schema is the foreign keys. It's pretty simple to disable them, but meanwhile the users can write some data, data that can lead to errors, without the enforced constraints.
So, there it is a script, made to empty the tables, without disabling any foreign key. Pay attention to the posible foreign keys from another schema, this script is to be run under the sys user, and the tables from another schema, that reference your tables, from the schema wanted to be empty, will be emptied as well. The ow variable is containing the name of the schema owner.
Enjoy!

declare
ow varchar2(100);
rcount number;
begin 

ow:='SCOTT';
rcount:=0;

for i in (select * from (
            select      a.owner powner,a.table_name tableswithoutpk, b.table_name ptable, 
            to_number(extractvalue(xmltype(dbms_xmlgen.getxml('SELECT count(*) c FROM ' || a.owner || '.' || '"'||a.table_name||'"')),'/ROWSET/ROW/C')) pcount,
            b.constraint_name pkname, b.status pkstatus, 
            c.table_name chtable, c.owner chowner, c.constraint_name fkname, c.status fkstatus,
                decode(c.table_name, null, null, to_number(extractvalue(xmltype(dbms_xmlgen.getxml('SELECT count(*) c FROM ' || c.owner || '.' || '"'||c.table_name||'"')),'/ROWSET/ROW/C'))) chCount 
            from all_tables a   
                left outer join all_constraints b on b.owner=ow and b.constraint_type='P' and a.table_name=b.table_name
                left outer join all_constraints c on c.R_constraint_name=b.constraint_name  
            where a.owner=ow order by ptable
            )
            where chcount is null
        )
        loop
        null;
       execute immediate 'delete from '||i.powner ||'.'||i.tableswithoutpk;
        end loop;
        
        
        while true LOOP

        for i in (select * from (
                            select   a.owner powner,a.table_name tableswithoutpk, b.table_name ptable, 
                                to_number(extractvalue(xmltype(dbms_xmlgen.getxml('SELECT count(*) c FROM ' || a.owner || '.' || '"'||a.table_name||'"')),'/ROWSET/ROW/C')) pcount,
                                b.constraint_name pkname, b.status pkstatus, 
                                c.table_name chtable, c.owner chowner, c.constraint_name fkname, c.status fkstatus,
                                decode(c.table_name, null, null, to_number(extractvalue(xmltype(dbms_xmlgen.getxml('SELECT count(*) c FROM ' || c.owner || '.' || '"'||c.table_name||'"')),'/ROWSET/ROW/C'))) chCount 
                                from all_tables a   
                                left outer join all_constraints b on b.owner=ow and b.constraint_type='P' and a.table_name=b.table_name
                                left outer join all_constraints c on c.R_constraint_name=b.constraint_name  
                                where a.owner=ow order by ptable
                                )
                    
                    where chcount >0)
                    loop
                    execute immediate 'delete from '||i.chowner||'.'||i.chtable;
                    end loop;
                    
                    
        for i in (select * from (
                            select   a.owner powner, a.table_name tableswithoutpk,b.table_name ptable, 
                                to_number(extractvalue(xmltype(dbms_xmlgen.getxml('SELECT count(*) c FROM ' || a.owner || '.' || '"'||a.table_name||'"')),'/ROWSET/ROW/C')) pcount,
                                b.constraint_name pkname, b.status pkstatus, 
                                c.table_name chtable, c.owner chowner, c.constraint_name fkname, c.status fkstatus,
                                decode(c.table_name, null, null, to_number(extractvalue(xmltype(dbms_xmlgen.getxml('SELECT count(*) c FROM ' || c.owner || '.' || '"'||c.table_name||'"')),'/ROWSET/ROW/C'))) chCount 
                                from all_tables a   
                                 join all_constraints b on b.owner=ow and b.constraint_type='P' and a.table_name=b.table_name
                                 join all_constraints c on c.R_constraint_name=b.constraint_name  
                                where a.owner=ow order by ptable
                                )
                    
                    where chcount =0 and pcount>0)
                    loop
                    execute immediate 'delete from '||i.powner||'.'||i.ptable;
                    end loop;                    
dbms_output.put_line('444');
   select sum(pcount) into rcount from 
                                (
                                    select * from (
                                    select   a.owner powner, a.table_name tableswithoutpk, b.table_name ptable, 
                                    to_number(extractvalue(xmltype(dbms_xmlgen.getxml('SELECT count(*) c FROM ' || a.owner || '.' || '"'||a.table_name||'"')),'/ROWSET/ROW/C')) pcount,
                                    b.constraint_name pkname, b.status pkstatus, 
                                    c.table_name chtable, c.owner chowner, c.constraint_name fkname, c.status fkstatus,
                                    decode(c.table_name, null, null, to_number(extractvalue(xmltype(dbms_xmlgen.getxml('SELECT count(*) c FROM ' || c.owner || '.' || '"'||c.table_name||'"')),'/ROWSET/ROW/C'))) chCount 
                                    from all_tables a   
                                    /*left outer */join all_constraints b on b.owner=ow and b.constraint_type='P' and a.table_name=b.table_name
                                    /*left outer*/ join all_constraints c on c.R_constraint_name=b.constraint_name  
                                    where a.owner=ow order by ptable
                                                    )
                                   )
                                   ;
   EXIT WHEN rcount=0;
      END LOOP;        
end;

... and don't forget to commit!!
If you want to make some exercises, using the SCOTT schema, you want to save somewhere the content of the SCOTT tables. Use these lines bellow:

 begin
for i in (select * from all_tables where owner='SCOTT') loop
    execute immediate 'create table '||i.table_name||' as select * from SCOTT.'||i.table_name;
    end loop;
end;
/    

... creates the backup tables, under the sys schema



begin
for i in (select * from all_tables where owner='SCOTT') loop
    execute immediate 'insert into scott.'||i.table_name||'  select * from '||i.table_name;
    end loop;
end;
/  

... reloads the SCOTT tables, for a future run...

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;