 Having many users (approx 100-150) using one tablespace [message #188795] |
Mon, 21 August 2006 13:23  |
nisarg
Messages: 6
Registered: July 2006
|
Junior Member |
|
|
Hi all,
I have been assigned a task to organise a big number of datafiles and tablespaces. The server is chiefly used by testing team to create their application database. There is no DBA as such specifically looking after this server. At the moment, everytime the testing team needs to do installation of the application, they create an individual tablespace and datafile and user with required priviledges, and assign the newly created tablespace to that user. There has been very little ad-hoc maintanence done on this server. Now the server has a massive number of tablespaces and datafiles and I have been assigned the job to organise them.
I am planning to create autoextendable tablespace and associated datafiles for each testing team-member. The team-member can then use their individual tablespaces to create their application user. I believe this would make it easier for them to create application schema and also will be more efficient to do backups.
I am a new kid on the block, thriving to learn oracle; am wondering:
-How far do you experts think this is a good idea. Any suggestions?
-Is there a limit on the number of users assigned to one tablespace?
-Will this help performance? Or will it have adverse effects on the server's performance?
-Will it be helpful to have transportable tablespaces for the team-members, if it can help backuping each team-members databases? If this is adviceble, how can do that?
Any inputs will be highly appreciated.
|
|
|
|
| Re: Having many users (approx 100-150) using one tablespace [message #188809 is a reply to message #188795] |
Mon, 21 August 2006 15:38  |
 |
Mahesh Rajendran
Messages: 10708
Registered: March 2002
Location: oracleDocoVille
|
Senior Member
Account Moderator |
|
|
I suggest you to search the forum before posting.Almost all the questions are already answered many times.
>>I am planning to create autoextendable tablespace and associated datafiles for each testing team-member.
autoextendable has both good and bad. In the better side, you no need to babysit the space requirements. On the bad side, at sometime, you will run out of disk-space and anyhow
your database operations fail. If your hard disks are full, you have to call your system admins to add more disk/raw disk space/file system whatever it is.
I do not prefer autoextention.
A simple script can monitor the space usage and we can manually increase space as recommended. A similar discussion here.
http://www.orafaq.com/forum/m/182620/42800/?srch=autoextend#msg_182620
>>Is there a limit on the number of users assigned to one tablespace?
No.
You can have any number of users having the same tablespace as default tablespace.
I would rather consider the backup/recovery/downtime implications. If all users/schema have data in same tablespace and if that tablespace is down, all users are affected. What happends you want to restore that tablespace? All other schemas are also affected. For development environment, each application can take a separate tablespace.
>>Will this help performance? Or will it have adverse effects on the server's performance?
What is "this" here?
It Depends on your server, number of users, application usage etc.
>>Will it be helpful to have transportable tablespaces for the team-members,
>>if it can help backuping each team-members databases? If this is adviceble,
>>how can do that?
Transportable tablespaces are meant as method for moving data between databases. It is NOT a backup solution.
Since this is just a development environment, it suppose it ok to have things like this. Seperate tablespace for each user.
In some cases (depending on your application ) you can group many users to use the same tablespace. It is upto your discretion.
What ever you do, take a proper backup.
|
|
|
|
| Re: Having many users (approx 100-150) using one tablespace [message #188815 is a reply to message #188795] |
Mon, 21 August 2006 16:32 |
nisarg
Messages: 6
Registered: July 2006
|
Junior Member |
|
|
Thanks very for your response!!
Let me quickly brief you through my case. As I mentioned, the db server is owned by the testing team. The team tests an application that is setup uses a dsn configured to use a schema on the database server. So, our testing team sets up a tablespace and creates a user/schema and setups up the application to be tested against it. This means, a tablespace is created every time a tester does a fresh setup of the application. And the frequency of doing a fresh setup of the application is quite high. On an average basis each user does a fresh setup of the application once in every 2 days.
So, my plan is to setup a tablespace for each tester, and they can create their users and schemas on it. So, say if 5 testers, it would mean 5 tablespaces, each tester can create their individual application database(user/schema) to use their personal tablespace.
As I said, I am not an experienced DBA and am still learning a lot of stuff I seek advice managing the stuff from you techies!
Again, inputs much appreciated....
|
|
|
|
|
|
| Re: Having many users (approx 100-150) using one tablespace [message #188875 is a reply to message #188818] |
Tue, 22 August 2006 04:24 |
nisarg
Messages: 6
Registered: July 2006
|
Junior Member |
|
|
Yes, the application is pretty generic one. The amount of I/O is not extremely high. The usual day-to-day testing expects a normal level of performance(i.e. a prompt reply back from the db).
Occasionally the application will need to be tested for performance. For that times, I will accept request from users(testers) to create them an independent tablespace for a massive db installation and can test the application against that.
How does that sound?
|
|
|
|
|
|
|
|
|
|
| Re: Having many users (approx 100-150) using one tablespace [message #188914 is a reply to message #188795] |
Tue, 22 August 2006 06:44 |
nisarg
Messages: 6
Registered: July 2006
|
Junior Member |
|
|
OK great!! Thanks very much for all the responses, Mahesh!
I am still slightly confused what do you excatly mean by saying spreading I/O across different disks.
Also, say if a user swells up a tablespace and needs extending. And at the same time I run out of disk-space to extend the datafile.
How can I have the tablespace to use multiple datafiles. From the instance above, say if I add a new disk on to the machine and want the tablespace to use the newly added datafile that is on an drive.
|
|
|
|
|
|
|
|
] 
Blog
Search
Help
Members
Register
Login
Home
