Home » RDBMS Server » Server Administration » Having many users (approx 100-150) using one tablespace
icon4.gif  Having many users (approx 100-150) using one tablespace [message #188795] Mon, 21 August 2006 13:23 Go to next message
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 Go to previous messageGo to next message
Mahesh Rajendran
Messages: 10672
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 Go to previous messageGo to next message
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 #188818 is a reply to message #188815] Mon, 21 August 2006 18:17 Go to previous messageGo to next message
Mahesh Rajendran
Messages: 10672
Registered: March 2002
Location: oracleDocoVille
Senior Member
Account Moderator
>> This means, a tablespace is created every time a tester does a fresh setup
That may be an overkill.
If this "application" you are talking about is just a generic one(and not i/o,performance bound),
what you have said
>>setup a tablespace for each tester, and they can create their users and schemas on it
Should work.

Re: Having many users (approx 100-150) using one tablespace [message #188875 is a reply to message #188818] Tue, 22 August 2006 04:24 Go to previous messageGo to next message
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 #188883 is a reply to message #188875] Tue, 22 August 2006 04:49 Go to previous messageGo to next message
Mahesh Rajendran
Messages: 10672
Registered: March 2002
Location: oracleDocoVille
Senior Member
Account Moderator
>> the application will need to be tested for performance
Just by seperating the tablespace, the application may not perform all good.
If this is a high i/o bound application ( as contrary to what your need is), spreading i/o across different tablespaces (datafiles in different disks) may help. Else, no big change.
Make use of LMT ( locally managed tablespace) with uniform extent size. In ideal cases, your "TEST" should include a fairly decent amount of data. As the data matures, your application performance may vary.
Re: Having many users (approx 100-150) using one tablespace [message #188900 is a reply to message #188883] Tue, 22 August 2006 05:30 Go to previous messageGo to next message
nisarg
Messages: 6
Registered: July 2006
Junior Member
So, just as wat you mentioned; if I need to setup my company's application and test it for heavy I/O (which will be a very rare case); I should spread the I/O across different tablespaces.

How can I have one user/schema to use multiple tablespaces?
Re: Having many users (approx 100-150) using one tablespace [message #188912 is a reply to message #188900] Tue, 22 August 2006 06:28 Go to previous messageGo to next message
Mahesh Rajendran
Messages: 10672
Registered: March 2002
Location: oracleDocoVille
Senior Member
Account Moderator
Quote:

alter user username quota < yourvalue /unlimited > on tablespace tablespace_name;

>>I should spread the I/O across different tablespaces
Different disks, if i may correct a little.
Re: Having many users (approx 100-150) using one tablespace [message #188914 is a reply to message #188795] Tue, 22 August 2006 06:44 Go to previous messageGo to next message
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.
Re: Having many users (approx 100-150) using one tablespace [message #188917 is a reply to message #188914] Tue, 22 August 2006 06:50 Go to previous messageGo to next message
Mahesh Rajendran
Messages: 10672
Registered: March 2002
Location: oracleDocoVille
Senior Member
Account Moderator
As said before, all the information is already available in documentation/this website.
You can get the documentation at http://tahiti.oracle.com/
>>How can I have the tablespace to use multiple datafiles
Alter tablespace tablespace_name add datafile '/disk1/fullpath/thedatafile_02.dbf' size yoursize;
Alter tablespace tablespace_name add datafile '/disk2/fullpath/thedatafile_03.dbf' size yoursize;



[Updated on: Tue, 22 August 2006 07:36]

Report message to a moderator

Re: Having many users (approx 100-150) using one tablespace [message #188922 is a reply to message #188795] Tue, 22 August 2006 07:31 Go to previous message
nisarg
Messages: 6
Registered: July 2006
Junior Member
Brilliant!!

Thank you so much...
This is a great place to fetch advice from, my postings will continue...!
Cheers!
Previous Topic: shutdwon and startup along with system reboot
Next Topic: Perl script and Database Partitioning
Goto Forum:
  


Current Time: Sun Dec 11 04:23:16 CST 2016

Total time taken to generate the page: 0.03969 seconds