The challenge of managing data in Oracle Database 12c is one that provides you with options. In this section, you 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 you 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 is Automatic Storage Management.
Archive Logs
When you put the database in archive logging mode, the redo logs are written out to a directory that is named in the SPFILE. If that directory becomes full and the database attempts to write another archive log, 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. Recovery Manager (RMAN), a utility used for backup and recovery, does a nice job of helping you manage this.
Tablespaces and Data Files
Space should be managed at the data file and tablespace level rather than at an object level like a table or index. Using locally managed tablespaces with uniform extent sizes will simplify your management. Do not worry that you have multiple extents in a tablespace or for an object. This does not create a performance issue because the extents contain a number of blocks that must be contiguous, and the extents will be reused and created as needed. You can see the amount of space available in your data files or tablespaces in OEM, as shown in Figure 1. The figure shows the amount of free space available in the currently allocated space. Using the segment management will also reduce the need to set PCTFREE and PCTUSED when creating tables. If you have used the autoextend feature to allow a data file to extend in size when more space is needed, the extra space is not shown in this graph.
TIP
Be careful if autoextend is set for temporary and undo tablespaces because they will quickly grow to use all of the space. We recommended that you set a limit for these tablespaces to which they can autoextend.
What do you do if you run out of space in a data file? Just enter OEM Express: From the storage menu, you can drill down into the tablespace and data file. 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 data file so that it doesn’t grow until it uses 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 here:
You can write your own scripts to compare the amount of allocated space for a data file in DBA_DATA_FILES view to the amount of free space in DBA_FREE_SPACE view.
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, you’ll take a look at setting up and managing users. After all, without database users, there is no point in doing any of this!
Leave a Reply