Author: Jodean Baker
-
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…
-
Import a Table- External Tables
One of the key reasons to export data is so that you can re-create database objects. You may want to do this as part of a backup strategy or to replicate data to a different database. Data Pump import uses an export dump file as its input and re-creates database objects contained in the export…
-
Checking for Archivelog Destination Fullness- Automation and Troubleshooting
Sometimes DBAs and SAs do not adequately plan and implement a location for storing archivelog files on disk, or there is more growth and activity than expected. In these scenarios, it is sometimes convenient to have a script that checks for space in the primary location and that sends out warnings before the archivelog destination…
-
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…
-
Specifying a Query- External Tables
You can use the QUERY parameter to instruct Data Pump to write to a dump file only rows that meet a certain criterion. You may want to do this if you’re re-creating a test environment and only need subsets of the data. Keep in mind that this technique is unaware of any foreign key constraints…
-
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…
-
Taking an Export- External Tables
A small amount of setup is required when you run a Data Pump export job. Here are the steps: 1. Create a database directory object that points to an OS directory that you want to write/read Data Pump files to/from. (We used the directory object with our external tables.) 2. Grant read and write privileges…
-
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…