Avi,
I've not used OLEDB much; it's pretty much obsolete. Much better to use SAS/Access to Oracle if you have it and ODBC if you don't. SAS/Access to Oracle is optimized to get the maximum performance and features out of the linkage, so using that could make the transfer much quicker.
If you are doing SQL pass-through, then the WHERE and other constructs go through to the host UNLESS SAS recognizes them as not being supported. You probably want to read the SAS manuals on OLEDB to see if that is causing the entire table (or set of tables) to come to SAS from Oracle.
The maximum size of a SAS dataset is generally bigger than the maximum allowed by the operating system; I suspect the OS disk drive is running out of space (or out of the allocation allowed by your network admin). You can modify the length of the individual columns as they are stored into the table by PROC SQL; see the manual for how to do that. You also may be getting the error on your WORK library, especially if SAS decides to bring everything over from Oracle, so check the source.
To link new data to an archive, you can bring in the new data and combine it with the archive in a separate step. Since space is an issue, you may want to create a VIEW to do the combining.
Doc Muhlbaier
Duke