Category: Tablespace Level
-
Identifying System Bottlenecks – Automation and Troubleshooting
Whenever there are application performance issues or availability problems, seemingly (from the DBA’s perspective), the first question asked is, “What is wrong with the database?” Regardless of the source of the problem, the DBA is often required to look if the database is behaving well. Approaching these issues when looking at the database and system-wide…
-
Loading a Regular Table from the External Table- External Tables
Now, you can load data contained in the external table into a regular database table. When you do this, you can take advantage of Oracle’s direct-path loading and parallel features. This example creates a regular database table that will be loaded with data from the external table: SQL> create table exa_info( exa_id NUMBER ,machine_count NUMBER…
-
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…
-
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…
-
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…