All UxOra articles related to Oracle database 11g/12c and SQL/PLSQL will be published under this category.
DBMS_STATS.GATHER_TABLE_STATS collects statistics on a partitioned table, generally it does so at the partition and table (global) level (the default behavior can be modified by changing the
GRANULARITY parameter). This is done in two steps:
- First, partition level stats are gathered by scanning the partition(s) that have stale or empty stats,
- Then a full table scan is executed to gather the global statistics.
As more partitions are added to a given table, the longer the execution time for
GATHER_TABLE_STATS, due to the full table scan requited for global stats.
The process of turning redo log files into archived redo log files is called archiving. It is only possible if the database is running in
ARCHIVELOG mode. Here are some advantages to be in
- Recover a database
- Update a standby database
- Perform a hot backup
In developpement, it can happen that you may want to shrink oracle datafiles after a huge delete (ie. drop user, drop big table, ...).
Here is a simple way to shrink datafiles using
RESIZE option, but it may not be the most efficient.