Hy guys,
i have a Oracle DB registered as Geodatabase Enterprise, i have create a new user with a specific tablespace. Now i must change this tablespace with a new tablespace with more space.
Can i create a new tablespace with SQL Plus and add to my user with SQL Plus or there is a specific procedure ?
Thanks
Regards
Solved! Go to Solution.
There are a number of potential solutions that don't require export to XML and back, which is the largest and slowest solution. Using this technique (and many others) won't address fragmentation of the SDE tablespace and high-water block allocation issues. I recommend you speak with an Oracle DBA before deciding on a procedure.
- V
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
Hi Vince,
thanks for your replay.
So if by SQL Plus i create a new tablespace and set this for the existing schema (user) the SDE user could be works fine?
If the user has overtopped the SDE tablespace, possibly not. There are too many unknowns to make an assertion either way:
If you have access to a more experienced DBA, I suggest you seek their guidance.
-V
Hi Vince,
thanks for your help.
After all your consideration i think that a good way for me is an export XML workspace of my User and after create a new user with new tablespace and import into it the XML workspace
What do you think?
Thanks
There are a number of potential solutions that don't require export to XML and back, which is the largest and slowest solution. Using this technique (and many others) won't address fragmentation of the SDE tablespace and high-water block allocation issues. I recommend you speak with an Oracle DBA before deciding on a procedure.
- V