Grant Initial Tablespace Quota to user
Hi Oracle Experts
I ship scripts to a range of clients. The database runs under its own schema and the schema owner has always been setup as a dba, so tablespace quotas have neber been an issue.
Recent some more clued up client dba's have objected to our user having dba, so we have assigned all the create permissions etc. We also used the GRANT UNLIMITED TABLESPACE for the schema owner. Now they have objected to this.
I;m quite happy to Grant a quota for the tablespaces but an unsure what should be the initial size.
I have 2 scenarios that coiuld happen - a production install (no test/traing data). From creating a clean db, when this is complete the following tabalespace sizes have changed
SYSAUX increased by 10,485,760 bytes
USERS increased by 26,214,400 bytes
SYSTEM increased by 10485,760 bytes
If I also load training data the increase to USERS is 61,603,840 bytes
What Quota grants should I assign to my user? Is it sufficient to assign any value and does Oracle automatically increase it, or do I have to calculate an amount?
I ship scripts to a range of clients. The database runs under its own schema and the schema owner has always been setup as a dba, so tablespace quotas have neber been an issue.
Recent some more clued up client dba's have objected to our user having dba, so we have assigned all the create permissions etc. We also used the GRANT UNLIMITED TABLESPACE for the schema owner. Now they have objected to this.
I;m quite happy to Grant a quota for the tablespaces but an unsure what should be the initial size.
I have 2 scenarios that coiuld happen - a production install (no test/traing data). From creating a clean db, when this is complete the following tabalespace sizes have changed
SYSAUX increased by 10,485,760 bytes
USERS increased by 26,214,400 bytes
SYSTEM increased by 10485,760 bytes
If I also load training data the increase to USERS is 61,603,840 bytes
What Quota grants should I assign to my user? Is it sufficient to assign any value and does Oracle automatically increase it, or do I have to calculate an amount?
I would recommend creating a separate tablespace just for your data, whatever your app is called.
Then set a quota unlimited on that tablespace only, and set that tablespace either large enough, or set it to AUTOEXTEND with a MAXSIZE of something safe (big enough for you to have room to breathe, but small enough not to worry your client dba).
Then set a quota unlimited on that tablespace only, and set that tablespace either large enough, or set it to AUTOEXTEND with a MAXSIZE of something safe (big enough for you to have room to breathe, but small enough not to worry your client dba).
The other advantage to using your own tablespace is you can do pluggable tablespaces and move your app between databases without export (if that is an advantage)
ASKER
Hmmm, the pluggable tablespaces has caught my interest, as often we require an export of the database to be sent to us for testing/problem resolution. Would the pluggable allow us to get a copy of that tablespace (presumably as a file) and then add it to a support db on our systems and have up and running?
Kelvin
Kelvin
Pluggable is a new term to me. I'm used to transportable tablespaces. Pluggable might be something new.
That is exactly what transportable tablespaces means. Take it from point A, move it and make it part of point B.
That is exactly what transportable tablespaces means. Take it from point A, move it and make it part of point B.
ASKER
And moving from a server in say UK, to a server in New Zealand - achievable?
It's a physical move so as long as the file shows up at the destination, it doesn't matter.
Oracle doesn't care as long as al the requirements are met.
The online docs talk about this in detail. On mobile right now so cannot provide the exact link.
Oracle doesn't care as long as al the requirements are met.
The online docs talk about this in detail. On mobile right now so cannot provide the exact link.
SOLUTION
membership
This solution is only available to members.
To access this solution, you must be a member of Experts Exchange.
ASKER CERTIFIED SOLUTION
membership
This solution is only available to members.
To access this solution, you must be a member of Experts Exchange.
ASKER
Thanks to you both
We cannot answer what you need to set it to.
I suggest you create your own app tablespaces and not use any Oracle defaults. This way your app can have unlimited of them.