I should note here that you should not ever create spatial data (or any data) tables as the SDE user! Doing so puts you at risk of corrupting your ArcGIS geodatabase!
Best practice on Oracle is to create at least one new TABLESPACE to store your data, then at least one user to own data (with DEFAULT TABLESPACE newlycreatedtablespace). The SDE tablespace should be restricted to the SDE user (much the same way random users shouldn't write to the SYSTEM tablespace).
Basic SQL security best practice is to make use of database roles. In this context, the owner user would only have connections associated with database design changes, and all normal application queries would come as a user other than the table owner (to prevent unfortunate incidents). SELECT or SELECT/INSERT/UPDATE/DELETE access to non-owners would be granted to ROLES, with one role created for each distinct access pattern, then users would be granted membership in roles based on their access needs.
At this point, your SDE tablespace is likely cluttered with non-administrative tables. You'll need to copy these non-admin tables to new tables in a new tablespace (as a new user), then very carefully delete all the non-admin tables. If you've done this correctly, you should be able to shrink down the SDE tablespace to the original size (200-300Mb, depending on release). If you cannot shrink the SDE tablespace, it's because the admin tables have already been fragmented to be interleaved with spatial data (there are ways to deal with this, but leaving the SDE tablespace unnaturally large is a viable option).
- V
