- Community Home
- >
- Servers and Operating Systems
- >
- Operating Systems
- >
- Operating System - HP-UX
- >
- Re: manage tablespace
Categories
Company
Local Language
Forums
Discussions
Forums
- Data Protection and Retention
- Entry Storage Systems
- Legacy
- Midrange and Enterprise Storage
- Storage Networking
- HPE Nimble Storage
Discussions
Discussions
Discussions
Forums
Forums
Discussions
Discussion Boards
Discussion Boards
Discussion Boards
Discussion Boards
- BladeSystem Infrastructure and Application Solutions
- Appliance Servers
- Alpha Servers
- BackOffice Products
- Internet Products
- HPE 9000 and HPE e3000 Servers
- Networking
- Netservers
- Secure OS Software for Linux
- Server Management (Insight Manager 7)
- Windows Server 2003
- Operating System - Tru64 Unix
- ProLiant Deployment and Provisioning
- Linux-Based Community / Regional
- Microsoft System Center Integration
Discussion Boards
Discussion Boards
Discussion Boards
Discussion Boards
Discussion Boards
Discussion Boards
Discussion Boards
Discussion Boards
Discussion Boards
Discussion Boards
Discussion Boards
Discussion Boards
Discussion Boards
Discussion Boards
Discussion Boards
Discussion Boards
Discussion Boards
Discussion Boards
Discussion Boards
Discussion Boards
Discussion Boards
Discussion Boards
Community
Resources
Forums
Blogs
- Subscribe to RSS Feed
- Mark Topic as New
- Mark Topic as Read
- Float this Topic for Current User
- Bookmark
- Subscribe
- Printer Friendly Page
- Mark as New
- Bookmark
- Subscribe
- Mute
- Subscribe to RSS Feed
- Permalink
- Report Inappropriate Content
тАО02-08-2007 09:34 PM
тАО02-08-2007 09:34 PM
I would like ask a simple question :
some tablespace on db are full so I have to increase the size with the instruction
ALTER DATABASE
DATAFILE '...../users01.dbf' RESIZE
500M
or the same with the tool oracle enterprise manager, or with the follow instruction:
ALTER TABLESPACE users
ADD DATAFILE
'...../users02.dbf' SIZE 200M
AUTOEXTEND ON NEXT 10M MAXSIZE 500M;
1) in which case is better add another datafile
instead of increase the existing ?
2) is possible to increase it on line, while users are working on it ?
thanks
Dartan
Solved! Go to Solution.
- Mark as New
- Bookmark
- Subscribe
- Mute
- Subscribe to RSS Feed
- Permalink
- Report Inappropriate Content
тАО02-08-2007 10:00 PM
тАО02-08-2007 10:00 PM
Re: manage tablespace
your question it's a good question.
By my side, I always use "alter tablespace ... add datafile..." (especially on unix server) so i can decide if autoextend or not (i use don't extend) and use other option.
hth
regards
pg
- Mark as New
- Bookmark
- Subscribe
- Mute
- Subscribe to RSS Feed
- Permalink
- Report Inappropriate Content
тАО02-08-2007 10:26 PM
тАО02-08-2007 10:26 PM
SolutionBetter for who ot what?
Performance? Simplicity ? Backups ?
- extending an existing datafile keeps it clean and simple. Nothing new to deal with when using OS tools for the DB files. When using Oracle tools the datafiles are pretty much transparent.
- adding a datafile gives you the option to pick a new mountpoint which in turn may give you more space to grow, better/more access paths to the IO subsystem. The relevance of this depends on exct details of the storage/hpux configuration.
- adding a datafile may be needed if you have a datafile size limitation (a real limitation, or just a policy perhpas)
>> 2) is possible to increase it on line, while users are working on it ?
Adding a datafile can be done online for sure. According to the manual reszing is also done online but I never tried that. Read the manual! Read the other references:
google: +site:oracle.com +"alter datafile" +resize
http://download-west.oracle.com/docs/cd/B19306_01/server.102/b14231/dfiles.htm
btw... this question, and any follow up questions are probaly better asked to the Oracle community, as there is little or no hpux aspect to it.
hth,
Hein van den Heuvel
HvdH Perfomance Consulting
- Mark as New
- Bookmark
- Subscribe
- Mute
- Subscribe to RSS Feed
- Permalink
- Report Inappropriate Content
тАО02-09-2007 04:31 AM
тАО02-09-2007 04:31 AM
Re: manage tablespace
I like to spread the load around in my very busiest database files - so they are likely to travel all over the database landing spots. But the smaller modules that don't grow and just need a little more space, I generally just resize more space right where they are.
Keep in mind that as you increase the number of datafiles, the number of open files on the server at once go up dramatically (each connection to the database can and will open a number of files itself). So it's important to keep an eye on all of various file limiting kernel parameters and make sure you don't go over (nfile, maxfiles, maxfiles_lim).
It's also a good time to take advantage of some abilities to increase your performance.
If this is a fast growing data area - I will often use the opportunity to not just add one file, add 5 or 6 datafiles across several mount points, and let my data stripe across quite a bit of hardware from my storage arrays.
Also, practically speaking, unless my agenda
is going for something more or different - I often let my files max out at 2G (1999M actually) - this is just an old habit from way back when I had noticed that lots of things seem to not like files over that size, and not just the file system itself. You can (and many folks do) however, run fine with larger files - it's just my preference.
Re: autoextend - you'll catch a lot of flack on this for leaving it turned on. I too leave it on ( and at times catch flack - mainly in this forum ... :-) ), but I never use it as the main source of data growth. One problem (there are more) is that it really leaves your big data files pretty well cut up over the file system space, especially if you have many files all taking their turn, each grabbing the next 10M or so. If you've got lots of tablespaces, it can get pretty ugly in there. I prefer to use it as a fail-safe mechanism, I routinely go over my file systems and look for things about to go over their file space limitations, and manuall intervene in various ways ( but certiainly not in little 10M chunks). And, in case I miss it - I have cron jobs that watch the alert file and scan for autoextending activity - which email and let me know that I need to jump in and check out the growth and intervene. Which all just says, don't let autoextend play even much of a minor role in your storage strategy. Of course, since you're asking about autoextending, I'm thinking that was probably your plan all along anyway. The point is, of course, just be careful with autoextend.
Good luck.
- Mark as New
- Bookmark
- Subscribe
- Mute
- Subscribe to RSS Feed
- Permalink
- Report Inappropriate Content
тАО02-09-2007 12:54 PM
тАО02-09-2007 12:54 PM
Re: manage tablespace
Our first thought was - lets go with fewer, larger files to make thinks simpler. Oracle takes this idea to the extreme in 10g with "bigfile tablespaces", where you can have a max of 1 datafile per tablespace, and that one file can be as large as 128 terabytes(!).
Anyway, our next thought was - what will recovery time look like if we have to restore a big datafile from a backup. Based on that concern, we decided to increase a datafile up to a max of 10GB, then add a new datafile. A neighboring DBA team decided they were comfortable at 25GB per datafile.
I resize datafiles and add new datafiles every day on busy production systems. No problem with it whatsoever.
Side note - after you add a datafile it's good practice to immediately update your controlfile backup (alter system backup controlfile to trace;). That'll make your life easier if you get into a recovery situation before your next regular backup occurs.
Hope that helps
Bill
- Mark as New
- Bookmark
- Subscribe
- Mute
- Subscribe to RSS Feed
- Permalink
- Report Inappropriate Content
тАО02-09-2007 07:20 PM
тАО02-09-2007 07:20 PM
Re: manage tablespace
Also note that with ASM, DBAs no longer need to manage files and drives individually. Instead, disk groups can be created consisting of disks and their assigned files. Essentially, the combination of OMF and ASM eliminates the need for a DBA to specify the file name and location for the physical database files when creating a new database, as well as in other database operations - you simply identify the destination disk group, and oracle takes care of the rest.
ASM can also be extended to support other administrative procedures, including backup/recovery and disk management.
hope this helps too!
kind regards
yogeeraj