There may/will be a SRID difference between SDE and SDO. So it may be better to not ask the SDE.ST_Geometry what its SRID is
SELECT site_id, name, SDO_GEOMETRY(sde.st_astext (location),sde.st_srid(location)) SDO_Location FROM hazardous_sites;
Of course, it may be faster to use ST_AsBinary() to convert via WKB.
SELECT site_id, name, SDO_GEOMETRY(sde.st_astext (location),8307) SDO_Location FROM hazardous_sites;
We have a clients data pump export installed in an Oracle 11g database with ARCSDE 10 to fix some bugs with our application.It occurs to me that you haven't told us some things. Does the "clients [sic] data pump export" contain SDE.ST_Geometry objects?
We would like to do some testing with this same data set but as SDO_GEOMETRY instead of the SDE.ST_GEOMETRY.
Is there any way to easily convert?