Tablespace management is outside the ken of ArcGIS. Best practice is to create one or more new tablespaces to manage tables owned by users. The SDE tablespace (or whatever has been created for SDE-owned objects) should never be used for user data -- if you overfill it, the geodatabase may fail to function.
Back in the old days, tablespace management was the primary task of database administrators, and there weren't any "AUTO" options available for segment management or BIGFILE TABLESPACEs to simplify allocation strategies. Unfortunately, recent de-emphasis of this aspect means that some ugly administration challenges can result.
Yes, you can use native SQL tools to manage both tablespace and user creation and allocation. It may be wise to review the ArcGIS documentation on minimum permissions, or to use the Create Database User tool at least once to make sure you've met the requirements for a data owner account, but once you've identified what is necessary, creating new users wouldn't require much work.
Don't forget that you can allocate more storage to tablespaces, up to the limits dictated by block size, BIGFILE/SMALLFILE property, and disk allocation on the device to which the datafile is allocated. If you have overfilled a tablespace, creating a new one won't alter the allocation of existing tables; you will likely need to transfer or recreate the table(s) in the new tablespace, which can cause "high water mark" issues on the previous tablespace. Untangling the nested issues may take a detailed implementation plan.
Good luck!
- V
