It can be quite useful to be able to monitor long running operations on your database, the view v$session_longops can be used to estimate the time necessary for certain operations to finish. Continue reading Monitoring long running operations
Moving objects from one tablespace to another
This script will help you to move all objects from one tablespace to another, note that you will need downtime to move tables to another tablespace with this script, the operation can be done online using the dbms_redefinition package thought.
Continue reading Moving objects from one tablespace to another
Database maintenance tasks
The default maintenance window is not a good fit for every database, by default the maintenance window start at 10 PM and run for 4 hours during the week and start at 6 AM and last for 20 hours during the week-end. A different window exists for each day of the week and all the windows are grouped inside the MAINTENANCE_WINDOW_GROUP. It is possible to alter each window independently or to create a single window for all days of the week.
The following maintenance tasks are performed during the maintenance window:
- Optimizer statistics (OPTIMIZER_STATS)
- Segment advisor (SEGMENT_ADVISOR)
- SQL Tuning (SQL_TUNE_ADVISOR)
- Health Monitor (HEALTH_MONITOR)
And the default window group named MAINTENANCE_WINDOW_GROUP is associated with a DEFAULT_MAINTENANCE_PLAN which has the following configuration:
| Consumer Group/subplan | Level 1 | Maximum Utilization Limit |
|---|---|---|
ORA$AUTOTASK |
5% | 90 |
OTHER_GROUPS |
20% | – |
SYS_GROUP |
75% | – |
In this plan 75% of the resources are affected to sessions created by SYS or SYSTEM user. If these resources are not used by the SYS_GROUP then they are transfered to the other groups. As you can see there is also a restriction for the automatic maintenance tasks, they cannot use more that 90% of the resources.
here is what you is configured by default, independently of the management pack configured and the edition you use. This configuration is from a standard edition database with control_management_pack_access set to NONE.
SQL> select * from DBA_AUTOTASK_WINDOW_CLIENTS; WINDOW_NAME WINDOW_NEXT_TIME WINDO AUTOTASK OPTIMIZE SEGMENT_ SQL_TUNE HEALTH_M -------------------- -------------------------------------------------- ----- -------- -------- -------- -------- -------- SUNDAY_WINDOW 27-MAR-16 06.00.00.000000 AM EST5EDT FALSE ENABLED ENABLED ENABLED ENABLED DISABLED SATURDAY_WINDOW 26-MAR-16 06.00.00.000000 AM EST5EDT FALSE ENABLED ENABLED ENABLED ENABLED DISABLED FRIDAY_WINDOW 25-MAR-16 10.00.00.000000 PM EST5EDT FALSE ENABLED ENABLED ENABLED ENABLED DISABLED THURSDAY_WINDOW 24-MAR-16 10.00.00.000000 PM EST5EDT FALSE ENABLED ENABLED ENABLED ENABLED DISABLED WEDNESDAY_WINDOW 23-MAR-16 10.00.00.000000 PM EST5EDT FALSE ENABLED ENABLED ENABLED ENABLED DISABLED TUESDAY_WINDOW 29-MAR-16 10.00.00.000000 PM EST5EDT FALSE ENABLED ENABLED ENABLED ENABLED DISABLED MONDAY_WINDOW 28-MAR-16 10.00.00.000000 PM EST5EDT FALSE ENABLED ENABLED ENABLED ENABLED DISABLED