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...

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;
/

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 

"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!

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;

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}'