Category: SQL Plan Management
-
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,…
-
Checking Database Availability- Automation and Troubleshooting
The first few checks do not require logging in to the database server. Rather, they can be performed remotely via SQL*Plus or SQLcl and OS commands. Performing the initial checks remotely over the network establishes whether all the system components are working. One quick check to determine whether the remote server is available, the database…
-
Export and Import an Entire Database- External Tables
When you export an entire database, this is referred to as a full export, and the resultant export file contains everything required to make a copy of your database. Unless restricted by filtering parameters, a full export consists of the following: • All DDL required to re-create tablespaces, users, user tables, indexes, constraints, triggers, sequences,…
-
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…
-
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…
-
Tablespace Level- External Tables
A tablespace-level export/import operates on objects contained within specific tablespaces. This example exports all objects contained in the USERS tablespace: $ expdp mv_maint/Pa$$w0rd123! directory=dp_dir dumpfile=tbsp.dmp tablespaces=users You can initiate a tablespace-level import by using a full export but specifying the TABLESPACES parameter: $ impdp mv_maint/Pa$$w0rd123! directory=dp_dir dumpfile=full.dmp tablespaces=users A tablespace-level import will attempt to create…
-
Locating the Alert Log and Trace Files – Automation and Troubleshooting
The default alert log directory path has this structure: $ORACLE_HOME/rdbms/log Or find it easily with the show parameter command: SQL> show parameter background You can override the default directory path for the alert log by setting the DIAGNOSTIC_DEST initialization parameter. Usually, the db_unique_name is the same as the instance_name, but it depends on the environment.…
-
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…
-
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…