Category: Orace Certification
-
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,…
-
Autonomous Database- Automation and Troubleshooting
The previous scripts and tasks when scheduled are just scratching the surface of automating jobs for the Oracle database. Processes that start to take the results of these scripts and apply the fixes and perform the next actions are getting closer to automation. Oracle 23c database has many processes and hooks that allow for environments…
-
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…
-
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…
-
Verifying the Integrity of RMAN Backups- Automation and Troubleshooting
As part of your backup-and-recovery strategy, you should periodically validate the integrity of the backup files. This is also included as part of using RMAN to back up the database, but a separate job can run against them to validate for restore. RMAN provides a RESTORE…VALIDATE command that checks for physical corruption within the backup…
-
Automating Jobs with Oracle Scheduler- Automation and Troubleshooting
Oracle Scheduler is a tool that provides a way of automating the scheduling of jobs. Oracle Scheduler is implemented via the DBMS_SCHEDULER internal PL/SQL package. Oracle Scheduler offers a sophisticated set of features for scheduling jobs, such as detailed scheduling, privileged-based models, and storing of schedules. There are more than 70 procedures and functions available…
-
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…
-
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,…
-
List Contents of Dump Files- External Tables
Data Pump has a robust method of creating a file that contains all the SQL that is executed when an import job runs. Data Pump uses the DBMS_METADATA package to create the DDL that you can use to re-create objects in the Data Pump dump file. Use the SQLFILE option of Data Pump import to…
-
Inline SQL from External Table- External Tables
It is possible to select directly from the file with the use of EXTERNAL without actually creating an external table in the data dictionary. This allows for external data to be part of a subquery, virtual view, or another transformation type of process. Here is an example of how this works: SELECT columns FROM EXTERNAL…