Oracle - Is it possible to maintain transaction across multiple schemas? I got this database in oracle which includes 4 schemas. Each schema represents a logical layer. Each transaction (Stored Procedure) in the upper layer breaks into a couple of transactions in the lower layer. So there is this scenario:

I execute a SP in the upper layer which results in some inserts in both layers but for some reason one of the transactions in the lower layer fails. Is it possible to rollback the whole thing?
yes you can, just be sure that the transactions are defined by the uppper layer - the client. A transaction has to be Atomic, so has to succeed (and commit) or has to fail (and rollback).