Manage Space - Oracle

 

CRITICAL SKILL 3.8 --  Manage Space

The challenge of managing data in your Oracle Database 10g is one that provides you with options. In this section, we will look at the methods that have been used in the many versions of the database to manage your information. Today’s version of the database provides us with options. The first that we will discuss is managing your data and the files in which they reside in a manual way. Another option, automatic storage management, is discussed in Chapter 9.

Archive Logs

When you put the database in archive logging mode, the redo logs are written out to a directory that is named in your parameter or SPFILE. If that directory becomes full and the database attempts to write another archive log, the database activity will be suspended until sufficient space is made available for the new file. Create a large directory and schedule jobs to move the archive log files from online storage to tape before you encounter a space issue. RMAN does a nice job of helping you manage this. Please see Chapter 5 for more information on this.

Tablespaces and Datafiles

Space should be managed at the datafile and tablespace level rather than at a lower level such as a table or index. Using locally managed tablespaces with uniform extent sizes will simplify your management. Do not worry that you have some extents in a tablespace or for an object. This does not create a performance issue since the extents contain a number of blocks that must be contiguous. You can see the amount of space available in your datafiles by selecting Datafiles in OEM, as in Figure 3-8. This shows the amount of free space available in the currently allocated space. If you have used the autoextend feature to allow a datafile to extend in size when more space is needed, the extra space is not shown in this graph. Do not allow temporary tablespaces or undo tablespaces to autoextend since they will grow to use all of the space.

TIP Do not autoextend temporary and undo tablespaces, since they will quickly grow to use all of the space to which they can autoextend.

What do you do if you run out of space in a datafile? Just enter OEM, click the datafile, and choose the Storage tab. Once there, you can change the autoextend feature and enter the size of the extensions that you would like. Do not forget to limit the size of the datafile so that it does not grow to use all of your space. After you’ve completed this, click Apply and you’re done. If you select the Show SQL button, you can see the alter database syntax, which is also shown next.

alter database datafile ‘/u01/oradata/ora10g/example01.dbf’
autoextend on next 50M maxsize 5000M;

Figure 3-8. Tablespace view in Enterprise Manager

You can write your own scripts to compare the amount of allocated space for a datafile in view dba_data_files to the amount of free space, as shown in view dba_free_space.

OEM also provides you with a more detailed map of how space is used. In OEM, select a tablespace and then navigate from Tools to Tuning Features, finally choosing Tablespace Map. This opens a graphical layout showing each segment in the tablespace. From the tablespace map, you can choose the Tablespace Analysis Report tab for a written report on the space being used.

Managing the database objects discussed earlier will be a large part of your role as a DBA. In the next section, let’s take a look at setting up and managing users. After all, without database users, there is no point in doing any of this! 

Progress Check: 

1. What’s better: “shutdown transactional” or “shutdown immediate”?

2. Do you only need to worry about logical schema objects that do not take up a large amount of space?

3. What happens if your archive log directory becomes full?

4. Why would you want to use a command-line interface rather than a GUI to perform your tasks as a DBA?

Progress Check Answers: 

1. Both leave your database in a consistent state. It depends on how long your transactions will take to complete or roll back. If all things are equal and you think that it will take as long to commit the transactions that are already running, then you should use “shutdown transactional” since commits will be allowed to complete and no data will be lost.

2. Logical schema objects need to be watched to ensure they are in a valid state.

3. If database attempts to write an archive log after the directory has become full, the database activity will be suspended until sufficient space is made available for the new file.

4. You may want to place the command in a script that is scheduled or run as a repetitive task.

This chapter is from Oracle Database 10g: A Beginner's Guide, by Abramson, Abbey and Corey. (McGraw-Hill/Osborne, 2004, ISBN: 0072230789). Check it out at your favorite bookstore today. Buy this book now.

>>> More Oracle Articles          >>> More By McGraw-Hill/Osborne

blog comments powered by Disqus