oracle grant on all tables in schema
BEGIN
FOR R IN (SELECT owner, table_name FROM all_tables
WHERE owner='OTHER_SCHEMA') LOOP
EXECUTE IMMEDIATE 'GRANT SELECT, INSERT, UPDATE, DELETE ON ' ||
R.owner || '.' || R.table_name || ' TO MY_SCHEMA';
END LOOP;
END;