In SQL Server, clustered indexes are common, and these help to
reorganize fragmented tables. Rebuilding clustered indexes in SQL Server
will place some locks and possibly some blocking on the index. Rebuilding
the clustered index also reorganizes a table. The performance of online
rebuilds has improved with newer versions of SQL Server.
Oracle can use clustered indexes, but it seems to be more common to
use nonclustered indexes. Oracle has an Automatic Segment Space
Management (ASSM) feature, which has improved with each version and
helps to reduce fragmentation during regular processing. As with SQL
Server, online rebuilds in newer versions of Oracle are more efficient.
With SQL Server, you can use DBCC commands to evaluate if an index
should be rebuilt. DBCC SHOWCONTIG shows fragmentation for tables, and
a table with a clustered index probably has the same fragmentations. Also,
the system table dm_db_index_physical_stats can return average
fragmentation for all of the indexes in the database. With Oracle, the
ANALYZE TABLE table_name VALIDATE STRUCTURE command
makes sure the index is in sync with the table. When CASCADE is used with
this command, information will be inserted into an index_stats table,
which you can use to evaluate if indexes need to be rebuilt.
Sqlplus> analyze table emp validate structure cascade;
Table analyzed.
sqlplus> select height, blocks, lf_rows, del_lf_rows,
btree_space, used_space
from index_stats where name='IDX_EMP1';
HEIGHT BLOCKS LF_ROWS del_lf_rows BTREE_SPACE USED_SPACE

1 8 14 3 8000 209
The index_stats table shows the height of the index. As a general
rule, an index with a height great than 4 might be considered for a rebuild.
Also look at the deleted leaf blocks (del_fl_rows) value. This amount
should be under 20 percent of the total leaf rows.

As noted earlier, Oracle supplies advisors to help assess maintenance
requirements. The Segment Advisor, part of the default maintenance jobs,
reports on reclaimable space. This could be the result of fragmentation in
the index or tables, or indicative of a bunch of deletions that have cleared
out old data.
Figure 7-3 shows some of the Segment Advisor recommendations about
chained rows, and it lists a couple of indexes that appear to have a BLOB
datatype. Due to the nature of this datatype (it can vary on the space it
consumes), chaining might be very typical here. In deciding on a course of
182
Oracle Database Administration for Microsoft SQL Server DBAs
action, you’ll need to consider that rebuilds for these datatypes are more
costly, and you might not be able to do them online.
You’ll need to weigh the performance and benefits gained by a rebuild
versus the actual cost of the maintenance in making your decision. If it
appears to be regular behavior of the index and table with many deletions
and insertions, and most of the space is able to be reused, that index might
not be at the top of the list to rebuild. It is also not as common to rebuild
b-tree indexes. Because of their structure, b-tree indexes tend to be self-
managing. Even with a lot of deletions, the space is generally reused by new
data being inserted, except if the primary key is on a sequence or date field.
Other types of indexes, such as clustered or bitmap, or those that have a
LOB datatype, might be considered for rebuilding. A coalesce of an index or
an online rebuild might be worth it.
Another possibility is for an index to be in an unusable state. This could
happen if a table was moved (rebuilding the indexes should always be done
after a move) or when direct loads are made into a table. Using SQL*Loader,
which is like using SQL Server’s bcp utility, for a direct load and bypassing
checking constraints could make a primary key index unusable. This can
also occur with partitioned tables, where the index is a global index across

all partitions, and one of the partitions was dropped to purge data, or
partitions were merged, which would be like reorganizing the partitions.
An unusable index will need to be repaired or rebuilt.
sqlplus> select owner, index_name, table_name from dba_indexes
where status="UNUSABLE';
simple fix for indexes listed
sqlplus> alter index index123 rebuild online;
Chapter 7: Database Maintenance
183
FIGURE 7-3.
Segment Advisor, chained row analysis
When an index becomes unusable, any queries against the table, unless
the parameter SKIP_UNUSABLE_INDEX are set to TRUE. In that case, Oracle
will not report an error on the indexes, and will allow selects, inserts, updates,
and deletes to occur against the table. (This parameter does not disable the
error messaging for unique indexes because of possible constraint violations.)
However, the queries will not be able to use the index, which might cause a
performance issue if this index is a key index. Although this will allow some
operations to continue, it’s better to rebuild the index and not have it in an
unusable state.
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.
In SQL Server, the DBCC SHOWCONTIG table_name command gives
clues as to whether a table needs to be reorganized. Also, rebuilding a
clustered index on the table will reorganize the table, which is a typical way
to handle table reorganization in SQL Server.

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. Figure 7-4 shows an example of
the Segment Advisor recommendations in OEM.
As with indexes, you’ll need to carefully consider the value of table
reorganization against its costs, especially with very large tables. In Figure 7-4,
the Segment Advisor is showing that 53.64MB can be reclaimed, which is
12.16 percent of the space. But regaining 50MB of space is probably not worth
reorganizing the table. Now, if this were 12 percent of 100GB, a reorganization
might be worthwhile.
If you decide to go ahead with a table reorganization, you can use OEM to
configure and schedule it. Under the Schema tab in the Database Objects area,
select Reorganize Objects, as shown in Figure 7-5. Here, the reorganization of
tables, indexes, schemas, and tablespaces can be set up in a job.
The options that are available in the following steps include doing the
rebuild online or offline, as shown in Figure 7-6. If the downtime is available,
184
Oracle Database Administration for Microsoft SQL Server DBAs
Chapter 7: Database Maintenance
185
FIGURE 7-5.
Reorganize Objects: Type
FIGURE 7-6.
Reorganize Objects: Options
FIGURE 7-4.
Segment Advisor recommendations
the table reorganization will run faster if the object does not need to be
available. Figure 7-6 also shows the option to perform the reorganization in
the current tablespace or another tablespace.

The job can then be scheduled to run immediately or at another time.
The final step has a summary of the commands that will be executed for this
process, as shown in Figure 7-7. You can review the script to better understand
the process.
Many tables can be reorganized by using the MOVE command to move
the table from one tablespace to another tablespace, or within the same
tablespace. There is also a DBMS_REDEFINITION package that will rebuild
tables for those with datatypes (LOB, RAW, and LONG RAW) that cannot
be handled by MOVE.
With some of the options to reorganize the table, space needs to be
available to temporarily house the rebuilt table.
## Reorganize the table in the same tablespace
sqlplus> alter table emp_info move;
can specify a tablespace to move to another
tablespace or keep it in the same one
186
Oracle Database Administration for Microsoft SQL Server DBAs
FIGURE 7-7.
Reorganize Objects: Review
dbms_redefinition package example
sqlplus> create table myschema.mytable_redef as
select * from myschema.mytable where 1=2;
sqlplus>exec dbms_redefinition.start_redef_table
('MYSCHEMA','MYTABLE','MYTABLE_REDEF');
sqlplus> exec dbms_redefinition.sync_interim_table
('MYSCHEMA','MYTABLE','MYTABLE_REDEF');
sqlplus> exec dbms_redefinition.finish_redef_table
('MYSCHEMA','MYTABLE','MYTABLE_REDEF');
Indexes will need to be re-created after the table reorganization, and the
old table will need to be dropped.

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, as shown in Figure 7-8.
With these alerts and a simple query against the dba_objects table, it
is easy to find the objects that are invalid.
sqlplus> select owner, object_name, object_type from dba_objects where
status='INVALID';
OWNER OBJECT_TYPE OBJECT_NAME

PROD_1 FUNCTION GET_ID_LIST
PROD_2 PACKAGE UPDATE_VAL1
PROD_1 VIEW ID_VW
Chapter 7: Database Maintenance
187
FIGURE 7-8.
Invalid object alerts in OEM
You can recompile invalid objects in a few ways:
■
Recompile all database objects that are invalid The utlrp.sql script,
in the ORACLE_HOME/rdbms/admin directory, will recompile all of
the objects in the whole database. You might consider running this
script after applying a patch or doing overall database maintenance.
You probably would not use this method to recompile one or two
procedures that might be invalid, and you would not run it during a
regular window of availability in the environment.
■

Recompile individual objects To recompile individual objects, you
can alter the object or use DBMS_DDL.ALTER_COMPILE. For day-
to-day maintenance, running a script to recompile individual objects
will be less disruptive to the database than recompiling all of them.
After the recompile, run a check to verify that the object was compiled
successfully.
■ Recompile objects at the schema level You can compile objects at
the schema level by using the DBMS_UTILITY package. If object
changes were applied to one schema, you can run a script to
recompile the objects just for that schema.
Here are examples of these options:
Recompile all database objects that are invalid
sqlplus> $ORACLE_HOME/rdbms/admin/utlrp;
Recompile objects at the schema level
sqlplus> exec DBMS_UTILITY.compile_schema(schema => 'MYSCHEMA');
Recompile individual objects
sqlplus> alter function prod_1.get_id_list compile;
sqlplus> exec DBMS_DDL.alter_compile('PACKAGE','PROD_2','UPDATE_VAL1');
Using a query to find the invalid objects, you can create a script to
recompile the object.
Sqlplus> select 'alter '|| object_type|| ' ' || owner || '.' ||
object_name || ' compile;' from dba_objects where status='INVALID';
188
Oracle Database Administration for Microsoft SQL Server DBAs
Grants
SQL Server has roles available to grant read-only or write permissions
against a database for the users of that database. If these roles are used,
individual grants on objects do not need to be maintained. However, you
can also grant individual permissions against an object. Whether using roles
or users for these permissions, knowing that these grants are present, or at

least making sure the access for the application is still available, is best
practice after doing maintenance.
Oracle does not have fixed roles for read-only or write permissions on a
schema; the roles need to be created with permissions granted. This does
not allow for granting permissions across the whole database, which
provides for separation of the schemas and isolation of permissions.
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. (The
name dba_tab_privs might be a little confusing, because it does contain
permissions on other objects besides tables, such as views, procedures,
packages, and functions.)
You can also maintain grants by auditing, which will let you know
which grants have been changed. This approach not only ensures that
access is maintained during an object change, but it also provides audit logs
of the roles and users who have permissions and any changes. This could
provide a needed compliance report.
To set up auditing on the grants, turn on audits for granting the
permissions, and set the parameter AUDIT_SYS_OPERATIONS = TRUE.
This parameter audits the actions of anyone connecting with SYSDBA or
SYSOPER permissions. With auditing enabled, the view dba_audit_
statement is available to see the grants issued or permissions revoked.
This provides good information about new grants, but not necessarily about
objects that were dropped and re-created without the grants. You also need
a table to capture which grants should be there, and not just what changed.
The auditing will require purging the audit tables, and the copy of the table
will need rows removed as grants are verified.
Here are a few quick examples of what can be done to maintain grants:
sqlplus> audit system grant;

Audit succeeded.
sqlplus> audit grant any object privilege by access;
Audit succeeded.
Chapter 7: Database Maintenance
189
sqlplus> audit grant any privilege by access;
Audit succeeded.
sqlplus> audit grant any role by access;
Audit succeeded.
Create table to manage the grants
sqlplus> grant insert, update, delete, select on emp to mmtest;
sqlplus> create table grants_expected as select * from dba_tab_privs ;
Table created.
sqlplus> revoke delete on emp from mmtest;
Revoke succeeded.
## check the table that has the saved grants and compare
## the grant is still listed in the table with the stored
## grants even though the privilege is no longer available
sqlplus> select grantee, owner, table_name, privilege
from grants_expected where (grantee,privilege,table_name, owner)
not in (select grantee,privilege, table_name, owner
from dba_tab_privs);
GRANTEE OWNER TABLE_NAME PRIVILEGE

MMTEST MMALCHER EMP DELETE
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.
A good practice is to create the synonym as the user accessing the

object, instead of as PUBLIC, which makes that name available to all users.
The specific permissions for the table still need to be granted to the users.
Once a public synonym is created, the same name cannot be used, even if it
is pointing to an object in a different schema.
In SQL Server, a default schema can be assigned so that the user is, in a
sense, accessing those schema objects by default; otherwise, the user needs
to fully qualify the object with dbo.table_name.
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.
The data dictionary view dba_synonyms shows synonyms. The
synonym name needs to be unique to the schema. If there are tables with
the same name in different schemas, they can receive different synonym
names, but at this point, it might be easier to fully qualify the table.
190
Oracle Database Administration for Microsoft SQL Server DBAs
As you’ve seen so far, object maintenance in Oracle has several pieces.
After database changes are rolled out, it’s important to verify there are no
invalid objects, and that grants and synonyms are still available. If you
rebuild indexes or reorganize tables, you will need to validate that the
indexes are still usable. Using alerts in OEM might be one way of verifying
these objects. You can also create jobs to run against the database, and use
the Oracle Scheduler to periodically run the scripts.
Job Scheduling
With SQL Server, the msdb database holds the information about jobs and
schedules, and the SQL Server Agent service must be running for the jobs to
be executed. It logs the information about the jobs and maintains a history
of successful runs and failed jobs with errors. The jobs also can be extracted

from SQL Server and created on another server.
In Oracle, the Oracle Scheduler handles job scheduling. PL/SQL and
Java procedures can be scheduled, as well as scripts outside the database,
such as shell scripts and executables. The Oracle Scheduler has an interface
in OEM. Using the DBMS_SCHEDULER package, you can schedule jobs
and get job information from the command line. The jobs are logged, and
since Oracle Database 10
g
R2, they can have multiple steps. The Oracle
Scheduler allows for using export and import to move the jobs from one
database to another. It also can take advantage of the high-availability
options since it is in the Oracle database. If the server failed, jobs can be
recovered, as with other database processes.
Table 7-4 shows a summary of job 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 11
g
). Selecting Jobs will show the current jobs scheduled against
the database, and jobs can be viewed, edited, executed, and created from
here.
Figure 7-9 shows an example of creating a job to rebuild an index. A job
is defined with a name and description. You can choose not to log the
running of the job, and to drop the job after completion. (Even if you are
creating a job to run just once, it might be a better idea to disable it, in case
you find that you need it again.)
Chapter 7: Database Maintenance
191