Update massive number of records

Simple Pl/SQL procedure which help to do massive update.

create or replace PROCEDURE move_column AS
  type num_table IS TABLE OF NUMBER INDEX BY pls_integer;
  round_trips number;
  recid_table num_table;
  avid_table num_table;
  scale NUMBER;
  starting_point NUMBER;
  ending_point NUMBER;
BEGIN
  SELECT recid,
    attrib_value_id bulk collect
  INTO recid_table,
    avid_table
  FROM very_big_table;
  scale := SQL % rowcount;

  round_trips := TRUNC((scale + 9999) / 10000);


  FOR bite IN 1 .. round_trips
  LOOP
    starting_point :=(bite -1) *10000 + 1;
    ending_point := least(scale,   bite *10000);
    forall this IN starting_point .. ending_point

      UPDATE another_very_big_table SET rec_id = recid_table(this)
       WHERE id = avid_table(this);
    COMMIT;
    --DBMS_OUTPUT.PUT_LINE('Move: ' || ending_point);
  END LOOP;

END move_column;

Problem with Oracle Enterprise manager

If you can't run Oracle Enterprise manager ...

$ ./opmnctl startall

ias-component/process-type/process-set:
HTTP_Server/HTTP_Server/HTTP_Server/

Error
--> Process (index=1,uid=186798,pid=9999)
failed to start a managed process after the maximum retry limit

$less Log:
/usr/home/oracle/product/10.1.3/OracleAS_1/opmn/logs/HTTP_Server~1

ias-component/process-type/process-set:
OC4J/home/default_group/

Error
--> Process (index=1,uid=1867980864,pid=450578)
time out while waiting for a managed process to start
Log:
/usr/home/oracle/product/10.1.3/OracleAS_1/opmn/logs/OC4J~home~default_group~1

You must chenge directory to Apache log directory an delete all files.

$cd ../../Apache/Apache/logs
$rm *

That's all ...

How to drop all tables from database using one SQL query

How to drop all tables from database using one SQL query without destroying database. It's impossible in Postgresql, MySQL ,Oracle. But we can generate sql.

#MySQL
mysql> SELECT CONCAT('DROP TABLE ', TABLE_NAME,' ;') FROM information_schema.TABLES WHERE TABLE_SCHEMA='mysql';
+----------------------------------------+
| CONCAT('DROP TABLE ', TABLE_NAME,' ;') |
+----------------------------------------+
| DROP TABLE columns_priv ;              | 
| DROP TABLE db ;                        | 
| DROP TABLE func ;                      | 
| DROP TABLE help_category ;             | 
| DROP TABLE help_keyword ;              | 
| DROP TABLE help_relation ;             | 
| DROP TABLE help_topic ;                | 
| DROP TABLE host ;                      | 
| DROP TABLE proc ;                      | 
| DROP TABLE procs_priv ;                | 
| DROP TABLE tables_priv ;               | 
| DROP TABLE time_zone ;                 | 
| DROP TABLE time_zone_leap_second ;     | 
| DROP TABLE time_zone_name ;            | 
| DROP TABLE time_zone_transition ;      | 
| DROP TABLE time_zone_transition_type ; | 
| DROP TABLE user ;                      | 
+----------------------------------------+
17 rows in set (0.02 sec)
Execute drop commands many times becouse of foreign keys ;-)

#Postgresql
SELECT 
'DROP TABLE '||c.relname ||' CASCADE;'
FROM pg_catalog.pg_class c
JOIN pg_catalog.pg_roles r ON r.oid = c.relowner
LEFT JOIN pg_catalog.pg_namespace n ON n.oid = c.relnamespace
WHERE c.relkind IN ('r','')
AND n.nspname NOT IN ('pg_catalog', 'pg_toast')
AND pg_catalog.pg_table_is_visible(c.oid)
ORDER BY 1;

?column?                     
-------------------------------------------------
DROP TABLE auth_group_permissions CASCADE;
DROP TABLE auth_group CASCADE;
DROP TABLE auth_message CASCADE;
DROP TABLE auth_permission CASCADE;
DROP TABLE auth_user_groups CASCADE;
...

#Oracle
SELECT 'DROP TABLE '||table_name||';' FROM user_tables;
'DROPTABLE'||TABLE_NAME||';'               
------------------------------------------ 
DROP TABLE SYS_EXPORT_SCHEMA_01;           
DROP TABLE MON_NO_CHECK;                   
DROP TABLE REWRITE_TABLE;