topic Re: SDE change tablespace in Geodatabase Questions

SDE change tablespace

DEVAPP — Fri, 11 Dec 2015 19:19:04 GMT

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

Re: SDE change tablespace

VinceAngelo — Fri, 11 Dec 2015 20:20:26 GMT

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

Re: SDE change tablespace

DEVAPP — Fri, 11 Dec 2015 21:24:00 GMT

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?

Re: SDE change tablespace

VinceAngelo — Fri, 11 Dec 2015 21:54:11 GMT

If the user has overtopped the SDE tablespace, possibly not. There are too many unknowns to make an assertion either way:

Was the user assigned to the SDE tablespace?
Was the SDE tablespace overfilled? If so, was it configured as a SMALLFILE or BIGFILE tablespace?
Did the tablespace max out or the disk partition fail to allocate additional storage?
Have you DROPped any tables which were created by ArcGIS by SQL?
Have you made a recent backup of the database, and is the archive log enabled?
Have you made an extra backup of the database, in its broken state?
Have you tried using ALTER INDEX .. REBUILD with a TABLESPACE option to reduce the load on the overburdened tablespace?

If you have access to a more experienced DBA, I suggest you seek their guidance.

-V

Re: SDE change tablespace

DEVAPP — Sat, 12 Dec 2015 08:09:01 GMT

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

Re: SDE change tablespace

VinceAngelo — Mon, 14 Dec 2015 15:42:59 GMT

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