Category: Autonomous Database
-
Stopping a Job- Automation and Troubleshooting
If you have a job that has been running for an abnormally long period of time, you may want to abort it. Use the STOP_JOB procedure to stop a currently running job. This example stops the RMAN_BACKUP job while it is running: SQL> exec dbms_scheduler.stop_job(job_name=>’rman_backup’); The STATUS column of DBA_SCHEDULER_JOB_LOG will show STOPPED for jobs…
-
Running Oracle Diagnostic Utilities – Automation and Troubleshooting
Oracle provides several utilities for diagnosing database performance issues: • Automatic workload repository (AWR) • Automatic database diagnostic monitor (ADDM) • Active session history (ASH) • Statspack AWR, ADDM, and ASH tools provide advance reporting capabilities that allow you to troubleshoot and resolve performance issues and are available through Diagnostics and Tuning Pack as an…
-
Oracle Data Pump- External Tables
We discussed external tables using SQL*Loader, but let’s take a little bit of time to look at Data Pump as well. This is a utility to unload and load data into the database. It can also provide you with a quick backup, replicate, and secure copy of your data and metadata. You can use Data…
-
Finding Resource-Intensive SQL Statements – Automation and Troubleshooting
One of the best ways to isolate a poorly performing query is to have a user or developer complain about a specific SQL statement. In this situation, there is no detective work involved. You can directly pinpoint the SQL query that is in need of tuning. However, you do not often have the luxury of…
-
Deleting a Job- Automation and Troubleshooting
If you no longer require a job, you should delete it from the scheduler. Use the DOP_JOB procedure to permanently remove a job. This example removes the RMAN_BACKUP job: SQL> begin dbms_scheduler.drop_job(job_name=>’rman_backup’); end; / The code will drop the job and remove any information regarding the dropped job from the DBA_SCHEDULER_JOBS view. Examples of Automated…
-
Starting and Stopping the Database and Listener- Automation and Troubleshooting
If a database server is to reboot or restart, it is desirable to have the Oracle databases and listener automatically restart with the server. This process used to be part of a parameter in the /etc/oratab file for the database to automatically restart. It would be called in the dbstart and dbshut commands and was…