Category: Stopping a Job
-
Generating SQL to Create an External Table- External Tables
If you are currently working with SQL*Loader and want to convert to using external tables, you can use the SQL*Loader to generate the SQL required to create the external table, using the EXTERNAL_TABLE option. A small example will help demonstrate this process. Suppose you have the following table DDL: SQL> create table books (book_id number,…
-
Identifying Bottlenecks via OS Utilities – Automation and Troubleshooting
Normally after checking the database and details in the logs and still not finding an issue, it is good to bring in server admins, but you can first check for additional bottlenecks and problems with the server. With the Oracle environment, there is a tendency to assume that you have a dedicated machine for one…
-
SQL Plan Management – Automation and Troubleshooting
SQL execution plans come in the form of SQL plan baselines for comparing and testing for regressions in SQL statements. SQL plans are captured, and with 23c, plan changes are detected at parse time and validated if the plan has changed. If you think machine learning is taking away the DBA’s performance tuning job, you…
-
Using top – Automation and Troubleshooting
Another quick utility for identifying resource-intensive processes is the top command. Use this tool to quickly identify which processes are the highest consumers of resources on the server. By default, top will repeatedly refresh every three seconds with information regarding the most CPU-intensive processes. $ top And you can run it batch mode and send…
-
Database Troubleshooting- Automation and Troubleshooting
So far there have been scripts provided to help avoid issues with the database. Even in the brief discussion about Autonomous Database, self-repairing was discussed as there are tasks that can be done automatically to fix and have the database up and available. But there are things that might need to be investigated such as…
-
Viewing Job Details- Automation and Troubleshooting
To view details about how a job is configured, query the DBA_SCHEDULER_JOBS view. This query selects information for the RMAN_BACKUP job: SQL> select job_name ,last_start_date ,last_run_duration ,next_run_date ,repeat_interval from dba_scheduler_jobs where job_name=’RMAN_BACKUP’; Each time a job runs, a record of the job execution is logged in the data dictionary. To check the status of a…
-
External Tables with Oracle Cloud Database- External Tables
Cloud databases let us easily use files from cloud storage, and this is just a quick example of leveraging files in your cloud environment with your database. You don’t have to have a directory created to use external tables, but you need to know the URI for the file that is the namespace and tenancy…
-
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…
-
Using AWR – Automation and Troubleshooting
An AWR report is good for viewing the entire system’s performance and identifying the top resource-consuming SQL queries. Run the following script to generate an AWR report: SQL> @?/rdbms/admin/awrrpt You can run the AWR reports from the PDB or CDB, root container. The reports will be from PDB using AWR_PDB views in that PDB, or…
-
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…