Flat 10% & upto 50% off + 10% Cashback + Free additional Courses. Hurry up


3.1 Maintenance Tasks


general maintenance tasks in sql server and oracle


3.2 Consistency Checks

Consistency checks validate database blocks and look for corruption in the datafiles. Consistency checks look at the physical integrity of the data blocks and rows of objects. They can also validate the structures of objects, and that the tables and indexes still have the corresponding values.

Oracle checks for block corruption as the database writers are handling the blocks of data. The DB_BLOCK_CHECKSUM parameter determines if blocks will be checked in memory. The TYPICAL setting for this parameter (the default) verifies checksums before writing to disk. With more data movement possibly happening in memory, detecting the corruption here before even writing to disk can be useful.

To have Oracle check the blocks in memory (the buffer cache), set DB_BLOCK_CHECKSUM to FULL. This setting will perform checksums on all changes before and after writing to the log. This does add overhead to the system, but FULL is the only setting that will check for block corruption in the buffer cache. This parameter is dynamic, so it can be altered to check on its effects in your environment.


3.3 Health Checks


health checks in sql server and oracle


3.4 Update Statistics

 In both SQL Server and Oracle, statistics are updated by default. In SQL Server, the AUTO_UPDATE_STATISTICS database option, when turned on, will update the statistics when they become stale. You can also run updates manually, using sp_updatestats or UPDATE STATISTICS.

In Oracle, the parameter STATISTICS_LEVEL set to TYPICAL or ALL enables automatic statistics gathering. In Oracle Database 10g, the GATHER_STATS_JOB job is scheduled to gather stale statistics and keep them updated.


  • System Statistics

The gathered statistics information is used by the cost-based optimizer to create query plans. Capturing statistics at different times for various activities is especially useful when the workload on the database is different, such as batch processing or reporting at night and processing transactions during the day.

sqlplus> exec dbms_stats.gather_system_stats(‘Start’);

— gather for an hour during peak activity

sqlplus> exec dbms_stats_gather_system_stats(‘Stop’);

You can also capture system statistics on the fixed data dictionary tables, which should be done during regular workload and run once.


  • Object Statistics


update statistics procedures in sql server and oracle


3.5 Object Maintenance

 Along with gathering statistics information about the objects, some maintenance and checks need to be done on the objects themselves. There might be fragmentation, so that the object needs to be rebuilt. Invalid objects might need to be recompiled. Even grants and permissions can be considered part of object maintenance.


  • Index Rebuild

In examining the database objects, you may see some that appear fragmented and in need of a rebuild. Such rebuilds increase log activity, put additional resources on the system, and may put locks on the object. Therefore, you should be selective and plan which indexes to include in the tasks. You can generate reports to plan maintenance on indexes for another time, if necessary.


  • Table Reorganization

 Like indexes, tables can become fragmented, due to chained rows, changes by updates, and deletions that leave space available that is not being reused. In some cases, these tables can benefit from reorganization. For example, a table might need to be reorganized after doing some data cleanup, or if monitoring shows free space can be reclaimed.

Oracle’s ASSM feature manages the space within a segment. Allowing Oracle to manage the space in segments for tables reduces the fragmentation of the table. The Segment Advisor again comes into play with tables, checking for chained rows and space that can be freed up.


  • Invalid Objects

Objects such as procedures, functions, and views can become invalid if a dependent object is altered. Normally, the object will recompile the next time the procedure is executed or the view is accessed, as long as there are no errors in the code. However, making sure that the objects are valid should be included in a maintenance plan. Alerts will pop up in OEM about invalid objects in a schema.

You can recompile invalid objects in a few ways:

  • Recompile all database objects that are invalid
  • Recompile objects at the schema level
  • Recompile individual objects


  • Grants

The dba_tab_privs and dba_col_privs views show the current grants that have been added to either a role or a user. One way to maintain grants is to have a copy of the grants that have been granted in a table and compare that information against the current dba_tab_privs view.


  • Synonyms

Users other than the schema owner may need access to a particular table or view, which requires them to fully qualify the object with schema_name.object_name. Alternatively, a synonym can be created for that object.

In Oracle, when tables are altered, the synonyms created on the object are not changed and remain in place. However, if an object is dropped, the synonym will become invalid, and when the object is re-created, the synonym might need to be recompiled. The object will appear with INVALID as the status in the dba_objects table.


3.6 Job Scheduling

scheduling in sql server and oracle

  • Creating a Job in Oracle Scheduler

The Oracle Scheduler is available from the Server tab in OEM (Oracle Database 11g). Selecting Jobs will show the current jobs scheduled against the database, and jobs can be viewed, edited, executed, and created from here.


creating a job in oem

For the command type, you have the following options:

■ Program name

■ PL/SQL (enter the code in the text box)

■ Stored procedure

■ Executable

■ Chain (to create steps and chain the jobs together)



You can also create jobs with the DBMS_SCHEDULER package. It takes parameters for job name, description, and action. You can set up a one-time job or a repeat interval, which can be by time, days, weeks, and so on. The start date could be the current time for immediate execution or a future date.


  • File Maintenance

Datafiles, log files, error logs, history logs, trace files—oh my! File maintenance is very important to the health of the database and maintaining a stable environment. Developing tasks and scripts will be useful for managing the many different files.


  • Shrinking and Resizing Files

In Oracle, the logs are sized and remain that same size, so shrinking the file is not the issue.

However, depending on how many times the transactions are looping through the redo logs, there might be a reason to adjust the size of the logs.

Datafiles are slightly different, because they are normally growing. But you might need to clean up data or start an archive process that frees up the space.


  1. Logs

If you resize the redo logs, you can create new groups with the bigger size. Then, as the older redo logs become inactive, they can be dropped. All of the redo log groups should have the same size set for the redo logs.


  1. Datafiles

To resize a datafile (to be either smaller or larger than its current size), use the ALTER DATABASE DATAFILE command.


  • Tablespace Monitoring

Oracle tablespaces are created with one or more datafiles. As the database grows, the tablespaces and datafiles need to be maintained to allow for the growth. Planning the size of the system tablespaces is recommended.

Oracle datafiles are set to a fixed size or to autoextend. You can monitor space at the tablespace level in OEM. From the Server tab, under the Storage category, choose Tablespaces to see a list of tablespaces.

The free space available threshold can be a specific amount or a percentage. The actual size of free space is useful for very large tablespaces. For example, 20 percent free of a 10GB datafile and 20 percent free of a 2TB datafile may have very different levels of urgency. The percent of allocated space amount does not take into account autoextend for the datafiles.


tablespace listing in oem


Error Logs, Alert Logs, and Trace Files

Oracle alert logs contain information about the status of the database and error messages. The alert log errors are on the first page of OEM. When the instance is restarted, the alert log is not cycled to the next log; writing continues to the current log.

The directories as set by the parameters background_dump_dest, user_dump_dest, and background_core_dump contain trace files and log files. In Oracle 11g there is also the diagnostic_dest which can be single directory for the trace and log files. If there is enough space allocated to the server to have unlimited files here, not much maintenance needs to be done. Otherwise, these directories should be purged by days.

Using operating system commands, you can find the files in the dump destinations, and the mtime sets how many days the files should be retained.

find “/u01/oracle/product/11.2.0/db_1/admin/orcl/udump”

-name “*.trc” -type f -mtime +15 -exec rm -f {} \;

find “/u01/oracle/product/11.2.0/db_1/admin/orcl/bdump”

-name “*.trc” -type f -mtime +15 -exec rm -f {} \;

find “/u01/oracle/product/11.2.0/db_1/admin/orcl/cdump”

-name “*.trc” -type f -mtime +15 -exec rm -f {} \;

 You should clean up the older alert logs as well. The command to do so might include the date or *.log, depending how the alert log is named when rotating logs.

Previous Next

Download Interview Questions asked by top MNCs in 2019?

"0 Responses on Database Maintenance"

    100% Secure Payments. All major credit & debit cards accepted Or Pay by Paypal.

    Sales Offer

    Sign Up or Login to view the Free Database Maintenance.